loops for columns (2000)

Topic

New Reply

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..

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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 , 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)

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

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

Reply | Quote

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…

Reply | Quote

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”).Formula = “=B1-1”

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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 This Star 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.

Reply | Quote

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 This Star 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.

Reply | Quote

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…

Reply | Quote

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 , 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)

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

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

Does the date come with the data file or do you need to create it in Excel?

Reply | Quote

one more time guys,,, here is a copy of the actual spreadsheet . the column A has all times. this reflects the time the jobs on ibm run. the headings have the date for the different days that it runs (through the end of the year).
St. Louis
3-Apr 4-Apr 5-Apr 6-Apr 7-Apr 8-Apr 9-Apr 10-Apr
3:00 40
3:30 67
4:00 72
4:30 62
5:00 49
5:30 79
6:00 91
6:30 285
7:00 321
7:30 196
8:00 253

total jobs 1515 0 0 0 0 0 0 0

this next data is the imported data from the text file that goes into a different sheet (say sheet 2) also found in column b above
40
67
72
62
49
79
91
285
321
196
253
this gets updated evey day same column same rows, Once its updated I then run the following macro to bring the data to sheet 1 (the first sheet above).
Sub Macro1()
‘
‘ Macro1 Macro
‘ Macro recorded 4/20/2004 by Compaq
‘

‘
Range(“BL3”).Select
ActiveCell.FormulaR1C1 = “=Sheet1!R1C1”
Range(“BL4”).Select
ActiveCell.FormulaR1C1 = “=Sheet1!R2C1”
Range(“BL5”).Select
ActiveCell.FormulaR1C1 = “=Sheet1!R3C1”
Range(“BL6”).Select
ActiveCell.FormulaR1C1 = “=Sheet1!R[-2]C1”
Range(“BL7”).Select
ActiveCell.FormulaR1C1 = “=Sheet1!R[-2]C1”
Range(“BL8”).Select
ActiveCell.FormulaR1C1 = “=Sheet1!R[-2]C1”
Range(“BL9”).Select
ActiveCell.FormulaR1C1 = “=Sheet1!R[-2]C1”
Range(“BL10”).Select
ActiveCell.FormulaR1C1 = “=Sheet1!R[-2]C1”
Range(“BL11”).Select
ActiveCell.FormulaR1C1 = “=Sheet1!R[-2]C1”
Range(“BL12”).Select
ActiveCell.FormulaR1C1 = “=Sheet1!R[-2]C1”
Range(“BL13”).Select
ActiveCell.FormulaR1C1 = “=Sheet1!R[-2]C1”
Range(“BL16”).Select
Application.CommandBars(“Stop Recording”).Visible = False
End Sub

I now need to know how to make the data go from column b to c to d etc… every day I open the new data. keep in mind the imported data column A rows 1-12 on sheet 2 never changes just the data does.
also where in my macro would I put the new macro (If you guys havent noticed by now I’m some what a newbe at this) do I have to use your macro in every cell or do I put it in the code up abaove? please help time is running out.

Reply | Quote

I am still not clear what you want exactly you keep talking about moving columns b to c, etc. If you really want to move b to C and C to D, why not just insert a new column A?

    Columns("A:A").Insert

It seems to me in your description, that you want to copy the info from Sheet2 in col 1 to the next free col in sheet 1. If so this should do it. Change sheet names as appropriate and starting ranges. (I am not sure what you are doing with your currently posted macro)

It copies from A1 till the end of the data in “Sheet2″and pastes in row 3 of the next available column in “Sheet1”

Sub CopyData()
    Dim wks1 As Worksheet
    Dim wks2 As Worksheet
    Dim rngCopy As Range
    Dim rngPaste As Range
    Set wks1 = Worksheets("Sheet1")
    Set wks2 = Worksheets("Sheet2")
    
    With wks2
        Set rngCopy = .Range(.Range("A1"), .Range("A65536").End(xlUp))
    End With
    
    Set rngPaste = wks1.Range("IV3").End(xlToLeft).Offset(0, 1)
    rngCopy.Copy
    rngPaste.PasteSpecial xlPasteValues
End Sub

If this is not what you are after, could you be a little more specific about what you need?

Steve

Reply | Quote

I am still not clear what you want exactly you keep talking about moving columns b to c, etc. If you really want to move b to C and C to D, why not just insert a new column A?

    Columns("A:A").Insert

It seems to me in your description, that you want to copy the info from Sheet2 in col 1 to the next free col in sheet 1. If so this should do it. Change sheet names as appropriate and starting ranges. (I am not sure what you are doing with your currently posted macro)

It copies from A1 till the end of the data in “Sheet2″and pastes in row 3 of the next available column in “Sheet1”

Sub CopyData()
    Dim wks1 As Worksheet
    Dim wks2 As Worksheet
    Dim rngCopy As Range
    Dim rngPaste As Range
    Set wks1 = Worksheets("Sheet1")
    Set wks2 = Worksheets("Sheet2")
    
    With wks2
        Set rngCopy = .Range(.Range("A1"), .Range("A65536").End(xlUp))
    End With
    
    Set rngPaste = wks1.Range("IV3").End(xlToLeft).Offset(0, 1)
    rngCopy.Copy
    rngPaste.PasteSpecial xlPasteValues
End Sub

If this is not what you are after, could you be a little more specific about what you need?

Steve

Reply | Quote

one more time guys,,, here is a copy of the actual spreadsheet . the column A has all times. this reflects the time the jobs on ibm run. the headings have the date for the different days that it runs (through the end of the year).
St. Louis
3-Apr 4-Apr 5-Apr 6-Apr 7-Apr 8-Apr 9-Apr 10-Apr
3:00 40
3:30 67
4:00 72
4:30 62
5:00 49
5:30 79
6:00 91
6:30 285
7:00 321
7:30 196
8:00 253

total jobs 1515 0 0 0 0 0 0 0

this next data is the imported data from the text file that goes into a different sheet (say sheet 2) also found in column b above
40
67
72
62
49
79
91
285
321
196
253
this gets updated evey day same column same rows, Once its updated I then run the following macro to bring the data to sheet 1 (the first sheet above).
Sub Macro1()
‘
‘ Macro1 Macro
‘ Macro recorded 4/20/2004 by Compaq
‘

‘
Range(“BL3”).Select
ActiveCell.FormulaR1C1 = “=Sheet1!R1C1”
Range(“BL4”).Select
ActiveCell.FormulaR1C1 = “=Sheet1!R2C1”
Range(“BL5”).Select
ActiveCell.FormulaR1C1 = “=Sheet1!R3C1”
Range(“BL6”).Select
ActiveCell.FormulaR1C1 = “=Sheet1!R[-2]C1”
Range(“BL7”).Select
ActiveCell.FormulaR1C1 = “=Sheet1!R[-2]C1”
Range(“BL8”).Select
ActiveCell.FormulaR1C1 = “=Sheet1!R[-2]C1”
Range(“BL9”).Select
ActiveCell.FormulaR1C1 = “=Sheet1!R[-2]C1”
Range(“BL10”).Select
ActiveCell.FormulaR1C1 = “=Sheet1!R[-2]C1”
Range(“BL11”).Select
ActiveCell.FormulaR1C1 = “=Sheet1!R[-2]C1”
Range(“BL12”).Select
ActiveCell.FormulaR1C1 = “=Sheet1!R[-2]C1”
Range(“BL13”).Select
ActiveCell.FormulaR1C1 = “=Sheet1!R[-2]C1”
Range(“BL16”).Select
Application.CommandBars(“Stop Recording”).Visible = False
End Sub

I now need to know how to make the data go from column b to c to d etc… every day I open the new data. keep in mind the imported data column A rows 1-12 on sheet 2 never changes just the data does.
also where in my macro would I put the new macro (If you guys havent noticed by now I’m some what a newbe at this) do I have to use your macro in every cell or do I put it in the code up abaove? please help time is running out.

Reply | Quote

sd you hit it on the head…. thats exactly what I want to do (your second idea). But how /where do I put this in my macro. Do I need to add it to each cell. or can I edit my macro and put it at the top or bottom… I’m totally unsure of this… and by the way thank you a million times over for all your assistance and those of the panel…

Reply | Quote

Right now this stands alone. You can “call” this routine when you want to copy ,

CopyData

or you can just add the appropriate lines of code to the end of your routine.

I don’t understand your question about adding it to each cell… The code copies the entire range of data in “one fell swoop”. Could you be more specific about your needs?

Steve

Reply | Quote

Right now this stands alone. You can “call” this routine when you want to copy ,

CopyData

or you can just add the appropriate lines of code to the end of your routine.

I don’t understand your question about adding it to each cell… The code copies the entire range of data in “one fell swoop”. Could you be more specific about your needs?

Steve

Reply | Quote

Replace the text of your existing macro with Steve’s. (It was Steve, but you replied to me.) If you need more information on managing macros, please see Legare’s Personal.xls Tutorial Star Post.

Reply | Quote

Replace the text of your existing macro with Steve’s. (It was Steve, but you replied to me.) If you need more information on managing macros, please see Legare’s Personal.xls Tutorial Star Post.

Reply | Quote

sd you hit it on the head…. thats exactly what I want to do (your second idea). But how /where do I put this in my macro. Do I need to add it to each cell. or can I edit my macro and put it at the top or bottom… I’m totally unsure of this… and by the way thank you a million times over for all your assistance and those of the panel…

Reply | Quote

Does the date come with the data file or do you need to create it in Excel?

Reply | Quote

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.EntireRow) = 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

Reply | Quote

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.EntireRow) = 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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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 these macros?

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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 these macros?

Reply | Quote

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.

Reply | Quote