Finding Last Row in Column

[EKinATX]

Hello,

I'm new to VBA and I'm having a little difficulty with a loop that is running to the end of worksheet instead of stopping at the last row in the column.

The second row of the code here seems to be the culprit. Any help is appreciated. Thanks.





Sub LTM()
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
For x = 1 To FinalRow
If IsNumeric(ActiveCell.Value) = True Then
ActiveCell.Offset(0, 22).Range("A1").Select
ActiveCell.FormulaR1C1 = _
"=INDEX('NS Tie Out'!R1C1:R120C31,MATCH(Legacy!RC[-22],'NS Tie Out'!C1,0),MATCH(Legacy!R4C3,'NS Tie Out'!R7,0))"
ActiveCell.Offset(0, -22).Range("A1").Select
Selection.End(xlDown).Select
Else
Selection.End(xlDown).Select
End If
Next x
End Sub

 

[NeonRedSharpie]

You never actually use your for loop. I don't see a mention of 'x' anywhere in the vba code. The formula to figure out the FinalRow is correct, so long as column A is populated the same as your table.

 

[EKinATX]

Thanks for your reply!

After the "For x" Line, should I enter something like:

ActiveCell.Row(x,1)Select

Thanks

 

[NeonRedSharpie]

Can you explain, in simple words, what the code is trying to do? How I understand it:

Loop through all populated cells
Check to see if column A is numeric
If it is, add that formula into it.
Check the next cell.

 

[EKinATX]

Column A has account numbers that are in random rows. For example, Column A Row 31, 46, 55, 57... through several hundred rows. I'm trying to do a lookup on these account numbers so that it returns a value corresponding to the account number(from another sheet) in a column to the right. I'm starting at the top of column A, and wanting the macro to go down the rows until it has reaches the last account number and stop.

Does that make sense. Thanks!

 

[NeonRedSharpie]

Sub LTM()    Dim FinalRow As Long


    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    For x = 1 To FinalRow
        If IsNumeric(Cells(x, 1)) = True Then
            Cells(x, 1).FormulaR1C1 = _
                "=INDEX('NS Tie Out'!R1C1:R120C31,MATCH(Legacy!RC[-22],'NS Tie Out'!C1,0)," & _
                    "MATCH(Legacy!R4C3,'NS Tie Out'!R7,0))"
        End If
    Next x
                    
End Sub

Try this. I don't know if it will do exactly what you want but it should.

 

[EKinATX]

It gets hung up on the first Row when I try to run. Will have to look into it later because something came up at work and I have to take my attention off of this for a little while.

Thanks for the help.