PDA

View Full Version : column question



vzachin
09-18-2007, 12:12 PM
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?


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



thanks
zach

mdmackillop
09-18-2007, 12:33 PM
What type of file are your getting your data from? Can you post a small sample?

vzachin
09-18-2007, 05:52 PM
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:

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



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

thanks for your thoughts on this
zach

shasur
09-18-2007, 07:26 PM
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
09-18-2007, 11:37 PM
Unless I know more about the Fields, I can't really advise.

RonMcK
09-19-2007, 09:58 AM
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
09-19-2007, 12:17 PM
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.