#1
|
|||
|
|||
Vlookup fill down blank cells in column
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
Vlookup fill down blank cells in column
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 |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
Vlookup fill down blank cells in column
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 |
#6
|
|||
|
|||
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 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) |
#7
|
|||
|
|||
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.
|
#8
|
|||
|
|||
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 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 |
#9
|
|||
|
|||
Vlookup fill down blank cells in column
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 |