Apply formula to the Range if cells are Blank!

[Mohamedazees]

Dear Sir,

I Need a excel Vba for apply formulas to the Columns "S" to "Z" if the cells in range - Column "R" is blank.

I already have a code but that update the formula for only One range of column and code is given below for your reference.

i have repeat these codes for each range - i.e., Column S to Z separately however i need to avoid the same and do this task at one click please help me.

Thanks in advance for your valuable supports.
Reference Code:
Sub Find_2BGSTIN()
Dim R As Range, rng As Range
With ActiveSheet
Set rngRates = .Range("Y4:Y" & Cells(Rows.Count, "K").End(xlUp).Row)
For Each C In rngRates
If C.Value = "" Then
C.Formula = "=IFERROR(VLOOKUP([@GRT],ITC_2B,5,FALSE),IFERROR(VLOOKUP([@GRD],ITC_2B[[GRD]:[ITC Availability]],4,FALSE),IFERROR(VLOOKUP([@GDT],ITC_2B[[GDT]:[ITC Availability]],3,FALSE),IFERROR(VLOOKUP([@RDT],ITC_2B[[RDT]:[ITC Availability]],2,FALSE),""""))))"


rngRates.Formula = rngRates.Value

End If
Next C
End With
ActiveSheet.AutoFilterMode = 0
End Sub

 

[Joe4]

It looks like you are looping through rows 4 to the last row with data in column K.
And you want to loop through columns S to Z.

So you could loop something like this:

Dim cl as Long, rw as Long, lr as Long

'Find last row in column K with data
lr = Cells(Rows.Count, "K").End(xlUp).Row

'Loop through columns S to Z (column indexes 19 to 26)
For cl = 19 to 26
'   Loop through all rows
    For rw = 4 to lr
'       Populate formula in current row if blank
        If Cells(rw, cl).Value = "" Then Cells(rw, cl).Formula = ...
        ...
    Next rw
Next cl

 

[Mohamedazees]

Daer Sir,

Thanks for your support.
I feel i am not properly explain by exact requirement to you Sir, since the formula need to apply to each Columns (S to Z) is different from other columns.

 

[Joe4]

OK, if each column has a different formula, then you need a separate line of VBA code for each column.
You could start it out like this, and continue on for the rest of the columns:

    Dim lr As Long

'   Find last row in column K with data
    lr = Cells(Rows.Count, "K").End(xlUp).Row
    
'   Populate blank cells in column S with formula
    Range("S4:S" & lr).SpecialCells(xlCellTypeBlanks).Formula = ...
    
'   Populate blank cells in column T with formula
    Range("T4:T" & lr).SpecialCells(xlCellTypeBlanks).Formula = ...

 

[Mohamedazees]

Thankyou you so much Sir, Excellent it works like a charm.

 

[Joe4]

You ae welcome.
Glad I was able to help!