VBA macro - multiplication

[PaulO1890]

Hi,

I am trying to create a macro to do following


Find a particular unit (eg ps) in col Y and multiply data (column AC) with 100.

Problem with my current code is it gives an error "type mismatch" when it tries to do the multiplication.


Dim sFind As String, sAddr As String
Dim rRng As Range, rCl As Range, rFnd As Range

sFind = "ps"
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 * 100

exit_proc:

End Sub

Any suggestions? what am I missing here?

Thanks,
Paul

[BrianMH]

I'm assuming it is a number stored as text on the spreadsheet.

Try

rFnd.Offset(, 4) = cdbl(rFnd.Offset(, 4).Value) * 100

or change the formatting of the spreadsheet to number.