Code help, please!

rjc4

Well-known Member
Joined
Nov 6, 2004
Messages
502
Hi All,

In U26:X200 is my data like below.
W has a number and color. The number can be one or
two digits. (eg. Vo4, GREEN or Vo53, PINK).

SA 2 Vo4, GREEN Mon
SA 4 Vo3, Pale GREEN Wed
VB 7 Vo1, BLACK Tues
HB 8 Vo4, Dark BLUE Mon

I have a combo box which gets the first two columns
Of data from my range and puts the result in G11 and H11.
So G11 is SA and H11 is 2.

This is the combo code that does this.
Code:
Option Explicit
Private Sub ComboBox4_Change()
With Me.ComboBox4
If .ListIndex > -1 Then
Me.Cells(11, "G") = .Value
'Me.Cells(11, "H") = Me.Cells(.ListIndex + 26, "v")
Me.Cells(11, "H") = Val(.List(.ListIndex, 1))
End If
End With
End Sub

However, I wish to also get the NUMBER ONLY in columnW
And insert this in cell J7. So say in the third record as example
G11=VB, H11=7 and J7=1.

Can anyone help me to do this.

Thankyou in advance.
RC
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

rjc4

Well-known Member
Joined
Nov 6, 2004
Messages
502
Hi again,
No replies so must assume not able to be done or too complex.

If anyone could tell me how then I can extract just the number, I think I can take it from there.

W has a number and color. The number can be one or
two digits. (eg. Vo4, GREEN or Vo53, PINK or Vo7, Light BLUE).
Just need to get the 4 or the 53 or the 7 from the cell.

Thankyou,
RC :(



Hi All,

In U26:X200 is my data like below.
W has a number and color. The number can be one or
two digits. (eg. Vo4, GREEN or Vo53, PINK).

SA 2 Vo4, GREEN Mon
SA 4 Vo3, Pale GREEN Wed
VB 7 Vo1, BLACK Tues
HB 8 Vo4, Dark BLUE Mon

I have a combo box which gets the first two columns
Of data from my range and puts the result in G11 and H11.
So G11 is SA and H11 is 2.

This is the combo code that does this.
Code:
Option Explicit
Private Sub ComboBox4_Change()
With Me.ComboBox4
If .ListIndex > -1 Then
Me.Cells(11, "G") = .Value
'Me.Cells(11, "H") = Me.Cells(.ListIndex + 26, "v")
Me.Cells(11, "H") = Val(.List(.ListIndex, 1))
End If
End With
End Sub

However, I wish to also get the NUMBER ONLY in columnW
And insert this in cell J7. So say in the third record as example
G11=VB, H11=7 and J7=1.

Can anyone help me to do this.

Thankyou in advance.
RC
 

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi

Are they always prefixed with Vo??? Or can there be other prefixes, of various lengths?

Tony
 

rjc4

Well-known Member
Joined
Nov 6, 2004
Messages
502
G'day Tony,

Yes. Vo is short for Volume number.
The number is always either 1 or 2 digits preceeded by Vo only.
The colour description though can be one or several words.

Thanks for your reply. Can you assist.

Cheers,
RC :)

Hi

Are they always prefixed with Vo??? Or can there be other prefixes, of various lengths?

Tony
 

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814

ADVERTISEMENT

RC

Not sure where you have the data, but you can use the substitute function to remove the Vo and just leave the number

holder = val(worksheetfunction.substitute(range("W11").value, "Vo",""))
msgbox holder


Tony
 

rjc4

Well-known Member
Joined
Nov 6, 2004
Messages
502
Hi Tony,
My data is in W26 down. Can't seem to get that to work.

This is what I have and it works fine except it only extracts one digit.
Cell W26 contains [Vo10, Dark BLUE]
=TRIM(MID(W26,FIND(",",W26)-1,1))

Above formula gets the 0 not 10.
If I have Vo4, then it gets the 4 no problems.

I can't see what is needed to get 2 digits if the number has two.

Can you see where it is wrong.

Thanks again,
RC








RC

Not sure where you have the data, but you can use the substitute function to remove the Vo and just leave the number

holder = val(worksheetfunction.substitute(range("W11").value, "Vo",""))
msgbox holder


Tony
 

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814

ADVERTISEMENT

Hi

Try

Code:
=VALUE(SUBSTITUTE(LEFT(W26,SEARCH(",",W26)-1),"Vo",""))


Tony
 

rjc4

Well-known Member
Joined
Nov 6, 2004
Messages
502
Hi Tony,
You helped me with a formula the other day which works perfectly.
However, I've run into an unforseen problem.

Some of my cells contain text only (say Dark BLUE) and as a result, the formula returns #value.

Can you suggest a modification to the formula that returns a blank when the Vo number is not there.

Sorry to be a pain. I've had numerous goes at it and can't find a solution.

Regards,
RC :oops:

Hi

Try

Code:
=VALUE(SUBSTITUTE(LEFT(W26,SEARCH(",",W26)-1),"Vo",""))


Tony
 

rjc4

Well-known Member
Joined
Nov 6, 2004
Messages
502
Hi again Tony,
False alarm. I've worked it out using ISNUMBER(SEARCH parameters.

Cheers,
RC :eek:



Hi Tony,
You helped me with a formula the other day which works perfectly.
However, I've run into an unforseen problem.

Some of my cells contain text only (say Dark BLUE) and as a result, the formula returns #value.

Can you suggest a modification to the formula that returns a blank when the Vo number is not there.

Sorry to be a pain. I've had numerous goes at it and can't find a solution.

Regards,
RC :oops:

Hi

Try

Code:
=VALUE(SUBSTITUTE(LEFT(W26,SEARCH(",",W26)-1),"Vo",""))


Tony
 

Watch MrExcel Video

Forum statistics

Threads
1,108,492
Messages
5,523,260
Members
409,506
Latest member
reneekeane

This Week's Hot Topics

Top