VBA for a formula

[lesliewheeler]

Hi,

I have and excel in which I have approximate 20,00,000 rows (cells vertically down) from which I am using the B column cell starting from B2 and going down the column as reference for vlookup, the formula works fine
formula entered in cell C3
=VLOOKUP(B2,$N$2:$Q$38,4,0)
The problem is the sheet has become very heavy with so many formula pasted and is crashing.

Is there any way a formula or vba code can help for solution and make file less heavy.

I want column C starting from C3 to get updated downward as per the formula I have given.

Any help is appreciated.

Thanks

Leslie

 

[offthelip]

You can try using this macro which does the same thing but stores the data value and should be very fast.
Note Not tested!!

Sub test()
lastrow = Cells(Rows.Count, "B").End(xlUp).Row
inarr = Range(Cells(1, 2), Cells(lastrow, 2))
datarr = Range(Cells(2, 14), Cells(38, 17))
outarr = Range(Cells(1, 3), Cells(lastrow, 3))
For i = 2 To lastrow
For j = 1 To 37
  If inarr(i, 1) = datarr(j, 1) Then
   outarr(i, 1) = datarr(j, 4)
   Exit For
  End If
Next j
Next i
Range(Cells(1, 3), Cells(lastrow, 3)) = outarr

End Sub

 

[lesliewheeler]

You can try using this macro which does the same thing but stores the data value and should be very fast.
Note Not tested!!

Sub test()
lastrow = Cells(Rows.Count, "B").End(xlUp).Row
inarr = Range(Cells(1, 2), Cells(lastrow, 2))
datarr = Range(Cells(2, 14), Cells(38, 17))
outarr = Range(Cells(1, 3), Cells(lastrow, 3))
For i = 2 To lastrow
For j = 1 To 37
  If inarr(i, 1) = datarr(j, 1) Then
   outarr(i, 1) = datarr(j, 4)
   Exit For
  End If
Next j
Next i
Range(Cells(1, 3), Cells(lastrow, 3)) = outarr

End Sub

It Didnt work

 

[offthelip]

What did happen? was there an error?? do you know how to use the debug faciltiy in vba?

 

[lesliewheeler]

What did happen? was there an error?? do you know how to use the debug faciltiy in vba?

Hey

It did work apologies for my earlier comment, I had to read ur code and the accordingly make the new sheet with references, I made some changes in my earlier one thats why it wasnt returning the value.

But just one small help instead value coming in C3 it is coming on C2.

Can you help with correction

 

[offthelip]

to start on row 3 change:

For i = 2 To lastrow

to

For i = 3 To lastrow