Vlookup fill down blank cells in column

NoSparks · #1 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 · #2 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