Vlookup

[donyc]

Hi all, I have rather silly question.. I have 5 columns and my 3rd column is empty.. I use VLOOKUP and double click on the cell so it populates for all the info..However when I go to 4th column it will populate the 1st cell but will not populate all cells below when I double click on the box.. I was told it is because the 3rd column is all empty..

Hope the above makes sense.. if not..can post an example..Is there a way around this?

thanks so much!

Danny

[martindwilson]

double click? why? i think you need to post an example.

[Paul]

When you double-click the lower right corner of a cell it acts like an automatic fill-down. This only works when there is data in the cells immediately to the left or right of the cells you're trying to auto-fill. If, for example, you had data in C1:C10 and C12:C20 and you entered a formula into D1. You could double-click the lower right corner of D1 and it would auto-fill the formula down to D10. It would stop there, because there is no value in D11. You would manually have to copy the formula to D12, and then you could double-click again to fill the rest of the way down to D20.

This functionality cannot be altered that I'm aware of, but if you wanted you could create a macro to run via the DoubleClick event to fill down values/formulas.. only problem is how would the macro know when to stop? Would it always base the "last row to fill to" on two columns to the left? Seems like more work than necessary to just click on the lower right corner of D1 and drag your mouse downward to the bottom of your data (or select D1:D?? and use Fill -> Down).

[donyc]

I am trying to modify it with below code but ran into a small problem with error '1004' - "Method 'Range' of object]_Global'failed'


I have my data IDs begining column B14. I use VLOOKUP in columns C and D.
Column E is empty
I am using VLOOKUP in column F
below is the code I modified. It is breaking on line:
Range("F14").AutoFill Destination:=Range("F14:B" & lastrow)

   Range("F14").Select

    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-4],'Data'!C[-3]:C[23],27,0)"

    Dim lastrow As Long

    lastrow = Range("B14" & Rows.Count).End(xlUp).Row

    Range("F14").AutoFill Destination:=Range("F14:B" & lastrow)

    Range("F14").Select

I am hoping that it will autofill column F all the way down of column B..

Any suggestions would be greatly appreciated!

Thanks so much