Find value via loop in another worksheet and change it

wtom0412

Board Regular
Joined
Jan 3, 2015
Messages
168
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
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

Board Regular
Joined
Jan 3, 2015
Messages
168
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
 

Some videos you may like

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
Joined
Oct 8, 2005
Messages
287
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
Joined
Jan 3, 2015
Messages
168
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
Joined
Oct 8, 2005
Messages
287
I'm really at a loss. Here is what my workbook looks like before I run anything


1603478804421.png
1603478811712.png

And here is the after:

1603478677777.png



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

1603478752305.png


I just can not duplicate the issue.
 

wtom0412

Board Regular
Joined
Jan 3, 2015
Messages
168
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
Joined
Jan 3, 2015
Messages
168
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?
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top