Replace Vlookup with a macro

[Guest]

I have a huge excel sheet being shared over the network the problem is that
when i run vlookup and save the file the file size goes very big. also the
data is really huge in these and its really inconvenient to run vlookup again
and again. i want to automate the process that when i open the workbook i
just run the macro and its done.

the current vlookup goes in cell c of worksheet 1 and compares A2 to whole
worksheet 2 and return cloumn 1 in cell c if its a exact match. this process
is sometimes hamper by spaces in which case it does not return a value. i
need a way to automatically delete all speace and html spaces also in sheet 1
column A .

Your help will be great and will save me plenty of time.

Thanks

 

[Carim]

Hi Arain,

Adjust the following macro to your specific needs :

Sub Report()
Dim oCell As Range
Dim Dest As Range
For Each oCell In ActiveSheet.Range("A2:A10")
Set Dest =
Sheets("Sheet2").Range("A2:A10").Find(What:=oCell).Offset(0, 1)
oCell.Offset(0, 2) = Dest.Value
Next oCell
End Sub


HTH
Cheers
Carim