Select Case Join(Application.Transpose not working

[Edgarvelez]

Sub Macro4CreatSplShpmpSht()
'
' Macro4CreatSplShpImpSht Macro
'

'




    Sheets("Dashboard").Select
   
    Dim sh1 As Worksheet, sh2 As Worksheet
    Set sh1 = Sheets("Dashboard")
    Set sh2 = Sheets("Split Shipment Import")
   
    Set wb = ActiveWorkbook
    Set wsDashboard = wb.Sheets("Dashboard")
    Set wsData = wb.Sheets("Split Shipment Import")
    Set wsDest = wb.Sheets("Split Shipment Import")
      
'--------------------------Single or Multiline Check----------------------------
    sh1.Range("C10").Select
   
Select Case Join(Application.Transpose(Active.Sheet("B22:E22").Value), "|")
  Case "Yes"
    sh1.Range("B24").Select

  Case "NO"
    sh1.Range("C24").Select
   
    'Empty---------
            Range("B29").Select
    ActiveCell.FormulaR1C1 = "Empty"
    Range("B30").Select

    'Full-----------
                Range("B29").Select
    ActiveCell.FormulaR1C1 = "Full"
    Range("B30").Select
End Select

Hi All, I have a bit of code that I can't get to work, kind of simple what I want to do, if any part of the range is empty then run the empty code if not then run the other.
I have used case before but I am missing something here.
Any help is appreciated and thanks in advance.

 

[kevin9999]

You might be overcomplicating this a bit. Where you say:

if any part of the range is empty then run the empty code if not then run the other.

Does that mean if any cell in the range "B22:E22" on the sheet Dashboard is 'empty' (do you mean contains no value whatsoever) then put "Empty" in cell B29 of the Dashboard sheet - otherwise put "Full" in cell B29 of the Dashboard sheet?

 

[Domenic]

Try...

    Dim str As String

    With Application
        str = Join(.Transpose(.Transpose(ActiveSheet.Range("B22:E22").Value)), "|")
    End With
   
    Select Case str
        'etc
        '
        '
    End Select

Hope this helps!

 

[Domenic]

As an explanation...

ActiveSheet.Range("B22:E22").Value refers to a 1-Row by 4-Column two-dimensional array

.Transpose(ActiveSheet.Range("B22:E22").Value) refers to a 4-Row by 1-Column two-dimensional aray

.Transpose(.Transpose(ActiveSheet.Range("B22:E22").Value)) refers to a one-dimensional array, which is what Join requires

 

[kevin9999]

Unless I'm misunderstanding what your intentions are (based on your existing code) does the following give you what you want? I suspect you may have left out some important code in your initial post though...

Sub Edgar()
    Dim ws As Worksheet, c As Range, i As Long
    Set ws = Worksheets("Dashboard")
    For Each c In ws.Range("B22:E22")
        If c <> "" Then i = i + 1
    Next c
    If i = 4 Then ws.Range("B29") = "Full" Else ws.Range("B29") = "Empty"
    ws.Range("B30").Select
End Sub

 

[Peter_SSs]

@Edgarvelez
When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

 

[Edgarvelez]

@Edgarvelez
When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

My apologies, will do going forward.

 

[Edgarvelez]

You might be overcomplicating this a bit. Where you say:

Does that mean if any cell in the range "B22:E22" on the sheet Dashboard is 'empty' (do you mean contains no value whatsoever) then put "Empty" in cell B29 of the Dashboard sheet - otherwise put "Full" in cell B29 of the Dashboard sheet?

Better explanation,
If all cells are empty the run Case "Yes" but if any one of the 4 is empty then Run Case "No"
That is what I am really trying to achieve.

 

[Peter_SSs]

@Edgarvelez
Also, no need to post the exact same information multiple times. I have removed the other two posts that say the same thing.

 

[Edgarvelez]

@Edgarvelez
Also, no need to post the exact same information multiple times. I have removed the other two posts that say the same thing.

My apologies will not happen again.