External data - macro question

[The Idea Dude]

Hi all,

I want to retrieve data from a text file that is on a website (the text file has peoples answers to a survey). Now, I can actually get the data retrieved, the problem is that I would prefer the data to go accross the spreadsheet, for example, the responses from person 1 would go in column A, and the responses for person 2 would go in column B, until I ran out of columns, and then data would continue back to column A but on X rows down.

Does this make sense? Any ideas?

I know this might be difficult with a plain text file as my source, but..

Thanks
This message was edited by The Idea Dude on 2002-10-14 22:59

 

[Andrew Poulsom]

The target row and column are controlled by variables c and Col respectively. This code will copy the data to the same sheet starting at cell B3 (row 3 column 2):

Sub Test()
    Const Answers As Integer = 5
    Const Blanks As Integer = 2
    Dim Sh As Worksheet
    Dim LastRow As Long
    Dim a As Long ' First row of original record
    Dim b As Long ' Last row of original record
    Dim c As Long ' First row of new record
    Dim Col As Integer
    Set Sh = Worksheets("Sheet1")
    LastRow = Sh.Range("A65536").End(xlUp).Row
    a = 1
    b = Answers
    c = 3
    Col = 2
    Do
        Sh.Range(Cells(a, 1), Cells(b, 1)).Copy Sh.Cells(c, Col)
        a = a + Answers + Blanks
        b = b + Answers + Blanks
        Col = Col + 1
        If Col > 256 Then
            Col = 2
            c = c + Answers + Blanks
        End If
    Loop While b <= LastRow
End Sub

This way you only get 255 columns of data though. My original code copied the data to a new sheet so you got 256 columns.

 

[The Idea Dude]

On 2002-10-15 03:07, Andrew Poulsom wrote:
The target row and column are controlled by variables c and Col respectively. This code will copy the data to the same sheet starting at cell B3 (row 3 column 2):

Sub Test()
    Const Answers As Integer = 5
    Const Blanks As Integer = 2
    Dim Sh As Worksheet
    Dim LastRow As Long
    Dim a As Long ' First row of original record
    Dim b As Long ' Last row of original record
    Dim c As Long ' First row of new record
    Dim Col As Integer
    Set Sh = Worksheets("Sheet1")
    LastRow = Sh.Range("A65536").End(xlUp).Row
    a = 1
    b = Answers
    c = 3
    Col = 2
    Do
        Sh.Range(Cells(a, 1), Cells(b, 1)).Copy Sh.Cells(c, Col)
        a = a + Answers + Blanks
        b = b + Answers + Blanks
        Col = Col + 1
        If Col > 256 Then
            Col = 2
            c = c + Answers + Blanks
        End If
    Loop While b <= LastRow
End Sub

This way you only get 255 columns of data though. My original code copied the data to a new sheet so you got 256 columns.

Thanks Andrew, but by starting at cell B3 I meant starting at the row equal to the value contained in B3 of a different sheet. I will try to nut it out, but your assistance once again would be great!

Thanks

 

[The Idea Dude]

I worked it out. I am getting better at this stuff

c = Sheets("Sheet1").Range("B2").Value