1 Replies - 904 Views - Last Post: 14 September 2012 - 03:42 AM Rate Topic: -----

#1 meatloaf  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 65
  • Joined: 05-September 07

Match, Address, Value confusion probably easy

Posted 13 September 2012 - 12:08 PM

Situation: I need the macro to read each row in Sheet 1, find corresponding Account# in Sheet R, then add the Total# in Sheet 1 to the Total# in Sheet R.
So it's like Sheet R is the total for each account for the year, and Sheet 1 is the total for a certain month that needs to be added to the year. There may be multiples of the same account in Sheet 1, but not in Sheet R.
Sheet R
Column 1(A)=Account#
Column 2(B )=Name
Column 3(C )=2012 Total
Sheet 1
Column 1(A)=Account#
Column 12 (L)=Total

Now my default for all lookups has been to use VLookup, but it doesn't make sense to use here:
VLookup(Sheets("1").Cells(intRow, 2).value, Sheets("R").Columns("A:C"). [here is confusing part because I don't want to return anything]
Then I found online that you should use Match instead, which I have never used before. So I thought okay, find the matching #, get the address and assign as variable and add to that..
My attempt:
Dim addy As String
Dim intRow As Integer
Dim comm As Long, acctcomm As Long
addy = Address(Match(Sheets("1").Cells(intRow, 1).value, Sheets("R").Columns("A:C"), 0)
comm = Range(addy).Offset(0,2).value
comm = comm + acctcomm
Range(addy).Offset(0,2).value = comm

It's just turning out stupid, illogical, and inefficient. Please help!

Is This A Good Question/Topic? 0
  • +

Replies To: Match, Address, Value confusion probably easy

#2 thava  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 180
  • View blog
  • Posts: 1,607
  • Joined: 17-April 07

Re: Match, Address, Value confusion probably easy

Posted 14 September 2012 - 03:42 AM

use the sumIF function
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1