Help with a loop
 

I am using Excel 97 and struggling to get the following
loop to work properly.

Can anyone assist me with where I am going wrong?

I want to pickup dates from one sheet and copy them also
as dates into another sheet but for some reason it
recognises that there is something in the cell but doesn't
pickup it's value!!

I have defined

StartDate & LastDate as Date
TotalDays as integer
Holiday as string

THE CODE
..............................................


Let A = 7
Let B = 7
Let C = 7
Let D = 7

' search for all sheets and get values
For Each asheet In ActiveWorkbook.Sheets

If asheet.Visible = True Then
sheetname = asheet.Name
Worksheets(sheetname).Activate


With ActiveSheet

EmployeeName = Cells(4, 1).Value
OfficeName = Cells(2, 2).Value
DeptName = Cells(2, 5).Value
FloorNo = Cells(3, 3).Value
DaysAvailable = Cells(31, 9).Value

If strOffice = "ALL" Then GoTo All
If OfficeName < strOffice Then GoTo Continue

All:
Do Until IsEmpty(asheet.Cells(A, 2))

StartDate = Cells(A, 2).Value
LastDate = Cells(B, 3).Value
TotalDays = Cells(C, 10).Value
HolDetails = Cells(D, 1).Value

' SUB TO MOVE VALUES TO LEAVE TABLE
DoMoveToLeaveTableDates

Let A = A + 1
Let B = B + 1
Let C = C + 1
Let D = D + 1

Loop

End With

Continue:

End If

Let A = 7
Let B = 7
Let C = 7
Let D = 7

Next asheet

.................................................. ..


Mark

Help with a loop
 

The obvious answer is that if OfficeName < strOffice, then your code to
copy the dates is never executed.

the second possible problem could be that the copying is being done in
DoMoveToLeaveTableDates

It is possible that this sub does not have visibility of your variables or
for some other reason does not successfully copy the values. Since you
only show a snippet of your code, and don't show this sub, it is impossible
to tell where the actual problem may lie. It could be as simple that
OfficeName and strOffice do not agree because one has an upper case letter
and the other doesn't.

Just above that IF test, you could put up a msgbox

msgbox OfficeName & " = " & strOffice & " = " & OfficeName = strOffice

or use the debugging tools to step through the code and watch the variable
values.

--
Regards,
Tom Ogilvy

"Mark" wrote in message
...
I am using Excel 97 and struggling to get the following
loop to work properly.

Can anyone assist me with where I am going wrong?

I want to pickup dates from one sheet and copy them also
as dates into another sheet but for some reason it
recognises that there is something in the cell but doesn't
pickup it's value!!

I have defined

StartDate & LastDate as Date
TotalDays as integer
Holiday as string

THE CODE
.............................................


Let A = 7
Let B = 7
Let C = 7
Let D = 7

' search for all sheets and get values
For Each asheet In ActiveWorkbook.Sheets

If asheet.Visible = True Then
sheetname = asheet.Name
Worksheets(sheetname).Activate


With ActiveSheet

EmployeeName = Cells(4, 1).Value
OfficeName = Cells(2, 2).Value
DeptName = Cells(2, 5).Value
FloorNo = Cells(3, 3).Value
DaysAvailable = Cells(31, 9).Value

If strOffice = "ALL" Then GoTo All
If OfficeName < strOffice Then GoTo Continue

All:
Do Until IsEmpty(asheet.Cells(A, 2))

StartDate = Cells(A, 2).Value
LastDate = Cells(B, 3).Value
TotalDays = Cells(C, 10).Value
HolDetails = Cells(D, 1).Value

' SUB TO MOVE VALUES TO LEAVE TABLE
DoMoveToLeaveTableDates

Let A = A + 1
Let B = B + 1
Let C = C + 1
Let D = D + 1

Loop

End With

Continue:

End If

Let A = 7
Let B = 7
Let C = 7
Let D = 7

Next asheet

.................................................. .


Mark