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

[arg123]

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.

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:

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!

 

[arg123]

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]

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.

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