Select Case Join Application.Transpose range change

[Edgarvelez]

Hi All,
Was given a code and has worked really good, however I had to make a change (Added a row in sht1) and my range is not longer ("D9:D13") and now it's now ("D9") and ("D11:D13")
Need some help fixing this line, tried a few thing but I get errors and its above my knowledge grade, any help is appreciated and thanks.

    sh1.Select
    Select Case Join(Application.Transpose(sh1.Range("D9:D13").Value), "|")
    
    Case "Metric Ton|GW|Single Line|Single Line Weight"
    sh1.Range("D32").Select
    ActiveCell.FormulaR1C1 = "=ROUND((R[-6]C/R[-8]C),3)"
    sh1.Range("D33").Select
    ActiveCell.FormulaR1C1 = "=ROUND((R[-6]C/R[-9]C),3)"
    sh1.Range("A1").Select
    'GW
    sh1.Range("D26").Copy
    sh2.Cells(Rows.Count, 8).End(xlUp)(2).Resize(sh1.Range("D30").Value).PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    sh1.Range("D30:D33").Select
    Selection.ClearContents
    sh1.Range("A1")

 

[steve the fish]

You could try:

For Each c In sh1.Range("D9,D11:D13")
    x = x & "|" & c.Value
Next
Select Case Mid(x, 2)
'...
End Select

 

[Edgarvelez]

got a compile error

 

[steve the fish]

What i gave has to replace

Select Case Join(Application.Transpose(sh1.Range("D9:D13").Value), "|")

which is where you now have the problem.

 

[Fluff]

Another option

   Select Case sh1.Range("D9").Value & "|" & Join(Application.Transpose(sh1.Range("D11:D13").Value), "|")

 

[mohadin]

Or

 Select Case WorksheetFunction.Substitute(Join(Application.Transpose(Sheets("sh1").Range("D9:D13")), "|"), "|", "", 1)

 

[Edgarvelez]

Fluff, it worked!!! and thank you so much, you guys are the best as always.

Select Case sh1.Range("D9").Value & "|" & Join(Application.Transpose(sh1.Range("D11:D13").Value), "|")

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[steve the fish]

Either worked as it happens.

 

[Fluff]

mohadin's code won't work if there is data in D10.