Possible Macro Solution To My Problem

  • Thread starter Thread starter Robert
  • Start date Start date
R

Robert

There are times when I have to dump financial information from the AS400 to
Excel. When I do, it comes down in the following format:

ACCOUNT/JOB # DESCRIPTION
7618-000-00000 COMMISSIONS
VL02527 MATHEWS GINA
VL02527 MATHEWS GINA
VL02537 MICHAELS MARG
VL02537 MICHAELS MARG
8111-000-00000 DEPRECIATION
APRIL 07 DEPR ENTRY
AUG 07 DEPR ENTRY
DEC 07 DEPR ENTRY
FEB 07 DEPR ENTRY

I need an easy way to move the account # and account name to the first two
columns like this:

7618-000-00000 COMMISSIONS VL02527 MATHEWS GINA
7618-000-00000 COMMISSIONS VL02527 MATHEWS GINA
7618-000-00000 COMMISSIONS VL02537 MICHAELS MARG
7618-000-00000 COMMISSIONS VL02537 MICHAELS MARG
8111-000-00000 DEPRECIATION APRIL 07 DEPR ENTRY
8111-000-00000 DEPRECIATION AUGUST 07 DEPR ENTRY
8111-000-00000 DEPRECIATION DECE 07 DEPR ENTRY
8111-000-00000 DEPRECIATION FEBR 07 DEPR ENTRY

I can do it manually, but there are 5,000+ lines and it takes a lot of time.
Is there any way to automate this?

Thanks.

Robert
 
There a lots of was to do this. I used a method that will run the quickest.
I relied on the fact that the account numbers contain a dash. The macro will
not work properly if the all the account numbers don't have dashes or if
there are dashes on other rows. I don't know any other method of
distiquishing between the acount rows and the other rows.


Sub MoveData()

'add two column
Columns("A:B").Insert
RowCount = 1
Do While Range("C" & RowCount) <> ""
If InStr(Range("C" & RowCount), "-") = 5 Then
Account = Range("C" & RowCount)
Name = Range("D" & RowCount)
Else
Range("A" & RowCount) = Account
Range("B" & RowCount) = Name
End If
RowCount = RowCount + 1
Loop

LastRow = Range("C" & RowCount).End(xlUp).Row
Rows("2:" & LastRow).Sort _
Header:=xlNo, _
key1:=Range("A2"), _
order1:=xlAscending

NewLastRow = Range("A" & RowCount).End(xlUp).Row
FirstDeleteRow = NewLastRow + 1

Rows(FirstDeleteRow & ":" & LastRow).Delete

Columns.AutoFit

End Sub
 
The text you identified as having been dumped into Excel, where is it at... all in one cell, all in Column A, some other arrangement?
 
Very nice Joel! I thin you just need to add this:
Dim lastrow As Long

Anyway, that worked for me...


HTH,
Ryan--
 
Joel,

Thanks for your response. Since I'm a complete novice when it comes to
macros, I've got a really stupid question. Exactly where do I go to apply
your suggestion and can I simply copy and paste it?
 
Back
Top