[Val] Mecro Error

[Istiyak]

Hi Team,


I have uploaded one file on my website.


http://www.istiyakshaikh.hpage.com > download > problem.xls


Eximine the file and let me know what is the problem.


Copy original data and paste it in column A to D (etc)


While i m running a mecro it is convering value wrongly. (sub convertnm())


Any query plz let me know.


Regards

!$T!

 

[Luke M]

1. Not sure which shortcut you were reffering to, but shortcuts are setup via the Macros menu - Options (not hard coded).

2. Not exactly sure which was original data, as you've got H:K highlighted in workbook, but macro refers to A:D. Either way, I think something like this would work:

[pre]

Range("H2:K24").Copy
Range("A1").PasteSpecial xlPasteValues

[/pre]
3. You can not use Undo after a Macro.

 

[NARAYANK991]

Hi Isti ,


The problem is that the values in J2 and J3 are numeric ; but thereafter , the remaining values in J4 through J24 are all text entries.


Taking J5 as an example , you have a string value 3,797.00


In Excel VBA , open the Immediate window , and type the following :


v = "3,797.00"


?v


You will get the display 3,797.00 , exactly the way it has been entered.


Now , type in :


?val(v)


You will get 3.


If you check the help on the VAL function , it says :


The Val function stops reading the string at the first character it can't recognize as part of a number. Symbols and characters that are often considered parts of numeric values, such as dollar signs and commas, are not recognized.


You can understand the reason for your problem.


Narayan


P.S. Your CTRL e macro runs correctly , and does what it is supposed to do.

 

[SirJB7]

Hi, Istiyak!

I got as confused as Luke M by different things but with the same result.

a) your original data (not to be changed) has not numeric values stored as wrong formatted text strings (interchanged the decimal point and the digits separator) for cells in range J2:K24 except for a few cells (J2, J3, and I didn't fully check but think that no more)

b) can you please tell us in words with a two lines example which are the expected values for the first two data rows?

Regards!

 

[Istiyak]

Hi Luke, Naryank, And SirJB,,


Sorry for the incomplete Details.


Original Data is just for your reference so every time u can copy that data in A:D and do an experiment of Mecro.Its not attached at any point with Mecro.


# SirJB7

I want Result 3447.00 as a number which is available as string.(Exmple of LUKE M)


Current situtaion

Before mecro - 3,447.00 As string

After Mecro - 3 as Number


Supposed situtaion

Before mecro - 3,447.00 As string

After Mecro - 3447.00 as Number

(For Convenienece Do one thing select cell which is as string and select-convert to numbers function from upper corner icon- which i want as a result)


Hope u will understand


Regards

!$T!

 

[SirJB7]

Hi, Istiyak!


Thanks for clarifying, I think that now I got it and maybe this approach would work.

Try using this formula for converting numbers and edited numbers to numeric values. It has only one constraint that it's the assumption of two decimal digits (so it divides by 100): if so, go ahead; otherwise, let me know how will be your data format.


=SUSTITUIR(SUSTITUIR(J3;",";"");".";"")/100 -----> in english: =SUBSTITUTE(SUBSTITUTE(J3,",",""),".","")/100


Apply it to proper range within macro.


Regards!

 

[NARAYANK991]

Hi Isti ,


Change your procedures as follows :

[pre]

Sub converttonm2()
For Each cell In [rng1]
cell.Value = CDbl(cell.Value)
Next cell
End Sub
Sub converttonm3()
For Each cell In [rng2]
cell.Value = CDbl(cell.Value)
Next cell
End Sub

[/pre]

Narayan

 

[Istiyak]

Hi NarayanK ,


what i can say,...??? Excel Ninja.... tussi great ho,.


Thanks a million.


Can I know why we are using CDbl..?


Regards

!$T!

 

[Luke M]

CDbl is the function used to Convert an expression to a Double


This forces any text values (ie, numbers stored as text) to become doubles (a type of number).

 

[SirJB7]

@all


Hi!

I'm missing something or if I have a "1.234,56" value stored in a cell and in my regional configuration settings the dot is decimal point and the comma is digits separator, using CDbl function I'll get 1.23456 (one and nearly a quarter) instead of 1234.56 (one thousand and...) which was the goal?

If I'm wrong, please excuse me, I'd blame my Excel version in Spanish so I remain innocent.

Regards!

 

[Istiyak]

Hi SirJB7,


1.234,56 it means One and .234 part of other one.


that's why excel converts 1.234,56 as 1.23456.


Regards

Istiyak

 

[SirJB7]

Hi, Istiyak!


It will all depends on your regional configuration (characters for decimal point and digit separators), that's why I asked so in my previous post.

Going back to your first uploaded file there were both type of values, look at cells J3:J6 :

[pre]

1.295,00
3.067,04
3,797.00
2,398.00

[/pre]

You can see it as if some values are numeric and other are text, or you can see as numbers but originated in sources with different regional settings and copied "as displayed", probably because of import or paste. I took the second path, and I supposed that all the values were higher than a thousand, not just less than ten.


Again, if I'm not wrong the formula I provided will do the job. I haven't checked all the others suggestions, I leave this dirty job for you.


The only approach I thought it was interesting was the formula solution instead of the macro solution.


Regards!

 

[Istiyak]

hi SirJB7


Ya fair enough you are right your formula is working there.


thanks for the same.


Regards

Istiyak

 

[SirJB7]

Hi, Istiyak!

The goal has been achieved. You could solve your issue, that's the important thing.

Welcome back whenever needed or wanted.

Regards!