Vlookup paste special problems

[ndcam78]

Hi, I have office 2007 on windows XP (SP3)
I have a list of numbers in Column A (65000 rows)
I need to paste different Vlookup formulas in to 6 columns (1 formula in each column) after this, The formula checks a user data sheet and fills in all address details etc.
Problem is when I paste special the formula into any column it completes all the way down to line 150000. I have tried using ignore blanks and filtering and removing blanks (this takes too long and crashes spreadsheet)

How can I paste the formula to just search what is required (65000 lines not the whole sheet) Again it would take too long to select every column for 65000 lines and past into the selection.

Formula I use is:
=VLOOKUP(A2,'User Details'!$A$2:$G$65000,2,FALSE)

 

[CharlesChuckieCharles]

Hi, I have office 2007 on windows XP (SP3)
I have a list of numbers in Column A (65000 rows)
I need to paste different Vlookup formulas in to 6 columns (1 formula in each column) after this, The formula checks a user data sheet and fills in all address details etc.
Problem is when I paste special the formula into any column it completes all the way down to line 150000. I have tried using ignore blanks and filtering and removing blanks (this takes too long and crashes spreadsheet)

How can I paste the formula to just search what is required (65000 lines not the whole sheet) Again it would take too long to select every column for 65000 lines and past into the selection.

Formula I use is:
=VLOOKUP(A2,'User Details'!$A$2:$G$65000,2,FALSE)

You Obviously have spaces trailing the numbers in column A

Public Sub CleanofSpaces()
Dim c As Range
ActiveSheet.UsedRange.Select
For Each c In Selection
    If (Not IsNumeric(c.Value)) And (Not c.HasFormula) Then
 
        c.Value = Trim(c.Value)
    End If
 
Next c
End Sub

Paste this into a new modules in VBA

compile

save workbook

highlight column A and run CleanofSpaces macro

Then try putting your formulas in copying down

 

[ndcam78]

Thanks.

I have no idea how to use VBA or even how to find it in excel 2007.
Use to be in tools lol

I will try a few things hope it solves it. Thanks again