Results 1 to 15 of 15
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    activeCell,Calculate Does Not Work or pass value

    Hi Again:
    (Excel 97 SR22 Win98)
    I need some help with the following code, which is called from another sub:

    Sub Current_Data_Code()
    Dim BottomRow As Long
    Worksheets("current_Data").Activate
    Range("C4:O10000").ClearContents
    Worksheets("current_Data").Range("c2:c2").QueryTab le.Refresh
    Range("k1:k1").Select
    Range("k1").Activate
    'Worksheets("current_Data").Range("K1:K1").Activat e
    Application.ActiveCell.Calculate
    BottomRow = ActiveSheet.Range("K1:K1")
    ActiveSheet.Range("L3:O3").Activate
    Range("L3:O3").Select
    Selection.AutoFill Destination:=Range("L3:O" & BottomRow)
    Worksheets("current_Data").Range("L3:O" & BottomRow).Calculate

  2. #2
    Lounger
    Join Date
    Apr 2001
    Location
    Michigan, USA
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: activeCell,Calculate Does Not Work or pass value

    you can try doing a "Brute Force Recalc" (for lack of a better term).
    try adding this code to the end of your procedure and see if it recalcs properly:

    SendKeys "(^%{F9})"

    also

    "BottomRow" = counta(C:C)

    may need to be

    "BottomRow" = worksheetfunction.counta(range("C:C"))

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: activeCell,Calculate Does Not Work or pass value

    An easier way of finding the bottom row might be:

    <pre> BottomRow = Worksheets("current_Data").Range("A65536").End(xlU p).Row
    </pre>

    Legare Coleman

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: activeCell,Calculate Does Not Work or pass value

    I tried the WorkSheetFunnction.CountA(Range("C:C")) and it worked OK. But SendKeys "(^%{F9})", inserted in the row following
    Range("k1").Activate
    did not do anything. Am I missing something?

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: activeCell,Calculate Does Not Work or pass value

    I think you will need to put a DoEvents after the SendKeys before they will be executed. I think you might also have to do the same thing to get the Calculate in your code to take affect.
    Legare Coleman

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: activeCell,Calculate Does Not Work or pass value

    Legare:
    I tried inserting
    BottomRow = WorksheetSheets("current_Data").Range("C65536").En d(xlUp).Row
    and blocking out BottomRow = ActiveSheet.Range("K1:K1")
    but I got the message "Sub or Function undefined"??
    Stephen
    <img src=/S/help.gif border=0 alt=help width=23 height=15>

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: activeCell,Calculate Does Not Work or pass value

    Legare:
    Like SendKeys "(^%{F9})".DoEvents?

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: activeCell,Calculate Does Not Work or pass value

    No, like:

    <pre> SendKeys "(^%{F9})"
    DoEvents
    </pre>


    BTW, I personally do not like using SendKeys and recommend doing it another way whenever possible.
    Legare Coleman

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: activeCell,Calculate Does Not Work or pass value

    Try changing WorksheetSheets to WorkSheets.
    Legare Coleman

  10. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: activeCell,Calculate Does Not Work or pass value

    Legare:
    I tried this and still did not get anywhere. Here is the code as of the last run (I threw everything at it):
    Sub Current_Data_Code()
    Dim BottomRow As Long
    Worksheets("current_Data").Activate
    Range("C4:K10000").ClearContents
    Worksheets("current_Data").Range("c2:c2").QueryTab le.Refresh
    Range("k1:k1").Select
    Range("k1").Activate
    Application.ActiveCell.Calculate
    SendKeys "(^%{F9})"
    DoEvents
    'Worksheets("current_Data").Range("K1:K1").Activat e
    BottomRow = ActiveSheet.Range("K1:K1")
    'BottomRow = WorksheetFunction.CountA(Range("C:C"))
    'BottomRow = WorksheetSheets("current_Data").Range("C65536").En d(xlUp).Row
    ActiveSheet.Range("L3:O3").Activate
    Range("L4:O10000").ClearContents
    Range("L3:O3").Select
    Selection.AutoFill Destination:=Range("L3:O" & BottomRow)
    Worksheets("current_Data").Range("L3:O" & BottomRow).Calculate
    Range("G1").Value = Now()
    Set pvtTable = Worksheets("current_Data").Range("R6:R6").PivotTab le
    pvtTable.RefreshTable
    Range("T4").Value = Now()
    Call projStart_FillDown 'Module6
    Call copy_transpose 'Module2
    Call Copy_From_Summary_ProjTo_Summ_FTEs 'Module1
    End Sub
    In case it could it have anything to do with the sub that is calling this sub, I have pasted this one below:

    Private Sub CommandButton2_Click()
    Dim LastRow As Long
    LastRow = ActiveSheet.Range("D11") + 4
    Range("c6:M500").ClearContents
    Range("c5:m5").Select
    Selection.AutoFill Destination:=Range("C5:M" & LastRow)
    Worksheets("projstart (2)").UsedRange.Columns("C:M").Calculate
    Call Current_Data_Code
    End Sub

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: activeCell,Calculate Does Not Work or pass value

    I'm not sure why that does not work. Maybe someone else will jump in with an idea.
    Legare Coleman

  12. #12
    Lounger
    Join Date
    Apr 2001
    Location
    Michigan, USA
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: activeCell,Calculate Does Not Work or pass value

    If after your macro runs, will hitting Ctrl+Alt+F9 all at the same time cause the cell in question (K1?) to calculate?

    if so, try putting SendKeys "(^%{F9})" as the very last line before "end sub" of the last procedure that runs.

  13. #13
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: activeCell,Calculate Does Not Work or pass value

    I finally got it to work by separating the code into two CmdClick buttons but I sure would like to feel like I learned something from 4 or 5 hours <img src=/S/toilet.gif border=0 alt=toilet width=24 height=26> today. As it is, I will have to instruct the user to click three buttons rather than one.
    Here is the code that works:
    Private Sub CommandButton1_Click()
    Range("C4:K10000").ClearContents
    Worksheets("current_Data").Range("c2:c2").QueryTab le.Refresh
    Worksheets("current_Data").Calculate
    Range("G1").Value = Now()
    End Sub


    Private Sub CommandButton2_Click()
    Dim BottomRow As Long
    SendKeys "(^%{F9})"
    DoEvents
    Worksheets("Current_Data").Range("K1:K1").Calculat e
    BottomRow = Worksheets("current_Data").Range("K1:K1")
    Range("L4:O10000").ClearContents
    Range("L3:O3").Select
    Selection.AutoFill Destination:=Range("L3:O" & BottomRow)
    Worksheets("current_Data").Range("L3:O" & BottomRow).Calculate
    Range("G1").Value = Now()
    Set pvtTable = Worksheets("current_Data").Range("R6:R6").PivotTab le
    pvtTable.RefreshTable
    Range("T4").Value = Now()
    Call projStart_FillDown 'Module6
    Call copy_transpose 'Module2
    Call Copy_From_Summary_ProjTo_Summ_FTEs 'Module1
    End Sub

    I would be glad to here from anyone who understands why the other methods did not work.
    <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  14. #14
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: activeCell,Calculate Does Not Work or pass value

    I really don't understand what is happening here. If splitting the routine into two worked, then using the DoEvents should have worked. The only thing I can figure is that the DoEvents did not give the Calculate enough time to finish. I can think of two ways around that without having to press two buttons.

    1- Repeat the DoEvents a number of times in a For loop like this:

    <pre> For I=1 to 100
    DoEvents
    Next I
    </pre>


    You would have to determine how many times to go through the loop by trial and error.

    2- Use OnTme to schedule the second half of the procedure to run a few seconds after the first half. You could do that by inserting the following as the last line of the CommandButton1_Click routine:

    <pre> Application.OnTime Now() + TimeValue("00:00:02"), "SecondHalf"
    </pre>


    Where "SecondHalf" is the name of the second half of the routine, currently named CommandButton2_Click. That name does have to be in quotations. The line of code above schedules the second routine to run two seconds after the first one ends.

    I still think that the better solution is to use the alternate code I gave you earlier for finding the last used cell.
    Legare Coleman

  15. #15
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: activeCell,Calculate Does Not Work or pass value

    I tried:
    BottomRow = WorksheetSheets("current_Data").Range("C65536").En d(xlUp).Row"
    ActiveSheet.Range("L3:O3").Activate
    Range("L4:O10000").ClearContents
    Range("L3:O3").Select
    Selection.AutoFill Destination:=Range("L3:O" & BottomRow)
    .......
    ......

    But, for some reason, the code did not pass the argument to the last lne of the above code. I suspect that it will work fine if I attach the code to the CmdClick Buttons. I will try it tomorrow or Wednesday along with your other suggestion and let you know what happened.
    Your suggestion
    "that the DoEvents did not give the Calculate enough time to finish" may be right because, I could see it hang on the K1 cell for a couple of seconds without seeing the value change.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •