Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find loop doesn't loop | Excel Discussion (Misc queries) | |||
How to loop | Excel Discussion (Misc queries) | |||
if & Loop | Excel Discussion (Misc queries) | |||
do..loop | Excel Discussion (Misc queries) | |||
Do Loop | Excel Discussion (Misc queries) |