Text Value from Textbox to Integer

[urubag]

Hi Guys,

I need your help,

Please note I am trying to use the value from a Textbox (Have a formula an extract a cell value (number with format as percentage) to convert to integer or number in order to proceed with a condition to determine if the value is X1 < 0 , X1 > 0 or X1 = 0 (This part already done but unfortunately I have to use zero as text ("0") , I have the issue that X1 = "0,00%" does not work so I think that maybe try to convert to integer can be easy and faster avoid changing the whole code.

Code below is what I am looking but I have never converted a string to Number or Integer, I tried many codes from other people but cause me an error.

Dim Sht, Sht2 As Worksheet
Dim ID, X1 As String
Dim i, lastRow, Y1 As Integer

Set Sht = ThisWorkbook.Sheets("OOO")
Set Sht2 = ThisWorkbook.Sheets("AAA")

Range("B2").Select
lastRow = Sht2.Range("B" & Rows.Count).End(xlUp).Row

For i = 2 To lastRow

ID = ActiveCell.Value 'There is a table with the name of the shape with a loop so activecell is going to be a name of a shape
X1 = Sht.Shapes(ID).TextFrame.Characters.Text 'The value extracted is a cell value, example can be "-5.67%" (Minor than 0) , "0.00%" (equal to Zero) and "7,90%" (Major to zero).

Y1 = CInt(X1) 'I was trying to do something like this but does not work :D so string can convert to integer or number
 'in order to determine the statements below and based on those be able to apply different formats based on Y1 Value.

 IF Y1< 0 Then

..................

 ELSEIF Y1> 0 Then

..................

 ELSEIF Y1= 0 Then

..................
 END IF

ActiveCell.Offset(1, 0). Activate

Next i
End Sub

Appreciate a lot if you can provide a small code for this.

 

[Kaper]

Hi,

You wrote:
"example can be "-5.67%" (Minor than 0) , "0.00%" (equal to Zero) and "7,90%" (Major to zero)".

I can see three problems here:
1) these example numbers have decimal parts, so the type of your variable shall be Double not Integer
2) You have a percent sign there, so the numbers are -0.0567 0 and 0.079 respectively. To easily convert to numbers percent sign shall be removed and the result of the conversion shall be divided by 100

so at the moment we are here:

Y1 = CDbl(Replace(X1, "%", "")) * IIf(InStr(X1, "%"), 0.01, 1)

but 3) in 2 of your examples you use dot (".") as decimal separator and in third one you use comma (",") as decimal separator. If your system decimal separator is comma (Like we do have here, in Poland), and you expect some users could write numbers with comma and some with dot, you might use such version:

Y1 = CDbl(Replace(Replace(X1, "%", ""), ".", ",")) * IIf(InStr(X1, "%"), 0.01, 1)

Final comments:
a) Checking if Y1 is 0

ELSEIF Y1= 0 Then

is not needed. You already checked before if Y1 is larger than 0 and smaller than 0 so at this point it has to be exactly ("exactly" with available to Excel precision so somewhere between -3*10^-308 and 3*10^-308 )
so

ELSE

will be enough in this line

b) declarations:

Dim Sht, Sht2 As Worksheet
Dim ID, X1 As String
Dim i, lastRow, Y1 As Integer

are equivalent to:

Dim Sht As Variant, Sht2 As Worksheet
Dim ID As Variant, X1 As String
Dim i As Variant, lastRow As Variant, Y1 As Integer

So it would be better to declare:

Dim Sht As Worksheet, Sht2 As Worksheet
Dim ID As String, X1 As String

and as the number of rows could possibly grow and just to learn best practices it is wise to declare these as Long and as mentioned earlier Y1 as Double (Single would do too)

Dim i As Long, lastRow As Long, Y1 As Double

I assume here that you really have shapes of given names and Sht.Shapes(ID)..... really returns a string like "-5.67%" etc. If yoy are not sure you may use Step-in <F8> in VBE and observe values in Locals window