Excel too helpful converting text to numbers

BDrew

Board Regular
Joined
Jun 8, 2008
Messages
79
With VBA I am applying a formula which is to return a Part Code Number as a text label to a cell and the formula is copied down a range and converted to Values only. The formula includes prepending “’” if a number is detected.
Problem: A few entries do not have any alphabetic characters, only digits and Excel treats these as a number.
Temporary solution: I enter the formula directly to the sheet and copy it down then convert to values.
Help required: what more can I do to have VBA perform this action without Excel converting to a number.

Code:
col = "Q"                str = "=tplQ"
                    .Range(col & "5").Formula = str
                    .Range(col & "5:" & col & z).FillDown
                    .Range(col & "5:" & col & z).Calculate
                    .Range(col & "5:" & col & z).Value = .Range(col & "5:" & col & z).Value

Brendan
 
Last edited by a moderator:

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
to always have return as text precede the value with a single quote '
 
Upvote 0
Thanks Moderator,
I did mention that I was doing that.
tplQ=KitAdj

KitAdj=REPT("'"&Kits,ISNUMBER(Kits))&REPT(Kits,NOT(ISNUMBER(Kits)))

this works when I enter =tplQ into the sheet but not when VBA enters the "=tplQ" as .formula

There are too many to do manually.

Brendan
 
Upvote 0
will it run without .formula, or use .value ?
 
Upvote 0
I tried each with the same result:

Code:
str = "=tplQ"                    .Range(col & "5").Formula = str
                    '.Range(col & "5").Value = str
                     '.Range(col & "5") = str
                    .Range(col & "5:" & col & z).FillDown
                    .Range(col & "5:" & col & z).Calculate
                    .Range(col & "5:" & col & z).Value = .Range(col & "5:" & col & z).Value

It is the last line that is converting the text to number.

Brendan
 
Upvote 0
Thank you Chip Pearson and those who helped restore his legacy: http://www.cpearson.com/excel/values.htm

Code:
str = "=tplQ"                    .Range(col & "5").Formula = str
                    '.Range(col & "5").Value = str
                     '.Range(col & "5") = str
                    .Range(col & "5:" & col & z).FillDown
                    .Range(col & "5:" & col & z).Calculate
                   Dim temp As Variant
                    temp = .Range(col & "5:" & col & z).Text
                    
                    '.Range(col & "5:" & col & z).Value = .Range(col & "5:" & col & z).Value
                    .Range(col & "5:" & col & z).Value = temp

This worked.

Thanks also Mole999 for making me feel like I am not alone.

Brendan
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top