Copy Down Formulas in Missing [SOLVED]

[jgj1988]

Hi, Im looking for help to create a macro to find blank cells in a certain range and then insert a formula in.

For example I have data in a5:Q25 but cells g22:g25 are blank. I want the macro to find the empty cells and fill in a vlookup formula. The code I pasted below is overwriting cells that are not blank.


Also I have a header on this data that starts in Row4, so rows 1-3 are mostly blank.


Sub Copy_down()


'Find First Blank Rows

Worksheets("PCR Log").Activate

Dim First As Range

Dim Last As Range

Set First = Range("g" & Rows.Count).End(xlUp)

Set Last = Range("f" & Rows.Count).End(xlUp)

Range("g" & First, "g" & Last).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[1],Skus!C[-4]:C[-1],4,FALSE),"""")"

 

[Luke M]

I think something like this will work, if you're just copying the formula down from the cell above it.

[pre]

Sub FillFormulas()
Dim c As Range

For Each c In Range("A5:Q25").SpecialCells(xlCellTypeBlanks)
c.Formula = c.Offset(-1, 0).Formula
Next c

End Sub

[/pre]

 

[SirJB7]

@SirJB7

Hi, myself!

So long...

You should go the oculist right now, you thought you were cured but this relapse is severe. Are you seeing ghosts?

Regards!

 

[jgj1988]

Thanks Luke,


Data will be constantly appended to this worksheet so the range of the blank cells will be different each time. I will always be looking for the range in column g, that starts with the first blank cell in column G, and the last non blank cell in column F. I want to remove the formulas after the macro so that the file is easier to work with.


SirJB7 - I have no idea what you are talking about.

 

[SirJB7]

@jgj1988

Hi!

Sorry if I confused you, it was an undercover subliminal message for Luke M.

Regards!

 

[NARAYANK991]

Hi ,


I think what you want may be something like this , though I am not sure how this works , in the sense that I do not understand the objective of taking the first blank cell in column G , and the last non-blank cell in column F :

[pre]

Sub Copy_down()
Dim First As Long
Dim Last As Long

Worksheets("Sheet1").Activate
First = Range("g5").End(xlDown).Row + 1
Last = Range("f" & Rows.Count).End(xlUp).Row

Range("g" & First, "g" & Last).Value = Evaluate("=IFERROR(VLOOKUP(RC[1],Skus!C[-4]:C[-1],4,FALSE),"""")")
End Sub

[/pre]
Logically , you should put in the values in all the cells which are blank ; if these cells are contiguous , then the definition of Last should be changed to get the last blank cell in column G.


If the blank cells are not contiguous , then the inserting of the formula value should be done using a loop.


Narayan

 

[Luke M]

@SirJB7

Yeah, I'm still here. Combination of other work keeping me very busy, and the growth of our community with other ninjas/experts has allowed me to rest and not feel like people are not getting their questions addressed. But rest assured, I am still here, lurking in the shadows...

 

[jgj1988]

Thanks Narayank that worked