Combining Fors and Ifs

TheMSsForum.com: The Microsoft Software Forum

Is it possible to combine the following :-

For Each MyCell In Sheets("Current Round Detailed").Range("C10:K10")
If (MyCell.Value > 0 And MyCell.Offset(12).Value = vbNullString) Or
_
(MyCell.Value > 1 And MyCell.Offset(14).Value = vbNullString)
Then

MsgBox "xxxxxxxxx"

Exit Sub
End If
Next MyCell

For Each MyCell In Sheets("Current Round Detailed").Range("C16:K16")
If (MyCell.Value > 0 And MyCell.Offset(17).Value = vbNullString) Or
_
(MyCell.Value > 1 And MyCell.Offset(19).Value = vbNullString)
Then

MsgBox "yyyyyyyyyyy"

Exit Sub
End If
Next MyCell

into something along the lines of :-

If
For Each MyCell In Sheets("Current Round Detailed").Range("C10:K10")
If (MyCell.Value > 0 And MyCell.Offset(12).Value = vbNullString) Or
_
(MyCell.Value > 1 And MyCell.Offset(14).Value = vbNullString)
End If
Next MyCell

Or

For Each MyCell In Sheets("Current Round Detailed").Range("C16:K16")
If (MyCell.Value > 0 And MyCell.Offset(17).Value = vbNullString) Or
_
(MyCell.Value > 1 And MyCell.Offset(19).Value = vbNullString)
End If
Next MyCell
Then "zzzzzzzzzzzzzzzz"

Thanks in advance
Sandy
Top

Re: Combining Fors and Ifs by Mark

Mark
Sat Mar 15 18:34:01 CDT 2008

See if something like this will work out...

Mark

'**************************************************************
Dim row As Long
Dim col As Long

For row = 16 To 20 ' rows 16 to 20
For col = 3 To 11 ' columns C to K
If Cells(row, col).Value > 0 And _
Cells(row, col).Offset(17).Value = vbNullString Then
' Do something here
End If
Next
Next

' Take out the for loop involving the row if you do not need it
' and define "row" to equal whatever you need like this:
' row = 10
' before the for loop





"Sandy" <sandy_stephen@DELETEhotmail.com> wrote in message
news:CBF01954-F28B-4E29-8F2E-F36FD494C3AF@microsoft.com...
> Is it possible to combine the following :-
>
> For Each MyCell In Sheets("Current Round Detailed").Range("C10:K10")
> If (MyCell.Value > 0 And MyCell.Offset(12).Value = vbNullString) Or
> _
> (MyCell.Value > 1 And MyCell.Offset(14).Value = vbNullString)
> Then
>
> MsgBox "xxxxxxxxx"
>
> Exit Sub
> End If
> Next MyCell
>
> For Each MyCell In Sheets("Current Round Detailed").Range("C16:K16")
> If (MyCell.Value > 0 And MyCell.Offset(17).Value = vbNullString) Or
> _
> (MyCell.Value > 1 And MyCell.Offset(19).Value = vbNullString)
> Then
>
> MsgBox "yyyyyyyyyyy"
>
> Exit Sub
> End If
> Next MyCell
>
> into something along the lines of :-
>
> If
> For Each MyCell In Sheets("Current Round Detailed").Range("C10:K10")
> If (MyCell.Value > 0 And MyCell.Offset(12).Value = vbNullString) Or
> _
> (MyCell.Value > 1 And MyCell.Offset(14).Value = vbNullString)
> End If
> Next MyCell
>
> Or
>
> For Each MyCell In Sheets("Current Round Detailed").Range("C16:K16")
> If (MyCell.Value > 0 And MyCell.Offset(17).Value = vbNullString) Or
> _
> (MyCell.Value > 1 And MyCell.Offset(19).Value = vbNullString)
> End If
> Next MyCell
> Then "zzzzzzzzzzzzzzzz"
>
> Thanks in advance
> Sandy

Top

Re: Combining Fors and Ifs by Sandy

Sandy
Thu Mar 20 17:27:47 CDT 2008

Mark
Sorry for delay in responding but I was away for a bit but thank you I
managed with your guidance.
Sandy

"Mark Ivey" <wmivey6311NOSPAM@hotmail.com> wrote in message
news:B78F4D6E-166B-4912-A127-B74E0E0A9D50@microsoft.com...
> See if something like this will work out...
>
> Mark
>
> '**************************************************************
> Dim row As Long
> Dim col As Long
>
> For row = 16 To 20 ' rows 16 to 20
> For col = 3 To 11 ' columns C to K
> If Cells(row, col).Value > 0 And _
> Cells(row, col).Offset(17).Value = vbNullString Then
> ' Do something here
> End If
> Next
> Next
>
> ' Take out the for loop involving the row if you do not need it
> ' and define "row" to equal whatever you need like this:
> ' row = 10
> ' before the for loop
>
>
>
>
>
> "Sandy" <sandy_stephen@DELETEhotmail.com> wrote in message
> news:CBF01954-F28B-4E29-8F2E-F36FD494C3AF@microsoft.com...
>> Is it possible to combine the following :-
>>
>> For Each MyCell In Sheets("Current Round Detailed").Range("C10:K10")
>> If (MyCell.Value > 0 And MyCell.Offset(12).Value = vbNullString)
>> Or _
>> (MyCell.Value > 1 And MyCell.Offset(14).Value = vbNullString)
>> Then
>>
>> MsgBox "xxxxxxxxx"
>>
>> Exit Sub
>> End If
>> Next MyCell
>>
>> For Each MyCell In Sheets("Current Round Detailed").Range("C16:K16")
>> If (MyCell.Value > 0 And MyCell.Offset(17).Value = vbNullString)
>> Or _
>> (MyCell.Value > 1 And MyCell.Offset(19).Value = vbNullString)
>> Then
>>
>> MsgBox "yyyyyyyyyyy"
>>
>> Exit Sub
>> End If
>> Next MyCell
>>
>> into something along the lines of :-
>>
>> If
>> For Each MyCell In Sheets("Current Round Detailed").Range("C10:K10")
>> If (MyCell.Value > 0 And MyCell.Offset(12).Value = vbNullString)
>> Or _
>> (MyCell.Value > 1 And MyCell.Offset(14).Value = vbNullString)
>> End If
>> Next MyCell
>>
>> Or
>>
>> For Each MyCell In Sheets("Current Round Detailed").Range("C16:K16")
>> If (MyCell.Value > 0 And MyCell.Offset(17).Value = vbNullString)
>> Or _
>> (MyCell.Value > 1 And MyCell.Offset(19).Value = vbNullString)
>> End If
>> Next MyCell
>> Then "zzzzzzzzzzzzzzzz"
>>
>> Thanks in advance
>> Sandy
>
Top

Re: Combining Fors and Ifs by Mark

Mark
Fri Mar 21 07:15:21 CDT 2008

Glad to help...

Mark

"Sandy" <sandy_stephen@DELETEhotmail.com> wrote in message
news:6A6DED19-D841-48DC-B74F-8772A776EF14@microsoft.com...
> Mark
> Sorry for delay in responding but I was away for a bit but thank you I
> managed with your guidance.
> Sandy
>
> "Mark Ivey" <wmivey6311NOSPAM@hotmail.com> wrote in message
> news:B78F4D6E-166B-4912-A127-B74E0E0A9D50@microsoft.com...
>> See if something like this will work out...
>>
>> Mark
>>
>> '**************************************************************
>> Dim row As Long
>> Dim col As Long
>>
>> For row = 16 To 20 ' rows 16 to 20
>> For col = 3 To 11 ' columns C to K
>> If Cells(row, col).Value > 0 And _
>> Cells(row, col).Offset(17).Value = vbNullString Then
>> ' Do something here
>> End If
>> Next
>> Next
>>
>> ' Take out the for loop involving the row if you do not need it
>> ' and define "row" to equal whatever you need like this:
>> ' row = 10
>> ' before the for loop
>>
>>
>>
>>
>>
>> "Sandy" <sandy_stephen@DELETEhotmail.com> wrote in message
>> news:CBF01954-F28B-4E29-8F2E-F36FD494C3AF@microsoft.com...
>>> Is it possible to combine the following :-
>>>
>>> For Each MyCell In Sheets("Current Round Detailed").Range("C10:K10")
>>> If (MyCell.Value > 0 And MyCell.Offset(12).Value = vbNullString)
>>> Or _
>>> (MyCell.Value > 1 And MyCell.Offset(14).Value = vbNullString)
>>> Then
>>>
>>> MsgBox "xxxxxxxxx"
>>>
>>> Exit Sub
>>> End If
>>> Next MyCell
>>>
>>> For Each MyCell In Sheets("Current Round Detailed").Range("C16:K16")
>>> If (MyCell.Value > 0 And MyCell.Offset(17).Value = vbNullString)
>>> Or _
>>> (MyCell.Value > 1 And MyCell.Offset(19).Value = vbNullString)
>>> Then
>>>
>>> MsgBox "yyyyyyyyyyy"
>>>
>>> Exit Sub
>>> End If
>>> Next MyCell
>>>
>>> into something along the lines of :-
>>>
>>> If
>>> For Each MyCell In Sheets("Current Round Detailed").Range("C10:K10")
>>> If (MyCell.Value > 0 And MyCell.Offset(12).Value = vbNullString)
>>> Or _
>>> (MyCell.Value > 1 And MyCell.Offset(14).Value = vbNullString)
>>> End If
>>> Next MyCell
>>>
>>> Or
>>>
>>> For Each MyCell In Sheets("Current Round Detailed").Range("C16:K16")
>>> If (MyCell.Value > 0 And MyCell.Offset(17).Value = vbNullString)
>>> Or _
>>> (MyCell.Value > 1 And MyCell.Offset(19).Value = vbNullString)
>>> End If
>>> Next MyCell
>>> Then "zzzzzzzzzzzzzzzz"
>>>
>>> Thanks in advance
>>> Sandy
>>
Top