MrExcel.com - Your One Stop for Excel Tips & Solutions

AUTOFILL / COPY & PASTE HELP

Posted by Jon on January 01, 2001 7:13 AM

Being very new to VBA I hope someone can help me with this little problem.

I am developing a text file cleaner and adding a VLOOKUP to Lookup some simple data .

My file varies in row length which is where my problem arises.
I insert a blank column and then enter my VLOOKUP formula. Now what I want to do is copy and paste this formula down the entire row length.

I recorded this and the recorded marco only selects the exact range of the original file I use

Here is a code snippet:

Range("C2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[4],'Cell ID.xls'!ID,2,FALSE)"
Range("C2").Select
Selection.AutoFill Destination:=Range("C2:C25")
Range("C2:C25").Select

Now I know I could probably change the range so that it references the entire worksheet but that is not what I want.

I want is to find the last non-blank row in Column A and then copy and paste my formula in Col C from c2 to the final row number.

I hope I make sense!!

Anyone any ideas?

Jon

Posted by Dave on January 01, 2001 7:23 AM


Hi Jon

Try Changing you code to this:

Dim LastRow As Long
LastRow = Range("C65532").End(xlUp).Row
With Range("C2")
.Value = "=VLOOKUP(RC[4],'Cell ID.xls'!ID,2,FALSE)"
.AutoFill Destination:=Range("C2:C" & LastRow)
End With

Hope this helps
Dave

  • OzGrid Business Applications

Posted by Jon on January 01, 2001 2:30 PM

Posted by Anom on January 01, 2001 4:15 PM

Here is another way provided you have a a continuous region, with no blank cells except for column "C"

Range("C2").Select
Selection.CurrentRegion.Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=VLOOKUP(RC[4],'Cell ID.xls'!ID,2,FALSE)"
Range("C2").Select