Multiple WorksheetFunctions.Index/Match

[KDS14589]

I have the following formula (which works fine) on a spreadsheet that I’m changing into VBA.

="RGB("&INDEX(ColorList!$C$5:$C$1000,MATCH(Office!$A$2,ColorList!$F$5:$F$1000))&","&INDEX(ColorList!$D$5:$D$1000,MATCH(Office!$A$2,ColorList!$F$5:$F$1000))&","&INDEX(ColorList!$E$5:$E$1000,MATCH(Office!$A2,ColorList!$F$5:$F$1000))&")"

the formula gives me RGB(204,255,145)
But when I add beginning text or separating commas or a ending ) i get nothing.

This is a sample of my last failure.

"RGB(" & WorksheetFunction.Index(ShGE04.Range("C5:C1000"), WorksheetFunction.Match(ShGE02.Range("A2"), ShGE04.Range("F5:F1000"), 0)) & "," &

WorksheetFunction.Index(ShGE04.Range("D5:D1000"), WorksheetFunction.Match(ShGE02.Range("A2"), ShGE04.Range("F5:F1000"), 0)) & "," &

WorksheetFunction.Index(ShGE04.Range("E5:E1000"), WorksheetFunction.Match(ShGE02.Range("A2"), ShGE04.Range("F5:F1000"), 0)) & ")"

ShGE04 is the CodeName for worksheet ColorList.
ShGE02 is the CodeName for worksheet Office.

If I ‘test’ one of those WorksheetFunction.Index/Match it will give me the correct number, it’s when I try to combine them that will not work.
Other worksheet code locations I have no problem adding text or special characters. But those are single Index/Match formulas where this problem has 3.


Can someone help?

 

[Alex Blakenburg]

Did you by any chance have it just the way you are showing it.
ie on separate lines which don't have a continuation mark "_" on the end
and a blank line in between.
This works fine and I tried yours adding the " _" to the end of each line and eliminating the spare blank rows and it worked fine for me too.

Dim strOut As String
strOut = "RGB(" & _
                WorksheetFunction.Index(ShGE04.Range("C5:C1000"), WorksheetFunction.Match(ShGE02.Range("A2"), ShGE04.Range("F5:F1000"), 0)) & _
                "," & _
                WorksheetFunction.Index(ShGE04.Range("D5:D1000"), WorksheetFunction.Match(ShGE02.Range("A2"), ShGE04.Range("F5:F1000"), 0)) & _
                "," & _
                WorksheetFunction.Index(ShGE04.Range("D5:D1000"), WorksheetFunction.Match(ShGE02.Range("A2"), ShGE04.Range("F5:F1000"), 0)) & _
                ")"

 

[KDS14589]

Did you by any chance have it just the way you are showing it.
ie on separate lines which don't have a continuation mark "_" on the end
and a blank line in between.
This works fine and I tried yours adding the " _" to the end of each line and eliminating the spare blank rows and it worked fine for me too.

Dim strOut As String
strOut = "RGB(" & _
                WorksheetFunction.Index(ShGE04.Range("C5:C1000"), WorksheetFunction.Match(ShGE02.Range("A2"), ShGE04.Range("F5:F1000"), 0)) & _
                "," & _
                WorksheetFunction.Index(ShGE04.Range("D5:D1000"), WorksheetFunction.Match(ShGE02.Range("A2"), ShGE04.Range("F5:F1000"), 0)) & _
                "," & _
                WorksheetFunction.Index(ShGE04.Range("D5:D1000"), WorksheetFunction.Match(ShGE02.Range("A2"), ShGE04.Range("F5:F1000"), 0)) & _
                ")"

Yes, I had the (space)_ at the end of each formula, such as you do (in fact I had exactly like yours). I just removed them to show the formula better here. The one detail I didn’t have was the Dim as String. I had Dim as long. I made that correction and now it works.

THANKS

 

[Alex Blakenburg]

Thanks for providing feedback on what you needed to do at your end. Glad we could help.