PDA

View Full Version : VBA code to add formula to blank cell



talkvinith
12-13-2015, 09:47 AM
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
12-13-2015, 11:17 AM
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
12-13-2015, 01:26 PM
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
12-13-2015, 05:49 PM
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
12-13-2015, 08:26 PM
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