Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Manipulating data
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Manipulating data
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" wrote: 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) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Manipulating data
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 "Bob Bridges" wrote: 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" wrote: 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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help With Manipulating Data | Excel Discussion (Misc queries) | |||
Help with manipulating data in excel? | Excel Discussion (Misc queries) | |||
Manipulating data from a portal (web) | Excel Programming | |||
Need help using VBA to compare data in Excel columns, then manipulating data | Excel Programming | |||
Manipulating Data in a row | Excel Programming |