Need a help of preparing excel macro

[Kamlu]

Hi

In the below data I want to run a kind of macro that will help me get
clearance or output in another column. Column A has invoice numbers with bill
no.s and column B has only bill numbers. In column O there is amount
relating to the bills in positive and negative. I want to use a macro or
formula that will get me outcome in the column as clearance by tallying up
the amounts to the bills. Please see example below

Point 1:
If a bill (column B) has an amount of 5 (column O) next to the column and
the same bill (column B) has a -5 (column O) next to it both in different
rows, the outcome in column Q for both of these amounts should come out as
clearance in both rows.

Column A Column B Column C Column O Column Q
Full Invoice No. Bill No. Invoice No.
Amt Output
GB14474406-523472291 523472291 GB14474406 3171.06 C
GB53656954-523472291 523472291 GB53656954 -3171.06 C
GB14474404-523472856 523472856 GB14474404 3174.11 C
GB53656915-523472856 523472856 GB53656915 -389.27
GB53656999-523472856 523472856 GB53656999 -3174.11 C
GB53657000-523472856 523472856 GB53657000 380.00

Point 2:
For the same data I want the clearance in the column where difference in
amount is less then 10. Please see below example.

Column A Column B Column C Column O Column Q
Full Invoice No. Bill No. Invoice No.
Amt Output
GB53656915-523472856 523472856 GB53656915 -389.27 C
GB53657000-523472856 523472856 GB53657000 380.00 C

Appreciate your help on the above request. Please let me know if you need
futher information.

 

[Sheeloo]

You want to sum for Bill no? That would be Col B, right?
Also do all bills have exactly two rows or can they have one, two or more
rows?

If they have exactly two rows then you can sort them on Bill no. add amounts
in consecutive rows.

Let me know these so that one of us can write the macro for you.

 

[Kamlu]

Hi Sheeloo,

No I want macro to put "C" in column Q where amount match for the same bill
no(Column B) both positive & negative as mentioned below...

There can be 4, 5, and 6 and so on rows for the same bill no..

Please let me know if you need any further info...

 

[Sheeloo]

The macro is given below.

It will write C in Col E if Net Amount is less or equal to 10.
Assumptions:
1. First row has headers
2. Data is sorted on Col B (Bill no.)
3. Sheet with the data is the active sheet when the macro is run

If you want the Net Amount in Col F then clear the comment in the line
'Cells(j, 6).Value = netAmt


_______________
Sub Balance()
Dim i, firstRow, lastRow As Long
Dim amt, netAmt As Double
Dim billNo As String

With ActiveSheet
lastRow = .Cells(.Rows.Count, "B").End(xlUp).Row + 1
End With

Range("E2:F" & lastRow).Clear

billNo = Cells(2, 2).Value
netAmt = Cells(2, 4).Value
startrow = 2

For i = 3 To lastRow
If (billNo = Cells(i, 2).Value) Then
netAmt = netAmt + Cells(i, 4).Value
Else
For j = startrow To (i - 1)
If netAmt <= 10 Then
Cells(j, 5).Value = "C"
End If
'Cells(j, 6).Value = netAmt
Next
netAmt = Cells(i, 4).Value
billNo = Cells(i, 2).Value
startrow = i
End If
Next
End Sub

 

[Chris Bode via OfficeKB.com]

You can do this using a macro
Please follow following steps
1.Right Click toolbar>click control box
2.From control box select a command button and draw it to your sheet
3.Double click the command button to open code window and paste following
codes
Private Sub CommandButton1_Click()
Dim row As Integer, col As Integer
row = 2
col = 2

Dim amt1 As Double, amt2 As Double
amt1 = 0
amt2 = 0

While Sheet1.Cells(row, col).Value <> ""

If Sheet1.Cells(row, col).Value = Sheet1.Cells(row - 1, col).Value
Then
amt1 = CDbl(Trim(Sheet1.Cells(row, col + 2).Value))
amt2 = CDbl(Trim(Sheet1.Cells(row - 1, col + 2).Value))

If Abs(amt1 + amt2) <= 10 Then

Sheet1.Cells(row, col + 3).Value = "C"
Sheet1.Cells(row - 1, col + 3).Value = "C"

End If

End If

row = row + 1
Wend
End Sub

Now you will get the result on clicking the command button

Hope this one works for you

Have a nice time….

Chris

 

[Sheeloo]

Chris,

This will not work as there may be one, two or more than two rows for a Bill
No.

 

[Kamlu]

Hi Sheeloo,

Appreciated your help but I have tried to run script but it is not working.

It stops at "netAmt = Cells(2, 4).Value". Could you please advise.

Best Regards,
Kam.

 

[Sheeloo]

It should work if you have amount in Col D...

Can you upload your file to wikisend and paste the link here or mail it to me?

I have uploaded my test file at http://wikisend.com/download/641694/Book1.xls
You can download it and try it first with my data then after pasting your
data on top of my data...

Remember you need to sort your data...

 

[Kamlu]

Hi Sheeloo,

Thanks for your help. I have checked this Macro & it is giving output but
not to 100%. Can you please give me email ID so I can send you example file.

Kam.

Chris,

This will not work as there may be one, two or more than two rows for a Bill
No.

 

[Sheeloo]

append @hotmail.com to to_sheeloo or click on my name for instructions to
get the mail

Hi Sheeloo,

Thanks for your help. I have checked this Macro & it is giving output but
not to 100%. Can you please give me email ID so I can send you example file.

Kam.

 

[Kamlu]

Hi Sheeloo,

I have sent yo uexample file. Can you please check..

Kam.

append @hotmail.com to to_sheeloo or click on my name for instructions to
get the mail