Posts by Delta-101

Delta-101 · May 16th 2007

Re: Macro Stops After Workbooks.Open

Yep, in hind site, I see that, so it won't solve the problem! Sorry about that!

Delta-101 · May 16th 2007

Re: Macro Stops After Workbooks.Open

David,
In your posted code it appears you have 2 spaces between [COLOR="Blue"]Press Analysis[/COLOR]

It could be just the way the Forum shows it, but if not ...

Dave

Delta-101 · May 16th 2007

Re: Macro Stops After Workbooks.Open

deleted. See next.

Delta-101 · May 15th 2007

Re: Find Cells Contaning Text Not Numbers

Many thanks Guys,

Reafidy, using the find BOLD seems to work fine, I will have to test that on the proper data and see what the results are like - but I think it will work!

Jindon, thanks for your input, I will have to tweak that a bit to see if it can give me the result I require, as is it does not allow me to extract each group on its own, but rather groups all the data together, But an interesting bit of code - and thanks to Reafidy for the typo correction.

..

Delta-101 · May 15th 2007

Re: Find Cells Contaning Text Not Numbers. (vba)

Yes, I could do that, but was attemting to avoid [COLOR="Blue"]looping[/COLOR] through thousands of cells to keep the code a little faster. ( I understand [COLOR="RoyalBlue"]find[/COLOR] is a lot faster than looping)
For each name, there could be potentially several thousand cells with the numeric data

Delta-101 · May 15th 2007

Hi,
I need to find cells containing text (Alpha characters only) in a single column, an return the row(+1 then -1). The column contains Group names (Unknown) , and under each name follows numeric data until the next group name. The cells are not formatted other than BOLD names. I need to exctract the numeric data in groups.

EG.
----
NameA
324
234
65
NameB
345
67
Footer
----
etc.
I have manged using the following code, but this always assumes there is an "e" in the name, but would prefer a more foolproof method. (I used "e" cos it is ok so far, BUT this could change in the future)

Code

Sc = "A"
Sr = "1"
With Worksheets(1).Range(Sc & Sr & ":A500")
Set c = .Find("e", LookIn:=xlValues)
If Not c Is Nothing Then
Fr = c.Row + 1
Set c = .Find("e", After:=Range(Sc & Fr), LookIn:=xlValues)
If Not c Is Nothing Then
Sr = c.Row - 1
If c = "Footer" Then NoMoreNames = True
End If
End If
End With
' Loops back after processing this batch

Display More

Any good ideas?

Delta-101 · May 11th 2007

Re: Copying Filtered Data

Tried it here, it sems to only copy visible data, but you could try this?

Code

For Each rngData In Range("E4:CR258").Columns
rngData.AutoFilter Field:=1, Criteria1:="<>"
rngdata.SpecialCells(xlCellTypeVisible).Copy
rngData.EntireColumn.Cells(263, 1).PasteSpecial xlPasteValues
rngData.AutoFilter
Next

Delta-101 · May 11th 2007

Re: Find Text Based On Active Cell Contents

Hi,

I am not an expert, but here is my solution:

VBA:

Code

Sub Find_Selected()
cellval = ActiveCell.Value
With Worksheets("Sheet2").Range("a1:d6")
Set c = .Find(cellval, LookIn:=xlValues)
If Not c Is Nothing Then
foundcell = c.Address
' do what you need to do
End If
End With
End Sub

Display More

'assign macro button to "Find_Selected" & change the range values and sheet name to suit,
'foundcell returns cell address where value is found on sheet2