Replace multiple "If Then" with "For" loop

[checkltout]

Can anyone please tell me how to use "for" loop for this?

Please Login or Register  to view this content.

[checkltout]

This is what I did:

Please Login or Register  to view this content.

It only shows error message for prem2, but not for prem3 and prem4.

[HaHoBe]

Hi, checkltout,

Your posts do not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

Ciao,
Holger

[checkltout]

Sorry bout that. Edited my post already.

[Sc0tt1e]

I'm not sure your logic works??

If a premium is 50,000 then it fails the <> 50,000 but meets all the other conditions (<> 20,000, <> 25,000 etc).

You may want to rethink the logic (maybe if premium = 20,000 or 25,000 and so on then "do something positive" else msgbox "Error: Enter a valid premium")???

You could then loop it with a prem & "i"

i = 1-4

[Richard Buttrey]

Hi,

Why have you chosen to use VBA when it at least seems possible that a standard Data validation could probably achieve the same result?

[Sc0tt1e]

I would try something along the lines of this (haven't a clue if it will work but this is where I would start).

Dim i as long
i = 1-4

If IsMissing (Prem & "i") Then
Prem & "i" = ""
Exit Sub

If Prem & "i" = 20000 or 25000 or 50000 or 100000 or 150000 or 200000 or 250000 Then
Exit Sub

Else

MsgBox "Error: Enter a valid premium"

Next i

[checkltout]

If a premium is 50,000 then it fails the <> 50,000 but meets all the other conditions (<> 20,000, <> 25,000 etc).

Yeah since I'm using AND function, the premium has to meet all conditions in order for the error message to appear. If the premium is 50,000, then it fails one of the conditions. What you're saying is the OR function I think..?

[checkltout]

Hi,

Why have you chosen to use VBA when it at least seems possible that a standard Data validation could probably achieve the same result?

Well I need to create a function in Excel to fix the premium amounts in a string. So if I type in the function a premium amount, it will change the premium amount in the string to what I want. Also I have never used data validation, so maybe data validation could do that as well.

[checkltout]

If IsMissing (Prem & "i") Then
Prem & "i" = ""
Exit Sub

I tried this but it says Expected: expression when I typed Prem & "i" = "". Sorry I'm a beginner in VBA so please bear with me.