Code Problem !

  • Thread starter Thread starter Mike R
  • Start date Start date
M

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
 
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
 
Mike R said:
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
Click to expand...

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

Keith
 
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
 
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)
Mike R said:
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
Click to expand...


.
Click to expand...
 
Back
Top