PDA

View Full Version : Solved: If Esle..If VBA



gimli
04-23-2010, 08:00 AM
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
04-23-2010, 08:15 AM
Here are two options.
Using if and else if
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


Using Select Case

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

mdmackillop
04-23-2010, 08:19 AM
Have you seen this (http://www.vbaexpress.com/kb/getarticle.php?kb_id=1054)?

gimli
04-23-2010, 09:11 AM
AaaHaaahh! Ok..now I got it :banghead:

Thanks much


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

mdmackillop
04-23-2010, 09:25 AM
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.


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

gimli
04-23-2010, 10:39 AM
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.



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

mdmackillop
04-23-2010, 11:09 AM
Try this, but it does rely on on the Event being triggered

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

gimli
04-23-2010, 11:33 AM
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...

:banghead:

gimli
04-23-2010, 11:45 AM
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.