Calculation

[Guest]

I have the following code in Worksheet SelectionChange
"Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Worksheets(2).Calculate
End Sub"

This for some reason disallows Paste, PasteSpecial and some other features.
Macros having copy and paste fail. I am now trying to have the calculation
applicapable to rows 1 to 341. I amended the code to the following, but the
recalculation is effective only on row 341.
"Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Worksheets(2).Rows(341).Calculate
End Sub"

Any assistance. Thank you

Robert

 

[Dr. Stephan Kassanke]

I have the following code in Worksheet SelectionChange
"Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Worksheets(2).Calculate
End Sub"

This for some reason disallows Paste, PasteSpecial and some other
features.
Macros having copy and paste fail. I am now trying to have the calculation
applicapable to rows 1 to 341. I amended the code to the following, but
the
recalculation is effective only on row 341.
"Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Worksheets(2).Rows(341).Calculate
End Sub"

Any assistance. Thank you

Robert

Robert,

try Worksheets(2).Rows("1:341").calculate.

cheers,
Stephan

 

[Norman Jones]

Hi Robert,

This for some reason disallows Paste, PasteSpecial and some other
features.

Much macro activity clears the clipboard.

Macros having copy and paste fail.

Rewrite the code to ensure that the paste operation follows the copy
operation without any intervening operations. As noted above, such
intervening operations may clear the clipboard and cause the problems you
report.

 

[Guest]

Thank you Norman, Everthing now works well.
Dr. Stephan, thank you for your input. Points noted.

 

[Guest]

In my earlier message, the addressees have been transposed.
Sorry Dr. Stephan, in my haste got mixed.

 

[Dr. Stephan Kassanke]

In my earlier message, the addressees have been transposed.
Sorry Dr. Stephan, in my haste got mixed.

Robert,

no problem, it's still early (at least for me ;-)

Just out of curiosity - is there a particular reason you trigger the
calculation each time the selection changes? This is a higly inefficient
approach as no values change by selecting another cell.

Stephan

 

[Guest]

Dr. I am embarrased to say the file I have with a lot of VLOOKUPs is 48Mb. My
sheet(2) in reference is actually a Data Entry Screen which does
calculations and more than 40 different values values are then posted to
other sheets. The data in these sheets need not be processed real-time. When
calculation is set to auto it took an intolerably long time before the "entry
screen" is ready for the next entry. I will later check for what may be
causing the delay.


Robert
PS. You would have guessed, I do not know any VBA.

 

[Dr. Stephan Kassanke]

Dr. I am embarrased to say the file I have with a lot of VLOOKUPs is 48Mb.
My
sheet(2) in reference is actually a Data Entry Screen which does
calculations and more than 40 different values values are then posted to
other sheets. The data in these sheets need not be processed real-time.
When
calculation is set to auto it took an intolerably long time before the
"entry
screen" is ready for the next entry. I will later check for what may be
causing the delay.


Robert
PS. You would have guessed, I do not know any VBA.

Dear Robert,

just my 2cent. You are currently using the SelectionChange event which is
fired each time you change the selection. You can recalculate your sheet
each time a *change* occurs by using the Change event like

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

Further speed can be achieved by testing whether Target (the cell where the
change occurs) intersects with your data entry cells thus irrelevant changes
do not force a recalculation.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not (Intersect(Range("A1:A5"), Target) Is Nothing) Then
MsgBox "Change in cell A1-A5"
Exit Sub ' or recalculate
' else do nothing
End If
End Sub

cheers,
Stephan

 

[Guest]

Doc, Thank you for your advice and lessons. I really appreciate them.
The Worksheet_Change you suggested is already used to capture the time of
entry (all of these have been copied from the ng). How many Worksheet_Change
can be incorporated andhow can your code be incorporated. My existing code-

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:

Application.EnableEvents = False
If Not Intersect(Target, Me.Range("Sequence")) Is Nothing Then
With Target
.Offset(25, 0).Value = Format(Now, "dd mmm yy hh:mm:ss")
.Offset(25, 1).Value = Environ("UserName")
End With
End If
‘THERE ARE ANOTHER 6 SUCH CODES HERE
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("Amt")) Is Nothing Then
With Target
.Offset(12, 0).Value = Format(Now, "dd mmm yy hh:mm:ss")
.Offset(12, 1).Value = Environ("UserName")
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

 

[Dr. Stephan Kassanke]

Doc, Thank you for your advice and lessons. I really appreciate them.
The Worksheet_Change you suggested is already used to capture the time of
entry (all of these have been copied from the ng). How many
Worksheet_Change
can be incorporated andhow can your code be incorporated. My existing
code-

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:

Application.EnableEvents = False
If Not Intersect(Target, Me.Range("Sequence")) Is Nothing Then
With Target
.Offset(25, 0).Value = Format(Now, "dd mmm yy hh:mm:ss")
.Offset(25, 1).Value = Environ("UserName")
End With
End If
'THERE ARE ANOTHER 6 SUCH CODES HERE
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("Amt")) Is Nothing Then
With Target
.Offset(12, 0).Value = Format(Now, "dd mmm yy hh:mm:ss")
.Offset(12, 1).Value = Environ("UserName")
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

Hi Robert,

there is only one change event for a worksheet but you can integrate your
calculation routine in the change event.

.........
'THERE ARE ANOTHER 6 SUCH CODES HERE
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("Amt")) Is Nothing Then
With Target
.Offset(12, 0).Value = Format(Now, "dd mmm yy hh:mm:ss")
.Offset(12, 1).Value = Environ("UserName")
End With
End If

' PUT CALCULATION STUFF HERE

ws_exit:
Application.EnableEvents = True
end sub

Stephan

 

[Guest]

Thanks Doc, I am going to give it a shot over the weekend.
Will definately let you know.