Hello. I currently have some come where a workbook has 8 columns of data. I insert blank columns on this sheet X amount of times. For example one time I run the macro I may have 4 blank columns, the next time I may end up with 2 blank columns. The blank column will always start in cell C3. I would like to paste a vlookup in C3 and go to end of all blank cells. Then after the formula is filled across X columns, I would like to fill the formula down to the end of the data in column B if that makes sense. I have attached an example of what I have so far. Thanks!
VBA Code:
Sheets("Duplicates").Select
Range("B2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Active Price").Select
Range("C1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Sheets("Active Price").Select
Range("C3").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC2&R2C,'F461 Current Price'!C9,1,FALSE)"
Range("C3").Copy
For j = 1 To col461
Sheets("Active Price").Select
On Error Resume Next
Range("C3", Range("C3")).End(xlToRight).SpecialCells(xlCellTypeBlanks).Select
On Error GoTo 0
Range("C3").Select
Range(Selection, Selection.End(xlToRight)).SpecialCells(xlCellTypeBlanks).Select
'Selection.PasteSpecial Paste:=xlPasteAll