VBA problem with alternative to index match formula

[chnarakisa]

Hi all

I am totally new to world of VBA and i am having the following scenario:
sheet1 with the columns: item, description, vendor, pack size, price
and sheet2 which is my database with 170000 rows with the same exact columns.
what i am trying to do is when i enter a value in sheet 1 in the item column, a macro that will be able to search the value of the active cell (sheet1 a2 for example) through my database (sheet2 a:a) and will bring the rest data for this item (vendor, pack size, price) in sheet1.

for the moment i am using an index match formula to achieve this but it is very slow so i thought i could give VBA a try, do you have any ideas on this case?

kind regards

[D_Marcel]

Hi chnarakisa!

Do the values is exclusives in the Worksheet 2?
If yes, why don't use VLOOKUP function in the Worksheet 1? If there is a single occurrence to each item, the values that you want will appear instantly.

If you prefer VBA, take a look on this example of the MVP Ron de Bruin:

http://www.rondebruin.nl/win/s9/win006.htm

Douglas Marcel

[chnarakisa]

Hi D_Marcel,
thank you for the quick reply, i used to have vlookup but i replaced them with index match (which was faster) i still get my results instantly, the problem comes when i try to insert or delete for example a line, it takes almost one to two minutes to recalculate the sheet, that's why i thought i might give VBA a try, because actually i will have only one calculation at a time and all the other data on my sheet will be just data no need to recalculate all these formulas all the time. I will give it a try following the instructions on the link you gave me.

thank you very much

[D_Marcel]

hi chnarakisa!
You're welcome friend. :friends:

Well in this case, beyond VBA, I guess that you have as an option, deactivate the automatic calculation of the sheet:

File > Options > Formulas > Calculation Options > Manual.
In this case, Excel will only calculate the formulas when you press F9.

Please, share with us if you develop something. If you have some trouble during programming, share also!

Douglas

[chnarakisa]

Hi D_Marcel,
i got the following code from another forum and it works fantastic:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
'if changed cell from column A'
If Not Application.Intersect(Range("C:C"), Target) Is Nothing Then
Dim sh2 As Worksheet
Dim res As Range
'change "Sheet2" to the sheet name that is true for you'
Set sh2 = ThisWorkbook.Worksheets("Database")
Set res = sh2.Range("A:A").Find(What:=Target.Value, LookAt:=xlWhole)
If res Is Nothing Then
MsgBox "There is no matching value on Sheet2"
Else
'if we found value on sheet2, than copy range B,C'
Target.Resize(1, 7).Value = res.Resize(1, 7).Value
End If
End If
Application.EnableEvents = True
End Sub

the challenge is to change the code so it will run on certain named ranges, so it will run through certain cells in column A:A and paste the data to the rows that are changed.

any ideas on this?