Find value via loop in another worksheet and change it

[wtom0412]

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.

Sub Change_Percentage()

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

On Error Resume Next
FoundItem = Sheets("Formulas").Range("C2:C65535").Find(What:=FindItem).Address
On Error GoTo 0

If FoundItem <> "" Then
    PasteLocation = Sheets("Formulas").Range(FoundItem).Offset(0, 2).Address

    Sheets("Margin Calculator").Range("M3").Copy Sheets("Formulas").Range(PasteLocation)
    
Else
    MsgBox ("Item not found. No action performed.")
End If

End Sub

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

Cheers, Toby

 

[wtom0412]

Hi,

I get the same error message after

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

Cheers, Toby

 

[Puertorekinsam]

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]

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

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

it presents that error message.

 

[Puertorekinsam]

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]

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

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

Else Founditem = ""

?

 

[wtom0412]

Actually, the FoundItem doesn't change to C3 in

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?