ExcelBanter - LEFT Function in Macro

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - LEFT Function in Macro (https://www.excelbanter.com/excel-programming/274909-left-function-macro.html)

LEFT Function in Macro

If I have a specific cell say on Sheet1!B8 that I want to
go to and use the left function on, in VB coding how do I
do this?

What I have is the user picks a Vendor number - Name item
from ComboBox3 and it inserts into cell Sheet1!B8 but then
once inserted I want to remove the text leaving only the
numbers.
Eg. In List: 1000 - Company Name
Result desired: 1000

It is always 4 digits, a space, a dash, a space, then a
name, so the length is always 4.

So I had the macro go to the cell via:

Application.Goto Reference:=Worksheets("Sheet1").Range
("B8")

Which goes correctly and selects the cell, but how to I
then say - with the current contents remove all but the 4
left most characters?

Sorry, I am new at VB.

Thanks!

SHauna

LEFT Function in Macro

lngNumber = CLng(Left$(ThisWorkSheet.Cells("B8").Value, 4))
dblNumber = CDbl(Left$(ThisWorkSheet.Cells("B8").Value, 4))
sngNumber = CSng(Left$(ThisWorkSheet.Cells("B8").Value, 4))

Hope that helps.

--
Jim Carlock
http://www.microcosmotalk.com
Feel free to post back to the newsgroup!

"Shauna Koppang" wrote in message
...
If I have a specific cell say on Sheet1!B8 that I want to
go to and use the left function on, in VB coding how do I
do this?

What I have is the user picks a Vendor number - Name item
from ComboBox3 and it inserts into cell Sheet1!B8 but then
once inserted I want to remove the text leaving only the
numbers.
Eg. In List: 1000 - Company Name
Result desired: 1000

It is always 4 digits, a space, a dash, a space, then a
name, so the length is always 4.

So I had the macro go to the cell via:

Application.Goto Reference:=Worksheets("Sheet1").Range
("B8")

Which goes correctly and selects the cell, but how to I
then say - with the current contents remove all but the 4
left most characters?

Sorry, I am new at VB.

Thanks!

SHauna

LEFT Function in Macro

activecell.Value=left(activecell.Value,4)

HTH
Paul
--------------------------------------------------------------------------------------------------------------
Be advised to back up your WorkBook before attempting to make changes.
--------------------------------------------------------------------------------------------------------------

LEFT Function in Macro

Thank you thank you thank you!!!

Worked perfectly!

Application.Goto Reference:=Worksheets("Sheet1").Range
("B8")
ActiveCell.Value = Left(ActiveCell.Value, 4)

Shauna
-----Original Message-----
activecell.Value=left(activecell.Value,4)

HTH
Paul
----------------------------------------------------------
----------------------------------------------------
Be advised to back up your WorkBook before attempting to
make changes.
----------------------------------------------------------
----------------------------------------------------

.

LEFT Function in Macro

dim temp
temp = Left(Worksheets("Sheet1").Range("B8"),4)
Worksheets("Sheet1").Range("B8")=temp

-----Original Message-----
If I have a specific cell say on Sheet1!B8 that I want
to
go to and use the left function on, in VB coding how do
I
do this?

What I have is the user picks a Vendor number - Name
item
from ComboBox3 and it inserts into cell Sheet1!B8 but
then
once inserted I want to remove the text leaving only the
numbers.
Eg. In List: 1000 - Company Name
Result desired: 1000

It is always 4 digits, a space, a dash, a space, then a
name, so the length is always 4.

So I had the macro go to the cell via:

Application.Goto Reference:=Worksheets("Sheet1").Range
("B8")

Which goes correctly and selects the cell, but how to I
then say - with the current contents remove all but the
4
left most characters?

Sorry, I am new at VB.

Thanks!

SHauna
.

LEFT Function in Macro

Oops,

Here's the correction:

Dim lngNumber&, dblNumber As Double
Dim sngNumber As Single

ThisWorkbook.ActiveSheet.Cells(1, 3) = "1234-xl-2003"
lngNumber = CLng(Left$(ThisWorkbook.ActiveSheet.Cells(1, 10).Value, 4))
dblNumber = CDbl(Left$(ThisWorkbook.ActiveSheet.Cells(1, 10).Value, 4))
sngNumber = CSng(Left$(ThisWorkbook.ActiveSheet.Cells(1, 10).Value, 4))
ThisWorkbook.ActiveSheet.Cells(2, 3) = CStr(lngNumber)
ThisWorkbook.ActiveSheet.Cells(3, 3) = CStr(dblNumber)
ThisWorkbook.ActiveSheet.Cells(4, 3) = CStr(sngNumber)

--
Jim Carlock
http://www.microcosmotalk.com
Feel free to post back to the newsgroup!

"Shauna Koppang" wrote in message
...
Hi Jim,

I'm sorry, but I put your code below my goto statment and
commented one then the other and tried running them and I
got runtime error 424 on each. This one had lngNumber
active:

Application.Goto Reference:=Worksheets("Sheet1").Range
("B8")

lngNumber = CLng(Left$(ThisWorkSheet.Cells
("B8").Value, 4))
'dblNumber = CDbl(Left$(ThisWorkSheet.Cells
("B8").Value, 4))
'sngNumber = CSng(Left$(ThisWorkSheet.Cells
("B8").Value, 4))

What am I doing wrong? Sorry but I am just learning this
stuff.

Thanks!
Shauna

-----Original Message-----
lngNumber = CLng(Left$(ThisWorkSheet.Cells("B8").Value,
4))
dblNumber = CDbl(Left$(ThisWorkSheet.Cells("B8").Value,
4))
sngNumber = CSng(Left$(ThisWorkSheet.Cells("B8").Value,
4))

Hope that helps.

--
Jim Carlock
http://www.microcosmotalk.com
Feel free to post back to the newsgroup!

"Shauna Koppang" wrote
in message
...
If I have a specific cell say on Sheet1!B8 that I want
to
go to and use the left function on, in VB coding how do
I
do this?

What I have is the user picks a Vendor number - Name
item
from ComboBox3 and it inserts into cell Sheet1!B8 but
then
once inserted I want to remove the text leaving only the
numbers.
Eg. In List: 1000 - Company Name
Result desired: 1000

It is always 4 digits, a space, a dash, a space, then a
name, so the length is always 4.

So I had the macro go to the cell via:

Application.Goto Reference:=Worksheets("Sheet1").Range
("B8")

Which goes correctly and selects the cell, but how to I
then say - with the current contents remove all but the
4
left most characters?

Sorry, I am new at VB.

Thanks!

SHauna

.

LEFT Function in Macro