VBA - Looping through visible cells and updating value

[dr4pes]

Hello all,

I've just recently began playing around in Excel and I'm struggling to figure this out. I've tried several variations of what I copied below, but I believe my understanding to be flawed. What I need to do is first filter the data to only include adjustments (ending in U1, U2, etc.). I then perform a Vlookup comparing the adjustment to the original payment and changing the date on the adjustment (Column M) to the original pay date found in a separate workbook.

I've tried several attempts, but the following is my most recent attempt. I thought this would loop through the visible cells in column M starting at M2 to avoid the header row and update the value to the vlookup I have. But instead, this applies the macro to M1 only. Any help is appreciated.

A couple notes, the affected rows - some will be contiguous, some will not. The full data is usually around 1500 rows and the filtered data is around 100, but these numbers need to be dynamic.

Sub Macro()    Lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    ActiveSheet.Range("$A$1:$W$" & Lastrow).AutoFilter Field:=5, Criteria1:="=*u*"
    For Each cell In Range("M2:M" & Lastrow).SpecialCells(xlCellTypeVisible)
        cell.Value = "=VLOOKUP(LEFT(RC[-8],10),[export.XLSX]Sheet1!C1:C2,2,FALSE)"
    Next cell

End Sub

Additionally, I need to then copy and paste the affected cells as values afterwards, but I believe that to be a separate task.

 

[AlphaFrog]

Try this (not tested)

- Use the .FormulaR1C1 property to apply a formula to a cell or range of cells.
- You can apply the formula to all the visible filtered cells en mas. No need to loop through each visible cell.
- The .Value = .Value line converts the formulas to their result.

[COLOR=darkblue]Sub[/COLOR] Macro()
    [COLOR=darkblue]Dim[/COLOR] LastRow [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]
    LastRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
    ActiveSheet.Range("$A$1:$W$" & LastRow).AutoFilter Field:=5, Criteria1:="=*u*"
    [COLOR=darkblue]With[/COLOR] Range("M2:M" & LastRow)
        .SpecialCells(xlCellTypeVisible).FormulaR1C1 = "=VLOOKUP(LEFT(RC[-8],10),[export.XLSX]Sheet1!C1:C2,2,FALSE)"
        .Value = .Value [COLOR=green]'Replace formulas with results[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
    ActiveSheet.AutoFilterMode = [COLOR=darkblue]False[/COLOR] [COLOR=green]'turn off autofilter[/COLOR]
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

 

[dr4pes]

Thank you so much for your reply.

I tried your solution, and I'm getting the same result. It's applying the formula to the correct column, but it's only applying it to the header (M1). I copied the data into a different sheet in the same workbook and the results are the same.

Is the problem in the LastRow bit?
I added MsgBox (LastRow) after the LastRow declaration and it's returning "1".

That was it. I didn't fully understand the code I was using to calculate the lastrow. The first column is only a header so the last row of the first column is 1, I changed it to (rows.count, 13) and it's working now. Thank you so much for your help.

 

[Fluff]

Try

    LastRow= Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row