Excelで時間帯ごとのガントチャート付きシフト表を瞬時に作成する方法！休憩時間つき！

先の質問で以下の内容でVBAに記述しました
Sub test()
Range(“A6”).Formula = “=+IFERROR(INDEX(シフト!$A$1:$AG$129,MATCH($A11,シフト!$A$1:$A$129,0)-1,MATCH($B$7,シフト!$A$8:$AG$8,0)),””)”
Range(“A6”) = Range(“A6”).Value
End Sub
などでオートメーションエラーがでます　少し区別がわからづ温和くしております
ほかの計算式だとうまくいくのですが
例えば以下の計算式
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range(“F10:BR10”)) Is Nothing Then
Range(“BY9”).Formula = “=IF($BY$253=0,0,LARGE($BZ$253:$CJ$253,$BY$253))”
Range(“BZ9”).Formula = “=MAX($BZ$253:$CJ$253)”
Range(“By9:Bz9”).Value = Range(“By9:BZ9”).Value
End If
End Sub

最初の計算式ではオートメーションエラーが出ます

早速　相談完成かと思われましたが　、少し不便が出ました
ユーザーフォームを表示しリストボックスクリック時値ははいります
問題なし
この後の動作で　マウスでセルを移動するのですが　矢印での移動も可能にしたいのですが　入力後フォーカスをセルに持っていき自由に移動したいのですが
ユーザーフォーム表示
Sub 入力()
On Error Resume Next
UserForm1.Show vbModeless
With UserForm1
.StartUpPosition = 0
.Top = 20
.Left = 30
End With
On Error GoTo 0
End Sub

リストぼっくクリック
Private Sub ListBox1_Click()
Application.ScreenUpdating = False
On Error Resume Next
Dim i As Integer
With ListBox1
For i = 0 To 5
ActiveCell.Offset(i, 0).Value = .List(.ListIndex, i)
Next i
End With
On Error GoTo 0
Application.ScreenUpdating = True
End Sub

UserForm1.Show vbModeless　このモードでセルは移動できるのですが
フォーカスがリストボックスに残ったままで　マウス操作になります
ユーザーフォームは開いたまま　入力後は　ENTER　矢印などでもセルを
移動したいのですが

少し形式を変えました　行の検索で　バラバラの行を抽出するようにいたしました
精査お願いいたします　他のサイトのものを検証可変したものです
Dim i As Long
Dim mySh As Worksheet
Dim myFlg As Boolean
Dim lastRow As Long, myRow As Long
Dim myK As String
Set myDic = CreateObject(“Scripting.Dictionary”)
‘ —(1)元データの項目を配列に格納
lastRow = Worksheets(“Sheet1”).Range(“E” & Rows.Count).End(xlUp).Row ‘種類列
myVal = Worksheets(“Sheet1”).Range(“E2”, “E” & lastRow).Value ‘種類列
‘ —(2)myDicへデータを格納
For Each c In myVal
If Not c = Empty Then
If Not myDic.Exists(c) Then
myDic.Add c, “”
End If
End If
Next
‘—-振り分け先のシートが存在するか否かをチェック
myKey = myDic.Keys
For i = 0 To myDic.Count – 1
For Each mySh In Worksheets
myFlg = False
If mySh.Name = myKey(i) Then
myFlg = True
mySh.Cells.Delete
Exit For
End If
Next mySh
‘—-振り分け先のシートがなかったらシートを追加する
If myFlg = False Then
ActiveWorkbook.Worksheets.Add(after:=Worksheets(Worksheets.Count)).Name = myKey(i)
End If
‘—-列見出しをコピー&貼り付け
Worksheets(“Sheet1”).Range(“A1:E1”).Copy Worksheets(myKey(i)).Range(“A1”)
Next i
‘—-データを転記する
For i = 2 To lastRow
myK = Worksheets(“Sheet1”).Range(“E” & i).Value
If myK “” Then
myRow = Worksheets(myK).Range(“E” & Rows.Count).End(xlUp).Row + 1
Worksheets(“Sheet1”).Range(“A” & i & “:E” & i).Copy _
Worksheets(myK).Range(“A” & myRow & “:E” & myRow)
End If
Next i
Set myDic = Nothing
End Sub
うまくいっているようなんですが　式も同時に入るので困っています
書式と値のみにするにはどのようにしたらよいでしょう？

早速ありがとうございます
=SUBSTITUTE(SUBSTITUTE(シフト!C11&”,”&シフト!C12&”,”&シフト!C13&”,”&シフト!C14&”,”&シフト!C15,”,,,”,””),”,,”,””)
で記入してみました
結果
C１０ に値があり他空白の場合
9:30,17:00,　最後に　コンマが残り　ｃｓｖ　NGでした
２個の場合OK
３個の場合OK
４個の場合NG
５個の場合OK　という結果でした　どうしても最後にコンマが残るパターンが
出現します　「　, ] コンマ一つを置換対象に入れてしまうと
今度はつながりがおかしくなります
　どうしたらよいのか
このような考えはいかがでしょう
&”,”&の部分をそれぞれ違うパターンでつないでおいて出てきたパターンを置換していく　何やら混乱してきました

ありがとうございます　ばっちりです
少しパフォーマンスもあがったような気がします
パフォーマンスで気になるところがあるのですが
A列に　★★★★のマークがある場合　その行を非表示にする
Sub 休4()
Application.ScreenUpdating = False
Static ChkNext As Boolean
If ChkNext = False Then
ChkNext = True
‘MsgBox “１回目の押下です。”
‘ここに１回目の実行コードを記述
Dim rw As Long ‘// 行カウンタ
For rw = 10 To Range(“A200”).End(xlUp).Row

If Range(“A” & rw).Value = “★★★★” Then
Rows(rw).Hidden = True
End If
Next
Else
ChkNext = False
Call 復4
End If
Application.ScreenUpdating = True
End Sub
と書いています　少しもっさりするのですが　改善方法はありますか？

しかしいつもながら　勉強させていただいて感謝しております
どのような文献を読んで勉強されているのか　差し支えなければご教授ください
私も、もうすぐ定年なので　ボケ防止でさらに勉強したいです

ほぼ完成と思いきや　致命的なことがありました
シフト複数あり　それぞれに　始業　終業　休憩１　復帰１　休憩２　復帰３
　　　　　　シフトA　　　　9:00 18:00 12:00 13:00　　15:00 15:15
このようなパターンを基本としマスターとして　別シートの時間範囲に入れてあります１０パターン程　
セルA1に入力規則で　リストを表示しそこに　シフトAと入るようにして
A2　A3　A4にVlookup関数を使いそれぞれの時間が入るようにしております
=IFERROR(VLOOKUP(A1,時間,13,0),””)
うまく取得してくれて　それぞれの値が　各セルに代入されました
が　問題ありました　マスターの数字を触ると　すべての数字が変わりました
あらー　A2が　9:00 したら　マスターを10:0０にするとすべて変わる
考えてみれば当然ですね　かといって　パターンを増やすのも選択が大変
入力された値を　手動入力すると　式が消える

で考え付いたのが　VBAでフォームを作り　リストを表示　その値をセルに代入
編集可能にしました
該当セルのクリックでフォームを表示　フォームのコード

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
‘ダブルでマクロ
If Not Application.Intersect(Target, Range(“A１:A21”)) Is Nothing Then
Cancel = True
UserForm1.Show
End If
End Sub
フォームのコード
Private Sub UserForm_Initialize()
Dim lastRow As Long
lastRow = Sheets(“設定”).Cells(Rows.Count, 1).End(xlUp).Row
With ListBox1
.ColumnHeads = True
.ColumnCount = 10
.ColumnWidths = “40;50;50;50;50;50;50;50”
.RowSource = “設定!” & Range(“I2”, “S50” & lastRow).Address
End With
リストが表示されました　問題はここから　それぞれの値をセルに代入するため
フォームコードに
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
ActiveCell = ListBox1.List(ListBox1.ListIndex, 0)
End Sub
を記入　A1に　選択の値が入ります　OK
A２からは何も入らないこの場合
A１に　シフトA
A2に　９：００
A3に　　１８：００
A4に　　１２：００　と以下順番にリストの値を入れたいのですが
ActiveCell.Offset(1).Select　を書いてみたのですが　ずれるだけ

代入が完了したら　フォームを自動で閉じたいのですが　
どうもこの辺が　苦手です　　よろしくお願いいたします

なるほどですね、やはり計算式ではかなり複雑で無理がありますね
検証いたします

いま取り組んでいるものが、ひと月の勤務表から　日毎に勤務時間を取り出し
日毎バーチャートを表示し　その値をｃｓｖ化してウェブの勤務表にUＰ
ＵＰロードするというのを作成中です
通常なら、そう難しいことではないのですが、
年中無休　勤務シフトバラバラ、休憩バラバラ　１５分を４回であったり
３０分を二回であったりと　なので苦労しております
そこでこのようになっています
シート１　月３０日分
一人の社員で７行使います　
１行目　社員ID
２行目　各シフト　例えばシフトAとか
３行目　勤務総時間
４行目　休憩１
５行目　休憩２
６行目　休憩３
７行目　休憩４　　としています
１日が　C列としますと　２行目のシフトをプルダウンで　選び
３，４，５，６，７とIFERROR(VLOOKUP(C10,基本シフト,3,0),””)で取得しています
３，４，５，６，７はそれぞれ
IF(H3=0,””,TEXT(H3,”[h]:mm”) &”,”&TEXT(I3,”[h]:mm”))でつなぎ
９：００，１８：００で表示させてます
時間帯だけ全部繋げて一つもセルに表示したいのですが　時間ごとに改行とかできれば　一つのセルに収まるのですが　良い方法はありませんか
また最初の総勤務時間だけフォントの大きさを違えるとか。。

先は長いですまた度々お世話になります