VBA if then applicable on range

[BioExcel]

Hi all,

Im quit a noob when it comes to ranges in VBA. I have a code that needs to applied to a range if possible, rather than entering the code I have multiple times.

When cell I13 has a certain text value, J13 must be mandatory. if J13 is not filled in, a messagebox appears. on the other hand, when I13 does not contain that specific text, J13 must be empty. Otherwise a messagebox appears.

I have the following code for this:

If [I13].value ="DRY ICE" and [J13] =vbNullstring Then
MsgBox "aantal is verplicht"
Exit sub
Elseif [I13].value <> "DRY ICE" and [J13] <> vbNullString then
Msgbox "geen DRY ICE"
Exit sub
End if

The code works fine, but should be applicable to range (I13:I31). As I've no idea how to put in the range, I could use some help on this.

 

[VoG]

Maybe like this

Dim i As Long
For i = 13 To 31
If Range("I" & i).Value = "DRY ICE" And Range("J" & i).Value = vbNullString Then
MsgBox "aantal is verplicht"
Exit Sub
ElseIf Range("I" & i).Value <> "DRY ICE" And Range("J" & i).Value <> vbNullString Then
MsgBox "geen DRY ICE"
Exit Sub
End If
Next i

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG28Jul06
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Str [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range("I13:I31")
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Dn.Value = "DRY ICE" And Dn.Offset(, 1).Value = vbNullString [COLOR="Navy"]Then[/COLOR]
        Str = Str & Dn.Address & " / " & "aantal is verplicht" & vbCrLf
    [COLOR="Navy"]ElseIf[/COLOR] Dn.Value <> "DRY ICE" And Dn.Offset(, 1) <> vbNullString [COLOR="Navy"]Then[/COLOR]
        Str = Str & Dn.Address & " / " & "geen DRY ICE" & vbCrLf
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
MsgBox Str
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[BioExcel]

Maybe like this

Dim i As Long
For i = 13 To 31
If Range("I" & i).Value = "DRY ICE" And Range("J" & i).Value = vbNullString Then
MsgBox "aantal is verplicht"
Exit Sub
ElseIf Range("I" & i).Value <> "DRY ICE" And Range("J" & i).Value <> vbNullString Then
MsgBox "geen DRY ICE"
Exit Sub
End If
Next i

Hi Peter, have implemented your code and works fine. Thx for updating my little knowledge on VBA