Excel too helpful converting text to numbers

BDrew

Board Regular
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:

BDrew

Board Regular
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
 

BDrew

Board Regular
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
 

BDrew

Board Regular
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
 

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top