PDA

View Full Version : Solved: Individual Cell Selection and Format Changes



pedrovarela
10-09-2008, 06:49 AM
Hi,

I have a huge Excel (dimensions: rows from 4 to 2434 and columns from D to BG) where I have a mixed of numbers and percentages stored in FORMAT GENERAL or NUMBER and with COMAS.
In order to use this numbers in an existing macro, I would like first to change all of them to FORMAT GENERAL and change the coma for a point.

How can I go through all the columns to compare if there is a coma? How can I store the number in format general?

Thnaks!!!!

Bob Phillips
10-09-2008, 06:54 AM
Why not just select the lot and format it as GEneral?

pedrovarela
10-09-2008, 07:25 AM
Thanks for the reply! I cannot do it because it's a mix of numbers. Some columns are in a sepcific number format. I'm intrested in running through each cell, and if I see a text with a coma, replace it with a point and strore it as a number with general format. I always us Range and don' know how to use cell...

Bob Phillips
10-09-2008, 08:10 AM
And what if a number has 2 commas, or 3, and so on?

georgiboy
10-09-2008, 10:06 AM
This will search for data stored as text and then remove "," and replace with "." and then change it to General format.

Sub FormatChange()
Dim MyRange, rCell As Range

Set MyRange = Range("A1:A10")

For Each rCell In MyRange.Cells

If rCell.NumberFormat = "@" Then

rCell.Replace What:=",", Replacement:="."

rCell.NumberFormat = "General"

End If

Next rCell


End Sub

Hope this is close to what you are looking for.

pedrovarela
10-10-2008, 03:00 AM
Unfortunately it didn' work. I have attached a reduced version of the file with the macro. With the "if @" it didn't do anything. If I remove that if, the format changed but the comas are still there. Any hint?

Bob Phillips
10-10-2008, 04:03 AM
Sub FormatChange()
Dim MyRange, rCell As Range

Set MyRange = Range("A1").CurrentRegion

For Each rCell In MyRange.Cells

If rCell.NumberFormat = "General" Then

rCell.Replace What:=",", Replacement:="."

rCell.NumberFormat = "General"
End If
Next rCell
End Sub

pedrovarela
10-10-2008, 05:12 AM
Sorry, I'm afraid with that solution I successfully change the Format but it doesn' replace the "," for ".". I continue to ahve all the numbers with comas. ANy hint why?

Bob Phillips
10-10-2008, 05:26 AM
Are you saying you want the commas in real numbers replaced by periods?

If so, as I asked earlier, what should 1,808,272 look like.

pedrovarela
10-10-2008, 05:58 AM
Hi,

Number like "294242" should not change.
Just in the pergentages , which all appear with a coma in either of these two formats(12,5% or -0,1), I would like to replace the "," for ".". Otherwise my macro doesn't work. So I wanted to search each cell for a ",", and if it find one then replace it for a "."

Thanks!!!!

georgiboy
10-10-2008, 11:17 AM
It is because the numbers are stored in number format to include commas, they are not actually there if you know what i mean. With this ammendment to the code it will take the commas away from the numbers and remove commas from the percentages but will change all cells to general format in the process.

Sub FormatChange()
Dim MyRange, rCell As Range

Set MyRange = Range("D1:BG6")

For Each rCell In MyRange.Cells

rCell.Replace What:=",", Replacement:="."

rCell.NumberFormat = "General"

Next rCell


End Sub

Hope this helps

georgiboy
10-10-2008, 11:33 AM
This might be more what you are after

Sub FormatChange()
Dim MyRange, rCell As Range

Set MyRange = Range("D1:BG6")

For Each rCell In MyRange.Cells

If InStr(Right(rCell.Value, 1), "%") Then
rCell.Replace What:=",", Replacement:="."
rCell.NumberFormat = "0.0%"
Else
rCell.NumberFormat = "General"

End If

Next rCell


End Sub

Hope this helps:banghead:

pedrovarela
10-13-2008, 01:26 AM
Hi,
It kkeps on keeping the commas. Finally I decided to make a Mnaul Find and Replace to change the commas for dots and the same to change the Format.

Thaks though for the help!!!!