PDA

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



Marcx013
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


Cash




100001



100002



100003


AR




110001



110002




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.

westconn1
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

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