How to take a cell value always like a string VBA

Jovan Guerra 21 Reputation points
2022-08-31T21:31:46.287+00:00

Hi, I have been reading some post and documentation, but still don't find the way my VBA program gives me the cell value like string and not like integrer.
For example if cell value contents "2,10", it takes it like the numeric value "2,1":

I've tryed:

  1. Dim item As String -- (It allows the item value change to any other kind of variable, or at least "numeric" like in my case)
  2. item = Cells(i, column).Text -- (The cell is formated as like Text and even that way it set item value like "2,1")
  3. item = Trim(Str(Cells(i, column).Text)) -- (It raises an error when value is for example "2,3,1,2")

Context: It is a formulary with some items like "2,1", ...,"2,3,1,1", "2,7", "2,8", "2,9", "2,10", ...,"3,1", "3,1,1", "3,1,1,1", "3,1,1,2", etc.
and I have to find the correct item to multiply the appropriate cuantity for the correct value or cost, but it hace some problems when trying to find items "2,10", "2,20".

Thank you in advance

Edit:
Hi @Dillon Silzer of course, here it is the piece of code that I used

Dim item As String  
Dim arr() As Variant  
Dim wb As Workbook  
ReDim Preserve arr(1 To 1)  
      
Folder = "C:\Users\jovan.guerra\Desktop\Jovan"  
strfile = Dir(Folder & "\*.xlsx")  
  
Do While Len(strfile) > 0  
  
    Dim aritem() As Variant  
    Dim arquant() As Variant  
  
    ReDim Preserve aritem(1 To 1)  
    ReDim Preserve arquant(1 To 1)  
      
    Path = Folder & "\\" & strfile  

    Set wb = Workbooks.Open(Path)  
          
    ActiveWorkbook.Worksheets("DB").Select  

    column = 2  
    counter = 0  
      
    For i = 21 To 120 'Range of cells where the Worksheet usually has the information'  
          
        Cells(i, column).Select  
        If ActiveCell.MergeCells = True Then 'Special condition of the requested data of my Worksheet'  
            item = ActiveCell.Text   
            quantitie = Range("U" & Trim(Str(i))).Value  
            Val = Range("Y" & Trim(Str(i))).Value  
            If Not IsError(Val) Then  
                If Val <> 0 Then  
                    If IsNumeric(item) Then 'It is not supposed to happen but I have it here cause of the trouble that I mentioned'  
                        If item <> 0 Then  
                            item = Trim(Str(item))  
                            stri = Split(item, ".")(0) & "," & Split(item, ".", 2)(1) 'Because when it transform the number to string, it change it to point format (Eg: 3,2 change to 3.2)'  
                            arr(UBound(arr)) = stri  
                            ReDim Preserve arr(1 To UBound(arr) + 1)  
                            aritem(UBound(aritem)) = stri  
                            ReDim Preserve aritem(1 To UBound(aritem) + 1)  
                            arquant(UBound(arquant)) = quantitie  
                            ReDim Preserve arquant(1 To UBound(arquant) + 1)  
                        End If  
                    Else  
                        stri = item  
                        arr(UBound(arr)) = stri  
                        ReDim Preserve arr(1 To UBound(arr) + 1)  
                        aritem(UBound(aritem)) = stri  
                        ReDim Preserve aritem(1 To UBound(aritem) + 1)  
                        arquant(UBound(arquant)) = quantitie  
                        ReDim Preserve arquant(1 To UBound(arquant) + 1)  
                    End If  
                End If  
            End If  
        End If  
          
        counter = counter + 1  
          
    Next i  

    [Some operations]  

    Erase aritem  
    Erase arquant  
      
    strfile = Dir  
      
Loop  
Microsoft 365 and Office Excel For business Windows
{count} votes

Accepted answer
  1. Sipho Dlamini 75 Reputation points
    2024-01-10T10:28:37.31+00:00

    In VBA, you add a single quote ( ' ) to the start of your string. So:

    YouString="'"+YourString

    Cells(2,2)=YourString


1 additional answer

Sort by: Most helpful
  1. Nothing Left To Lose 396 Reputation points
    2022-09-05T23:14:07.977+00:00

    Re: "still don't find the way my VBA program gives me the cell value like string and not like integer."

    Format the cell as a number with two decimal places: 0.00
    Then...
    item = ActiveCell.Text
    Item returns "2.10"

    '---
    Nothing Left to Lose


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.