VBA for a formula

lesliewheeler

New Member
Joined
Dec 1, 2016
Messages
32
Office Version
  1. 2013
Platform
  1. Windows
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
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,813
Office Version
  1. 2010
Platform
  1. Windows
You can try using this macro which does the same thing but stores the data value and should be very fast.
Note Not tested!!
VBA Code:
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
 
Solution

lesliewheeler

New Member
Joined
Dec 1, 2016
Messages
32
Office Version
  1. 2013
Platform
  1. Windows
You can try using this macro which does the same thing but stores the data value and should be very fast.
Note Not tested!!
VBA Code:
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

Well-known Member
Joined
Dec 23, 2017
Messages
1,813
Office Version
  1. 2010
Platform
  1. Windows
What did happen? was there an error?? do you know how to use the debug faciltiy in vba?
 

lesliewheeler

New Member
Joined
Dec 1, 2016
Messages
32
Office Version
  1. 2013
Platform
  1. Windows
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

Well-known Member
Joined
Dec 23, 2017
Messages
1,813
Office Version
  1. 2010
Platform
  1. Windows
to start on row 3 change:
VBA Code:
For i = 2 To lastrow
to
VBA Code:
For i = 3 To lastrow
 

Watch MrExcel Video

Forum statistics

Threads
1,129,916
Messages
5,638,976
Members
417,063
Latest member
thematulaak

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top