VB function to scan rows of a column

[pban92]

<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> <w:UseFELayout/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0cm 5.4pt 0cm 5.4pt; mso-para-margin:0cm; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-fareast-font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> I need to make a macro which can start scanning from (R1C1) until it reaches the empty R[x]C[1] and performs the action: R[x]C[1] = R[x-1]C[1] + 1.
Cant find the function to do this. So any help would be appreciated. Regards!

 

[texasalynn]

does this work?

Sub AddNewRow()
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row + 1
Cells(LR, 1) = Cells(LR - 1, 1) + 1
End Sub

 

[pban92]

Works like a dream! \m/

 

[pban92]

Hi texasalynn, had to come back again!
In case of multiple columns for similar actions with different number of rows, I am not sure what to modify. As a test, I tried with two columns but severely failed.

Sub AddNewRow()

Dim c1, c2 As Long

c1 = Range("A" & Rows.Count).End(xlUp).Row + 1
Cells(c1, 1) = Cells(c1 - 1, 1) + 1

c2 = Range("B" & Rows.Count).End(xlUp).Row + 1
Cells(c2, 1) = Cells(c2 - 1, 1) + 1
     
End Sub

 

[pban92]

Yet another failed attempt with two columns!

Sub AddNewRow()

Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row + 1
Cells(LR, 1) = Cells(LR - 1, 1) + 1
      
Dim COL1 As Long
COL1 = Range("B" & Rows.Count).End(xlUp).Row + 1
Cells(COL1, 1) = Cells(COL1 - 1, 1) + 1
            
End Sub

 

[texasalynn]

Hi texasalynn, had to come back again!
In case of multiple columns for similar actions with different number of rows, I am not sure what to modify. As a test, I tried with two columns but severely failed.

Sub AddNewRow()
 
Dim c1, c2 As Long
 
c1 = Range("A" & Rows.Count).End(xlUp).Row + 1
Cells(c1, 1) = Cells(c1 - 1, 1) + 1
 
c2 = Range("B" & Rows.Count).End(xlUp).Row + 1
Cells(c2, 1) = Cells(c2 - 1, 1) + 1
 
End Sub

c2 = Range("B" & Rows.Count).End(xlUp).Row + 1
Cells(c2, "B") = Cells(c2 - 1, "B") + 1