• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro to Copy down Vlookup until Blank Cell

jgj1988

New Member
I am writing a macro that will fill in vlookups, I thought what I had wrote would stop when the cell to the left(In Column E) was blank. But it fills down to the very last row.


'=VLOOKUP(E3,'SKU DETAIL'!B:D,3,FALSE)

Range("f3:f" & (Range("e" & Rows.Count).End(xlDown).Row)).FormulaR1C1 = _

"=VLOOKUP(RC[-1],'sku detail'!C[-4]:C[-2],3,FALSE)"


Can anyone tell me what I'm missing to get this to stop once a blank cell in column E appears?
 
Hi, jgj1988!

You said you wrote a macro but you post two formulas. Please clarify and upload a sample file if necessary.

Regards!
 
Hi


I'm not a big fan of putting formulas in cells from a macro, much better to do the calculation inside the macro, however if you want the formulas try the below:

[pre]
Code:
Dim row_count As Long
Dim i As Integer
row_count = Application.WorksheetFunction.CountA(Columns("E:E"))
For i = 3 To row_count
Cells(i, 6).Value = "=VLOOKUP(E" & i & ",'SKU DETAIL'!B:D,3,FALSE)"
Next i
[/pre]
 
Dave, I tried the formula you gave me, but the vlookup copies down to the second to last cell. Leaving the last non blank cell without a formula.

On the line:


For i = 3 to row_ count


I added a + 1 to the end and it works. But I would like to understand this better, why would this not be counting all the non blank cells?


Thanks,


James
 
Oh ok I see, yes e1 is blank but e2 has a value. What would I have to do differently to get this line to instead of count all column E, count from cell e2 down to the bottom or stop at cell e1000 for example?


row_count = Application.WorksheetFunction.CountA(Columns("E:E"))


thanks again
 
Back
Top