VBA - Error 13 - Type mismatch

[paul11]

Hi,

I have this macro which looks for the unit 'ps' in Column Y and multiplies 1E+12 with the corresponding data in column AC. When it tries to multiply 1E+12, it throws a type mismatch error.

Below is the code.

Sub Multiply_Tdata()
'
' Change the value of the data (AC) column - multiply by 1E+12
'


'
    Dim sFind As String, sAddr As String
    Dim rRng As Range, rCl As Range, rFnd As Range
     
    sFind = "ps"   '<-could use InputBox
    Set rRng = Range("Y1:Y27000")
    With rRng
        Set rCl = .Find(sFind, LookIn:=xlValues)
        If Not rCl Is Nothing Then
            sAddr = rCl.Address
            Do
                If rFnd Is Nothing Then
                    Set rFnd = rCl
                Else: Set rFnd = Union(rCl, rFnd)
                End If
                Set rCl = .FindNext(rCl)
            Loop While Not rCl Is Nothing And rCl.Address <> sAddr
        Else: MsgBox "Cannot find " & sFind
            GoTo exit_proc
        End If
    End With


'    Debugger throws a type mismatch error on below line
    
     rFnd.Offset(, 4) = rFnd.Offset(, 4).Value * 1000000000000
    
exit_proc:


End Sub

What am I missing here??

Thanks,
P11

 

[Ron Coderre]

You'll receive that error if the Col_AC value on the same row as the Col_Y cell that contains "ps" contains text, instead of a number.
Example:
Y1: ps
AC1: AAAAA

 

[paul11]

You'll receive that error if the Col_AC value on the same row as the Col_Y cell that contains "ps" contains text, instead of a number.
Example:
Y1: ps
AC1: AAAAA

Thank you. Looks like Y1 was the problem. I set it to Y2:Y27000 and now it is working fine.

Set rRng = Range("Y1:Y27000")

-P11