Loop down rows to first numerical character

[Michael McClellan]

I'm looking to build a macro that will loop down rows and then stop
and the first numerical character, modify some adjacent cells, then
move on to the next numerical character.

Does anybody have a clue?

 

[Guest]

Start at the top of the row you want to examin
Sub Macro1()
Do Until ActiveCell.Value = ""
If IsNumeric(ActiveCell.Value) Then
'Do this that or the other
ActiveCell.Offset(1, 0).Range("A1").Select
Else
ActiveCell.Offset(1, 0).Range("A1").Select
End If
Loop
End Sub
Hope it helps.

 

[Bernie Deitrick]

Michael,

You can use the special cells method: the example below will put "This is a
number" into the adjacent cell in column D for every number in column C.

Note that the "xlCellTypeConstant" means that the numbers are actual
constants, not the values returned by formulas, which would require
"xlCellTypeFormulas" instead.

HTH,
Bernie
MS Excel MVP


Sub Example()
Dim myCell As Range
For Each myCell In Columns("C:C").SpecialCells(xlCellTypeConstants, 1)
myCell(1, 2).Value = "This is a number"
Next myCell
End Sub

 

[Michael McClellan]

OK, I can tell you are on the right track Bernie. I should have been
clear in stating that there is a column where there are text values
and numerical values and I need to loop down the rows and select the
numerical values and extract some data from that particular row then
paste in the another workbook.

My questions now are:

1. What does the Each statement do?
2. How will your code know when to stop looping?
3. The mycell(1,2). Is this some sort of implicit offset?

 

[Tom Ogilvy]

1. What does the Each statement do?
Loops through all the cells in column C that contain numbers (stored
as numbers)
2. How will your code know when to stop looping?
It goes through each cell once, so when all have been processed, it is
done - implicit capability of for each construct
3. The mycell(1,2). Is this some sort of implicit offset?
Mycell.Item(1,2) meaning same row, one column to the right.
Mycell.Item(1,1) is the same as Mycell.

 

[Michael McClellan]

OK Gentlemen,

I think I need to change my strategy a bit. I actually need to loop
down to the first non-bold entry. All the data I don't need is bold
for some reason.

Any ideas?

 

[Bernie Deitrick]

Michael,

Sub Example2()
Dim myCell As Range
For Each myCell In Columns("C:C").SpecialCells(xlCellTypeConstants, 1)
If myCell.Font.Bold Then myCell(1, 2).Value = "This is a bold number"
Next myCell
End Sub

HTH,
Bernie
MS Excel MVP