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!!
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!!
This should take care of your Formula problem.
This goes to the last used cell of Column B in Column C.
For hiding or un-hiding Sheet1, you could useSub Try() Range("C2:C" & Cells(Rows.Count, 2).End(xlUp).Row).Formula = "=VLOOKUP(RC[-1],Sheet1!R2C1:R7C2,2,0)" End Sub
Sub Maybe() If Sheets("Sheet1").Visible = True Then Sheets("Sheet1").Visible = False Else Sheets("Sheet1").Visible = True End Sub
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.
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.
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 SubSub 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