For..Next..Loop

[default105]

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

 

[Dave Peterson]

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]

Actually I am looking for a cell that contains a date(s) that is/are in
v3:v34. the sheet with A1:BC231 the dates are populated only after the year
is entered, but based on the year the date is never in the same place, so I
have to search for it then mark the appropriate cell with a V for vacation.
These dates only appear once on that sheet. If there is a way to use a
formula to populate another cell(which I don't think is possible) I will
gladly do that. Does that help you better understand the sheet?
--
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

 

[default105]

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]

Maybe...

Is there a pattern to the rows you need? I'm not sure what etc would mean here.

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?

 

[default105]

Yes, starting at row 3 and every 18 rows thereafter (3, 21, 39, 57 ....to
219) Each row that needs searched to a maximum column of BD.

I do appreciate the help and information. Excel is its own beast. I have
mostly dealt VBA with Access which in my opinion is much easier(probably
because I am familiar with it.)

--
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]

With Sheet16
FirstCol = .Range("A1").Column
LastCol = .Range("bd1").Column
FirstRow = 3
LastRow = 219

'skip every other column
For iCol = FirstCol To LastCol Step 2
For iRow = FirstRow To LastRow Step 18

....

 

[default105]

Don't beat on me yet, please but I have some questions. I think you
misunderstood what some of the value were. I noticed you are looking for V in
sheet4 range, this actually contains a range of the dates needed for
vacation. ie. 1/9/2009 in v3, 1/16/2009 in v4 and so on. Sheet16 is the
printed book schedule with employees names in a column and the dates in the
row above the employees. The cells in the column to the right of the
employees names are empty to be marked with V for vacation, P for personal or
H for holiday. That is why I need to find the date then offset the row to
set the V to mark vacation for that employee. Does that help. If you wish I
can send you the xls so you can see better what it is if you wish. It is
really hard to explain without confusing the person reading it?

Thanks again

I have posted the code you submitted with some changes and comments. I
don't see how this examines each of the cells in the range in sheet4 with the
current cell in sheet16. I have debugged this numerous times and res is
always error 2042

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)) 'not
looking for V but different dates are in this range
End With

With Sheet16

FirstCol = .Range("E1").Column
LastCol = .Range("ba1").Column
FirstRow = 3
LastRow = 219

'skip every other column
'skip every other column
For iCol = FirstCol To LastCol Step 1
For iRow = FirstRow To LastRow Step 18
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 + 1, iCol).Value = "V"
End If
Next iRow
Next iCol
End With
--
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]

No thanks to the workbook.

The code I suggested didn't look for V's. It tried to match the value in
sheet16 to whatever was in sheet4 column V.

But I don't see how matching up a date to a list of dates would apply to every
name in that list. Does everyone take the same vacation days at your
business????

And it looks like your code does that offset by one row that you want. Doesn't
it work?

 

[default105]

No it does not work error 2042 for res, no the dates are always different. I
could not find anything in the help file but it looks like this is set up to
set the last row with the one that has a v in it. I am probably wrong but
that is why I am asking. That is why I just used a specified range that is
longer then anyone has in days available in vacation days. I would love to
programmically see what cells have values in column V but how do you set it
to look at Not empty.

Set myListRng = .Range("V3", .Cells(.Rows.Count, "V").End(xlUp))

Ok, this will be a long explanation so please bear with me. Workbook
worksheets are as follows. Sheet1 name 4 Rotating, Sheet2 name 5 Rotating,
Sheet3 name 4 rot 64wk view, sheet4 - sheet14 are personalized to one
individual employee only, sheet4 pete, sheet5 tom, etc... sheet15 name ISO
Week, sheet16 book scheduling (a report basically) Sheet17 Holidays.
Sheet4 -sheet14 are identical except for one cell, it defines an offset from
sheet 1 which is the current people in the department. It was setup like
this so if on change had to be made to many formula you can just copy and
paste to the other sheets and just adjust the one cell value.
That being said v3:34 is for the employee on sheet4 for the year 2009,
w3:w34 is for that employee for the year 2010, This worksheet is set up to
the year 2025. On sheet15 I use a select case on the worksheet change to
decide what to run and I clearcontents the range of cells on sheets16
everytime the year on sheet15 is changed This is the sheet (sheet15, e1) that
drives the entire workbook. When you enter in the years in e1 it calculates
all the weeks and days for that year on sheet15. sheet1 is static and has
the employees shift schedules, startup days(special days designated by color)
project schedules(designated by color) Every year is laid out until the year
2025. Sheet4 -sheet14 use formulas and one udf to populate a range of cells
identify shift and special conditions again by week(why you ask, I wondered
the same but some employees wanted to have a personal printout for easy
reference) So I utilized this to use to store vacation schedules on these
sheets.
Sheet16 is just a report with 53 printable pages for a hard copy book for
the department. Which I want to programmically populate.

My goal is to have it to all be as mistake proof as possible. The code I
was using and what I submitted was a snippet, it is still being built up be I
had a working structure however I just did not seem proper or efficient,
which you so kindly pointed out. I need one working smoothly before bitting
of the hole thing.

 

[Dave Peterson]

Did you change the way res was declared.

It was declared:
dim res as variant
for a reason. That way if there is no match, you can check it with
iserror(res).

I don't think the code suggested is gonna work very well.

I think I'd try to drive the range to check based on the name of the user (and
the name of the sheet).

You may be able to use:

dim YearCol as long
with worksheets(thecellthatcontainsthename.value)
yearcol = year(cellwithdate.value) - 2009 + .range("W1").column
Set myListRng = .Range(.cells(3,yearcol), .Cells(.Rows.Count,
yearcol).End(xlUp))
end with

But the cell with the name has to match the worksheet name exactly.
Tom Smith
won't match
Tom Smith

 

[default105]

I found the problem. It seems this is tied to excel with the
Application.match and a date in a cell. You have to use the following
res = Application.Match(CLng(.Cells(iRow, iCol).Value), PBlackburnVacRng, 0)
Clng was all it needed to work however it will differently error so you have
to resume next so it will work without starting the debugger when it reaches
a range that is not in long format. Thanks for a point in the right
direction. The code cycles much faster.
--
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]

application.match() doesn't need the "on error resume next" line.

application.worksheetfunction.match() does.