PLEASE HELP??? copy every other cell value to a range of cells

  • Thread starter Thread starter Newbie
  • Start date Start date
N

Newbie

My table data

5/1 5/2
Customer A
Qty1 10 10
Qty2 1 2
------------------------------------------
Balance 9 17

Customer B
Qty1 10 10
Qty2 5 2
----------------------------------------------
Balance 5 13

I am trying to copy and paste a balance formula for each customer group. The
formula is basically Qty1 (Previous cell) - Qty2(Previous cell) + Qty1
(next cell)-Qty2(of next cell).
Example (Customer A's data) the math is:

Qty 1 (for date 5/1) minus Qty 2 (for date 5/1) plus Qty 1(for date 5/2)
minus Qty 2 (for date 5/2). Resulting value=9 for 5/1 (since there is no
previous cell) and 17 for 5/2.

I know how to paste a formula across one row but do not know how to repeat
this for each customer's Balance row.

Help????? I've searched for days on-line for a solution :(((
 
If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.
 
Hi Don!
First off thanks so much for your help!
I think I understand what you are asking--I will email the file to you and
give you the before and after results. I'm not sure what you mean by
"newsgroup and subject line"? Do you mean the title of my request and the
Excel programming category?
Thanks!
 
Option Explicit
Sub SAS_DoFormulas()
Dim lr As Long
Dim i As Double
Dim j As Long
Application.ScreenUpdating = False

lr = Cells(Rows.Count, 1).End(xlUp).Row
For i = 4 To lr Step 3
Cells(i, "c") = Cells(i - 2, "c") - Cells(i - 1, "c")
For j = 4 To Cells(i - 1, Columns.Count).End(xlToLeft).Column
Cells(i, j) = Cells(i, j - 1) + Cells(i - 2, j) - Cells(i - 1, j)
Next j
Next i

Application.ScreenUpdating = True
End Sub
 
Back
Top