After further looking at what you posted, is there a way to look only at the
rows I need to examine on sheet16 row 3, row 21, row 39 etc?

--
Pete Blackburn - words to live by:
"Don''t ever let somebody tell you. You can''t do something.You got a
dream,You gotta protect it." Edited Quote from the Pursuit Of Happiness








"Dave Peterson" wrote:

You're looking at A1:BC231 in Sheet16, right?

If A1 matches a value in sheet4 V3:V34, then you plop V into B1. But then when
your loop gets to B1, it looks for a match between that V and the values in
V3:V34 of sheet4. This doesn't make sense to me. If V3:V34 contains a V, then
all of Row 1 (B1:BD1) will end up with V's.

Maybe you wanted to check every other column in A1:BC231. Check the odd number
columns and plop the V's into the even number columns???

And instead of looping and doing all those comparisons, you could use excel's
=match() to see if there's a match.

So...

If those assumptions are ok...

Option Explicit
Sub testme03()

Dim FirstCol As Long
Dim LastCol As Long
Dim iCol As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim myListRng As Range
Dim res As Variant 'could be an error

With Sheet4
Set myListRng = .Range("v3:V34")
'or if your data is empty after row 34, you can
'use the last used cell in that column
Set myListRng = .Range("V3", .Cells(.Rows.Count, "V").End(xlUp))
End With

With Sheet16
FirstCol = .Range("A1").Column
LastCol = .Range("bc1").Column
FirstRow = 1
LastRow = 231

'skip every other column
For iCol = FirstCol To LastCol Step 2
For iRow = FirstRow To LastRow
res = Application.Match(.Cells(iRow, iCol).Value, myListRng, 0)
If IsError(res) Then
'not found, empty out that cell???
.Cells(iRow, iCol + 1).Value = ""
Else
.Cells(iRow, iCol + 1).Value = "V"
End If
Next iRow
Next iCol
End With
End Sub

ps.

this line:
Dim cell, vcell As Range
actually declares vcell as a range, but cell as a variant.

You could use multiple lines:
Dim cell As Range
Dim vcell As Range

or a single line:
dim cell as range, vcell as range



default105 wrote:

Excel 2k

How does excel vba handle for next loop value from a range? When it looks
at the range V3:V34, does it start at V3 then V4 then V5 etc... The
Example below is a snippet of the code I have and I would like to shorten the
runtime to be the most efficient. If it does start at the top of the range
then I assume that the isempty() function will always work. If not would you
please explain the best way to do this. This is used on the worksheet change
event to locate days on a schedule and mark the appropriate cell. This sheet
changes by the year entered, that is why I have to evaluate the range. I
hope this is enough information to answer this question. Much "thanks" in
advance.
Dim cell, vcell As Range
For Each cell In Sheet16.Range("A1:BC231")
For Each vcell In Sheet4.Range("V3:V34")
If IsEmpty(vcell) = True Then
Exit For
End If
If cell.Value = vcell.Value Then


Sheets(cell.Parent.Name).Range(cell.Offset(1, 0).Address(False, False)) = "V"
Exit For
End If
Next vcell
Next vcell
--
Pete Blackburn - words to live by:
"Don''t ever let somebody tell you. You can''t do something.You got a
dream,You gotta protect it." Edited Quote from the Pursuit Of Happiness

--

Dave Peterson