Convert Percent Entered as Text in Wksht to Number in VBA

[Keith Young]

This should be easy but am having a very difficult time.

The percent 10.00% is entered exactly as this in a worksheet cell as text.
What VBA function will convert to a number? Thought Val or CDbl would do it
but both generate an error (Type Mismatch)

Msgbox Val(Sheet1.Range("A1").value)
Msgbox CDbl(Sheet1.Range("A1").value)

Thanks in advance for your help. Keith

 

[Bernard Liengme]

Either of these formulas will do the trick: (a) =VALUE(A1), (b) =--A1

 

[Bernard Liengme]

If VBA must be used

Sub trythis()
Set d = Range("A1")
perval = Val(Mid(d, 1, Len(d) - 1)) / 100
MsgBox perval
End Sub

best wishes

 

[Keith Young]

yes, that is what I needed. Thank you very much

Strange that the worksheet function Value will convert it but the VBA
function Val will not.

 

[Bernard Liengme]

The big problem is that VBA knows nothing about % as a way to indicate a
value is to be treated as being divided by 100
all the best