Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop down rows to first numerical character
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop down rows to first numerical character
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. "Michael McClellan" wrote: 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop down rows to first numerical character
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" wrote in message m... 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop down rows to first numerical character
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 a 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? "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... 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" wrote in message m... 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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop down rows to first numerical character
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. -- Regards, Tom Ogilvy "Michael McClellan" wrote in message om... 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 a 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? "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... 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" wrote in message m... 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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop down rows to first non-bold entry
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" <deitbe @ consumer dot org wrote in message ... 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" wrote in message m... 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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop down rows to first non-bold entry
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 "Michael McClellan" wrote in message m... 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" <deitbe @ consumer dot org wrote in message ... 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" wrote in message m... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Automatically put rows containing a number in numerical order | Excel Discussion (Misc queries) | |||
How to change rows, columns to alpha numerical | Excel Discussion (Misc queries) | |||
How to enter numerical numbers in rows where value is same | Excel Worksheet Functions | |||
Numerical grade to Alpha character | Excel Discussion (Misc queries) | |||
How to randomely distribute non-numerical values across rows? | Excel Worksheet Functions |