Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
iblonger
 
Posts: n/a
Default Macro to move to next column

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
  #2   Report Post  
Posted to microsoft.public.excel.misc
Bernie Deitrick
 
Posts: n/a
Default Macro to move to next column

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


"iblonger" wrote in message
...
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



  #3   Report Post  
Posted to microsoft.public.excel.misc
iblonger
 
Posts: n/a
Default Macro to move to next column

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

"Bernie Deitrick" wrote:

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


"iblonger" wrote in message
...
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




  #4   Report Post  
Posted to microsoft.public.excel.misc
Bernie Deitrick
 
Posts: n/a
Default Macro to move to next column

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



"iblonger" wrote in message
...
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

"Bernie Deitrick" wrote:

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


"iblonger" wrote in message
...
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






  #5   Report Post  
Posted to microsoft.public.excel.misc
iblonger
 
Posts: n/a
Default Macro to move to next column

That worked great! THANKS SO MUCH FOR THE HELP.

"Bernie Deitrick" wrote:

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



"iblonger" wrote in message
...
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

"Bernie Deitrick" wrote:

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


"iblonger" wrote in message
...
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








  #6   Report Post  
Posted to microsoft.public.excel.misc
Bernie Deitrick
 
Posts: n/a
Default Macro to move to next column

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

Bernie
MS Excel MVP

That worked great! THANKS SO MUCH FOR THE HELP.



  #7   Report Post  
Posted to microsoft.public.excel.misc
iblonger
 
Posts: n/a
Default Macro to move to next column

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

"Bernie Deitrick" wrote:

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

Bernie
MS Excel MVP

That worked great! THANKS SO MUCH FOR THE HELP.




  #8   Report Post  
Posted to microsoft.public.excel.misc
Bernie Deitrick
 
Posts: n/a
Default Macro to move to next column

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


"iblonger" wrote in message
...
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

"Bernie Deitrick" wrote:

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

Bernie
MS Excel MVP

That worked great! THANKS SO MUCH FOR THE HELP.






  #9   Report Post  
Posted to microsoft.public.excel.misc
iblonger
 
Posts: n/a
Default Macro to move to next column

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

"Bernie Deitrick" wrote:

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


"iblonger" wrote in message
...
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

"Bernie Deitrick" wrote:

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

Bernie
MS Excel MVP

That worked great! THANKS SO MUCH FOR THE HELP.






  #10   Report Post  
Posted to microsoft.public.excel.misc
Bernie Deitrick
 
Posts: n/a
Default Macro to move to next column

Bradley,

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


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

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

Bernie


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Macro to open workbook and copy and paste values in to orig workbo Dena X Excel Worksheet Functions 1 December 16th 05 12:13 AM
Macro won't move in proper progression Laurahoney Excel Discussion (Misc queries) 1 November 23rd 05 08:18 PM
Macro Help In Excel welshlad Excel Discussion (Misc queries) 14 October 26th 05 02:34 PM
Closing File Error jcliquidtension Excel Discussion (Misc queries) 4 October 20th 05 12:22 PM
Macro to move data to different column based on data in another co malycom Excel Discussion (Misc queries) 3 August 2nd 05 07:07 PM


All times are GMT +1. The time now is 11:42 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"