Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    2 Star Lounger
    Join Date
    Apr 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    loops for columns (2000)

    I'm using excell 2000. I need to know how to create a loop. I have data that is being FTP'd to a text file on a server, From there I import that text file into an excell spreadsheet where I have macro's set to bring in the data from the imported sheet into the cells desired. I now need to find a way to make sure that every time I open the spreadsheet with new imported data that said data is moved from column a to column b. so every day I update my data and open my spreadsheet I will have a months worth of data. Can anybody out there help me.... I have reached the critical stage..

  2. #2
    New Lounger
    Join Date
    Apr 2004
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: loops for columns (2000)

    I don't fully understand what you're asking, but probably the easiest way to shift data from Column A to Column B is just to insert a new Column A ..

    Columns("A:A").Insert

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: loops for columns (2000)

    Not sure exactly what you need or want. COuld you provide a few more details.

    I will assume you have some type of code that runs now when the workbook opens that Imports the text file from the server into the "desired cells".
    If the text file is going in the "desired cells" I am not sure what values are not in the correct place. Where do the values in Col A come from that you want to move to Col B? I am also unclear of why you need to create a loop.

    To answer what I see as your question (Move Col A to Col [img]/forums/images/smilies/cool.gif[/img], you can add this code snippet to your macro in the appropriate place. It moves the data from Col A of Sheet1 to Col B of Sheet1 (change the sheet name as appropriate)

    <pre>With Worksheets("Sheet1")
    .Range(.Range("A1"), .Range("A65536").End(xlUp)).Cut _
    Destination:=.Range("B1")
    End With</pre>


    To ensure that it runs you can add your code to the workbook open event to run whenever the file is opened. If this is not what you are after, you will need to provide additional info so we can help.

    Steve

  4. #4
    2 Star Lounger
    Join Date
    Apr 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: loops for columns (2000)

    ok, first of all thanks for replying to my post. here is my situation.....
    1).I have a program/job on an ibm mainframe that strips data from a file - it then creates a new file called FILEA.
    2). I then take FILEA and ftp the file into a text file - called FILEB
    3). I created an excell spreadsheet with two different sheets (sheet1 & sheet2).
    4). I imported FILEB into sheet1 (all this has is one column 12 rows - all data numeric - no headings)
    5). I then wrote a macro on sheet2 to bring in the data from the individual cells on sheet1 to their corresponding cells on sheet2
    6). Sheet2 consists of headers of dates from column A through column XXXXX.
    7). I need a macro that will take my daily updated information on sheet1 and put it in the individual columns on sheet2 daily.

    sheet1
    ---------------------------
    2 |
    --------------------------- <-------- data gets updated every day from program on ibm. so text file gets updated, which means sheet1 gets updated.
    3 |
    --------------------------
    27|
    --------------------------


    _5-apr___|____6-apr__|___7-apr__|__8-apr__|__9-apr__|_etc....
    2 | 54 | 73 |
    ---------------------------------|------------------------- <-------- new daily data gets applied to new columns from ibm job/text file/sheet1s (same columnA rows1-12).
    3 | 8 | 2 |
    ---------------------------------| ------------------------
    27 | 34 | 123 |
    ------------------------------------------------------------

    so can anybody help...

  5. #5
    New Lounger
    Join Date
    Apr 2004
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: loops for columns (2000)

    Still don't really understand exactly what you want, or what you need a loop for. Also, you show Sheet2 with dates going left to right and yet you seem to be asking how to insert your data right to left. Do you really want to put the latest data under the earliest date?

    As best I can work out you want to add a new Column at the beginning of Sheet2 and populate Row 1 with a heading date (1 day less than the one which was there before) and then populate the column below (Rows 2 through n+1) from Sheet1 Rows 1 through n. To do this you can combine what I gave you with what sdckapr gave you with a little bit of modification to get ..

    ActiveWorkbook.Worksheets("Sheet2").Columns("A:A") .Insert
    ActiveWorkbook.Worksheets("Sheet2").Range("A1").Fo rmula = "=B1-1"

    With Worksheets("Sheet1")
    .Range(.Range("A1"), .Range("A65536").End(xlUp)).Cut ActiveWorkbook.Worksheets("Sheet2").Range("A2")
    End With

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: loops for columns (2000)

    You re-posted this question in another thread. There was a reply there (<post#=364425>post 364425</post#>) but I have locked that thread to avoid duplication. Please check out that reply.

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: loops for columns (2000)

    If you want to retain the columnar format of the data, here's a simple macro without much error-checking that will probably do what you want, assuming Row 1 is populated with data. However, please consider my warning as an echo of the other posters questions; this isn't a great way to keep data: you are limited to 256 columns per sheet, and that's very close to the number of workdays in a year. What will you do when you run out of columns? Also, data structured this way is not easy to run analyses on. I agree with TonyJollans that you would better converting this data to row-wise data, and then use Steve's approach of adding it to Sheet2 by checking from the bottom up.

    Sub Macro1()
    Dim rngTarg As Range
    Set rngTarg = _
    Worksheets("Sheet2").Columns(257 - _
    Application.WorksheetFunction.CountBlank(Rows(1))) .EntireColumn ' change Rows(1) to a row that always has data if not Row 1
    Worksheets("Sheet1").Columns("A:A").Copy rngTarg
    Application.CutCopyMode = False
    Set rngTarg = Nothing
    End Sub

    If there's no row which reliably always contains data, use this instead:

    Sub Macro1()
    Dim rngTarg As Range
    Dim intCol As Integer
    intCol = 1
    Do
    Set rngTarg = Worksheets("Sheet2").Columns(intCol).EntireColumn
    intCol = intCol + 1
    Loop Until Application.WorksheetFunction.CountBlank(rngTarg) = 65536
    Worksheets("Sheet1").Columns("A:A").Copy rngTarg
    Application.CutCopyMode = False
    Set rngTarg = Nothing
    End Sub

    Post back top this thread if I have not understood your objective.
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: loops for columns (2000)

    On a "housekeeping" note, your data is hard to follow because of the way you laid it out; you may have discovered that the Lounge automatically compresses multiple contiguous spaces to one space. The way around this is to use Lounge Tables, and in <!post=This Star Post,238347>This Star Post<!/post> HansV has written Excel code to copy a part of a spreadsheet to a Lounge Post. The same thread also contains code for Word tables. The other alternative is to post a Workbook of up to 100k in size as an attachment, after censoring any confidential data and removing confidential Workbook properties.
    -John ... I float in liquid gardens
    UTC -7ąDS

  9. #9
    2 Star Lounger
    Join Date
    Apr 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: loops for columns (2000)

    ok here are the basics. I need to keep track of how many jobs are running within the ibm queue at a certain hour of the day (12 hours a day - hence rows 1 through 12). I need to do this every day until the end of this year (hence the dates). But I need the most current date in the column on the right. anotherwords... 5-april, 6-april, 7-april etc... I hope this gives you guys a better understanding.

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: loops for columns (2000)

    Why not do columns 1-2 and then you have a lot more rows to work with.

    I am still not clear on what your question/problem really is. Could you detail more what your goal is (not neccessarily how you are doing it now). What do you get (daily) as a text file and what do you want to analze for? There might be better setups than what you are doing (both for importing and for analyses and output)

    Steve

  11. #11
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: loops for columns (2000)

    Does the date come with the data file or do you need to create it in Excel?
    -John ... I float in liquid gardens
    UTC -7ąDS

  12. #12
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: loops for columns (2000)

    Try it this way:

    Sub MacroM()
    Dim rngTarg As Range
    Dim lngRow As Long
    Do
    lngRow = lngRow + 1
    Set rngTarg = Worksheets("Sheet2").Cells(lngRow, 2)
    Loop Until Application.WorksheetFunction.CountBlank(rngTarg.E ntireRow) = 256
    Worksheets("Sheet1").Columns("A:A").CurrentRegion. Copy
    rngTarg.PasteSpecial Transpose:=True
    Application.CutCopyMode = False
    rngTarg.Offset(0, -1).Value = Date
    Set rngTarg = Nothing
    End Sub
    -John ... I float in liquid gardens
    UTC -7ąDS

  13. #13
    2 Star Lounger
    Join Date
    Apr 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: loops for columns (2000)

    the date was already inputted by the manager unsure of how he put it there. i just need a simple macro that will update the next column with the same row of data as input from the sheet that had imported my text file. when I open my spreedsheet now every time I open it my text file has different data but that data is in the same column and rows column A rows 1-12. what I need is something that will take that data and put it in the next available column on another sheet that my text file is not imported into.

  14. #14
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: loops for columns (2000)

    It's still unclear how your data is laid out. If the date is already part of the data and you wish to retain columnar format, have you tried one of <!post=these,364478>these<!/post> macros?
    -John ... I float in liquid gardens
    UTC -7ąDS

  15. #15
    Star Lounger
    Join Date
    Feb 2001
    Location
    Virginia Beach, Virginia, USA
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: loops for columns (2000)

    And now a completely different solution.
    If I understand your problem, what about using the built in TextToColumns command? Select column of data, define how you want to parse it and away you go. You can record the options in a macro for later use. I use this all the time for parsing text files.
    Sorry, if this isn't what you are trying to do.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •