Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...
MS Office Forum / Excel / New Users / December 2006

Tip: Looking for answers? Try searching our database.

Excel formatting

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Sam - 07 Dec 2006 15:55 GMT
Could someone please help with a macro or any other suggestion to
format an
Excel sheet. Basically data is downloaded from a bank every week and
the
size of the file can vary but the formatting is constant. That is each
record is 7 lines. So first record is line1-7, second record is line
8-14,
third record is line 15-21 and so on.

I need to format this data and basically I can record a macro for one
data set that is for the record on line 1 to 7. My question is how can
I set it to replicate so that it replciates for line 8-14 and then
15-21 and so on. The source formatting remains the same except that the
total length of the file( no of records) changes every week.

Thanks very much for your help.

Regards

Sam
Reply to this Message
David McRitchie - 07 Dec 2006 16:04 GMT
Hi Sam,
I would suggest using  Quicken  instead of Excel.

But  I think if you look at your choices for download, there
should be   CSV  or (Comma Separated Values)   choice
available,  or might even be marked as Excel.
---
HTH,
David McRitchie, Microsoft MVP - Excel
My Excel Pages:  http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:        http://www.mvps.org/dmcritchie/excel/search.htm

> Could someone please help with a macro or any other suggestion to
> format an
[quoted text clipped - 16 lines]
>
> Sam
Reply to this Message
Sam Commar - 07 Dec 2006 17:10 GMT
David

Thanks so much. I am looking but cant find the relevant info. Can you please
point me to the relevant info on the site.
> Hi Sam,
> I would suggest using  Quicken  instead of Excel.
[quoted text clipped - 28 lines]
>>
>> Sam
Reply to this Message
jlepack - 07 Dec 2006 17:27 GMT
Post your macro and I'm sure someone (me, if I get there first) will
make it automate.

If you're ambitious, all you need is to make it lopp and jump down 8
lines and do it all again.

But again, if you gave a sample of data and the expected output, or
even just your macro, then it can be fixed up in a jiff.

Cheers,
Jason Lepack

> David
>
[quoted text clipped - 32 lines]
> >>
> >> Sam
Reply to this Message
Sam Commar - 07 Dec 2006 18:31 GMT
Please find attached the macro.

I want it to run for every record. My destination record set is 2 lines
while my source record set is 6 lines.

Sub Macro4()
'
' Macro4 Macro
' Macro recorded 12/7/2006 by DSC
'
' Keyboard Shortcut: Ctrl+Shift+J
'
   Sheets("Sheet2").Select
   Range("A1").Select
   ActiveCell.FormulaR1C1 = "Document"
   Range("C1").Select
   Sheets("Sheet1").Select
   Range("G6").Select
   Selection.Copy
   Sheets("Sheet2").Select
   ActiveSheet.Paste
   Sheets("Sheet1").Select
   Range("F1").Select
   Application.CutCopyMode = False
   Selection.Copy
   Sheets("Sheet2").Select
   Range("D1").Select
   ActiveSheet.Paste
   Sheets("Sheet1").Select
   ActiveWindow.ScrollColumn = 2
   ActiveWindow.ScrollColumn = 3
   ActiveWindow.ScrollColumn = 4
   Range("P1").Select
   Application.CutCopyMode = False
   Selection.Copy
   Sheets("Sheet2").Select
   Range("H1").Select
   ActiveSheet.Paste
   Range("A2").Select
   Application.CutCopyMode = False
   ActiveCell.FormulaR1C1 = "Transaction"
   Range("C2").Select
   Sheets("Sheet1").Select
   Range("G3").Select
   Selection.Copy
   Sheets("Sheet2").Select
   ActiveSheet.Paste
   Sheets("Sheet1").Select
   Range("G4").Select
   Application.CutCopyMode = False
   Selection.Copy
   Sheets("Sheet2").Select
   Range("F2").Select
   ActiveSheet.Paste
   Range("H1").Select
   Application.CutCopyMode = False
   Selection.Copy
   Range("K2").Select
   ActiveSheet.Paste
   Sheets("Sheet1").Select
   Range("G2").Select
   Application.CutCopyMode = False
   Selection.Copy
   Sheets("Sheet2").Select
   Range("M2").Select
   ActiveSheet.Paste
   Columns("K:K").Select
   Application.CutCopyMode = False
   Selection.NumberFormat = "0.00"
   Columns("H:H").Select
   Selection.NumberFormat = "0.00"
End Sub

> Post your macro and I'm sure someone (me, if I get there first) will
> make it automate.
[quoted text clipped - 46 lines]
>> >>
>> >> Sam
Reply to this Message
jlepack - 07 Dec 2006 19:27 GMT
Try this:

Public Sub bankToMe()
' BankToMe() - coded by Jason Lepack on 12/07/2006
' Moves data from "sheet1" to a new sheet in the way specified by Sam
Commar
' Each record on "sheet1" is 6 lines with a space between, or 7 lines
' Each record on "output" is 2 lines with a space between

   ' two worksheets and a range on each worksheet
   Dim wsA As Worksheet, wsB As Worksheet
   Dim rA As Range, rB As Range

   ' initialize
   Set wsA = ActiveWorkbook.Sheets("sheet1")
   Set wsB = ActiveWorkbook.Sheets.Add
   wsB.Name = "Output"
   Set rA = wsA.Range("A1")
   Set rB = wsB.Range("A1")

   Application.ScreenUpdating = False

   ' loop until there isn't a next record
   Do While Not rA.Offset(0, 5).Value = ""
       rB.Value = "Document"
       rB.Offset(0, 2).Value = rA.Offset(5, 6) ' G6 -> C1
       rB.Offset(0, 3).Value = rA.Offset(0, 5).Value ' F1 -> D1
       rB.Offset(0, 15).Value = rA.Offset(0, 7).Value ' H1 -> P1
       rB.Offset(1, 0).Value = "Transaction"
       rB.Offset(1, 2).Value = rA.Offset(2, 6).Value ' G3 -> C2
       rB.Offset(1, 5).Value = rA.Offset(3, 6).Value ' G4 -> F2
       rB.Offset(1, 10).Value = rA.Offset(0, 15).Value ' P1 -> K2
       rB.Offset(1, 12).Value = rA.Offset(1, 6).Value ' G2 -> M2
       Set rA = rA.Offset(7, 0) ' this is the number of lines in a
record in "sheet1"
       Set rB = rB.Offset(3, 0) ' this is the number of lines in a
record in "output"
   Loop
   ' format columns H and K as numbers
   Set rB = Range("H:H,K:K")
   rB.NumberFormat = "0.00"

   ' clean up
   Set rA = Nothing
   Set rB = Nothing
   Set wsA = Nothing
   Set wsB = Nothing
   Application.ScreenUpdating = True
End Sub

> Please find attached the macro.
>
[quoted text clipped - 119 lines]
> >> >>
> >> >> Sam
Reply to this Message
Sam - 08 Dec 2006 14:32 GMT
Thanks a billion. I willtry this today with the latest bank output

Thanks again. Your time is truly apprecaited.
Thanks
Sam
> Try this:
>
[quoted text clipped - 169 lines]
> > >> >>
> > >> >> Sam
Reply to this Message
jlepack - 08 Dec 2006 17:03 GMT
Make sure you reply here with how it went.  There are a few questions
that you'll have that readily jump to mind, so let me know.

Cheers,
Jason Lepack

> Thanks a billion. I willtry this today with the latest bank output
>
[quoted text clipped - 174 lines]
> > > >> >>
> > > >> >> Sam
Reply to this Message
Sam Commar - 09 Dec 2006 11:05 GMT
Jason

I tested this and for the most part the data formatted fine however it
stopped after the first record and did not loop on.
Again every record is 6 lines.

Thanks for your help.

Sam
> Make sure you reply here with how it went.  There are a few questions
> that you'll have that readily jump to mind, so let me know.
[quoted text clipped - 193 lines]
>> > > >> >>
>> > > >> >> Sam
Reply to this Message
jlepack - 09 Dec 2006 22:41 GMT
So then there is no space between the records so then this should work.
Public Sub bankToMe()
' BankToMe() - coded by Jason Lepack on 12/07/2006
' Moves data from "sheet1" to a new sheet in the way specified by Sam
Commar
' Each record on "sheet1" is 6 lines with a space between, or 7 lines
' Each record on "output" is 2 lines with a space between

   ' two worksheets and a range on each worksheet
   Dim wsA As Worksheet, wsB As Worksheet
   Dim rA As Range, rB As Range

   ' initialize
   Set wsA = ActiveWorkbook.Sheets("sheet1")
   Set wsB = ActiveWorkbook.Sheets.Add
   wsB.Name = "Output"
   Set rA = wsA.Range("A1")
   Set rB = wsB.Range("A1")

   Application.ScreenUpdating = False

   ' loop until there isn't a next record
   Do While Not rA.Offset(0, 5).Value = ""
       rB.Value = "Document"
       rB.Offset(0, 2).Value = rA.Offset(5, 6) ' G6 -> C1
       rB.Offset(0, 3).Value = rA.Offset(0, 5).Value ' F1 -> D1
       rB.Offset(0, 15).Value = rA.Offset(0, 7).Value ' H1 -> P1
       rB.Offset(1, 0).Value = "Transaction"
       rB.Offset(1, 2).Value = rA.Offset(2, 6).Value ' G3 -> C2
       rB.Offset(1, 5).Value = rA.Offset(3, 6).Value ' G4 -> F2
       rB.Offset(1, 10).Value = rA.Offset(0, 15).Value ' P1 -> K2
       rB.Offset(1, 12).Value = rA.Offset(1, 6).Value ' G2 -> M2
       Set rA = rA.Offset(6, 0) ' this is the number of lines in a
record in "sheet1"
       Set rB = rB.Offset(3, 0) ' this is the number of lines in a
record in "output"
   Loop
   ' format columns H and K as numbers
   Set rB = Range("H:H,K:K")
   rB.NumberFormat = "0.00"

   ' clean up
   Set rA = Nothing
   Set rB = Nothing
   Set wsA = Nothing
   Set wsB = Nothing
   Application.ScreenUpdating = True
End Sub

> Jason
>
[quoted text clipped - 202 lines]
> >> > > >> >>
> >> > > >> >> Sam
Reply to this Message
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage
Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.