Type MisMatch Error

[manthony]

Hi, I am getting a type mismatch error on the code below in the "i = CLng(Cl.Value)" line. In a nutshell I have a formula in column AP that will give me a number or a blank. If a number then I am trying to offset/cut and paste the number down x rows (whatever the number value is) and over one column to column AQ. For example if in cell AP3 I have the number 6, then I would like the number 6 to be cut from cell AP3 and pasted into cell AQ9. The code below works only when I hard-code the number in but not when the number is there due to a formula. The problem seems to be in the " i = CLng(Cl.Value)" line. Any ideas?

Sub cutNpaste()
Dim Cl As Range, i As Long
For Each Cl In Columns("AQ").SpecialCells(xlCellTypeConstants, 23)

If Cl.Row > 1 Then
i = CLng(Cl.Value)
Cl.Cut Destination:=Cl.Offset(i, 1)
End If
Next Cl

 

[JoeMo]

If any cell you are looping through has a formula returning "" (zero-length string) you will get a type mis-match - nullstrings are not capable of being coerced to a long data type. Try:

If Cl.Row > 1 Then
If Cl.Value <> "" then
i = CLng(Cl.Value)
Cl.Cut Destination:=Cl.Offset(i, 1)
End If
End If

 

[manthony]

Hi Joe,
Thank you so much this works like a charm. Thank you for the help and for the tip too.