Can anyone simply the following VBA for me? many thanks

[marcusbox]

Hi all,

Could anybody simply the following code with faster execution? Besides, how can I separate the last script "If Range("AH295") <> "" Then Update_Histogram" into another Sub without crashing the original one? many thanks

Private Const BecordToSheetName = "Tickers"



Sub Worksheet_Calculate()
Application.EnableEvents = False
With ThisWorkbook.Sheets(BecordToSheetName)
Dim r As Long
r = Application.WorksheetFunction.CountA(.columns(34)) + 1
If Range("M12") <> 0 Then
.Cells(r, 34).value = Range("M12").value
.Cells(r, 38).value = Range("Z12").value
.Cells(r, 35).value = Range("M31").value
.Cells(r, 36).value = Range("M10").value
.Cells(r, 37).value = Range("M83").value
Range("N12").value = Range("AF12").value
End If
End With
If Range("AH295") <> "" Then Update_Histogram
Application.EnableEvents = True
End Sub

 

[tusharm]

Not quite sure what you want but if you want to speed up the code, maybe you need to turn off automatic calculation.

Bracket your code (just like you did for EnableEvents) as in

    Dim oldCalc: oldCalc = Application.Calculation
    Application.Calculation = xlCalculationManual
    '...
    Application.Calculate
    Application.Calculation = oldCalc

Also, using EnableEvents with error trapping is a prescription for a disaster. See Monitor Events particularly the section 'An Excel specific trap: the EnableEvents property'

Hi all,

Could anybody simply the following code with faster execution? Besides, how can I separate the last script "If Range("AH295") <> "" Then Update_Histogram" into another Sub without crashing the original one? many thanks

Private Const BecordToSheetName = "Tickers"



Sub Worksheet_Calculate()
Application.EnableEvents = False
With ThisWorkbook.Sheets(BecordToSheetName)
Dim r As Long
r = Application.WorksheetFunction.CountA(.columns(34)) + 1
If Range("M12") <> 0 Then
.Cells(r, 34).value = Range("M12").value
.Cells(r, 38).value = Range("Z12").value
.Cells(r, 35).value = Range("M31").value
.Cells(r, 36).value = Range("M10").value
.Cells(r, 37).value = Range("M83").value
Range("N12").value = Range("AF12").value
End If
End With
If Range("AH295") <> "" Then Update_Histogram
Application.EnableEvents = True
End Sub

 

[MARK858]

The code you posted ran on my laptop in 16.42 milliseconds (0.01642 seconds) on the first run and 4.62 milliseconds on a second run with hard-coded numbers.
Obviously not running the Update_Histogram program as we have no idea what that program is (don't like the way to find the last row in Column 34 code though).

I suspect that 16.42 milliseconds is fast enough and so if I was you I'd be looking at the Update_Histogram program.

As for

Besides, how can I separate the last script "If Range("AH295") <> "" Then Update_Histogram" into another Sub without crashing the original one?

If the sub that you have shown is "the original one" then you can just delete the line. If the code shown is not "the original one" then you haven't provided enough info to answer the question.

 

[marcusbox]

Thanks Tushar!! it's awesome

 

[marcusbox]

Thanks for the great link Mark858