Manipulating data

[Alfredo_CPA]

Any ideas how to solve this?
I have a database like this (starting in column A):

Date Ref # G/L Desc Amount Desc
5/1/2009 11000 ap 10 ma
5/1/2009 21020 act 20 rf
5/1/2009 21022 fs (30) pz
5/2/2009 41000 or (5) na
5/2/2009 41000 lk 6 lo
5/2/2009 21020 cr 1 co
5/2/2009 21022 ty (2) je
5/3/2009 76140 wt 10 aa
5/3/2009 21029 pn (10) pa

I'm working in excel 2003
The database has all the days of the month (sorted by day)
The number of rows per day can be different

My data after running the macro should look like this:
Date Ref # G/L Desc Amount Desc
5/1/2009 R 5/1/2009 3 11000 ap ma 10 ma
5/1/2009 R 5/1/2009 3 21020 act rf 20 rf
5/1/2009 R 5/1/2009 3 21022 fs pz (30) pz
5/2/2009 R 5/2/2009 4 41000 or na (5) na
5/2/2009 R 5/2/2009 4 41000 lk lo 6 lo
5/2/2009 R 5/2/2009 4 21020 cr co 1 co
5/2/2009 R 5/2/2009 4 21022 ty je (2) je
5/3/2009 R 5/3/2009 2 76140 wt aa 10 aa
5/3/2009 R 5/3/2009 2 21029 pn pa (10) pa

What I need in my macro:
For each day: Identify the days that are the same and put that number in the
column "#"
Put a label in the "Ref" column for the same days (e.g. R 5/1/2009)
Concatenate the two descriptions (e.g. ap ma)
I also need this: the sum of every individual day (column amount) shold be
zero. If an error is done by the person sending the information the amount
will be different than zero, in that case I need the macro to skip the days
that don't add zero (a manual correction should be done)

Any ideas??

Thanks

 

[Bob Bridges]

I don't understand all these requirements, but some of them are easy enough:

You don't need your macro to fit in the G/L column; the formula
=COUNTIF(C1,RC1) will do it. (That would be something like =COUNTIF(A:A,A2)
in A1 notation, except you'll have to make sure the row is correct.)

If all you need in the Ref column is ="R "&A2, why not just use that instead
of have VBA code do it?

If you want to do the description concatenation in VBA it shouldn't be too
hard. But I don't know what to tell you; that is, I don't know why you don't
simply do it in VBA in that case. Silly question, I realize; what I mean is
"So why don't you? What did you try, and why isn't it working, or what did
it do instead of what you meant it to?" What problem are you experiencing,
in other words?

 

[Alfredo_CPA]

I was working with formulas, but I'm trying to learn more VBA. I became with
this code, probably not the most efficient but it does the work

Thanks

Sub JERF()
If ActiveCell.Column <> 1 Then
MsgBox " You Are In Wrong Column ! (you should be in Column A -
First Day) ", 16, "My Friend"
Else
MyAns = MsgBox("Are you in the first row of the first Day??",
vbYesNo + vbQuestion, "START??")
If MyAns = vbNo Then Exit Sub Else
MyFirstRow = ActiveCell.Row
Selection.End(xlDown).Select
MylastRow = ActiveCell.Row
Range("B" & MyFirstRow & ":" & "B" & MylastRow).Select
Selection.FormulaR1C1 = "=""R "" & RC[-1] & "" LTX"""
Range("L" & MyFirstRow & ":" & "L" & MylastRow).Select
Selection.FormulaR1C1 = "=RC[-7]&"" /"" &RC[-4]"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Cut
ActiveCell.Offset(0, -7).Select
ActiveSheet.Paste
ActiveCell.Offset(0, -4).Select
Do Until IsEmpty(ActiveCell)
MyInitialrow = ActiveCell.Row
MyInitialDate = ActiveCell.Value
Do Until ActiveCell.Offset(1, 0).Value <> MyInitialDate
ActiveCell.Offset(1, 0).Select
Loop
MylastRow = ActiveCell.Row
Range("F" & MyInitialrow & ":" & "F" & MylastRow).Select
If Application.Round(Application.Sum(Selection), 2) = 0 Then
Range("C" & MyInitialrow & ":" & "C" & MylastRow).Select
Selection.FormulaR1C1 = Selection.Rows.Count
End If
Range("A" & MylastRow + 1).Select
Loop
End If
End Sub