PDA

View Full Version : Multiple similar If Statements



Indigenous
07-18-2018, 02:17 AM
If you have many if statements which are similar, would you use "Select Case", another function, or something else?
My code is in progress but looks something like this.
The only variation is the character, which may be "A", "B", "C", etc.



If (CLoc > 0 And InStr(1, TextLeft, "A") <> 0 And CharLeft = "A") Then
Cell.Characters(.SelStart + 1, 0).Insert "Test text"
End If
If (CLoc > 0 And InStr(1, TextLeft, "B") <> 0 And CharLeft = "B") Then
Cell.Characters(.SelStart + 1, 0).Insert "Test text"
End If
If (CLoc > 0 And InStr(1, TextLeft, "C") <> 0 And CharLeft = "C") Then
Cell.Characters(.SelStart + 1, 0).Insert "Test text"
End If
'etc

'Attempt with Case Select
Dim Char As String
Select Case Char
Case "A", "B", "C" 'etc
If (CLoc > 0 And InStr(1, TextLeft, Char) <> 0 And CharLeft = Char) Then
Cell.Characters(.SelStart + 1, 0).Insert "Test text"
End If
Case Else
Msgbox "No"
End Select

georgiboy
07-18-2018, 04:21 AM
Think i would be tempted to create a function like below:

Sub test()
Dim var As Variant
var = Split("A,B,C,D", ",")

For x = 0 To UBound(var)
y = CheckString(Sheet1.Range("A1"), var(x))
Next x

End Sub


Function CheckString(rng As Range, srchStr)
If InStr(1, rng.Value, srchStr) <> 0 Then
rng.Characters(InStr(1, rng.Value, srchStr) + 1, 0).Insert "Test text"
End If
End Function

Hope this helps

Indigenous
07-18-2018, 04:34 AM
Thanks. I will go in the direction of using a function then.

Indigenous
07-18-2018, 05:23 AM
Actually it appears that Select Case can be used. Is there a way to make the code work?

Paul_Hossler
07-18-2018, 06:13 AM
How about something like this?




Option Explicit

Sub test()
Dim i As Long, c As Long, CLoc As Long
Dim s As String


For c = 65 To 90 ' A to Z
s = Chr(c)
If CLoc > 0 Then
If InStr(1, TextLeft, s) <> 0 And charleft = s Then
Cell.Characters(.SelStart + 1, 0).Insert "Test text"
End If
End If
Next c

End Sub

Indigenous
07-18-2018, 06:28 AM
Great, thanks, I'll give this a try too.