Using loop and sum functions with Vlookup

Question

I've got a macro that essentially searches column C in Sheet1 for the value "Rec" and copies the corresponding value in column D, then pastes it into the last open cell in column B of Sheet2. It does just what it is supposed to do and is as follows:

Sub FindPasteGSVInNextCell()

Worksheets("Sheet2").Activate

Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Value = _
WorksheetFunction.VLookup("Rec", Sheet1.Range("C2:H25"), 2, False)

End Sub

I now want the code, instead of just searching for a single "Rec" value, to search for all rows with "Rec" in column C and to sum up all of their corresponding values in column D, then place that sum into Sheet2.

I am assuming that I need some kind of Do Until loop or something, but I am not exactly sure how to format it... I am a beginner with VBA, so any help would be greatly appreciated.

You could simply use a sumif equation on the second sheet. — Scott Craner
I agree with @ScottCraner this can be done easily with a SUMIF formula, no need for vba. Is there a specific reason you're trying to use VBA for this task? — tigeravatar
I think it needs to incorporate VBA because it's going to be a big spreadsheet that updates daily when new data populates Sheet1. It has to run the data sum for ~40 different categories and import each of them into the respective day on Sheet2. So I don't think a basic sumif would work because the master data set that it is computing from is going to change daily if that makes sense.. Might be more complicated, but in the long run I think vba would make it a lot easier--just need to figure out this looping stuff.. — Willarci3

Scott Craner Scott Craner · Accepted Answer · 2015-09-24T19:46:58

vlookup will not work as it will continue to only grab the first instance of "Rec".

On Sheet 2 list all the possible categories in column A then in column B1 put

 = sumif(Sheet1!C:C,A1,Sheet1!D:D)

then copy down. This will Get you the totals by category.

If you want to use VBA, you will still need a list of categories setup somewhere, either hard coded or listed somewhere that you can loop through.

If your list was in column A on Sheet2 then you would:

dim ws as worksheet
set ws = Worksheets("Sheet2")
For each i in ws.range(ws.Range("A1"),ws.Range("A1").offset(xldown)).Cells
    i.offset(,1) = WorksheetFunction.Sumif(Worksheets("Sheets1").Range("C:C"), _
        i,Worksheets("Sheets1").Range("D:D"))
next i

Using loop and sum functions with Vlookup

1 Answers