function to find answer for a multi-step equation with parts (cell values) across multiple sheets -- all inside of a for loop (updated)

arg123

New Member
Joined
Jun 8, 2016
Messages
26
Hello all,

I tried this once before and it was poorly worked out. I requested it be removed. Apologies for the dup.

I feel like an intermediate user but functions in VBA with complicated multi-step equations, and in this case with variable cell references has got my mind reeling.

Its probably much simpler than I'm thinking or my lack of experience shows in not having really executed similar code in the past.

Basically (forgive the lack of proper syntax, just trying to get the idea across):
Equation:
a = ((( b ^ ( c-d )) / e) )/ 1000

Where:
ouch = sheets("a").range("a20:a1000")

For Each cell in range ouch

a = cell.offset(0, 1)
b = sheets("b").range("m12")
c = sheets("b").range("m11")
d = cell.offset(0, 2)
e = sheets("b").range("p2")

*note any cell.offset is on sheet "a" and all other values are on sheet "b"

Now for the ...far from fluent... code I have tried and failed with because I'm inexperienced with these.

VBA Code:
Dim StdRng As Range
Set StdRng = Sheets("CopyStandardDataHere").Range("C3:J6")
Dim trgts As Range
Set trgts = Sheets("Export").Range("B21:B3092")

For Each cell In StdRng
    If cell.Value <> "" Then
        For Each cell2 In trgts
            If cell2.Value = "WH-V-8" Then
            cell2.Offset(0, 4) = (((Sheets("CopyStandardDataHere").Range("D$12") ^ (Sheets("CopyStandardDataHere").Range("D$11") - cell2.Offset(0, 1))) / Sheets("CopyStandardDataHere").Range("$M$2")) / 1000)
            End If
        Next
   End If
Next

I know, laughable... but hey, logically it makes sense to me -- just in the wrong language.

Alternatively I tried with functions:

VBA Code:
Dim StdRng As Range
Set StdRng = Sheets("CopyStandardDataHere").Range("C3:J6")
Dim trgts As Range
Set trgts = Sheets("Export").Range("B21:B3092")
Dim WHV8Eamp As Range
Set WHV8Emp = Sheets("CopyStandardDataHere").Range("C$12")
Dim WHV8Int As Range
Set WHV8Int = Sheets("CopyStandardDataHere").Range("C$11")
Dim RxnVol As Range
Set RxnVol = Sheets("CopyStandardDataHere").Range("$M$2")

For Each cell In StdRng
    If cell.Value <> "" Then
        For Each cell2 In trgts
            If cell2.Value = "WH-V-8" Then
            cell2.Offset(0, 4) = "=Quotient ("=Quotient ("=POWER (WHV8Eamp, "=WHV8Int-coffset")", RxnVol"), 1000"
            End If
        Next
   End If
Next

Thank you all for you time in checking this out and any assistance or guidance that may be offered! Cheers!
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

arg123

New Member
Joined
Jun 8, 2016
Messages
26
the second VBA code set is supposed to read as the following:
cell2.Offset(0, 4) = "=Quotient ("=Quotient ("=POWER (WHV8Eamp, = WHV8Int-coffset)", RxnVol)", 1000)"

not
cell2.Offset(0, 4) = "=Quotient ("=Quotient ("=POWER (WHV8Eamp, "=WHV8Int-coffset")", RxnVol"), 1000"

Cheers, cheers
 

arg123

New Member
Joined
Jun 8, 2016
Messages
26
I have the equation working out except it was giving a division by zero error. One of the cells I was calling to was dim'd a Range, dim'd an Integer, dim'd a Long, etc... And it never worked out. Because, I'm assuming, the value of the cell is 0.015. Well, As much as I need that to be vairable, I just couldn't get it. Decimal didn't work for me and I'm new to dim'ing as a variable and setting it equal to a number of places with cDec. But I figured I'll leave the 0.015 as is for now and make changes to the number as needed.

Til then, this worked out:

Each variable was dim'd as long except for the looped variables which were dim'd as Range -- its a lot of extra lines I'm sure doesn't need to be here, unless someone wants to see it.

VBA Code:
For Each cell In StdRng
    If cell.Value <> "" Then
        For Each cell2 In trgts
            If cell2.Value = "WH-V-8" And cell2.Offset(0, 1) <> "Undetermined" Then
            cell2.Offset(0, 4).Value = (WHV8Eamp ^ ((WHV8Int - cell2.Offset(0, 1).Value)) / 0.015) / 1000
            ElseIf cell2.Value = "WH-V-9" And cell2.Offset(0, 1) <> "Undetermined" Then
            cell2.Offset(0, 4).Value = (WHV9Eamp ^ ((WHV9Int - cell2.Offset(0, 1).Value)) / 0.015) / 1000
            ElseIf cell2.Value = "WH-V-10" And cell2.Offset(0, 1) <> "Undetermined" Then
            cell2.Offset(0, 4).Value = (WHV10Eamp ^ ((WHV10Int - cell2.Offset(0, 1).Value)) / 0.015) / 1000
            ElseIf cell2.Value = "WH-V-1" And cell2.Offset(0, 1) <> "Undetermined" Then
            cell2.Offset(0, 4).Value = (WHV1Eamp ^ ((WHV1Int - cell2.Offset(0, 1).Value)) / 0.015) / 1000
            ElseIf cell2.Value = "WH-V-3" And cell2.Offset(0, 1) <> "Undetermined" Then
            cell2.Offset(0, 4).Value = (WHV3Eamp ^ ((WHV3Int - cell2.Offset(0, 1).Value)) / 0.015) / 1000
            ElseIf cell2.Value = "WH-V-15" And cell2.Offset(0, 1) <> "Undetermined" Then
            cell2.Offset(0, 4).Value = (WHV15Eamp ^ ((WHV15Int - cell2.Offset(0, 1).Value)) / 0.015) / 1000
            ElseIf cell2.Value = "WH-V-16" And cell2.Offset(0, 1) <> "Undetermined" Then
            cell2.Offset(0, 4).Value = (WHV16Eamp ^ ((WHV16Int - cell2.Offset(0, 1).Value)) / 0.015) / 1000
            ElseIf cell2.Value = "WH-V-11" And cell2.Offset(0, 1) <> "Undetermined" Then
            cell2.Offset(0, 4).Value = (WHV11Eamp ^ ((WHV11Int - cell2.Offset(0, 1).Value)) / 0.015) / 1000
            Else
            End If
        Next
   End If
Next
 
Solution

Forum statistics

Threads
1,141,857
Messages
5,709,027
Members
421,605
Latest member
Danny_G

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