If Cell Is not Empty Then...VBA

[Jones1413]

Hello,

I am trying to write VBA for the following:

If Range.("I2:I") is not empty Then I want the cells in Range.("V2:V") to insert a formula. The cells in column I may or may not have data in them and there could be blanks cells in between each row. Basically the rows of data change each time I run a report so I don't know where the last line of data will be. I already have the formula written out that I want to insert in to column V, I'm just having trouble writing the first part of the macro to find the non-empty cells of data in column I.

Thanks!

 

[Joe4]

You can dynamically find the last row in column I with data, and then loop through all rows and enter your formula in column V if column I of that same row has data.
The structure might look like this:

Sub MyMacro()

    Dim lr As Long
    Dim r As Long
   
    Application.ScreenUpdating = False
   
'   Find last row in column I with data
    lr = Cells(Rows.Count, "I").End(xlUp).Row
   
'   Loop through all rows
    For r = 2 To lr
'       Check to see if column I is not blank
        If Cells(r, "I") <> "" Then
'           Populate column V with formula
            Cells(r, "V").Formula = "ENTER YOUR FORMULA HERE"
        End If
    Next r
   
    Application.ScreenUpdating = True
   
End Sub

You would just need to enter the formula part.

 

[Jones1413]

This is the formula that I put in: "=IFNA(XLOOKUP(I2,'FY23 Start Dates'!$C$2:$C$53,'FY23 Start Dates'!$A$2:$A$53),"""")"'

Where "I2" is the lookup value, I want that to go to I3, I4, I5, etc as the formula populates in those cells. However, it always stays as I2 for the lookup value.

 

[Joe4]

Try changing this line in my original code:

Cells(r, "V").Formula = "ENTER YOUR FORMULA HERE"

with this:

Cells(r, "V")..FormulaR1C1 = _
        "=IFNA(XLOOKUP(RC[-13],'FY23 Start Dates'!R2C3:R53C3,'FY23 Start Dates'!R2C1:R53C1),"""")"

Note that you can get this code by turning on the Macro Recorder and entering the formula in cell V2.