Find value via loop in another worksheet and change it

wtom0412

Board Regular
Good Morning All,

My worksheet calculates Sales Margins based on a formula (recipe) for 134 Product (and other costs: labour, other materials etc).

I have two sheets, Margin Calculator (MC) and Formulas. Formulas (as the name suggests) contains all of the various formulas for our products, and MC does the calculations.

Once I select a product on MC, data gets pulled from Formulas via a VLOOKUP.

The data consists of Ingredient Name and % of ingredient used. There can be up to 28 ingredients depending on what product is selected. This data appears in MC in P3:P30 and Q3:Q30.
There is a unique Helper Cell on MC (O3:O30) that exists on Formulas (C:C) to help with the look up.

When I am looking at Sales Margins, I want to be able to play with the % of Ingredient used to achieve the best Margin possible.

So, I want to type a value into a cells M3:M30 in Margin Calculator, and once I am happy with the amendments, click a button and have VBA change the corresponding values in Formulas.

I found some code on the net, and it works on the first value, but I can't figure out how to loop through both MC M3:M30 and Formuls C:C to effect the next value.

VBA Code:
``````Sub Change_Percentage()

FindItem = Sheets("Margin Calculator").Range("O3").Value

On Error Resume Next
On Error GoTo 0

If FoundItem <> "" Then

Sheets("Margin Calculator").Range("M3").Copy Sheets("Formulas").Range(PasteLocation)

Else
End If

End Sub``````

I am so sorry if this message is confusing, and any help would be greatly appreciated.

Cheers, Toby

wtom0412

Board Regular
Hi,

I get the same error message after

VBA Code:
``        If x > 0 Then Founditem = Sheets("Formulas").Columns(3).Find(What:=Finditem).Address Else Founditem = ""``

Cheers, Toby

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Puertorekinsam

Active Member
I'm unable to duplicate the error... when you say the second loop. Are you running it again? changing the value in O3? I'm just trying to repeat the error.

wtom0412

Board Regular
I am stepping through the code with F8. On the first loop it works, but when it gets to

VBA Code:
``If x > 0 Then Founditem = Sheets("Formulas").Columns(3).Find(What:=Finditem).Address Else Founditem = ""``

it presents that error message.

Puertorekinsam

Active Member
I'm really at a loss. Here is what my workbook looks like before I run anything

And here is the after:

My code is running through until the variable i = 9, which is where the first blank is occurring in "Margin Calculator" column M

I just can not duplicate the issue.

wtom0412

Board Regular
Hi, I think the issue might be that first FoundItem in the line

VBA Code:
``If x > 0 Then Founditem = Sheets("Formulas").Columns(3).Find(What:=Finditem).Address Else Founditem = ""``

is not switching to C3, which means it doesn't match with the helper cell on the Margin Calculator at O5?

The error message occurs before

Code:
``Else Founditem = ""``
?

wtom0412

Board Regular
Actually, the FoundItem doesn't change to C3 in

Code:
``Do Until Sheets("Margin Calculator").Cells(i, 13) = "" Or Founditem = ""``
?

Sorry, my previous response should have said

is not switching to C3, which means it doesn't match with the helper cell on the Margin Calculator at O4?

Replies
19
Views
465
Replies
2
Views
25
Replies
0
Views
71
Replies
8
Views
325
Replies
9
Views
260

1,127,581
Messages
5,625,617
Members
416,124
Latest member
DeMoNloK

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.

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

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