Solved: Summing variable ranges

[lanhao]

Hi again, I have to say thank you for every bit of help that i have gotten so far on this, was given a different curveball todya with this 'wonderful' project of mine.

What i need to do, is basically have it take a look at the date that is pulled onto the spreadsheet in question, and then come up with a sum for each of three columns.

I have to figure out how to get it to find the range of the values in each of the columns (specifically, the columns are B, C and D) and sum them, so I think I need to use the R1C1 method for telling VBA what to do.. I also need to have it find the last row of data put in.

As always, thanks again for taking the time to review this one.

Code included below:

Sub Retrieve()
Set sht1 = Worksheets("Rep Stats")
Set sht2 = Worksheets("QCInfo")
Dim i As Integer 'row counter for the data page "QCInfo" aids in finding the last row with info
Dim j As Integer 'row counter for the "Rep Stats" puts data in the correct row
i = 1
j = 10

Application.ScreenUpdating = False
' ClearAll Macro
' Macro recorded 5/10/2006 by Andy Lewis
'
Range("A10:H51").Select
Range("A10").Activate
Selection.ClearContents
Application.GoTo Reference:=Range("A10"), Scroll:=False

'Keep doing until we have searched through the whole data page "Comments"
Do While IsEmpty(sht2.Cells(i, 1).Value) = False
If sht1.Range("A3") = sht2.Range("A" & i) And sht1.Range("D3") <= sht2.Range("B" & i) _
And sht1.Range("E3") >= sht2.Range("B" & i) Then 'this places the data if the names match
sht1.Range("A" & j) = sht2.Range("B" & i).Value
sht1.Range("B" & j) = sht2.Range("C" & i).Value
sht1.Range("C" & j) = sht2.Range("D" & i).Value
sht1.Range("D" & j) = sht2.Range("E" & i).Value
sht1.Range("F" & j) = sht2.Range("F" & i).Value
sht1.Range("G" & j) = sht2.Range("G" & i).Value
j = j + 1
End If
i = i + 1

Loop
Application.ScreenUpdating = True
End Sub

[acw]

Hi

Can you put up a small sample file to show what the data looks like, and give an idea of what you are trying to achieve.


Tony

[mdmackillop]

Not totally clear, but maybe a partial solution

Sub Totals()
TotC = Application.WorksheetFunction.Sum(Range(Cells(1, 3), Cells(Rows.Count, 3).End(xlUp)))
TotD = Application.WorksheetFunction.Sum(Range(Cells(1, 4), Cells(Rows.Count, 4).End(xlUp)))
'etc.
End Sub

[lanhao]

Ok, I got the first two lines in, but for some reason it is not allowing me to get it set up for the third row, which is the calculation from (well use Col C and D in this case as the exmaple was put up there as). It keeps on locking itself up, any ideas?

[mdmackillop]

Can you post what you have now?

[lanhao]

Ok, here is the updated code, for soem reason it is not counting the last row of information in the sum formula.

Sub Retrieve()
Set sht1 = Worksheets("Rep Stats")
Set sht2 = Worksheets("QCInfo")
Dim i As Integer 'row counter for the data page "QCInfo" aids in finding the last row with info
Dim j As Integer 'row counter for the "Rep Stats" puts data in the correct row
i = 1
j = 10
Application.ScreenUpdating = False
' ClearAll Macro
' Macro recorded 5/10/2006 by Andy Lewis
'
Range("A10:H51").Select
Range("A10").Activate
Selection.ClearContents
Range("B6:D6").Select
Selection.ClearContents
Application.GoTo Reference:=Range("A10"), Scroll:=False
Do While IsEmpty(sht2.Cells(i, 1).Value) = False 'keep doing until we have searched through the whole data page "Comments"

If sht1.Range("A3") = sht2.Range("A" & i) And sht1.Range("D3") <= sht2.Range("B" & i) _
And sht1.Range("E3") >= sht2.Range("B" & i) Then 'this places the data if the names match
sht1.Range("B6") = Application.WorksheetFunction.Sum(Range(Cells(9, 2), Cells(Rows.Count, 2).End(xlUp)))
sht1.Range("C6") = Application.WorksheetFunction.Sum(Range(Cells(9, 3), Cells(Rows.Count, 3).End(xlUp)))
sht1.Range("D6") = "=If(RC[-2]=0,0,RC[-2]/RC[-1]%)"
sht1.Range("A" & j) = sht2.Range("B" & i).Value
sht1.Range("B" & j) = sht2.Range("C" & i).Value
sht1.Range("C" & j) = sht2.Range("D" & i).Value
sht1.Range("D" & j) = sht2.Range("E" & i).Value
sht1.Range("F" & j) = sht2.Range("F" & i).Value
sht1.Range("G" & j) = sht2.Range("G" & i).Value
j = j + 1
End If
i = i + 1

Loop
Application.ScreenUpdating = True
End Sub

[mdmackillop]

Your code is writing the last values after the total is counted. There seems to be no reason why the total is inside the loop. Move the lines
sht1.Range("B6") = Application.....
sht1.Range("C6") = Application.....
after the Loop command.
Regards
MD

[lanhao]

thanks so much for the help on that, I still have to get the syntax down for this stuff. At least I didn't have the accidnet of having my excel go into an infinte loop again ^^;

I appreciate it alot :)