VBA Split not working

Charmwah

Board Regular
Joined
Jan 23, 2017
Messages
59
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:

Code:
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
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,539
Office Version
365
Platform
Windows
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

Well-known Member
Joined
Jun 27, 2011
Messages
1,993
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:

Code:
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

Board Regular
Joined
Jan 23, 2017
Messages
59
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

Board Regular
Joined
Jan 23, 2017
Messages
59
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:

Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,559
Office Version
365
Platform
Windows
How about
Code:
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

Board Regular
Joined
Jan 23, 2017
Messages
59
Hi Fluff

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

How about
Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
38,559
Office Version
365
Platform
Windows
Glad we could help & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,095,359
Messages
5,444,005
Members
405,259
Latest member
charliechaz

This Week's Hot Topics

Top