Vlookup fill down blank cells in column

mbesspiata · #1 09-23-2016, 10:04 AM

I want to use vlookup in a column and then autofill that column after entering the formula in the first cell of the column. I need the autofill to ONLY fill in the blank cells in the column as there is data in the bottom of the column already.
I want to use this in a current macro I am using. Currently I have the macro only create the vlookup formula and enter the data in the first cell. I then manually drag the formula down to the last blank cell in the column. I want to use a vba to have it fill in the rest of the blank cells.
Here is the beginning part of the marco where I insert the vlookup formula. Column E has blank cells in an undetermined amount of cells with each report I produce. I only want to fill in the date for those blank cells and don't want the vlookup formula to be applied to cells with data in them for the rest of column E.
Range("E2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],'[CBP Port and Other Agency Listing.xlsx]Combined'!C4:C5,2,FALSE)"

This is what the worksheet looks like and column E is has blank cells down for a different number each week I run the report. (see attached screenshot)

How can this be done or can it be done?

Mike

NoSparks · #2 09-23-2016, 11:11 AM

give this a try

Code:

Range("E2").FormulaR1C1 = _
    "=VLOOKUP(RC[-1],'[CBP Port and Other Agency Listing.xlsx]Combined'!C4:C5,2,FALSE)"
Range("E2:E" & Cells(2, "E").End(xlDown).Row - 1).FillDown

mbesspiata · #3 09-23-2016, 12:04 PM

This fills the entire column down except for the last cell. Remember that the column has data in it at some point down column E. So I need the vlookup formula to stop at the LAST empty cell in that column.
Thank you for the reply though. Much appreciated.

Mike

NoSparks · #4 09-23-2016, 01:39 PM

Please post a sample file where this doesn't work, there must be something you're not telling us.

I will admit, I couldn't test anything on your picture.

mbesspiata · #5 09-23-2016, 02:46 PM

See attached files. One is the macro text currently in use where I have to manually drag the E2 formula down through all blank cells.
The other two are the main report and the one used for the lookup number.
Hope this helps.
I've cleaned up unnecessary data from the reports.

Mike

NoSparks · #6 09-23-2016, 04:54 PM

Assuming what you have works, you already have an autofill for column "D" that fills to row 8000.
I guess your question is what to replace the 8000 with for autfilling in column "E"

That's what this part of my suggestion calculates, it's 1 row before the first row holding the lower data in column "E".

Code:

Cells(2, "E").End(xlDown).Row - 1

So, where you have the macro putting the formula into E2, and then selecting E2,
add the following line after those ones and see if it works.

Code:

Selection.AutoFill Destination:=Range("E2:E" & Cells(2, "E").End(xlDown).Row - 1)

mbesspiata · #7 09-23-2016, 06:02 PM

Thanks for working with me on this but the macro you suggest will fill column E with the vlookup formula from E2 all the way down column E for everything in column D. I need filldown to STOP in column E when it hits the first cell that already has a value in it. I just need the blank cells filled with the vlookup formula.

NoSparks · #8 09-23-2016, 11:08 PM

OK, I think the Seizures Pending Acceptance.xlsx you've posted is what you have at the end of the macro running as you currently have it.
The formula has been inserted into E2 by the macro and the rows in the column down to row 61 appear to be empty.

If you look at this part of the macro you have

Code:

Columns("D:D").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("D:D").Select
    Selection.ColumnWidth = 15
    Range("D2").Select
    ActiveCell.FormulaR1C1 = "=MID(RC[-3],5,4)"
    Range("D2").Select
    Selection.AutoFill Destination:=Range("D2:D8000")
    Range("D2:D8000").Select
    Columns("D:D").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("D:D").Select
    Range("D2").Activate
    Application.Run "enter_values"
    Columns("D:D").Select
    Columns("D:D").EntireColumn.AutoFit

You are inserting a new column D, making the original column D the new column E, and you never touch column E again until you insert the formula.
With the new column D, you insert a formula into D2, and autofill to D8000.
Then copy column D and paste it over itself as values.
Next you run the little "enter_values" sub which actually does the same .value=.value thing, again, on the same column.
I suspect the intention was for one of these operations to be on the old column D (the new E) and one on the new column D.

Any how, if I remove the formula from E2 and run this test macro on the sheet, things seem to be as they should be, give it a try

Code:

Sub Insert_Formula_and_Autofill()

    With Columns(5)
        .Value = .Value
    End With
    
    Range("E2").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-1],'[CBP Port and Other Agency Listing.xlsx]Combined'!C4:C5,2,FALSE)"
    Range("E2").Select
    Selection.AutoFill Destination:=Range("E2:E" & Cells(2, "E").End(xlDown).Row - 1)
    
End Sub

mbesspiata · #9 09-26-2016, 04:44 AM

Thanks NoSparks that worked perfectly.
I really appreciate your help on this.

Mike

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Auto Fill only Blank Cells in a Column	mbesspiata3	Excel Programming	1	09-22-2016 07:18 PM
Return Sum value of one column from cells not blank in another column	zulugandalf	Excel	3	08-14-2014 03:37 AM
How-TO format cells (FILL) by comparing cells	zanat0s	Excel	1	07-03-2012 04:27 AM