Code help, please!

[rjc4]

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

 

[rjc4]

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]

Hi

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

Tony

 

[rjc4]

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]

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]

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]

Hi

Try

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

Tony

 

[rjc4]

Hello Tony,
Perfect!

Thank you very much.
RC

Hi

Try

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

Tony

 

[rjc4]

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

Hi

Try

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

Tony

 

[rjc4]

Hi again Tony,
False alarm. I've worked it out using ISNUMBER(SEARCH parameters.

Cheers,
RC

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

Hi

Try

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

Tony