Reading a cell based on a vairable

[cqmman]

Hello,

I am trying to do something really simple, but getting a bit stuck.

I have sheet1 with a unique list of names, and sheet2 with a list of
the same names (not unique) and a number next to then. I would like to
go down the list in sheet1, and put a total next to the name, from all
the entries in sheet2 which match.

The first thing I want to do, is read the contents of cell A1 into a
variable. I need to do it in a loop though, so I need to reference the
cell via this.

How can I do something like:

variable = cell( a, rowNum)
(where "variable" and "rowNum" are variables?)


Thanks

 

[Don Guillett]

maybe simpler approach

Sub sumifothersheet()
Set colc = Sheets("sheet2").Columns("c")
Set cold = Sheets("sheet2").Columns("d")

For Each c In Range("c3:c5")'unique list in sh1
c.Offset(, 6).Value = Application.SumIf(colc, c, cold)
Next c
End Sub

 

[dranon]

Hello,

I am trying to do something really simple, but getting a bit stuck.

I have sheet1 with a unique list of names, and sheet2 with a list of
the same names (not unique) and a number next to then. I would like to
go down the list in sheet1, and put a total next to the name, from all
the entries in sheet2 which match.

The first thing I want to do, is read the contents of cell A1 into a
variable. I need to do it in a loop though, so I need to reference the
cell via this.

How can I do something like:

variable = cell( a, rowNum)
(where "variable" and "rowNum" are variables?)

I have no idea why you want to do the first thing you want to do.
Just skip right to the answers:

Put this in Cell B1 of sheet1:

=SUMPRODUCT((Sheet2!$A$1:$A$1000=A1)*(Sheet2!$B$1:$B$1000))

Adjust the 1000 to be at least as high as the number of rows in
sheet2.

Copy down from B1 to as many rows in sheet1 that have names in column
A.

 

[cqmman]

maybe simpler approach

Sub sumifothersheet()
Set colc = Sheets("sheet2").Columns("c")
Set cold = Sheets("sheet2").Columns("d")

For Each c In Range("c3:c5")'unique list in sh1
   c.Offset(, 6).Value = Application.SumIf(colc, c, cold)
Next c
End Sub

Thanks, that was much easy that what I was going to do!