[VBA] When a cell value change other sheet, trigger other cells function, and change the result macro

[ozan efendi]

Hi, ihave this code below and it gets data from sheet1 and does index and match function. But, the problem is, while i am using this function in sheet2, i can get the results flawlessly. But when i change the reference cells value in sheet1, nothing happens. I guess, that is because the "Activesheet.EnableCalculation" code. It only triggers if i change a cell value in sheet2 (i write the function in sheet2). I' m trying to fix this for two days, unfortunalety i couldn' t find. Can anyone help on this macro? Thanks.

Function Index_func1(x As Variant, y As Variant) As Variant
Dim Model As Variant
Dim Bakim As Variant


ActiveSheet.EnableCalculation = False


Model = Application.Match(x, Worksheets("Sheet1").Range("a1:a10000"), 0)
Bakim = Application.Match(y, Worksheets("Sheet1").Range("a1:dd1"), 0)
Index_func1 = Application.Index(Worksheets("Sheet1").Range("a1:dd10000"), Model, Bakim)


ActiveSheet.EnableCalculation = True


End Function

 

[p45cal]

Well, if it's only sheet2 that you need to change EnableCalculation of then instead of
ActiveSheet.EnableCalculation=False/True, change it to
Sheets("Sheet2").EnableCalculation=False/True.

If it's any sheet except sheet1 that you want to change the EnableCalculation of then use something like:
If activesheet.name <> "Sheet1" then activesheet.EnableCalculation=False/True

 

[ozan efendi]

hi pi45cal. thanks for the response. actually this isnt what i meant. i have a table of a cars price list in sheet1 ( for example i have a price of all audi cars. the row part is car model the column part is cars age.). according to sheet1 if i write a model of audi in cell A1in sheet2 with index and match formulas i can find the price. if i write another model name in cell A1 i get the correct result. however when i change the place of the model (for example from B5 to B16) the function in sheet1 does not trigger. so what i need is a code triggering the function code while a change occurs in both sheets. thanks.

 

[p45cal]

Got you!
I think the reason is this; Because the formula (as written on the worksheet) does not refer to sheet1, changes on sheet1 do not trigger a recalculation of your formulae. If you were to change your function to refer to the table on sheet1 I think it will update properly. So:

Function Index_func1(x, y, SourceTable As Range)
Dim Model As Long
Dim Bakim As Long
Model = Application.Match(x, SourceTable.Columns(1), 0)
Bakim = Application.Match(y, SourceTable.Rows(1), 0)
Index_func1 = Application.Index(SourceTable, Model, Bakim)
End Function

with a change to how the formula is used on the sheet to the likes of:
=index_func1(C3,D2,Sheet1!$a$1:$dd$10000)

There should be no need for any lines in the function related to Calculation.

BTW, a1:dd10000 is quite a large range, you really have that many car ages/models??!
It will add significant time to recalculation if there are more than a few of your formulae on a sheet.

Also, you UDF can be shortened to:

Function Index_func1(x, y, SourceTable)
Index_func1 = Application.Index(SourceTable, Application.Match(x, SourceTable.Columns(1), 0), Application.Match(y, SourceTable.Rows(1), 0))
End Function

or even:

Function Index_func1(x, y, SourceTable)
Index_func1 = SourceTable.Parent.Cells(SourceTable.Columns(1).Find(x).Row, SourceTable.Rows(1).Find(y).Column)
End Function