Convert text to value

User avatar
VegasNath
5StarLounger
Posts: 1185
Joined: 24 Jan 2010, 12:02
Location: Wales, UK.

Convert text to value

Post by VegasNath »

I am trying to concert the text in a cell to a value.....

Range("C" & rg) = Value(Range("C" & rg))

What am I doing wrong?
:wales: Nathan :uk:
There's no place like home.....

User avatar
StuartR
Administrator
Posts: 12695
Joined: 16 Jan 2010, 15:49
Location: London, Europe

Re: Convert text to value

Post by StuartR »

Try Range("C" & rg) = Range("C" & rg).Value
StuartR


User avatar
HansV
Administrator
Posts: 78977
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Convert text to value

Post by HansV »

Stuart has already answered your question, but I'd like to add some info.
If you type Value in the Visual Basic Editor, for example in a module or in the Immediate window, and press F1 for help, you'll see that Value is a property, not a function. This gives you a clue how to use it.
Properties use the syntax ObjectName.PropertyName.
Functions use the syntax FunctionName(Arguments)
Best wishes,
Hans

User avatar
VegasNath
5StarLounger
Posts: 1185
Joined: 24 Jan 2010, 12:02
Location: Wales, UK.

Re: Convert text to value

Post by VegasNath »

Thanks both.

May I ask.... What is the vba equivalent of pressing F2 in a cell?
:wales: Nathan :uk:
There's no place like home.....

User avatar
HansV
Administrator
Posts: 78977
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Convert text to value

Post by HansV »

What do you want to accomplish?
Best wishes,
Hans

User avatar
VegasNath
5StarLounger
Posts: 1185
Joined: 24 Jan 2010, 12:02
Location: Wales, UK.

Re: Convert text to value

Post by VegasNath »

I want to refresh the cell format which pressing F2 does.
:wales: Nathan :uk:
There's no place like home.....

User avatar
HansV
Administrator
Posts: 78977
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Convert text to value

Post by HansV »

Refresh the cell format???
Best wishes,
Hans

User avatar
VegasNath
5StarLounger
Posts: 1185
Joined: 24 Jan 2010, 12:02
Location: Wales, UK.

Re: Convert text to value

Post by VegasNath »

HansV wrote:Refresh the cell format???
Like the attached....
test.xls
You do not have the required permissions to view the files attached to this post.
:wales: Nathan :uk:
There's no place like home.....

User avatar
HansV
Administrator
Posts: 78977
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Convert text to value

Post by HansV »

You can use

Code: Select all

With Range("F3:F17")
  .Value = .Value
End With
or if you want to apply it to the currently selected cells

Code: Select all

With Selection
  .Value = .Value
End With
Note: formulas will be replaced with their result.
Best wishes,
Hans

User avatar
VegasNath
5StarLounger
Posts: 1185
Joined: 24 Jan 2010, 12:02
Location: Wales, UK.

Re: Convert text to value

Post by VegasNath »

Inside a loop, I am using:

Range("A" & rg).Value = Range("A" & rg).Value
Range("H" & rg).Value = Range("H" & rg).Value

But this is not producing the result?
:wales: Nathan :uk:
There's no place like home.....

User avatar
HansV
Administrator
Posts: 78977
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Convert text to value

Post by HansV »

In your example, the cells are in column F. Your code affects columns A and H...
Best wishes,
Hans

User avatar
VegasNath
5StarLounger
Posts: 1185
Joined: 24 Jan 2010, 12:02
Location: Wales, UK.

Re: Convert text to value

Post by VegasNath »

The example was just a mock up of the problem. My real data is in columns A & H and I know that each cell is being evaluated as I am also colouring it (in test) to ensure that I am not missing anything.

Code: Select all

    For rg = lr To 2 Step -1
...............

        Range("A" & rg).Value = Range("A" & rg).Value
        Range("H" & rg).Value = Range("H" & rg).Value
        
        Range("A" & rg).Interior.ColorIndex = 3
        Range("H" & rg).Interior.ColorIndex = 3
    Next
:wales: Nathan :uk:
There's no place like home.....

User avatar
HansV
Administrator
Posts: 78977
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Convert text to value

Post by HansV »

Perhaps the number format of the offending cells is incorrect - if it is set to text, pressing F2 then Enter or running the above code won't help. Try adding

Range("A" & rg).NumberFormat = "General"
Range("H" & rg).NumberFormat = "General"

before the lines that set the value.
Best wishes,
Hans

User avatar
VegasNath
5StarLounger
Posts: 1185
Joined: 24 Jan 2010, 12:02
Location: Wales, UK.

Re: Convert text to value

Post by VegasNath »

Aha, that works for the value's (even though they are already value's), strange???

But it's not working for the date's <22/01/2010 00:00:00> needs to be <22/01/2010>

Thanks
:wales: Nathan :uk:
There's no place like home.....

User avatar
HansV
Administrator
Posts: 78977
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Convert text to value

Post by HansV »

Try

Code: Select all

If IsDate(Range("A" & rg).Value) Then
  Range("A" & rg).NumberFormat = "m/d/yyyy"
Else
  Range("A" & rg).NumberFormat = "General"
End If
If IsDate(Range("H" & rg).Value) Then
  Range("H" & rg).NumberFormat = "m/d/yyyy"
Else
  Range("H" & rg).NumberFormat = "General"
End If
Best wishes,
Hans

User avatar
VegasNath
5StarLounger
Posts: 1185
Joined: 24 Jan 2010, 12:02
Location: Wales, UK.

Re: Convert text to value

Post by VegasNath »

No joy :(

Weird because all cells show the same format, some work and some don't.
Capturexxxx.GIF
You do not have the required permissions to view the files attached to this post.
:wales: Nathan :uk:
There's no place like home.....

User avatar
HansV
Administrator
Posts: 78977
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Convert text to value

Post by HansV »

Code: Select all

If IsDate(Range("A" & rg).Value) Then
  Range("A" & rg).NumberFormat = "m/d/yyyy"
  Range("A" & rg).Value = CDate(Range("A" & rg).Value)
Else
  Range("A" & rg).NumberFormat = "General"
End If
If IsDate(Range("H" & rg).Value) Then
  Range("H" & rg).NumberFormat = "m/d/yyyy"
  Range("H" & rg).Value = CDate(Range("H" & rg).Value)
Else
  Range("H" & rg).NumberFormat = "General"
End If
Best wishes,
Hans

User avatar
VegasNath
5StarLounger
Posts: 1185
Joined: 24 Jan 2010, 12:02
Location: Wales, UK.

Re: Convert text to value

Post by VegasNath »

That did it, Many Thanks Hans!

I so wish our company would build programs with legible output files. :(
:wales: Nathan :uk:
There's no place like home.....

User avatar
HansV
Administrator
Posts: 78977
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Convert text to value

Post by HansV »

VegasNath wrote:I so wish our company would build programs with legible output files.(
That would be the sensible solution... :crazy:
Best wishes,
Hans