# 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

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

#### 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
519
Replies
2
Views
34
Replies
0
Views
93
Replies
0
Views
23
Replies
8
Views
358

1,130,258
Messages
5,641,155
Members
417,195
Latest member
Vishal kumar

### 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.

### Which adblocker are you using?

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