View Full Version : Writing a loop with an offset and storing value in code while summing

06-13-2014, 05:31 PM
Hello gurus,

I am new to the world of VBA and would greatly appreciate an example of how to loop using an offset, run logic, sum, and paste to another tab. My specific example is as follows:

Tab 1, I define a chart of accounts:

Column 1
Column 2








Tab 2, an amount is defined for column 2.

Column 1 Column 2
100001 $10
100002 $15

I need to write code that will search the serial# in Tab 2, column 1 against Tab 1, column 2. Once it finds the serial #, it needs to search Tab 1, column 1 to find out what this serial # belongs to. In the example above, the ending answer should be Cash = $25.

06-14-2014, 02:56 AM
you can try like

= range("tab1").Find(110001).Offset(,-1).End(xlUp).Value
returns AR
where tab1 is a named range of tab 1
pass a cell value in place of the literal 110001
you can then sum the matching accounts

you could probably also do it by formula, which may be more suitable

Bob Phillips
06-14-2014, 03:51 AM
Why use VBA, why not just use VLOOKUP?