error 13 while running MAcro 1

[Smoothas]

Hello,

I got pruned this macro out of one I current use. IT traverses the spreadsheet and appends the cell to the one above if eveything else on the row is empty.

However, when I runn the pruned version, it completes the sheet, but I then get :-

Run time error "13"
Type mismatch

the macro is :-

Dim R As Range, C As Range, All_Data As Range, lRow As Long
'
Application.StatusBar = "Appending wrapped notes...Please Wait"

'''''Append wrapped data
Set All_Data = Range([G2], Cells(Cells.Rows.Count, "H").End(xlUp))

For Each R In All_Data
If R = "" Then
For Each C In Range(R, R.End(xlToRight))
If C.Value <> "" Then
With Cells(lRow, C.Column)
.Value = .Value & " " & C.Value
C.Clear
End With
End If
Next
Else
lRow = R.Row
End If
Next R

the debugger stops at " IF C.Value <>"" Then


Thanks in advance as always

 

[SkipVought]

Hi,

This...

Set All_Data = Range([G2], Cells(Cells.Rows.Count, [b]"H"[/b]).End(xlUp))

and this...

For Each C In Range(R, R.End(xlToRight))

do not make sense together.

Why not just column G with the End(xlToRight)????

Set All_Data = Range([G2], Cells(Cells.Rows.Count, [b]"G"[/b]).End(xlUp))

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Smoothas]

hi,
Thanks for the advise Skip, I'll give that a go.

as for the error, turns out there was a #NAME? entry in one of the last cells. The Teast read " + call home ".
Excel read the + as the start of a expression, and stuck a = infront of it, cuasing the #NAME? and my error.
I've amended the whole macro to now search for any cell starting =+ and replace it with PLUS.
Macro works great now.

 

[SkipVought]

You could also do this to avoid having to run that post process fix...

YourCell.Value = [b][red]"'"[/red][/b] & YourVariableContaining+CallHome

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Smoothas]

brillant. Thanks again Skip