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

 

[jtakw]

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

Edit:

I meant this:

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

 

[JLGWhiz]

Edit:

I meant this:

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

@jtakw, the message box statement works for me as is. So the problem is not the quote marks. The problem lies either in the declaration statement or the values in the cells. Until those can be brought to agree with each other as data types, the error will occur.

 

[jovie]

Edit:

I meant this:

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

I dun think this can be work, because once this being changed to "'" or "''", it will turn the msg in to green color, means, it just some words in the code

 

[JLGWhiz]

I dun think this can be work, because once this being changed to "'" or "''", it will turn the msg in to green color, means, it just some words in the code

You are correct, jovie. The problem is not the quote marks. One of your variables, either 'tools' and 'diameter' are the likely suspects. For whatever reason, one or both of them are not matching up with what VBA thinks they should be as a data type. That is what causes the error to occur. When a property or and object is used in the code in a way that is different from how it is declared in the Dim statement, or in a way that causes it to differ from what VBA thinks it should be, it will throw the errror. But that particular error message can be tricky, because under certain conditions, a blank cell can cause it to display. I have spent hours trying to debug some of those. But if you are trying to get the message box to display the values of the variables, then the double quote marks are not needed. If you want to just show the words 'tools' and 'diameter' then you could eliminate the ampersands and all the quote marks except the first and last on the message.

 

[jovie]

You are correct, jovie. The problem is not the quote marks. One of your variables, either 'tools' and 'diameter' are the likely suspects. For whatever reason, one or both of them are not matching up with what VBA thinks they should be as a data type. That is what causes the error to occur. When a property or and object is used in the code in a way that is different from how it is declared in the Dim statement, or in a way that causes it to differ from what VBA thinks it should be, it will throw the errror. But that particular error message can be tricky, because under certain conditions, a blank cell can cause it to display. I have spent hours trying to debug some of those. But if you are trying to get the message box to display the values of the variables, then the double quote marks are not needed. If you want to just show the words 'tools' and 'diameter' then you could eliminate the ampersands and all the quote marks except the first and last on the message.

Because I want to show what tools and what diameter is being used, so I used """, if I just simply used ', then it just show 'tools', 'diameters', and that is not I want, I have think this for quite a long time already, I also change the item and diameter's type to variant, but all result same error

 

[JLGWhiz]

This might be what you want.

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

 

[jovie]

This might be what you want.

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

But also result to same error. I really confused le

 

[RoryA]

What do these return:

 msgbox range("diameter").Count
msgbox range("diameter").text

 

[jovie]

What do these return:

 msgbox range("diameter").Count
msgbox range("diameter").text

150, what does it mean???

 

[jovie]

Then error 94, point to

msgbox range("diameter").text