Issues with my Do Until Code

[Heyitslopes]

Good morning i'm having issues with a line in my code and or i'm not using it right.

Everything seems to be working except the bolded line of code. It brings in the correct data from the other sheets and it sums them but it just copies the data in row 3, column 6 all the way till end of my Do until and not each matching cell.

I'm essentially trying to do a Do until last row in Column 4 if it and if it is true pull the matching cell data onto the sheet

Dim row As Integer
row = 3
Do Until activesheet.Cells(row, 4).Value = ""
activesheet.Cells(row, 6).Formula = activesheet.Cells(row, 4).Value * activesheet.Cells(row, 5).Value
activesheet.Cells(row, 7).Formula = "=SUM('Instructions:End'!E4)"
activesheet.Cells(row, 8).Formula = activesheet.Cells(row, 4).Value * activesheet.Cells(row, 7).Value
activesheet.Cells(row, 9).Formula = activesheet.Cells(row, 8).Value / activesheet.Cells(row, 6).Value
row = row + 1
Loop

Any Ideas?

 

[Rick Rothstein]

Good morning i'm having issues with a line in my code and or i'm not using it right.

Everything seems to be working except the bolded line of code. It brings in the correct data from the other sheets and it sums them but it just copies the data in row 3, column 6 all the way till end of my Do until and not each matching cell.

I'm essentially trying to do a Do until last row in Column 4 if it and if it is true pull the matching cell data onto the sheet

Dim row As Integer
row = 3
Do Until activesheet.Cells(row, 4).Value = ""
activesheet.Cells(row, 6).Formula = activesheet.Cells(row, 4).Value * activesheet.Cells(row, 5).Value
activesheet.Cells(row, 7).Formula = "=SUM('Instructions:End'!E4)"
activesheet.Cells(row, 8).Formula = activesheet.Cells(row, 4).Value * activesheet.Cells(row, 7).Value
activesheet.Cells(row, 9).Formula = activesheet.Cells(row, 8).Value / activesheet.Cells(row, 6).Value
row = row + 1
Loop

Any Ideas?

You are not changing the value of activesheet.Cells(row, 4).Value anywhere within your loop so its value remains the same... that means your Do Until test will always be what it was at the beginning of the loop... so, if you don't update that cell during the execution of the loop, then there is nothing that will stop the loop.

 

[Joe4]

You are not changing the value of activesheet.Cells(row, 4).Value anywhere within your loop so its value remains the same... that means your Do Until test will always be what it was at the beginning of the loop... so, if you don't update that cell during the execution of the loop, then there is nothing that will stop the loop.

Rick,
Did you miss the

row=row+1

line at the bottom of the code, just before the loop line?

It looks like the loop is running down the 4th column until it encounters a blank cell. That *SHOULD* work, as least as far as stopping the loop goes.

This simple Test (a stripped down variation of their code) seems to work/confirm that.

Sub Test()

    Dim row As Integer
    row = 3

    Do Until ActiveSheet.Cells(row, 4).Value = ""
        row = row + 1
    Loop
    
    MsgBox "Last line of data in 4th column is " & row - 1
    
End Sub

 

[Rick Rothstein]

Rick,
Did you miss the

row=row+1

line at the bottom of the code, just before the loop line?

I sure did.

It looks like the loop is running down the 4th column until it encounters a blank cell. That *SHOULD* work, as least as far as stopping the loop goes.

I agree which probably means the cells contain an invisible character (space or non-breaking space). My bet is on the non-breaking space due possibly from the column being copied from a webpage perhaps.

 

[Heyitslopes]

Is there another way I can achieve this then? I'm just trying to have the code search column D4 down until that last row of data and if data in those cells exists then
pull the sum of each cell in column E in every sheet and show that total in it's corresponding cell on a totals sheet.

 

[wideboydixon]

The code you have will show the same value in column G all the way down the sheet as it's the same formula each time. Are you trying to fetch different data from each row in the other sheet? Something like this?

activesheet.Cells(row, 7).Formula = "=SUM('Instructions:End'!E" & CStr(row + 1) & ")"

?

WBD

 

[Heyitslopes]

Yes thank you the is exactly was I was looking for, how does the & CStr(row + 1) & ")" part of the code work?

 

[wideboydixon]

& is used for string concatenation. CStr converts the argument to a string. row + 1 = 4 to start with and then increments each time. So we're concatenating the following:

"=SUM('Instructions:End'!E"
CStr(row + 1) = CStr(3 + 1) = CStr(4) = "4"
")"

In the first row processed this will set the formula to "=SUM('Instructions:End'!E4)"
On the next row it will be "=SUM('Instructions:End'!E5)"

And so on down the column.

Hope that makes sense.

WBD