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!!
Printable View
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 useCode:Sub Try()
Range("C2:C" & Cells(Rows.Count, 2).End(xlUp).Row).Formula = "=VLOOKUP(RC[-1],Sheet1!R2C1:R7C2,2,0)"
End Sub
Code: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.
Code: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
Code: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