If Cell Is not Empty Then...VBA

Jones1413

New Member
Joined
Jul 26, 2019
Messages
47
Office Version
  1. 365
Platform
  1. Windows
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!
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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:
VBA Code:
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.
 
Upvote 0
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.
 
Upvote 0
Try changing this line in my original code:
VBA Code:
Cells(r, "V").Formula = "ENTER YOUR FORMULA HERE"
with this:
VBA Code:
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.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,140
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top