Solved: If Esle..If VBA

[gimli]

Hey all,

I attached a file that has a simple if statement in a cell. If someone wouldnt mind converting that to VBA (IF/ELSEIF?)that would be great. I am a noob and just been looking around for samples. If I get a few samples I can build on them.

If anyone has their own samples to upload that would also be great.

thanks much

[mbarron]

Here are two options.
Using if and else if
[VBA]Sub ifs()
If Range("J9") <= 10 Then
Selection = 1
ElseIf Range("J9") > 10 And Range("J9") <= 20 Then
Selection = 2
Else
Selection = 3
End If
End Sub
[/vba]

Using Select Case
[vba]
Sub cases()
Select Case Range("J9")
Case Is <= 10
Selection = 1
Case 10 To 20
Selection = 2
Case Else
Selection = 3
End Select
End Sub
[/VBA]

[mdmackillop]

Have you seen this?

[gimli]

AaaHaaahh! Ok..now I got it

Thanks much

[VBA]
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Exits
Application.EnableEvents = False
If Target.Address = "$J$9" Then
Call ifs
End If
Exits:
Application.EnableEvents = True
End Sub
Sub ifs()
If Range("J9") <= 10 Then
Range("D10") = 1
ElseIf Range("J9") > 10 And Range("J9") <= 20 Then
Range("D10") = 2
Else
Range("D10") = 3
End If
End Sub

[/VBA]

[mdmackillop]

Hi Gimli,
Try passing values to a sub to carry out the tasks. It means the same code can function for any passed ranges, as in this strange scenario.

[vba]
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Exits
Application.EnableEvents = False
If Target.Address = "$J$9" Then
Call ifs(Target, Range("D10"))
Else
Call ifs(Target, Range("E10"))
End If
Exits:
Application.EnableEvents = True
End Sub

Sub ifs(Test As Range, Result As Range)
If Test <= 10 Then
Result = 1
ElseIf Test > 10 And Test <= 20 Then
Result = 2
Else
Result = 3
End If
End Sub

[/vba]

[gimli]

MD

Not sure what that link is you posted to all that code..

Below is the code from a sheet thats working..the only thing missing is if cell F13 is activated I also want SUB cortneyroxs to activate. Right now it activates when combo box TOLCHOICE changes. I tried the remed out code but doesnt work. Not sure how to nest multible calls.


[vba]
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Exits
Application.EnableEvents = False
If Target.Address = "$F$9" Then
Call Brittneyisnothot
End If
'If Target.Address = "$F$13" Then this doesnt work
'Call Cortneyroxs
'End If

Exits:
Application.EnableEvents = True
End Sub
Private Sub TOLCHOICE_Change()
Call Cortneyroxs
End Sub

Sub Cortneyroxs()
If Range("F13") >= 24 And Range("BX15") <> "A" Then
Range("E13") = "text here"
ElseIf Range("BX$15") = "B" Then
Range("E13") = Range("$CF$16")
ElseIf Range("BX$15") = "C" Then
Range("E13") = Range("$CF$17")
ElseIf Range("BX$15") = "D" Then
Range("E13") = Range("$CQ$16")
ElseIf Range("BX$15") = "E" Then
Range("E13") = Range("$CQ$17")
ElseIf Range("BX$15") = "F" Then
Range("E13") = Range("$CQ$18")
ElseIf Range("BX$15") = "G" Then
Range("E13") = Range("$DC$14")
Else: Range("E13") = " "
End If
End Sub
[/vba]

[mdmackillop]

Try this, but it does rely on on the Event being triggered
[VBA]
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Exits
Application.EnableEvents = False
Select Case Target.Address
Case "$F$9"
Call Brittneyisnothot
Case "$F$13"
Call Cortneyroxs
End Select
Exits:
Application.EnableEvents = True
End Sub

[/VBA]

[gimli]

F9 is triggering sub like its soposed to
F13 is not triggering the sub

plus im getting a runtime error 1004 when using the dropdown box to select...

[gimli]

Ok..got it now. Retyped in the code instead of copy paste and it works..lol

not sure why but...VBA is buggy or I am...probably me.

thanks for the help.