0

I am quite new to VBA and tried some different approaches but not getting the result I need though. The task is to develop a macro that runs a formula in column L (IF and a VLOOKUP) all the way until the last cell of the previous column, K. It cannot be a fixed cell since every day the data changes, i.e., more rows of data are added.

Any input is appreciated, thank you!

Sub ZBR_Users_Review()
'
' ZBR_Users_Review Macro
' Analysis to ensure all current ZBR users have equally assigned the corresponding Tagetik role
'

       
       
    Range("L5").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(VLOOKUP(RC[-11],'Tagetik CCO role users assigned'!R5C1:R1048576C6,6,FALSE)=0, ""No"", ""Assigned"")"
    Range("L5").Select
    Selection.AutoFill Destination:=Range("L5:L160")
    Range("L5:L160").Select
    Range("L4").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ColorIndex = 15
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlTop
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    ActiveCell.FormulaR1C1 = "Check"
    Rows("4:4").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$4:$L$100000").AutoFilter Field:=12, Criteria1:="#N/A"
    Range("L161").Select
End Sub

UPDATE: With the advice of @Toddleson, I got the answer to this case, sharing the code below in case someone ever needs something similar.

Sub ZBR_Users_Review()
'
' ZBR_Users_Review Macro
' Analysis to ensure all current ZBR users have equally assigned the corresponding Tagetik role
'

    Dim lRow As Long
        
            
            'Find the last non-blank cell in column A(1)
            lRow = Cells(Rows.Count, 1).End(xlUp).Row
       
       
    Range("L5").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(VLOOKUP(RC[-11],'Tagetik CCO role users assigned'!R5C1:R1048576C6,6,FALSE)=0, ""No"", ""Assigned"")"
    Range("L5").Select
    Selection.AutoFill Destination:=Range("L5:L" & lRow)
    Range("L5:L" & lRow).Select
    Range("L4").Select

#Rest of the code is the same as above
Gonçalo Malveiro
  • 69
  • 1
  • 1
  • 7
  • Does this answer your question? [Error in finding last used cell in Excel with VBA](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) – Toddleson Sep 15 '21 at 15:23
  • 1
    Once you have a `lastrow` variable you can use it when defining the range like `Range("L5:L" & lastrow)` – Toddleson Sep 15 '21 at 15:27
  • Got it working :) Thanks for the advice! – Gonçalo Malveiro Sep 15 '21 at 15:39
  • 1
    You can simplify the code further by removing useless `Select`s and `Selection`s. Like `Range("L5").FormulaR1C1 = ...` and `Range("L5").AutoFill ...`. In fact, you don't even need to do those seperately, you replace all 5 lines with `Range("L5:L" & lRow).FormulaR1C1 = ...` – Toddleson Sep 15 '21 at 17:27

0 Answers0