Formula Chooser

[sassriverrat]

Hey Guys-

I have this If Then piece I've put together that's supposed to choose one of two formulas for a cell, based on what the previous sheet is, and then input that formula into the cell. It does this for two different cells. Currently I have something wrong because both cells just come back equaling zero.

Thanks for the help!
Note- It's in the middle of another macro that builds the whole sheet, thus no end sub etc etc

Dim Form1 As Double    Dim Form2 As Double
    Dim Form3 As Double
    Dim Form4 As Double
    Dim ws As Worksheet
    Set ws = ActiveSheet.Previous
    Form1 = Range("N7").FormulaR1C1 = "=IF(R[22]C[10]="""",R[1]C-PrevSheet(R[1]C),R[22]C[10])"
    Form2 = Range("N7").FormulaR1C1 = "=R[1]C"
    Form3 = Range("N8").FormulaR1C1 = "=IF(R[22]C[10]="""",IF(R[12]C[12]=""Yes"",(IF(PrevSheet(R[3]C)='Voyage Specifics'!R[3]C[-11],'Voyage Specifics'!R[3]C[-11],PrevSheet(R[3]C))),R[1]C[4]+'Voyage Specifics'!R[4]C[-11]),R[22]C[10])"
    Form4 = Range("N8").FormulaR1C1 = "=IF(R[22]C[10]="""",'Voyage Specifics'!R[3]C[-11],R[22]C[10]"
    
    If ws.Name Like "Noon*" Then
    ActiveSheet.Range("N7") = Form1
    ActiveSheet.Range("N8") = Form3
    Else: ActiveSheet.Range("N7") = Form2
          ActiveSheet.Range("N8") = Form4
    End If

 

[sassriverrat]

bump

 

[shg]

  Const sFrm1       As String = "=IF(R[22]C[10]="""",R[1]C-PrevSheet(R[1]C),R[22]C[10])"
  Const sFrm2       As String = "=R[1]C"
  Const sFrm3       As String = "=IF(R[22]C[10]="""",IF(R[12]C[12]=""Yes"",(IF(PrevSheet(R[3]C)='Voyage Specifics'!R[3]C[-11],'Voyage Specifics'!R[3]C[-11],PrevSheet(R[3]C))),R[1]C[4]+'Voyage Specifics'!R[4]C[-11]),R[22]C[10])"
  Const sFrm4       As String = "=IF(R[22]C[10]="""",'Voyage Specifics'!R[3]C[-11],R[22]C[10]"

  If LCase(ActiveSheet.Previous.Name) Like "noon*" Then
    Range("N7") = sFrm1
    Range("N8") = sFrm3
  Else
    Range("N7") = sFrm2
    Range("N8") = sFrm4
  End If

 

[sassriverrat]

Thank you.


Do you know how to make a formula that will add "A1" from every Sheet named "Noon" (plus index number) (i.e. noon+Noon2+Noon3, etc)?

thanks!

 

[shg]

I don't know what that means.

 

[sassriverrat]

So two things-

One- your coding piece above is exactly as I have it and I've gone so far as to have "sFrm#" all the way through sFrm12. However, this is hanging up on sFrm4 each time- all of the others work. See below in my highlighted code where it's hanging up.

Two- as for my other question- If I wanted a function or macro that takes the number in cell "A1" from every sheet named Noon (Noon, Noon2, Noon3, etc) and added it in a formula SO, it would be somewhat as Follows. On Sheet "Arrival", the formula in cell "A1" would be =TotalAdder("A1")+B1 where TotalAdder is my macro/function, it's adding A1 from all sheets with the name "Noon" in it, and it's adding B1 from the activesheet ("Arrival"). Make sense?

Thanks!

  Const sFrm1       As String = "=IF(R[22]C[10]="""",R[1]C-PrevSheet(R[1]C),R[22]C[10])"  Const sFrm2       As String = "=R[1]C"
  Const sFrm3       As String = "=IF(R[22]C[10]="""",IF(R[12]C[12]=""Yes"",(IF(PrevSheet(R[3]C)='Voyage Specifics'!R[3]C[-11],'Voyage Specifics'!R[3]C[-11],PrevSheet(R[3]C))),R[1]C[4]+'Voyage Specifics'!R[4]C[-11]),R[22]C[10])"
  Const sFrm4       As String = "=IF(R[22]C[10]="""",'Voyage Specifics'!R[3]C[-11],R[22]C[10]"

  If LCase(ActiveSheet.Previous.Name) Like "noon*" Then
    Range("N7") = sFrm1
    Range("N8") = sFrm3
  Else
    Range("N7") = sFrm2
    Range("N8") = sFrm4
  End If