ActiveCell Problem

I can indepently get Trace to work, and Copyrange to print one line at a
time.
However, I can not both to work in conjunction with each other.

wht happens, is the a1..b1 rows, pause for 3 sec and then goes down one row
to a2..b2, pauses for two seconds, and then copies to e1..f1.
Here is the problem.
I now want to copy from e1..f1 to e5..f5 and loop until there is a blank
line
in column A,B
---------------------------------------------------
Public NextTime As Date
Sub Trace()
Sheet1.Range("A1").Select
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 3
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
Call LoopOn
End Sub

Sub LoopOn()
    ActiveCell.Offset(1, 0).Select
   Range("E1").Value = ActiveCell.Value
    ActiveCell.Offset(0, 1).Select
   Range("F1").Value = ActiveCell.Value
    Call FindNum
  NextTime = Now + TimeValue("00:00:02")
  Application.OnTime NextTime, "LoopOn"
    If Range("E1").Value = "" Then
    Call LoopOff
    End If
   End Sub

Sub FindNum()
    Range("A1").Activate
    Do Until ActiveCell.Value = " "
        If ActiveCell.Value = Range("E1").Value Then
        Exit Sub
        End If
        ActiveCell.Offset(1, 0).Activate
     Loop
     'Call CopyRange2
  End Sub

Sub LoopOff()
   Application.OnTime NextTime, "LoopOn", , False
End Sub

Sub CopyRange2()
    Sheets("sheet1").Range("E5:F5").Select
    'find your empty cell
    Do Until ActiveCell.Formula = ""
        ActiveCell.Offset(1, 0).Select
    Loop
    ActiveCell.Formula = Range("E1:F1").Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
        False, Transpose:=False
    Application.CutCopyMode = True

 End Sub
--------------------------------------------------------------
Thanks in advance



0
smandula (16)
12/22/2003 4:00:06 AM
excel.misc 78881 articles. 3 followers. Follow

4 Replies
233 Views

Similar Articles

[PageSpeed] 5

I'm kind of confused at what you're doing.

But this line (in copyrange2) looks like it's an error:

ActiveCell.Formula = Range("E1:F1").Copy

If the copy is successful, you'll get a True in the activecell.

But my real problem is I don't understand what you really want.  Can you post
back with a description of what should happen?  I think you'll get a better
answer, then.

And is there a reason you want to use .ontime and .wait's?  Most people want the
macro to run as quickly as possible--with no delays.



smandula wrote:
> 
> I can indepently get Trace to work, and Copyrange to print one line at a
> time.
> However, I can not both to work in conjunction with each other.
> 
> wht happens, is the a1..b1 rows, pause for 3 sec and then goes down one row
> to a2..b2, pauses for two seconds, and then copies to e1..f1.
> Here is the problem.
> I now want to copy from e1..f1 to e5..f5 and loop until there is a blank
> line
> in column A,B
> ---------------------------------------------------
> Public NextTime As Date
> Sub Trace()
> Sheet1.Range("A1").Select
> newHour = Hour(Now())
> newMinute = Minute(Now())
> newSecond = Second(Now()) + 3
> waitTime = TimeSerial(newHour, newMinute, newSecond)
> Application.Wait waitTime
> Call LoopOn
> End Sub
> 
> Sub LoopOn()
>     ActiveCell.Offset(1, 0).Select
>    Range("E1").Value = ActiveCell.Value
>     ActiveCell.Offset(0, 1).Select
>    Range("F1").Value = ActiveCell.Value
>     Call FindNum
>   NextTime = Now + TimeValue("00:00:02")
>   Application.OnTime NextTime, "LoopOn"
>     If Range("E1").Value = "" Then
>     Call LoopOff
>     End If
>    End Sub
> 
> Sub FindNum()
>     Range("A1").Activate
>     Do Until ActiveCell.Value = " "
>         If ActiveCell.Value = Range("E1").Value Then
>         Exit Sub
>         End If
>         ActiveCell.Offset(1, 0).Activate
>      Loop
>      'Call CopyRange2
>   End Sub
> 
> Sub LoopOff()
>    Application.OnTime NextTime, "LoopOn", , False
> End Sub
> 
> Sub CopyRange2()
>     Sheets("sheet1").Range("E5:F5").Select
>     'find your empty cell
>     Do Until ActiveCell.Formula = ""
>         ActiveCell.Offset(1, 0).Select
>     Loop
>     ActiveCell.Formula = Range("E1:F1").Copy
>     Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
> _
>         False, Transpose:=False
>     Application.CutCopyMode = True
> 
>  End Sub
> --------------------------------------------------------------
> Thanks in advance

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10085)
12/22/2003 3:19:50 PM
Thank's for replying back!

> I'm kind of confused at what you're doing.

What I am trying to do in the long run is to build a stock ticker simulator.
On the same worksheet, the data columns, are cols A and B with
row 1 (A1)Time and (B1) Current Price, these are both titles, with the
following
data starts at row 2:
Time          Current Price
11:30              4.50
11:35              4.75
11:40              5.00
etc.
This feeds at two second intervals to E1 and F1 which at as monitors
for the simulated live feed that is coming from columns Aand B.
Eventually I will place Cols. A and B on a separate Date sheet, hidden
from view.
Cell E5 and F5, echo E1 and F1 (the monitor cells).
This is were I can not get the active cell to go from Cols A,B  to E1,F1 to
E5,F5
and then back to ColsA,B next available row. It all stops on a blank row.
This it in a nut shell.
From here I can chart, or run stats against the made simulated feed data.


> But this line (in copyrange2) looks like it's an error:
>
> ActiveCell.Formula = Range("E1:F1").Copy
>
> If the copy is successful, you'll get a True in the activecell.
Your are right, but, run Trace by itself and Copyrange2 by itself ,
to see the results individually.

It is getting the two Macro to run together which is my problem.

>
> But my real problem is I don't understand what you really want.  Can you
post
> back with a description of what should happen?  I think you'll get a
better
> answer, then.

I hope I have explained things a bit better.
>
> And is there a reason you want to use .ontime and .wait's?  Most people
want the
> macro to run as quickly as possible--with no delays.

To simulate a sped up real time.

Thanks for looking at the program.



0
smandula (16)
12/22/2003 7:05:03 PM
I'm still confused.

And I find activating cells in multiple routines even more confusing.  Maybe
just keeping track of where one "activecell" should be and where the other
"activecell" should be:

Option Explicit

Public NextTime As Date
Dim CurCell As Range
Dim destCell As Range

Sub Trace()
    Dim waitTime As Date
    Dim newHour As Long
    Dim newMinute As Long
    Dim newSecond As Long
    
    Set CurCell = Sheet1.Range("a1")  'start in A1
    Set destCell = Sheet1.Range("e5") 'start pasting in E5:F5
    
    newHour = Hour(Now())
    newMinute = Minute(Now())
    newSecond = Second(Now()) + 3
    waitTime = TimeSerial(newHour, newMinute, newSecond)
    Application.Wait waitTime
    Call LoopOn
End Sub
Sub LoopOn()

    Set CurCell = CurCell.Offset(1, 0)
    Range("E1").Value = CurCell.Value
    Range("F1").Value = CurCell.Offset(0, 1).Value
    
    Call CopyRange2
    
    NextTime = Now + TimeValue("00:00:02")
    Application.OnTime NextTime, "LoopOn"
    
    If Trim(CurCell.Value) = "" Then
        Call LoopOff
    End If
    
   End Sub

Sub LoopOff()
   Application.OnTime NextTime, "LoopOn", , False
End Sub

Sub CopyRange2()
    destCell.Resize(1, 2).Value = Sheet1.Range("e1:f1").Value
    Set destCell = destCell.Offset(1, 0)
End Sub


smandula wrote:
> 
> Thank's for replying back!
> 
> > I'm kind of confused at what you're doing.
> 
> What I am trying to do in the long run is to build a stock ticker simulator.
> On the same worksheet, the data columns, are cols A and B with
> row 1 (A1)Time and (B1) Current Price, these are both titles, with the
> following
> data starts at row 2:
> Time          Current Price
> 11:30              4.50
> 11:35              4.75
> 11:40              5.00
> etc.
> This feeds at two second intervals to E1 and F1 which at as monitors
> for the simulated live feed that is coming from columns Aand B.
> Eventually I will place Cols. A and B on a separate Date sheet, hidden
> from view.
> Cell E5 and F5, echo E1 and F1 (the monitor cells).
> This is were I can not get the active cell to go from Cols A,B  to E1,F1 to
> E5,F5
> and then back to ColsA,B next available row. It all stops on a blank row.
> This it in a nut shell.
> From here I can chart, or run stats against the made simulated feed data.
> 
> > But this line (in copyrange2) looks like it's an error:
> >
> > ActiveCell.Formula = Range("E1:F1").Copy
> >
> > If the copy is successful, you'll get a True in the activecell.
> Your are right, but, run Trace by itself and Copyrange2 by itself ,
> to see the results individually.
> 
> It is getting the two Macro to run together which is my problem.
> 
> >
> > But my real problem is I don't understand what you really want.  Can you
> post
> > back with a description of what should happen?  I think you'll get a
> better
> > answer, then.
> 
> I hope I have explained things a bit better.
> >
> > And is there a reason you want to use .ontime and .wait's?  Most people
> want the
> > macro to run as quickly as possible--with no delays.
> 
> To simulate a sped up real time.
> 
> Thanks for looking at the program.

-- 

Dave Peterson
ec35720@msn.com
0
ec35720 (10085)
12/22/2003 9:03:06 PM
Thanks a Million, Million

Your programme works great.

With Thanks
Steve


> And I find activating cells in multiple routines even more confusing.
Maybe
> just keeping track of where one "activecell" should be and where the other
> "activecell" should be:



0
smandula (16)
12/22/2003 9:45:59 PM
Reply:

Similar Artilces:

CRM Outlook Add In Sending Problem
We've been using CRM 3.0 for about 4 months now, and the Outlook Add In for 2. I've been the primary Outlook user until the last month as I was familiarizing myself with the flow before we rolled it out to everyone. This problem happened to me, but I attributed it more to Oultook 2007... now several of our users are having the problem (2003 and 2007). The problem is this, when I send an email to a CRM Contact - occasionally, when I look in my sent items, the contact does not appear on the sent email and they never received it. For example, If I have a CRM contact named "John S...

Problem with Interactive Charting -- Did something change?
A few months ago, in Portfolio, I was able to click on a graph of one investment and then add stocks I wanted to compare to the first one by clicking a dropdown window on the graph. Today I have to type in each comparison I want to see. Anyone know what happened? I am not aware of anything which has changed unless there is some form of service that has been discontinued or has expired without any notice from MS. ...

Problem with Adobe
My friend is having trouble with Adobe. He says: The pop ups I am getting say: Eula NVIDIA graphics Driver has stooped working and needs to close Down load manager active x has encountered a problem and needs to close Adobe reader and acrobat manager has encountered a problem and needs to close Adobe tech. said " Adobe was having a problem with Internet Explorer 8" Ideas on how to fix, anyone? TIA, Paul From: "Paul H" <NoSpamphobergNoSpam@att.net> | My friend is having trouble with Adobe. He says: | The pop ups I am getting say: | ...

Work orders problems
Hi, We use RMS (SQL 2000) in a retail environment as well as a POS environment, although this is obviously not recommended. In the POS environment, we use work orders to keep a "tab" of the client's bill until it is eventually tendered off. On a few occasions, we have had some weird things happen with work orders. On a few occasions, open work orders have managed to disappear whilst being saved. I.e. When the waiter adds more items to the work order and saves it, it just disappears. Other times, work orders have frozen up while the waiter was picking up the entire work ...

Problem with CMapStringToPtr 's Lookup problem
HI. I have written the following code..in VC++(win32 console with MFC support) CMapStringToPtr chat; vector<UserMessage *> v; /* UserMessage is a class.. */ UserMessage *obj_msg=new UserMessage(fromUser,fromUserName,'n',msg); void * rValue; CString toUser="abcd"; v.push_back(obj_msg); // Insering an object into vector v chat.SetAt(toUser,&v); // Putting the vector 's reference into map "chat" chat.Lookup(toU...

focus problem
I am trying to code the following: I open an Excel sheet with ActiveX from Inet Explorer 5+ with a form button. I go to the Excel window and make a few changes. Then I go back to the Explorer window and want to capture some cell information from the Excel sheet. But then I get an "ExcelApp is undefined" error. Can someone give me some ideas on how to solve this? here is the code: <html> <head> <script language="JavaScript"> function openxlspage() { ExcelApp = new ActiveXObject("Excel.Application"); ExcelApp.DisplayAlerts = false; ExcelApp.Wor...

Problem with pointers in sockets
I am new to the world of MFC.I was reviewing an MFC code which is dealing with socket programming.There is a pointer of CConnectedSocket class m_pUpperportSocket and a pointer to the dialog class m_pDlg. I want to know the basic concept behind using the following two statements. m_pDlg ->m_pUpperportSocket and (*m_pDlg -> m_pUpperportSocket) harsh wrote: > I am new to the world of MFC.I was reviewing an MFC code which is > dealing with socket programming.There is a pointer of CConnectedSocket > class m_pUpperportSocket and a pointer to the dialog class m_pDlg. > > I w...

IE problem with OWA 2003. It is a IE problem machine specific
When I try to create a new message in OWA 2003, the new message window pops up but where I am supposed to type the body of the message, there is a red x. This is only on one machine because I have tested on other machines and it works fine. Am I missing a control or something? Any help would be appreciated. Thanks, Noel Haydon MCSE MCDBA ...

Security problems in web-services
Hi all I have wrote win aplication(client) which call method in web-service in another machine. It work then I run it in localhost, but if I change web-reference from "localhost" to my IP and bring my application to another machine and run it write "security error, use system.net.WebPermission to grant access...". Can you help me and say how resolve this problem Alex Show something that is 3 seconds of value, monkey. "alexmsu" <Alexmsu@mail.ru> wrote in message news:C507E498-FDA0-4F76-B433-85F0F05BCC94@microsoft.com... > Hi all. > I have wrote w...

problems with? I don't know
Morning, I use a worksheet at work, which works out all scrap, customer returns and other stuff. The file has about 20 different tabs showing graphs and various tables linked together. There is one sheet which shows its months 'customer returns'. The footer on this page needed to be edited as is shows information from 2003. (We copied and renamed this to create this 2005 copy). I edited the header and footer with the information needed and pressed ok. This is the wierd part; I then looked at another tab within that document then went back to the tab where I edited the information, I ...

Problem with Query
SQL2008 I am trying to get the following query to work Declare @FileName varchar(max) Declare @FullPath varchar(max) Declare @Licence varchar(6) Declare @LedgerKey uniqueidentifier Declare @ReportType varchar(25) Declare @Date varchar(10) Declare @Count Int Declare @FileValue Money Declare @TEST Varchar(max) SET @TEST = ' INSERT INTO [Table1] ([Licence] ,[LedgerKey] ,[ReportType] ,[CreatedDate] ,[ViewedDate] ,[Viewer] ,[ReportFileName] ,[TransCount] ,[FileValue...

Defining an Activecell
Each month I add new data to an existing sheet. I then want to run a macro to insert a new column and a formula. Trouble is this data increases rows each month and I need to be able to identify the final row each time so I can copy the formula down. ie in Jan the data finished at row 100. In Feb it finishes at row 200. I need the macro to be able to know to copy the formula down to row 200 in Feb for example. Hi Ant You can use this function fir finding the last row with data on thye worksheet Function LastRow(sh As Worksheet) On Error Resume Next LastRow = sh.Cells.Find(What:=...

Printing problem #10
I have a page size of 5.5 x 8.5 set up. When I try to print, one side is upside down. This is with publisher 2007. I had hoped it would be simplified. Can anyone lead me out this mess. I need this publication out the door ASAP!!!!!! Michael in Oak Ridge, TN -- Music washes the grunge of daily living. After you select Print, look at the Printer Preferences and see if you find an option to "flip" page or something similar. -- Don - Publisher 2000� Vancouver, USA "Michael" <Michorium@comcast.net> wrote in message news:72E458E4-E928-4E2E-81F7-B75BBFEA48B1@m...

Memory Leak problem in ATL
Hi all, I am new to ATL programming. I have been given the task of plugging Memory leaks in an existing ATL dll. While debugging I found that when the CComPtr destructor for some of the objects is called, it dutifully calls the Release method, which in turn calls the InternalRelease method. But the RefCount of the object is still not 0. So, ATL is not able to delete the object from memory. This is causing memory leaks. Please guide me in tackling this issue. Is there a way in which i can locate the instances when the RefCount was incremented, but a corresponding Release was not called (The c...

Problem: creating memory CDC for drawing and saving into BMP object in different color mode
Definition of problem: 1. I have some function Draw(CDC* dc) for drawing smth on CDC object. The body of function is "black box", I won't change it. 2. I need draw it into memory CDC object. Well, following code will do it for me: CDC mydc; mydc.CreateCompatibleDC(pDC); // pDC is some display DC CBitmap m_bitmap; m_bitmap.CreateCompatibleBitmap(mydc, width, height); // <- creating an internal bitmap CBitmap* m_pOldBitmap = dc.SelectObject(&m_bitmap); Draw(&mydc); // draw scene ........ do smth with mydc ...... if (m_pOldBitmap) mydc.SelectObject(m_pOldBitmap); The...

OWA Problems.
Outlook Web Access keeps crashing. The only way I know how to get it going again is to reboot the server. I have no idea what is up with it, because it has worked fine forever until now. Any ideas about what to look for/at? I don't recognize anything in the Event Viewer related to OWA, but maybe I'm unaware of some kind of service/dependancy linkage... Mark "Mark" <anonymous@discussions.microsoft.com> wrote in message news:31e701c3fd44$14fc2c20$a001280a@phx.gbl... > Outlook Web Access keeps crashing. The only way I know > how to get it going again is...

Money Budget and Mortgage problems HELP!
I set up a budget with my mortgage payment. The problem is, th mortgage payment is acutally showing up on Special when I view th budget and I can't budget anything under special. How do I get th budget to recognize my mortgage so that I don't have to line items i budget one with zero target (special debt) and one with zero expense (debt loan) I am having the same problem. If you or anyone else figures out the answer, please let me know at chelly(remove this)@gci.net. THANK YOU! "marzomarz" wrote: > I set up a budget with my mortgage payment. The problem is, the &...

Access 2003 Form Wizard and Toggle Button Confusion/Problems
I am trying to use the Access 2003 Form Wizard to create a subform that will response to a toggle button that I created and to display that subform within my main form. However, I do not get the wizard window that is suppose to allow me to indicate that I want this to be a linked form so that it will appear within my main form (I see this example in a book that I have). Nor can I figure out how to manually create a link to that subform so that it is associated to the toggle buttion that I created so that it will appear within my main form. (I figured out how to write simple code so ...

Macbook Trackpad right click problem
Apple & Microsoft claim that they do not have the correct driver for MacBook Trackpad to run the full function eg. single click, double click on trackpad & right click function. Please anyone have any suggestions on third party driver is welcome. Thank you. I'm not sure what this has to do with CRM... but: The track pad functionality should all work If you have installed Apples BootCamp drivers. The drivers provided enable all the various hardware - trackpad, bluetooth and wireless, IR, volume and brightness controls etc. The drivers used to be downloadable from Apples sit...

Cd Burning Problems
I am trying to burn a few songs to an Audio CD-R in Windows Media, I have added them to the burn list, however the start burn button at the bottom of the pane does not light up for me to click it to actually start the burn.... Am i missing a step? This worked for me in the past and now i am ver confused? "betty" wrote: > I am trying to burn a few songs to an Audio CD-R in Windows Media, I have > added them to the burn list, however the start burn button at the bottom of > the pane does not light up for me to click it to actually start the burn.... &...

Problems, Problems
I recently installed Windows XP upgrade on my home computer. According to the instructions, all programs would be saved on the computer and work in Windows XP. This held true for my internet connections, favorites, documents etc, but I'm having problems with my email. Under my account name, I had to redo my internet settings, and while I can receive and send email now, I cannot access any of my old contacts, or emails saved in my inbox or other files! To make matters worse, when I try to open Outlook express under my hubby's account (the systems adminstrator on the compute...

Uninstall Sales for Outlook problem
I had to reinstall the server program because CRM trashed it. Now I have it up and running again but I can't install the Sales for Outlook because there is already an install there. When I try to uninstall it (in add/remove programs) it says that this action is only valid for products that are currently installed. How do I manually uninstall Microsoft CRM Sales for Outlook? I am trying so hard to use this program to see if it will be a benefit to customers but after 4 months on and off of trying to get it working, a am not sure I see the point. -- )))))))))))))))))))))))))))))))))...

Percentage problem
Hello, A record can belong to one of three groups and each record has a value attached to it. How can I calculate the total for each group as a percentage of the overall total? like 1 £33 33% 2 £33 33% 3 £33 33% ...

Fix for EXCHANGE to hotmail sending problem bdat
TURN OFF THE AUTO PROTECT FEATURE FOR INTERNET EMAIL IN THE SYMANTEC DESKTOP CLIENT THAT YOU INSTALLED ON YOUR EXCHANGE SERVER. IT MESSES UP BDAT ARGGGG!!!!!!! SOMETIMES I'M SUCH AN IDIOT!!! ...

class problem
He, I wrote the following code under //attributes: protected: BOOL m_bAdding; m_bAdding = FALSE; when I'm compiling it. It give me this error: C:\Documents and Settings\wouter\Mijn documenten\Rocket Administration\Rocket AdministrationView.h(32) : error C2501: 'm_bAdding' : missing storage-class or type specifiers Rocket AdministrationView.cpp C:\Documents and Settings\wouter\Mijn documenten\Rocket Administration\Rocket AdministrationView.h(32) : error C2501: 'm_bAdding' : missing storage-class or type specifiers Can someone help me out? Wouter do not initia...