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

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>

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" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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
>>
>
>

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>>> 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
>>>
>>
>>
>
>