Runtime error 13 - Mismatch

[Stipje]

Hi,

Im trying to Call a macro in vba automatically when the value of cell A3 is changed.
The macro runs, but when its finished I get the Run-time error 13 - Mismatch
Now I read on different places that this means for instance a string and a number are mixed up in a formula.
I just don't see how this applies here.

This is the code. The line in red shows where the error comes from.

Private Sub Worksheet_Change(ByVal Target As Range)



If Target.Address = "$A$3" And Target.Value < 8 Then
Range("C4:C375").Select
Selection.UnMerge
Call Version2
End If

End Sub

I hope you can help me

 

[Andrew Poulsom]

What does Target contain? You will get a type mismatch if it contains an error.

 

[MARK858]

First of all you don't need to select the cell (see below). If it still errors can you post the code for the Version2 macro.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$3" And Target.Value < 8 Then
  Range("C4:C375").UnMerge
  Call Version2
End If  
End Sub

Edit: and don't miss Andrew's question in the previous post

 

[Stipje]

Thank you for your reactions
As far as I know the MAcro version2() Doesn't contain an error.
A Mark858, I just started working with macro's , so I guess that's a shorter way for writing it, I changed it and the macro still works the same that way.

This is the macro it calls:

Sub Version2()




'~~> For Group MERGING (Merge Cells and Keep All text)


On Error GoTo ErrMergeAll


Application.DisplayAlerts = False


Dim Cl As Range 'Dim defines variables
Dim strTemp As String
Dim SingleCell As Range
Dim WeekOneCell As Range
Dim ListOfCells As Range
Dim FirstWeek As Range

Set ListOfCells = Range("A7", Range("A7").End(xlDown))
Set FirstWeek = Range("A4:A8")

For Each WeekOneCell In FirstWeek
If WeekOneCell.Value = 1 Then
WeekOneCell.Offset(0, 2).Select
Range(Selection, Selection.Offset(4, 0)).Select
If WeekOneCell.Value = 1 Then Exit For
ElseIf WeekOneCell.Value = 2 Then
WeekOneCell.Offset(0, 2).Select
Range(Selection, Selection.Offset(3, 0)).Select
If WeekOneCell.Value = 2 Then Exit For
ElseIf WeekOneCell.Value = 3 Then
WeekOneCell.Offset(0, 2).Select
Range(Selection, Selection.Offset(2, 0)).Select
If WeekOneCell.Value = 3 Then Exit For
ElseIf WeekOneCell.Value = 4 Then
WeekOneCell.Offset(0, 2).Select
Range(Selection, Selection.Offset(1, 0)).Select
If WeekOneCell.Value = 4 Then Exit For
ElseIf WeekOneCell.Value = 5 Then
End If

Next WeekOneCell


For Each SingleCell In ListOfCells
If SingleCell.Value = 1 Then
SingleCell.Offset(0, 2).Select
Range(Selection, Selection.Offset(4, 0)).Select
End If




'~~> Collect values from all the cells and separate them with spaces
For Each Cl In Selection
If Len(Trim(strTemp)) = 0 Then
strTemp = strTemp & Cl.Value
Else
strTemp = strTemp & vbNewLine & Cl.Value
End If
Next


strTemp = Trim(strTemp)


'~~> Merging of cells
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.MergeCells = False
End With
Selection.Merge



Next SingleCell


'~~> Set new value of the range
Selection.Value = strTemp


Application.DisplayAlerts = True
Exit Sub


ErrMergeAll:
MsgBox Err.Description, vbInformation
Application.DisplayAlerts = True



End Sub

 

[MARK858]

When your code errors on the yellow line if you hover your mouse over Target.Address is the range it shows what you were expecting to see?

 

[Stipje]

No, I expected it to have something to do with the cell A3, and it shows C365:C369. Do you know why this is?
It does merge C365:C369, which was what I hoped it did. But it also shows a number in the cell and i don't know why.
It shows 2,85714285714286 and under that 1,85714285714286 and it goes on, I don't know why it brings up these numbers either

 

[MARK858]

Do you know why this is?

Because you have selected the range so it thinks that is the Target.Address.


First of all change your worksheet code to the code below just to get you past the error

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Address <> "$A$3" Then Exit Sub
    If Target.Value < 8 Then
        Range("C4:C375").Select
        Selection.UnMerge
        Call Version2
    End If
End Sub

then comment out you the displayalerts = false and put a breakpoint on the line below in the Version2 macro (to do this click in the line and press F9).
Now when you change A3 with a number below 8 the code will run and stop at the line.

If you now repeatedly press F8 and watch what happens on your sheet you will see where the numbers come from.


line to put the breakpoint on is

For Each Cl In Selection

 

[Stipje]

Thank you very much! I solved both questions now.

Indeed your solution got me past the error, which was a big relief.

The numbers I solved by making a slight change to the macro, for your interest:

For Each Cl In Selection
If Len(Trim(strTemp)) = 0 Then
strTemp = strTemp & Cl.Value
Else
strTemp = strTemp & vbNewLine & Cl.Value
End If
Next

I changed to:

For Each Cl In Selection
If Len(Trim(strTemp)) = 0 Then
strTemp = ""
Else
strTemp = strTemp & vbNewLine & Cl.Value
End If
Next

Thank you so much for your help. I'm only beginning to look into macro's and I find it very interesting and enjoyable.
It's such a headache when you can't figure where you are wrong though

 

[MARK858]

Happy you worked it out. I was hoping you could solve it with just a bit of a nudge as it is easier to learn it that way