VBA Split not working

[Charmwah]

Hi all

No doubt an easy fix to this. I have the following code whereby i'm trying to split a two word string into just the last word. The string originates from a combobox selection:

Private Sub FamilyCombo_Change()


Dim Coat As String
Dim Coaty As String
    Coat = FamilyCombo
    Coaty = Split(Range(Coat).Value)
    Me.CoatingCombo.List = Range(Coaty).Value


End Sub

The error I get is "run time error 1004 method range of object global failed". The 'Coat' string returns a named range within the worksheet if that has any bearing?

Kind regards

 

[Norie]

Is Coat meant to be the name of a named range?

If it is have you checked the named range selected in the combobox?

Also, have you checked it's scope?

Based on that you might need to include a worksheet reference for the range.

 

[gallen]

I think I see what you're attempting I think.

Norie is of course correct. Also Coaty needs to be declared as an array by using parenthesis:

Private Sub FamilyCombo_Change()

Dim Coat As String
Dim Coaty[COLOR=#ff0000]()[/COLOR] As String


    Coat = FamilyCombo
    Coaty = Split(Range([COLOR=#ff0000]"[/COLOR]Coat[COLOR=#ff0000]"[/COLOR]).Value)
    Me.CoatingCombo.List = Range(Coaty).Value


End Sub

 

[Charmwah]

Hi Norie

The selection returned from the FamilyCombo combobox will be a two word string, but only the last word of the string is the named range.

Thanks

 

[Charmwah]

The below doesn't work either since 'coat' is not the named range, nor is the value from the combobox. Its the last word of the two word string that is the named range, thats what I'm trying to extract...

I think I see what you're attempting I think.

Norie is of course correct. Also Coaty needs to be declared as an array by using parenthesis:

Private Sub FamilyCombo_Change()

Dim Coat As String
Dim Coaty[COLOR=#ff0000]()[/COLOR] As String


    Coat = FamilyCombo
    Coaty = Split(Range([COLOR=#ff0000]"[/COLOR]Coat[COLOR=#ff0000]"[/COLOR]).Value)
    Me.CoatingCombo.List = Range(Coaty).Value


End Sub

 

[Fluff]

How about

Private Sub FamilyCombo_Change()


Dim Coat As String
Dim Coaty As Variant
    Coat = FamilyCombo
    Coaty = Split(Coat)(1)
    Me.CoatingCombo.List = Range(Coaty).Value


End Sub

 

[Charmwah]

Hi Fluff

You've solved it once again, thanks very much =D

How about

Private Sub FamilyCombo_Change()


Dim Coat As String
Dim Coaty As Variant
    Coat = FamilyCombo
    Coaty = Split(Coat)(1)
    Me.CoatingCombo.List = Range(Coaty).Value


End Sub

 

[Fluff]

Glad we could help & thanks for the feedback