can anyone help me to see what is wrong with my code

[jovie]

It said error 13 type mismatch

Sub Most_frequently_used_tools()
Dim strData As String
Dim item As String
Dim diameter As String
Dim rng As Range
Dim maximum As Variant
Dim rngCol As Range
Dim lngRow As Long
Dim rngAdd As Range
     
'Enter desired range in which to find the maximum value
strData = "Total_tools_out"

Set rng = Range(strData)
     
'Determines maximum value in range
maximum = Application.WorksheetFunction.Max(rng)
 
For Each rngCol In rng.Columns

'Returns row number of the maximum value, in the column which has the same
lngRow = Application.WorksheetFunction.Match(maximum, rngCol, 0)
  
'Returns cell address of the maximum value
Set rngAdd = rngCol.Cells(lngRow, 1)
    
'Selects smallest value to highlight with color
rngAdd.Select

With Selection
tools = Range("item").Value
End With

With Selection
diameter = Range("diameter").Value and point here   
End With
    
With Selection
.Interior.Color = RGB(255, 140, 0)
End With
        
'Message displays the searched range, smallest value, and its address
MsgBox "Most frequently used tools is (""" & tools & """) and size is (""" & diameter & """) total used " & maximum & " number of tools."
Next

With Selection
.Interior.ColorIndex = 0
End With

End Sub

 

[JLGWhiz]

You have diameter declared as a String. If it is a number then it would give you that error. If your value can be a decimal value then diameter probralby should be declared as a Double. Or you could play it safe and declare it as a Variant and let VBA figure it out. Be sure your declarations (Dim statements) reflect how the variables are actually used to avoid the Type Mismatch messages.

 

[jovie]

Actually my diameter is combine with the diameter symbol, numbers and alphabet, and I am not sure which type is it, so I just put string.

 

[jovie]

Er....... I just change the type of diameter to variant, and it show

MsgBox "Most frequently used tools is (""" & tools & """) and size is (""" & diameter & """) total used " & maximum & " number of tools."

This one is error 13 type mis match...
Can help me?

 

[Smitty]

In Break mode you can hover over each variable to see which one is having an issue. That should at least point you in the right direction.

 

[jovie]

I check my variable, but it seems like no problem, just the

MsgBox "Most frequently used tools is (""" & tools & """) and size is (""" & diameter & """) total used " & maximum & " number of tools."

this one got problem, and I dun know y

 

[jtakw]

I check my variable, but it seems like no problem, just the
this one got problem, and I dun know y

Hi, I don't think you can use quotes in your code like this, """, change it to apostrophe like "'" or "'" & "'"

 

[RoryA]

What exactly is in your range called diameter? I suspect either that refers to more than one cell, or it contains an error.

 

[jovie]

What exactly is in your range called diameter? I suspect either that refers to more than one cell, or it contains an error.

Actually is size, like R5 x SH7.5
or diameter(symbol)3.0mm

 

[RoryA]

Neither of those could cause the errors you describe.