VBA code to add formula to blank cell

[talkvinith]

Hi

Can any one help me??!!

I want to add formula only to blank cells in the C column and this needs to add till the active cells in B column. Also need a coding to hide and unhide sheet1.

Thank You!!

[jolivanes]

This should take care of your Formula problem.
This goes to the last used cell of Column B in Column C.

Sub Try()
    Range("C2:C" & Cells(Rows.Count, 2).End(xlUp).Row).Formula = "=VLOOKUP(RC[-1],Sheet1!R2C1:R7C2,2,0)"
End Sub

For hiding or un-hiding Sheet1, you could use

Sub Maybe()
    If Sheets("Sheet1").Visible = True Then Sheets("Sheet1").Visible = False Else Sheets("Sheet1").Visible = True
End Sub

[talkvinith]

Thanks for your reply!!!

But the formula replaced even already filled cells in C column.

Is it possible to provide a solution for that also.

[jolivanes]

Does it do anything bad?
It will just replace the formulae with the same formula.
If you want, you can only put the formula in the empty cells by looping or filtering. I can't see the logic in multiple lines of code if a single, simple line works.
Looping will be slower if you have a large range also.
Let us know though if you do want a different solution.

[jolivanes]

You could try these methods if you want to.

Sub Formula_With_Loop()
    Dim lr As Long, j As Long
    lr = Cells(Rows.Count, 2).End(xlUp).Row
    For j = 2 To lr
        If Cells(j, 3).Value = "" Then Cells(j, 3).Formula = "=VLOOKUP(RC[-1],Sheet1!R2C1:R7C2,2,0)"
    Next j
End Sub

Sub With_Special_Cells()
    Dim lr As Long
    lr = Cells(Rows.Count, 2).End(xlUp).Row
    With Range("C2:C" & lr).SpecialCells(4)
        .Formula = "=VLOOKUP(RC[-1],Sheet1!R2C1:R7C2,2,0)"
    End With
End Sub