activeCell,Calculate Does Not Work or pass value

Topic

New Reply

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”).QueryTable.Refresh
Range(“k1:k1”).Select
Range(“k1”).Activate
‘Worksheets(“current_Data”).Range(“K1:K1”).Activate
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

Reply | Quote

Replies

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”))

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

No, like:

    SendKeys "(^%{F9})"
    DoEvents

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

Reply | Quote

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”).QueryTable.Refresh
Range(“k1:k1”).Select
Range(“k1”).Activate
Application.ActiveCell.Calculate
SendKeys “(^%{F9})”
DoEvents
‘Worksheets(“current_Data”).Range(“K1:K1”).Activate
BottomRow = ActiveSheet.Range(“K1:K1”)
‘BottomRow = WorksheetFunction.CountA(Range(“C:C”))
‘BottomRow = WorksheetSheets(“current_Data”).Range(“C65536”).End(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”).PivotTable
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(“D1:D1”) + 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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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 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”).QueryTable.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”).Calculate
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”).PivotTable
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.

Reply | Quote

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:

    For I=1 to 100
        DoEvents
    Next I

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:

    Application.OnTime Now() + TimeValue("00:00:02"), "SecondHalf"

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.

Reply | Quote

I tried:
BottomRow = WorksheetSheets(“current_Data”).Range(“C65536″).End(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.

Reply | Quote

An easier way of finding the bottom row might be:

    BottomRow = Worksheets("current_Data").Range("A65536").End(xlUp).Row

Reply | Quote

Legare:
I tried inserting
BottomRow = WorksheetSheets(“current_Data”).Range(“C65536”).End(xlUp).Row
and blocking out BottomRow = ActiveSheet.Range(“K1:K1”)
but I got the message “Sub or Function undefined”??
Stephen

Reply | Quote

Try changing WorksheetSheets to WorkSheets.

Reply | Quote