PDA

View Full Version : VBA Get a value from a cell and compare to multiple conditions ElseIf



ElGrande
09-18-2017, 05:56 AM
Hi
I'm trying to take a value from a cell give it to a variable and match it against different condisions and when it match give a value to a different variable and put it in a other cell.
Anyone got some idea's?

Function ElseIf_dn()

'Select Sheet1
Sheets("Blad1").Select
Range("G2").Select

kv10 = Cells(7, 2).Value


If kv10 <= "0,6" Then dn10 = 15
ElseIf kv10 <= "4,0" Then dn10 = 22
ElseIf kv10 <= "10,0" Then dn10 = 28
ElseIf kv10 <= "30,0" Then dn10 = 35
ElseIf kv10 <= "65,0" Then dn10 = 42
ElseIf kv10 <= "130,0" Then dn10 = 54


End If


Cells(9, 2).Value = dn10

End Function

SamT
09-18-2017, 06:38 AM
That looks OK to me. Of course there are 99 different ways that would also work.

One Suggestion: Add

Else kv10 = 9999 before the End If

Ah! One more... Make it a Sub, not a Function. Both work, but Subs modify Cells, Functions Return Values.

ElGrande
09-18-2017, 07:28 AM
Thanks.. I tried that but I get a Error Else without If on the first ElseIf row.

Sub ror()


Dim kv10 As Integer
Dim dn10 As Single


Sheets("Blad1").Select
Range("G2").Select

kv10 = Cells(7, 2).Value


If kv10 <= "0,6" Then dn10 = 15
ElseIf kv10 <= "4" Then dn10 = 22
ElseIf kv10 <= "10" Then dn10 = 28
ElseIf kv10 <= "30" Then dn10 = 35
ElseIf kv10 <= "65" Then dn10 = 42
ElseIf kv10 <= "130" Then dn10 = 54
Else: kv10 = 9999
End If


Cells(9, 2).Value = dn10

End Sub

SamT
09-18-2017, 01:25 PM
This is the one line If...Then... Form; Nothing follows it
If kv10 <= "0,6" Then dn10 = 15

Use the multiline Form
If kv10 <= "0,6" Then
dn10 = 15
ElseIf... Then blah blah

ElGrande
09-19-2017, 02:23 AM
It works.. Thanks a lot.. ;-)