Help with VBA

[Russk68]

Hi All
I need an automated method of replacing a formula when a change in a specific cell is made. I have been using a VBA method of double clicking in a cell to replace a formula. I have many layouts like this that consist of 12 rows. The next layout would be 14 thru 25. Column A and B are Index,Match formulas.
Sometimes column B needs to be overwritten. Lets say B5 is overwritten.
Sometimes the truck # might change. If it does, I need B5:B12 to update but B5 has no formula. I would then double click in B5 and the formula would be replaced. This is fast but it's not fail safe.
Is it possible to have the formulas in B5:B12 replaced with VBA when A2 changes? And the repeat for each layout.

B5 Formula: =IFERROR(INDEX('Patch By UNIVERSE'!$BH$3:$BH$5000,MATCH(AY5,'Patch By UNIVERSE'!$BG$3:$BG$5000,0)),"")

This would be awesome!

Thank you!

	A
2	Truck 1

	A	B
4	Label	Item
5	Crate 1	Apple
6	Crate 2	Orange
7
8
19
10
11
12

 

[JLGWhiz]

Try this in the sheet code module.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$2" Then Exit Sub
Application.EnableEvents = False
Range("B5:B12").Formula = "=IFERROR(INDEX('Patch By UNIVERSE'!$BH$3:$BH$5000,MATCH(AY5,'Patch By UNIVERSE'!$BG$3:$BG$5000,0)),"""")"
Application.EnableEvents = True
End Sub

 

[Russk68]

Hey Whiz
That works exactly how I explained it!
1 thing that I didn't think to mention was that a change in A2 is the result from a formula.
Any tricks to make that work?
Thank you for getting me this far!

 

[Peter_SSs]

What is the formula in A2?

 

[Russk68]

=IFERROR(INDEX(CableBuild!AX$3:AX$1000,MATCH('Truss Snakes'!$BU2,'Multi Build'!CW$3:CW$1000,0)),"Location")

 

[JLGWhiz]

You can try this, but it will run whether the change is in A2 or sormewhere else that generates a calculation. Maybe that would not matter.

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Range("B5:B12").Formula = "=IFERROR(INDEX('Patch By UNIVERSE'!$BH$3:$BH$5000,MATCH(AY5,'Patch By UNIVERSE'!$BG$3:$BG$5000,0)),"""")"
Application.EnableEvents = True
End Sub

 

[Russk68]

You can try this, but it will run whether the change is in A2 or sormewhere else that generates a calculation. Maybe that would not matter.

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Range("B5:B12").Formula = "=IFERROR(INDEX('Patch By UNIVERSE'!$BH$3:$BH$5000,MATCH(AY5,'Patch By UNIVERSE'!$BG$3:$BG$5000,0)),"""")"
Application.EnableEvents = True
End Sub

That would matter. I have an idea that might work if your interested in helping me.

 

[JLGWhiz]

Well, if the changes that cause A2 to change are occuring in column W of another sheet, Maybe you need the change macro on that sheet instead of the one with the A2 value.


 

[Peter_SSs]

Well, if the changes that cause A2 to change are occuring in column W of another sheet, Maybe you need the change macro on that sheet instead of the one with the A2 value.

That's why I asked for the formula of course, but that formula indicated that cells on three different sheets could cause A2 to change. Building Worksheet_Change events into all three sheets to do this task is not ideal so I would stick with the Calculate event so long as it is not slowing the sheet too much.

 

[JLGWhiz]

The problem with using calculate is that it can change some manual entries to formulas prematurely because almost any change will trigger the calculate event. I cannot think of a way to confine the event to just certain columns on specific sheets.