Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
LEFT Function in Macro
activecell.Value=left(activecell.Value,4)
HTH Paul -------------------------------------------------------------------------------------------------------------- Be advised to back up your WorkBook before attempting to make changes. -------------------------------------------------------------------------------------------------------------- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. ---------------------------------------------------------- ---------------------------------------------------- . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to nest a left function within a sumif function? | Excel Worksheet Functions | |||
macro to sort right to left | Excel Discussion (Misc queries) | |||
LEFT function-all to left of a comma? | Excel Worksheet Functions | |||
Left vs Left$ function | Excel Discussion (Misc queries) | |||
HOW DO I NEST THE VLOOKUP FUNCTION WITH THE LEFT FUNCTION | Excel Worksheet Functions |