PDA

View Full Version : Problem with loop & variable values



AKK
07-28-2009, 09:40 AM
I am writing a macro that copies data into cells from the same cells in a different workbook & worksheets with the same names. Originally, I wrote a macro that did exactly that & worked perfectly:


Private Sub CommandButton1_Click()

Dim strSheet As String
Dim ix As Integer
Dim iy As Integer
Dim iz As Integer

For iz = 3 To 5
For iy = 3 To 7
For ix = 1 To 5

strSheet = Sheets(iz).Name
Workbooks("TestWorkBook2.xls").Sheets(strSheet).Cells(iy,
ix)= "=[TestWorkBook1.xls]" & strSheet & "!R" & iy & "C" &
ix

Next ix
Next iy
Next iz

End Sub


Then, I modified it a little, because I only wanted it to copy values to cells that were a certain color and empty:


Private Sub CommandButton1_Click()

Dim strSheet As String
Dim ix As Integer
Dim iy As Integer
Dim iz As Integer

For iz = 3 To 5
For iy = 1 To 100
For ix = 1 To 100

strSheet = Sheets(iz).Name

If Cells(iy, ix).Interior.ColorIndex = 34 And Cells
iy, ix).Value = "" Then
Workbooks("TestWorkBook2.xls").Sheets(strSheet).Cells
iy, ix) = "=[TestWorkBook1.xls]" & strSheet & "!R" &
iy & "C" & ix
End If

Next ix
Next iy
Next iz

End Sub


Somehow, this has caused a problem with looping through the values in the variables. It will execute perfectly on the first sheet, but when it moves to sheet 2 and resets iy to 1, ix moves on to 101 instead of stopping at 100 & restarting at 1. Can anyone see why this is?

Thanks for your help!

Benzadeus
07-28-2009, 09:52 AM
I did not get that error...

When ix moves to 101, it continues with 102, 103 and so on?

AKK
07-28-2009, 10:05 AM
Yep, just keeps going from 101, 102, 103, etc.

Bob Phillips
07-28-2009, 10:22 AM
Nor me, ix goes to 101 as it will but then it moves to the next level as the counter is exceeded.

AKK
07-28-2009, 10:51 AM
Hm...I guess I could be interpretting things wrong. (With 3 counters, it's not very efficient to step through the process and keep track of what is changing when.)

In any case, all the appropriate cells on the 3rd sheet (first one to run) get the right values from their counterparts in the other workbook, but even if the counters are stepping through the right cells on the other two pages, the values aren't showing up in the cells. If it's not a problem with the counters, I'm confused about how it could work on one page and not the next two (with no error messages).

AKK
07-28-2009, 10:59 AM
So I just editted the code to start on sheet 4 instead of 3, and only run through 10 rows and 15 columns:


Private Sub CommandButton1_Click()

Dim strSheet As String
Dim ix As Integer
Dim iy As Integer
Dim iz As Integer

For iz = 4 To 5
For iy = 1 To 10
For ix = 1 To 15

strSheet = Sheets(iz).Name
If Cells(iy, ix).Interior.ColorIndex = 34 And Cells(iy, ix).Value = "" Then
Workbooks("TestWorkBook2.xls").Sheets(strSheet).Cells(iy, ix) = "=[TestWorkBook1.xls]" & strSheet & "!R" & iy & "C" & ix
End If

Next ix
Next iy
Next iz

End Sub


All the appropriate cells in sheets 4 & 5 get the right values from the other workbook, but cells I4 - I9 on both sheets (all white) got values entered into them from the other sheet as well. Now I'm even more confused...

Paul_Hossler
07-28-2009, 11:13 AM
I'm not sure if it'll help, but try to qualify


If Cells(iy, ix).Interior.ColorIndex = 34 And Cells(iy, ix).Value = "" Then

with an explict WS reference.

As is, I believe it will use the active sheet all the time, and I don't know if you want that

Paul

Benzadeus
07-28-2009, 11:17 AM
The white cells are getting values because you have to fully qualify the cells in the logical test:

If Sheets(strSheet).Cells(iy, ix).Interior.ColorIndex = 34 And Sheets(strSheet).Cells(iy, ix).Value = "" Then
Workbooks("TestWorkBook2.xls").Sheets(strSheet).Cells(iy, ix) = "=[TestWorkBook1.xls]" & strSheet & "!R" & iy & "C" & ix
End If


Otherwise the macro will get the interior's colorindex of the active sheet when the macro ran.

Benzadeus
07-28-2009, 11:18 AM
Double post - sorry.