murphlax636
Technical User
Hey,
This loop has me stumped. I've been at work trying to modify it and simply manipulating the numbers seems to get me nowhere. I would like some more understanding of what exactly is happening and also any tips on my new loop. Thank you in advance for any help you can give me.
This is the original loop
'difference between description row and corresponding allocation row for projects
'projectAllocationStep = 27
For I = 50 To 32 Step (-1)
If mybook.Worksheets("Summary").Cells(I,A).Value= "By Project:" Then
projectAllocationStep = (I + 1) - 18
End If
Next
I understand that the loop goes through the individual spreadsheets to populate the data. However, I don't get if
I = 50 to 32 represents Ascii characters. I assumed they were the row numbers and that the step was counting backwards. The cells were merged in the original spreadsheets but this year they are unmerged and less of them. Also, the "By Project:" cell is a blank this year. The 18 is the row number in which the data begins. This year, because of the unmerged cells, the data begins in row 14. To accommodate the changes I changed the loop to
'difference between description row and corresponding allocation row for projects
'projectAllocationStep = 17
For I = 36 To 22 Step (-1)
If mybook.Worksheets("Summary").Cells(I,A).Value= "Project Cost/ Allocation" Then
projectAllocationStep = (I + 2) - 14
End If
Next
This change does not seem to have much of an effect. However, the data in my basebook is fine until the last row in some tabs in which the cell values are shifted down for some reason. One thing I did not understand was further down the code the End If statement included:
End If
projectAllocationStep = projectAllocationStep - 1
Next
I removed that statement for my new loop but was unsure if I had to include it with a new number.
Please let me know if there are important details I am missing out. I have never edited a macro and I was proud that it somewhat worked. Thanks again for the help.
This loop has me stumped. I've been at work trying to modify it and simply manipulating the numbers seems to get me nowhere. I would like some more understanding of what exactly is happening and also any tips on my new loop. Thank you in advance for any help you can give me.
This is the original loop
'difference between description row and corresponding allocation row for projects
'projectAllocationStep = 27
For I = 50 To 32 Step (-1)
If mybook.Worksheets("Summary").Cells(I,A).Value= "By Project:" Then
projectAllocationStep = (I + 1) - 18
End If
Next
I understand that the loop goes through the individual spreadsheets to populate the data. However, I don't get if
I = 50 to 32 represents Ascii characters. I assumed they were the row numbers and that the step was counting backwards. The cells were merged in the original spreadsheets but this year they are unmerged and less of them. Also, the "By Project:" cell is a blank this year. The 18 is the row number in which the data begins. This year, because of the unmerged cells, the data begins in row 14. To accommodate the changes I changed the loop to
'difference between description row and corresponding allocation row for projects
'projectAllocationStep = 17
For I = 36 To 22 Step (-1)
If mybook.Worksheets("Summary").Cells(I,A).Value= "Project Cost/ Allocation" Then
projectAllocationStep = (I + 2) - 14
End If
Next
This change does not seem to have much of an effect. However, the data in my basebook is fine until the last row in some tabs in which the cell values are shifted down for some reason. One thing I did not understand was further down the code the End If statement included:
End If
projectAllocationStep = projectAllocationStep - 1
Next
I removed that statement for my new loop but was unsure if I had to include it with a new number.
Please let me know if there are important details I am missing out. I have never edited a macro and I was proud that it somewhat worked. Thanks again for the help.