Code Problem !

[Mike R]

Hi Guys, Can anybody help me with the following piece of
code !?. It works to a point, but I keep getting the error
message "Type Mismatch, run error 13" after execution.
Also if I change any cell on the worksheet the macro
executes!, even though i have specified the target cell.
Basically the macro should on run based on a value a user
enters in "J13".

Any help very much appreciated

Mike R

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.ScreenUpdating = False
With Application
.Calculation = xlManual
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
If Target.Value >= "$j$13" Then
Range("c13").ClearContents
Range("j13").Select
Application.CutCopyMode = False
Selection.Copy
Range("c13").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _

Application.EnableEvents = True
Application.ScreenUpdating = False
With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
End If
End Sub

 

[John Ford]

Doesn't the Type Mismatch error give a line
number? That would help narrow it down.

Is the Selection.PasteSpecial line correct? Looks
like it's missing something after the
continuation.

-jcf

|
| Hi Guys, Can anybody help me with the following
piece of
| code !?. It works to a point, but I keep getting
the error
| message "Type Mismatch, run error 13" after
execution.
| Also if I change any cell on the worksheet the
macro
| executes!, even though i have specified the
target cell.
| Basically the macro should on run based on a
value a user
| enters in "J13".
|
| Any help very much appreciated
|
| Mike R
|
| Private Sub Worksheet_Change(ByVal Target As
Excel.Range)
| Application.ScreenUpdating = False
| With Application
| .Calculation = xlManual
| .MaxChange = 0.001
| End With
| ActiveWorkbook.PrecisionAsDisplayed = False
| If Target.Value >= "$j$13" Then
| Range("c13").ClearContents
| Range("j13").Select
| Application.CutCopyMode = False
| Selection.Copy
| Range("c13").Select
| Selection.PasteSpecial Paste:=xlValues,
| Operation:=xlNone, SkipBlanks:= _
|
| Application.EnableEvents = True
| Application.ScreenUpdating = False
| With Application
| .Calculation = xlAutomatic
| .MaxChange = 0.001
| End With
| ActiveWorkbook.PrecisionAsDisplayed =
False
| End If
| End Sub

 

[Keith Willshaw]

Hi Guys, Can anybody help me with the following piece of
code !?. It works to a point, but I keep getting the error
message "Type Mismatch, run error 13" after execution.
Also if I change any cell on the worksheet the macro
executes!, even though i have specified the target cell.
Basically the macro should on run based on a value a user
enters in "J13".

Any help very much appreciated

Mike R

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.ScreenUpdating = False
With Application
.Calculation = xlManual
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
If Target.Value >= "$j$13" Then

This makes no sense. What are you trying to achieve here ?

Keith

 

[Mike R]

Thanks John, code was missing from Selection.PasteSpecial
line.

 

[Don Guillett]

If all you want to do is copy the j13 values to c13 then this will do it.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
if target.address <> "$J$13" then exit sub
[c13].value=target
end sub

 

[Mike R]

Thanks Don, that's what I was looking for., makes
everthing alot easier

-----Original Message-----
If all you want to do is copy the j13 values to c13 then this will do it.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
if target.address <> "$J$13" then exit sub
[c13].value=target
end sub
--
Don Guillett
SalesAid Software
Granite Shoals, TX
(e-mail address removed)

Hi Guys, Can anybody help me with the following piece of
code !?. It works to a point, but I keep getting the error
message "Type Mismatch, run error 13" after execution.
Also if I change any cell on the worksheet the macro
executes!, even though i have specified the target cell.
Basically the macro should on run based on a value a user
enters in "J13".

Any help very much appreciated

Mike R

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.ScreenUpdating = False
With Application
.Calculation = xlManual
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
If Target.Value >= "$j$13" Then
Range("c13").ClearContents
Range("j13").Select
Application.CutCopyMode = False
Selection.Copy
Range("c13").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _

Application.EnableEvents = True
Application.ScreenUpdating = False
With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
End If
End Sub

.