loop with lastrow

[BradMS]

How would I change a5000 to last row?

Sub PosX()




Dim r As Range
Dim i As Long
Dim LastRow As Long


Set r = Range("A1:A5000")
For i = r.Rows.Count To 1 Step -1
With r.Cells(i, 1)
If .Value = "POSNX" Then

Range("A" & i + 0).Range("$h$1").FormulaR1C1 = _
"=RC[-4]"



'With ActiveWorkbook.ActiveSheet
' LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
' .Range("A11:AD" & LastRow).Select
'End With


End If
End With
Next i
End Sub

 

[Fluff]

How about

Sub PosX()
   Dim r As Range
   
   For Each r In Range("A1", Range("A" & Rows.Count).End(xlUp))
      If r.Value = "POSNX" Then r.Offset(, 7).FormulaR1C1 = "=rc[-4]"
   Next r
End Sub

 

[mumps]

Try:

Sub PosX()
    Application.ScreenUpdating = False
    Dim i As Long, LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For i = 1 To LastRow
        If Cells(i, 1).Value = "POSNX" Then
            Range("A" & i + 0).Range("$h$1").FormulaR1C1 = "=RC[-4]"
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

I'm not sure what you want to do with this line of code:

Range("A" & i + 0).Range("$h$1").FormulaR1C1 = "=RC[-4]"

 

[BradMS]

Try:

Sub PosX()
    Application.ScreenUpdating = False
    Dim i As Long, LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For i = 1 To LastRow
        If Cells(i, 1).Value = "POSNX" Then
            Range("A" & i + 0).Range("$h$1").FormulaR1C1 = "=RC[-4]"
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

I'm not sure what you want to do with this line of code:

Range("A" & i + 0).Range("$h$1").FormulaR1C1 = "=RC[-4]"

Here is another part, you can see the i +0 changes

Sub PosY()




Dim r As Range
Dim i As Long


Set r = Range("A1:A5000")
For i = r.Rows.Count To 1 Step -1
With r.Cells(i, 1)
If .Value = "POSNY" Then

Range("A" & i - 1).Range("$i$1").FormulaR1C1 = _
"=IF(R[1]C[-5]="""","""",R[1]C[-5])"

End If
End With
Next i
End Sub

 

[mumps]

Have you tried Fluff's code in Post #2 ?

 

[BradMS]

Have you tried Fluff's code in Post #2 ?

I have gotten both ways to work correctly! Thanks Everyone!

 

[Fluff]

Glad you got it sorted & thanks for the feedback