Macro to move to next column

I

iblonger

I have a macro that is used to pull data from a data collection device
(winwedge). It is pulling three fields. It is a huge amount of data that
will fill all 65536 rows within an hour. I would like to make it jump to the
next set of columns when it reaches this point. Any help is greatly
appriciated.
Thanks,
Bradley
 
B

Bernie Deitrick

Bradley,

You would get better answers if you post your code....

Anyway, you could use something like

If lngRow = 65000 Then
CurCol = CurCol + 3
lngRow = 1
End If

somewhere within your code.

HTH,
Bernie
MS Excel MVP
 
I

iblonger

Here is the code. I did not write this, it came from the manufacturer. I
know very little about what I'm looking at and am trying to learn. I did try
putting what you gave me in and it didn not work. I am assuming that I made
a mistake somewhere?

Sub GetSWData()
Static RowPointer As Long
RowPointer = RowPointer + 1
Chan = DDEInitiate("WinWedge", "Com1")
F1 = DDERequest(Chan, "Field(1)")
WedgeData$ = F1(1)
Sheets("sheet1").Cells(RowPointer, 1).Formula = WedgeData$
F1 = DDERequest(Chan, "Field(2)")
WedgeData$ = F1(1)
Sheets("sheet1").Cells(RowPointer, 2).Formula = WedgeData$
F1 = DDERequest(Chan, "Field(3)")
WedgeData$ = F1(1)
Sheets("sheet1").Cells(RowPointer, 3).Formula = WedgeData$
DDETerminate Chan

End Sub

Thanks,
Bradley
 
B

Bernie Deitrick

Bradley,

Try the version below. It will move to the next columns after 65000 rows. I used the RowPointer and
some math to select the column and row to write to...

HTH,
Bernie
MS Excel MVP

Sub GetSWData2()
'Modified by Bernie Deitrick to wrap columns
Static RowPointer As Long
Const ROWCOUNT As Long = 65000

Chan = DDEInitiate("WinWedge", "Com1")
F1 = DDERequest(Chan, "Field(1)")
WedgeData$ = F1(1)
Sheets("sheet1").Cells(RowPointer Mod ROWCOUNT + 1, _
(RowPointer \ ROWCOUNT) * 3 + 1).Formula = WedgeData$
F1 = DDERequest(Chan, "Field(2)")
WedgeData$ = F1(1)
Sheets("sheet1").Cells(RowPointer Mod ROWCOUNT + 1, _
(RowPointer \ ROWCOUNT) * 3 + 2).Formula = WedgeData$
F1 = DDERequest(Chan, "Field(3)")
WedgeData$ = F1(1)
Sheets("sheet1").Cells(RowPointer Mod ROWCOUNT + 1, _
(RowPointer \ ROWCOUNT) * 3 + 3).Formula = WedgeData$
DDETerminate Chan

RowPointer = RowPointer + 1

End Sub
 
I

iblonger

That worked great! THANKS SO MUCH FOR THE HELP.

Bernie Deitrick said:
Bradley,

Try the version below. It will move to the next columns after 65000 rows. I used the RowPointer and
some math to select the column and row to write to...

HTH,
Bernie
MS Excel MVP

Sub GetSWData2()
'Modified by Bernie Deitrick to wrap columns
Static RowPointer As Long
Const ROWCOUNT As Long = 65000

Chan = DDEInitiate("WinWedge", "Com1")
F1 = DDERequest(Chan, "Field(1)")
WedgeData$ = F1(1)
Sheets("sheet1").Cells(RowPointer Mod ROWCOUNT + 1, _
(RowPointer \ ROWCOUNT) * 3 + 1).Formula = WedgeData$
F1 = DDERequest(Chan, "Field(2)")
WedgeData$ = F1(1)
Sheets("sheet1").Cells(RowPointer Mod ROWCOUNT + 1, _
(RowPointer \ ROWCOUNT) * 3 + 2).Formula = WedgeData$
F1 = DDERequest(Chan, "Field(3)")
WedgeData$ = F1(1)
Sheets("sheet1").Cells(RowPointer Mod ROWCOUNT + 1, _
(RowPointer \ ROWCOUNT) * 3 + 3).Formula = WedgeData$
DDETerminate Chan

RowPointer = RowPointer + 1

End Sub
Click to expand...
 
B

Bernie Deitrick

You're quite welcome. Thanks for letting me know that it worked out OK for you.

Bernie
MS Excel MVP
 
I

iblonger

Now I need to try to add the date and time the data was captured. Can I do
this by just adding a line to the code?
Thanks,
Bradley
 
B

Bernie Deitrick

Bradley,

This will put the data and time into column 4 of the same row as your data....

Sheets("sheet1").Cells(RowPointer, 4).Value = Format(Now(), "mmm d, yyyy hh:mm")

Put it in just after this line:

Sheets("sheet1").Cells(RowPointer, 3).Formula = WedgeData$

HTH,
Bernie
MS Excel MVP
 
I

iblonger

Bernie,
Thanks for leading me in the right direction. I did have to change it a
little using what you showed me previously with the rowpointer. See look at
that not only did you help but you taught me something too.
Thanks a million,
Bradley
 
B

Bernie Deitrick

Bradley,
See look at
that not only did you help but you taught me something too.
Thanks a million,
Click to expand...

That's great, and you're quite welcome.

Now you'll have to start answering posts here, too ;-)

Bernie
 
Top