Modifying a loop - need help understanding changes

[murphlax636]

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.

 

[SkipVought]

Hi,

"If mybook.Worksheets("Summary").Cells(I,[red]A[/red]).Value= "By Project:" Then"

What is the value of b][red]A[/red][/b].

Amybe you intended...

If mybook.Worksheets("Summary").Cells(I,[b][red]"A"[/red][/b]).Value= "By Project:" Then

Skip,

_{To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white]}

 

[murphlax636]

Thanks for the reply. I actually copied the code wrong and there were quotation marks around the A. I think the problem lies within the projectAllocationStep = (I + 2) - 14. I simply cannot figure it out.

 

[fumei]

I = 50 to 32 represents Ascii characters. I assumed they were the row numbers and that the step was counting backwards.

They are NOT row numbers. They are just....numbers. Nothing but numbers. What you do with the numbers is up to you, but they are just numbers.

50 does not mean Row 50, or 32 mean Row 32. You can (and are) using them to point to rows, but the numbers themselves are only numbers.

For I = 36 To 22 Step (-1)
If mybook.Worksheets("Summary").Cells(I,A).Value= "Project Cost/ Allocation" Then
projectAllocationStep = (I + 2) - 14

ONLY means:

If mybook.Worksheets("Summary").Cells(36,A).Value= "Project Cost/ Allocation" Then
projectAllocationStep = (36 + 2) - 14

If mybook.Worksheets("Summary").Cells(35,A).Value= "Project Cost/ Allocation" Then
projectAllocationStep = (35 + 2) - 14

etc etc, until I = 22

That's it.

faq219-2884

Gerry
My paintings and sculpture

 

[murphlax636]

Skip and Gerry,

Thank you very much for your help, it is much appreciated!