Inserting algebraic function into cells depending on variable in range

[novartis1]

Hello all,

I'm trying to insert an algebraic formula into a Range of cells depending on the value of cell("F1"). For instance if cell("F1")=1 then I want to use formula A, if cell("F1")=2 then I want to use formula B, and so on. Currently I have a simple Function set up with If/Then statements, but whenever I change cell("F1") it fails to implement the new formula. Currently the code is in "module 2" I think that I probably need to use

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Application.Intersect(Target, Range("B4")) Is Nothing Then

If Target.Value >= 1 Then
    Application.Run "function"
End If
End If
End Sub

but am unsure as to how, since I can't specify which function to use. My code is as follows.

Function InsertFormula()

    If Range("F1") = 1 Then
        Worksheets("Simethicone Calculation").Range("G12:G21").Formula = "=(((E12*$B$6*30)/($B$7*40*C12))*D12)"
        Worksheets("Simethicone Calculation").Range("H12:H21").Formula = "=(G12/57)*100"
        Worksheets("Simethicone Calculation").Range("E2").Formula = "LBM-040-05 Release"
    ElseIf Range("F1") = 2 Then
        Worksheets("Simethicone Calculation").Range("G12:G21").Formula = "=(((E12*$B$6*50)/($B$7*50*C12))*D12)"
        Worksheets("Simethicone Calculation").Range("H12:H21").Formula = "=(G12/57)*100"
        Worksheets("Simethicone Calculation").Range("E2").Formula = "LBM-040-05 Stability"
    ElseIf Range("F1") = 3 Then
        Worksheets("Simethicone Calculation").Range("G12:G21").Formula = "=(((E12*$B$6*60)/($B$7*40*C12))*D12)"
        Worksheets("Simethicone Calculation").Range("H12:H21").Formula = "=(G12/57)*100"
        Worksheets("Simethicone Calculation").Range("E2").Formula = "LBM-046-04"
        
End Function

I am a novice programmer, so if you could be as simple and specific as possible i would greatly appreciate it.

Thank You in Advance,
Andy

 

[AnotherHiggins]

I don't think you want a User Defined Function. Are you just going to run a macro to populate the cells with formulas?

Instead of a monster IF statement, take a look at Select Case.

Something like

'...
    Select Case Range("F1")
        Case 1
            Range("G12:G21").Formula = "=(((E12*$B$6*30)/($B$7*40*C12))*D12)"
            Range("H12:H21").Formula = "=(G12/57)*100"
            Range("E2").Formula = "LBM-040-05 Release"
        Case 2
            Range("G12:G21").Formula = "=(((E12*$B$6*50)/($B$7*50*C12))*D12)"
            Range("H12:H21").Formula = "=(G12/57)*100"
            Range("E2").Formula = "LBM-040-05 Stability"
    End Select
'...

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[novartis1]

John,
Thanks for replying so quickly. I put this code in "sheet1", i'm not totally sure where to put it, but I keep getting a "Run-time error 1004, application-defined or object-defined error" when i hit the "run sub/userform" (the triangle play button)

Sub SelectCase()
    
    
    Select Case Range("F1").Value
    
        Case 1
        'LBM-040-05 Release
            Range("G12:G21").Formula = "=(((E12*$B$6*30)/($B$7*40*C12))*D12)"
            Range("H12:H21").Formula = "=(G12/57)*100"
            Range("E2").Formula = "LBM-040-05 Release"
        Case 2
        'LBM-040-05 Stability
            Range("G12:G21").Formula = "=(((E12*$B$6*50)/($B$7*50*C12))*D12)"
            Range("H12:H21").Formula = "=(G12/57)*100"
            Range("E2").Formula = "LBM-040-05 Stability"
      
    End Select

End Sub

Any Ideas?

Thanks,
Andy

 

[AnotherHiggins]

The code should be put in a Module rather than in the sheet (Right Click anywhere in the VBAProject and select Insert > Module). But I just copied and pasted the code you posted into the sheet and it works for me....

What line/word does the code stop on when you get the error?

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[novartis1]

The error occurs on the first line of code after "Case 2" even after i put the code in "module 2." I put the code in a new worksheet and it worked fine. I take that to mean that I must be doing something wrong elsewhere in my code, but i'm not sure where, or even where to look. I don't know if it matters, but I already have a select case in my code, if that might be a possible source of the error.

Also this may be jumping the gun, since I can't get the error fixed yet, but the only way i could get it to change between the 2 cases in the new worksheet I made was to hit run from the the VBA editor window after changing "F1". Is there a way that I can just imput the number, hit enter in excel, and have the code execute without needing to go to the VBA window?

Again thank you for all your help, it is greatly appreciated,
Andy

 

[novartis1]

Hi John,

I just wanted to tell you that I got my code working, I think the error was comming from the fact that my sheet was protected, so I put in a sheets.unprotect line and the error resolved itself. I also put in a

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Application.Intersect(Target, Range("F1")) Is Nothing Then
If Target.Value >= 0 Then
    Application.Run "MacroSelectCase"
End If
End If
End Sub

to make the worksheet change automatically. I want to thank you for the suggestion about the select case instead of the If/Then statements, and your taking the time to explain things thoroughly to a novice programmer. Again, Thank you very much, your help was much appreciated.

Andy

 

[combo]

If C1 can be only 1, 2.. and you have fixed formulas, you can consider CHOOSE function instead, without any VBA.

combo