lanhao
06-14-2006, 03:50 PM
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
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