How to convert a "120" defined as a String to a "120" defined as Integer !! >_<

C

crazy_vba

Hey Folks !
Here comes back the Crazy_vba that I am.. and still working on his code
:p

Thanks to the previous help, and the download of a pack called
morefunc, I can get more advanced and easy coding now. (the website is
http://xcell05.free.fr/)

Anyway...Here is my **new** problem.

I got in one cell this sentence (string):

B16 : "Franchisor is a public company. Franchisor has 120 employee(s);
59 employee(s) in franchise department"

I've tried in the past days, and thanks to your answers to extract the
specific numbers 120 and 59... I used the Range("e14").Value =
Val(Right(Range("b16").Value, 38)) and Range("d14").Value =
Val(Right(Range("b16").Value, 55)).

But, cos there's a But ! This method was not working as on some
downloaded webpages, the sentence was longer or shorter...so the 38 and
55 values were not returning the info that I needed.

I'm now using a more powerfull technique, with the function STEXTE=
-included in the morefunc pack. Basically, I give it the cell in which
I'm working, the number of words I'm looking for and their
positions...

[ "=STEXTE(RC[-1],i,1)" ] where RC[-1] is the cell I'm looking in; i is
the ith word position in the cell; 1 is the number of words i'm looking
for, here only one "word".

So let's go back to our cell B16:
"Franchisor is a public company. Franchisor has 120 employee(s); 59
employee(s) in franchise department"

basically, with the STEXTE function, if I want to get the number 120
and 59, I will need to type the following formula:
=STEXTE(B16;8;1) to get 120 out; and
=STEXTE(B16;10;1) to get 59 out.

alright so far? But you would tell me: Hey Crazy_Vba, You will still
have the same problem if the NUMBERS 120 and 59 are not at the 8th/10th
place in your sentence in the case of another company.
I would say: You're right sir !

That is why I've tried to code this was my macro:

For i = 1 To 50
ActiveCell.FormulaR1C1 = "=STEXTE(RC[-1],i,1)"
test = ActiveCell.Value

If VarType(test) = vbInteger Then
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "Integer"
End If

If VarType(test) = vbString Then
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "String"
End If
Next i

My For ... Next is not "perfect" yet but it is not my problem (yet!)
As you have understood by reading my code, I'm taking each word one by
one (hence the for next) and "test" it with a If...End If procedure to
know if it is a String or an Integer.

However, by doing a step-by-step run of my macro, it does select each
word one by one, display it, and check if it is a string or an
integer... BUT IT IS ALWAYS DEFINED AND UNDERSTOOD AS A STRING ! Even
if the "extracted word" is 120.

So Here is my idea: to insert in my code a Convertion Phase, that will
"try" to convert the extracted word into integer, and then, if it is an
integer, the macro should stop!

What I'm looking for would be something like that :

For i = 1 To 50
ActiveCell.FormulaR1C1 = "=STEXTE(RC[-1],i,1)"

TRANSFORM ACTIVECELL.VALUE INTO INTEGER

test = ActiveCell.Value

If VarType(test) = vbInteger Then
STOP MACRO

If VarType(test) = vbString Then
CONTINUE TO LOOK FOR THE OTHER WORDS IN THE CELL
End If
Next i

Any idea ? :)
I'm dead, I've been looking thoughout the forum with key words like
"convert strings integer double", even on google, but cannot find
exactly what I need!

how to convert a damn "120" defined as a string into a "120" defined as
integer ! :) and cook it with a nice For...Next procedure !

Thanks in advance for your consideration and help guys!
Crazy Vba
 
T

Tim Williams

cint(stringVariable)

Tim

crazy_vba said:
Hey Folks !
Here comes back the Crazy_vba that I am.. and still working on his code
:p

Thanks to the previous help, and the download of a pack called
morefunc, I can get more advanced and easy coding now. (the website is
http://xcell05.free.fr/)

Anyway...Here is my **new** problem.

I got in one cell this sentence (string):

B16 : "Franchisor is a public company. Franchisor has 120 employee(s);
59 employee(s) in franchise department"

I've tried in the past days, and thanks to your answers to extract the
specific numbers 120 and 59... I used the Range("e14").Value =
Val(Right(Range("b16").Value, 38)) and Range("d14").Value =
Val(Right(Range("b16").Value, 55)).

But, cos there's a But ! This method was not working as on some
downloaded webpages, the sentence was longer or shorter...so the 38 and
55 values were not returning the info that I needed.

I'm now using a more powerfull technique, with the function STEXTE=
-included in the morefunc pack. Basically, I give it the cell in which
I'm working, the number of words I'm looking for and their
positions...

[ "=STEXTE(RC[-1],i,1)" ] where RC[-1] is the cell I'm looking in; i is
the ith word position in the cell; 1 is the number of words i'm looking
for, here only one "word".

So let's go back to our cell B16:
"Franchisor is a public company. Franchisor has 120 employee(s); 59
employee(s) in franchise department"

basically, with the STEXTE function, if I want to get the number 120
and 59, I will need to type the following formula:
=STEXTE(B16;8;1) to get 120 out; and
=STEXTE(B16;10;1) to get 59 out.

alright so far? But you would tell me: Hey Crazy_Vba, You will still
have the same problem if the NUMBERS 120 and 59 are not at the 8th/10th
place in your sentence in the case of another company.
I would say: You're right sir !

That is why I've tried to code this was my macro:

For i = 1 To 50
ActiveCell.FormulaR1C1 = "=STEXTE(RC[-1],i,1)"
test = ActiveCell.Value

If VarType(test) = vbInteger Then
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "Integer"
End If

If VarType(test) = vbString Then
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "String"
End If
Next i

My For ... Next is not "perfect" yet but it is not my problem (yet!)
As you have understood by reading my code, I'm taking each word one by
one (hence the for next) and "test" it with a If...End If procedure to
know if it is a String or an Integer.

However, by doing a step-by-step run of my macro, it does select each
word one by one, display it, and check if it is a string or an
integer... BUT IT IS ALWAYS DEFINED AND UNDERSTOOD AS A STRING ! Even
if the "extracted word" is 120.

So Here is my idea: to insert in my code a Convertion Phase, that will
"try" to convert the extracted word into integer, and then, if it is an
integer, the macro should stop!

What I'm looking for would be something like that :

For i = 1 To 50
ActiveCell.FormulaR1C1 = "=STEXTE(RC[-1],i,1)"

TRANSFORM ACTIVECELL.VALUE INTO INTEGER

test = ActiveCell.Value

If VarType(test) = vbInteger Then
STOP MACRO

If VarType(test) = vbString Then
CONTINUE TO LOOK FOR THE OTHER WORDS IN THE CELL
End If
Next i

Any idea ? :)
I'm dead, I've been looking thoughout the forum with key words like
"convert strings integer double", even on google, but cannot find
exactly what I need!

how to convert a damn "120" defined as a string into a "120" defined as
integer ! :) and cook it with a nice For...Next procedure !

Thanks in advance for your consideration and help guys!
Crazy Vba
Click to expand...
 
T

Tim Williams

Try this:

'**********************************************
'Returns the iNum'th whole number from sText
'Eg: =GetNumber("10 or maybe 45 employees",2)
Function GetNumber(sText As String, iNum As Integer) As Variant

Static regEx As Object
Dim m
Dim i As Integer

If regEx Is Nothing Then
Set regEx = CreateObject("vbscript.regexp")
regEx.Pattern = "(\d+)"
regEx.Global = True
regEx.IgnoreCase = True
End If

Set m = regEx.Execute(sText)
If m.Count >= iNum Then
GetNumber = m(iNum - 1)
Else
GetNumber = ""
End If

End Function
'**********************************************

Tim



crazy_vba said:
Hey Folks !
Here comes back the Crazy_vba that I am.. and still working on his code
:p

Thanks to the previous help, and the download of a pack called
morefunc, I can get more advanced and easy coding now. (the website is
http://xcell05.free.fr/)

Anyway...Here is my **new** problem.

I got in one cell this sentence (string):

B16 : "Franchisor is a public company. Franchisor has 120 employee(s);
59 employee(s) in franchise department"

I've tried in the past days, and thanks to your answers to extract the
specific numbers 120 and 59... I used the Range("e14").Value =
Val(Right(Range("b16").Value, 38)) and Range("d14").Value =
Val(Right(Range("b16").Value, 55)).

But, cos there's a But ! This method was not working as on some
downloaded webpages, the sentence was longer or shorter...so the 38 and
55 values were not returning the info that I needed.

I'm now using a more powerfull technique, with the function STEXTE=
-included in the morefunc pack. Basically, I give it the cell in which
I'm working, the number of words I'm looking for and their
positions...

[ "=STEXTE(RC[-1],i,1)" ] where RC[-1] is the cell I'm looking in; i is
the ith word position in the cell; 1 is the number of words i'm looking
for, here only one "word".

So let's go back to our cell B16:
"Franchisor is a public company. Franchisor has 120 employee(s); 59
employee(s) in franchise department"

basically, with the STEXTE function, if I want to get the number 120
and 59, I will need to type the following formula:
=STEXTE(B16;8;1) to get 120 out; and
=STEXTE(B16;10;1) to get 59 out.

alright so far? But you would tell me: Hey Crazy_Vba, You will still
have the same problem if the NUMBERS 120 and 59 are not at the 8th/10th
place in your sentence in the case of another company.
I would say: You're right sir !

That is why I've tried to code this was my macro:

For i = 1 To 50
ActiveCell.FormulaR1C1 = "=STEXTE(RC[-1],i,1)"
test = ActiveCell.Value

If VarType(test) = vbInteger Then
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "Integer"
End If

If VarType(test) = vbString Then
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "String"
End If
Next i

My For ... Next is not "perfect" yet but it is not my problem (yet!)
As you have understood by reading my code, I'm taking each word one by
one (hence the for next) and "test" it with a If...End If procedure to
know if it is a String or an Integer.

However, by doing a step-by-step run of my macro, it does select each
word one by one, display it, and check if it is a string or an
integer... BUT IT IS ALWAYS DEFINED AND UNDERSTOOD AS A STRING ! Even
if the "extracted word" is 120.

So Here is my idea: to insert in my code a Convertion Phase, that will
"try" to convert the extracted word into integer, and then, if it is an
integer, the macro should stop!

What I'm looking for would be something like that :

For i = 1 To 50
ActiveCell.FormulaR1C1 = "=STEXTE(RC[-1],i,1)"

TRANSFORM ACTIVECELL.VALUE INTO INTEGER

test = ActiveCell.Value

If VarType(test) = vbInteger Then
STOP MACRO

If VarType(test) = vbString Then
CONTINUE TO LOOK FOR THE OTHER WORDS IN THE CELL
End If
Next i

Any idea ? :)
I'm dead, I've been looking thoughout the forum with key words like
"convert strings integer double", even on google, but cannot find
exactly what I need!

how to convert a damn "120" defined as a string into a "120" defined as
integer ! :) and cook it with a nice For...Next procedure !

Thanks in advance for your consideration and help guys!
Crazy Vba
Click to expand...
 
C

crazy_vba

ain't working :-/
In fact,
in my For...Next procedure, there is the moment when the "strin
variable" IS a string... and then I got a error msg saying
Incompatibilty of Type...
How can you use this cint function with a For...Next procedure?
Thanks Tim !
 
C

crazy_vba

The problem here is that I don't understand what you're doing in you
code :-/
Too complex :(
How can I implement a function in my macro with the For...Nex
procedure Tim?

Tim said:
Try this:

'**********************************************
'Returns the iNum'th whole number from sText
'Eg: =GetNumber("10 or maybe 45 employees",2)
Function GetNumber(sText As String, iNum As Integer) As Variant

Static regEx As Object
Dim m
Dim i As Integer

If regEx Is Nothing Then
Set regEx = CreateObject("vbscript.regexp")
regEx.Pattern = "(\d+)"
regEx.Global = True
regEx.IgnoreCase = True
End If

Set m = regEx.Execute(sText)
If m.Count >= iNum Then
GetNumber = m(iNum - 1)
Else
GetNumber = ""
End If

End Function
'**********************************************

Tim



"crazy_vba" <[email protected]
wrote in message
Hey Folks !
Here comes back the Crazy_vba that I am.. and still working on hi code
:p

Thanks to the previous help, and the download of a pack called
morefunc, I can get more advanced and easy coding now. (the websit is
http://xcell05.free.fr/)

Anyway...Here is my **new** problem.

I got in one cell this sentence (string):

B16 : "Franchisor is a public company. Franchisor has 12 employee(s);
59 employee(s) in franchise department"

I've tried in the past days, and thanks to your answers to extrac the
specific numbers 120 and 59... I used the Range("e14").Value =
Val(Right(Range("b16").Value, 38)) and Range("d14").Value =
Val(Right(Range("b16").Value, 55)).

But, cos there's a But ! This method was not working as on some
downloaded webpages, the sentence was longer or shorter...so the 3 and
55 values were not returning the info that I needed.

I'm now using a more powerfull technique, with the function STEXTE=
-included in the morefunc pack. Basically, I give it the cell i which
I'm working, the number of words I'm looking for and their
positions...

[ "=STEXTE(RC[-1],i,1)" ] where RC[-1] is the cell I'm looking in; is
the ith word position in the cell; 1 is the number of words i' looking
for, here only one "word".

So let's go back to our cell B16:
"Franchisor is a public company. Franchisor has 120 employee(s); 59
employee(s) in franchise department"

basically, with the STEXTE function, if I want to get the number 120
and 59, I will need to type the following formula:
=STEXTE(B16;8;1) to get 120 out; and
=STEXTE(B16;10;1) to get 59 out.

alright so far? But you would tell me: Hey Crazy_Vba, You will still
have the same problem if the NUMBERS 120 and 59 are not at th 8th/10th
place in your sentence in the case of another company.
I would say: You're right sir !

That is why I've tried to code this was my macro:

For i = 1 To 50
ActiveCell.FormulaR1C1 = "=STEXTE(RC[-1],i,1)"
test = ActiveCell.Value

If VarType(test) = vbInteger Then
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "Integer"
End If

If VarType(test) = vbString Then
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = "String"
End If
Next i

My For ... Next is not "perfect" yet but it is not my problem (yet!)
As you have understood by reading my code, I'm taking each word on by
one (hence the for next) and "test" it with a If...End If procedur to
know if it is a String or an Integer.

However, by doing a step-by-step run of my macro, it does selec each
word one by one, display it, and check if it is a string or an
integer... BUT IT IS ALWAYS DEFINED AND UNDERSTOOD AS A STRING Even
if the "extracted word" is 120.

So Here is my idea: to insert in my code a Convertion Phase, tha will
"try" to convert the extracted word into integer, and then, if it is an
integer, the macro should stop!

What I'm looking for would be something like that :

For i = 1 To 50
ActiveCell.FormulaR1C1 = "=STEXTE(RC[-1],i,1)"

TRANSFORM ACTIVECELL.VALUE INTO INTEGER

test = ActiveCell.Value

If VarType(test) = vbInteger Then
STOP MACRO

If VarType(test) = vbString Then
CONTINUE TO LOOK FOR THE OTHER WORDS IN THE CELL
End If
Next i

Any idea ? :)
I'm dead, I've been looking thoughout the forum with key words like
"convert strings integer double", even on google, but cannot find
exactly what I need!

how to convert a damn "120" defined as a string into a "120" defined as
integer ! :) and cook it with a nice For...Next procedure !

Thanks in advance for your consideration and help guys!
Crazy Vba


--
crazy_vba
------------------------------------------------------------------------
crazy_vba's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=33679
View this thread: http://www.excelforum.com/showthread.php?threadid=539716
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Vartype() and vbObject 4
String to Rounded Integer 3
Dictionary object: Error assigning user defined data type to item 2
Naming a listbox as a string 2
"Variable not defined" error for form that does not initially exist... 1
For loop - update your expression to evaluate 1
Attempting a dynamically defined conditional in VBA 1
Copy a R1C1 formula to a user defined range results #NAME? error 2

Top