VBA - Error 13 - Type mismatch

paul11

New Member
Joined
Dec 30, 2012
Messages
2
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.

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

MrExcel MVP
Joined
Jan 14, 2009
Messages
2,307
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

New Member
Joined
Dec 30, 2012
Messages
2
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.

Code:
Set rRng = Range("Y1:Y27000")
-P11
 

Forum statistics

Threads
1,082,551
Messages
5,366,281
Members
400,882
Latest member
zaldy_a

Some videos you may like

This Week's Hot Topics

Top