Convert Percent Entered as Text in Wksht to Number in VBA
 

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

Convert Percent Entered as Text in Wksht to Number in VBA
 

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

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Keith Young" wrote in message
...
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

Convert Percent Entered as Text in Wksht to Number in VBA
 

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Keith Young" wrote in message
...
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

Convert Percent Entered as Text in Wksht to Number in VBA
 

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" wrote in message
...
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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Keith Young" wrote in message
...
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

Convert Percent Entered as Text in Wksht to Number in VBA
 

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Keith Young" wrote in message
...
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" wrote in message
...
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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Keith Young" wrote in message
...
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