column question

[vzachin]

Hi,

i'm downloading data that may be about 5000 rows or more &
maximum 200 columns.
is there a better way to code the columns?

[vba]
Rw = Rw + 1
With Worksheets("EHMCR")
.Cells(Rw, "A").Value = Field1
.Cells(Rw, "B").Value = Field2
.Cells(Rw, "C").Value = Field3
.Cells(Rw, "D").Value = Field4
.Cells(Rw, "E").Value = Field5
...etc...
.Cells(Rw, "GR").Value = Field200
End With
[/vba]


thanks
zach

[mdmackillop]

What type of file are your getting your data from? Can you post a small sample?

[vzachin]

hi malcolm,

i'm screenscraping data from a mainframe application using attachmate. each record may contain anywhere from 20 fields to 200 fields of data.
i was getting tired of typing all the way up to column "GR":


i was thinking something like this:
[VBA]
col = 0
With Worksheets("test")
.Cells(Rw, col).Value = Field1
.Cells(Rw, col + 1).Value = Field2
.Cells(Rw, col + 2).Value = Field3
.Cells(Rw, col + 3).Value = Field4
.Cells(Rw, col + 4).Value = Field5

and so forth

[/VBA]

but that's still a lot of typing. i'm almost finished now...

thanks for your thoughts on this
zach

[shasur]

If you are getting the input as some text or formatted file. You can try Query Table. That would be faster

Once your Query Table is loaded you can delink it

[mdmackillop]

Unless I know more about the Fields, I can't really advise.

[RonMcK]

hi malcolm,

i'm screenscraping data from a mainframe application using attachmate. each record may contain anywhere from 20 fields to 200 fields of data.
i was getting tired of typing all the way up to column "GR":
[...]
thanks for your thoughts on this
zach

Zach,

So, please upload a wee sample of your screenscrapings; also, explain why (under what conditions) the data ranges between 20 and 200 columns.

Thanks!

[mikerickson]

If you can get your data in an array (1-based)

Range(.Cells(rowNm,1),.Cells(rowNm,UBound(myArray))).Value=myArray

is a quick way to write the array to the spreadsheet. Much quicker than looping.

It also works with 2-dimensional arrays.