VBA - I need help conceptualizing how to do something

[jardenp]

I'm not sure how to proceed conceptually here and would appreciate the input of the masters. You all are my go-to experts!

I have two sheets:

Sheet "Transactions"

Date	Invoice	Equipment Number	Meter	Cost	Account
1/5/2021	205	100	1000	$250	Oak
1/8/2021	210	100	1500	$1000	Fir
1/12/2021	215	200	3000	$500	Tulip
1/15/2021	220	200	3500	$1200	Oak

Sheet "Use"

Date	Equipment Number	Beginning Meter	Ending Meter	Widget Units Used	Account
1/6/2021	100	1000	1300	350	Fir
1/7/2021	100	1300	1500	220	Beech
1/13/2021	200	3000	3200	250	Tulip
1/14/2021	200	3200	3500	325	Pine

Basically, I have two sets of data that sort of line up but not quite fully. I need to reassign costs of transactions according to the widget units used by an account between the unit's transaction and the unit's previous transactions. The data I have to line up transactions and use are meter readings.

For equipment number 100, it had a $1000 transaction on 1/8/21 that covered 500 meter units since its last transaction. 570 widget units were used between that transaction and the last. So that's $1000 for 570 widget units or $1000/570 per widget unit. Therefore, account Fir should be charged $614.04 (350 widget units @ $1000/570 per widget unit) and account Beech should be charged $385.96 (220 widget units @ $1000/570 per widget unit). Likewise, for equipment number 200. It had a $1200 transaction on 1/15/21 that covered 575 widgets that should result in account Tulip being charged $521.74 (250 widget units @ $1200/575 per widget unit) and Pine being charged $678.26 (325 widget units @ $1200/575 per widget unit).

The end result would be listing of the original invoice and a breakdown of costs per account (maximum of 6 accounts). The two lines from the example transactions would be:

Invoice	Total Cost	Original Account	ReassignedAccount1	ReassignedCost1	ReassignedAccount2	ReassignedCost2	...	ReassignedAccount6	ReassignedCost6
210	$1000	Fir	Fir	$614.04	Beech	$385.96
220	$1200	Oak	Tulip	$521.74	Pine	$678.26

To complicate things, the meter readings in the use data may stradle a couple transactions. For example, an account may use 500 widget units, 300 of which may be part of one transaction and the other 200 part of the following transaction.

I'm not asking for a finished piece of code. I just can't figure out how to match up the two data sets. So far I have that each transaction needs to have a cost per widget unit calculated and then applied to the number of widget units. But I'm not sure which VBA device/technique to use to figure out which transaction(s) apply to a use data line.

Any help would be greatly appreciated!

Thanks!