セル参照時のアプリケーション定義またはオブジェクト定義のエラーです。

結合されたセルの文字列を、変数に入れたいのですが、件名の通りのエラーが表示されます。
いままで左上のセル番号のみを指定すれば問題なく処理されたのですが、今回に限っては処理されません。

このエラーについて調べたのですが、「VBAが予期せぬエラー」とのことで、改善点が見つかりません。

下記の通り、色々な方法で試したのですが全て同様のエラーといなります。

  Bcate = Range(Cells(9, 9), Cells(rowcate, colcate)).Find(cate).Column
　zyaname = Cells(6,Bcate)

  Bcate = Range(Cells(9, 9), Cells(rowcate, colcate)).Find(cate).Column
  zyaname = Cells(6,Bcate).Value

  Bcate = Range(Cells(9, 9), Cells(rowcate, colcate)).Find(cate).Column
  zyaname = Range(Cells(6, Bcate), Cells(9, Bcate))

  Bcate = Range(Cells(9, 9), Cells(rowcate, colcate)).Find(cate).Column
  zyaname = Range(Cells(6, Bcate), Cells(9, Bcate)).Value

変数Bcateへは問題なく変数が入力されており（10）、目的の文字列が有るのはJ列です。
セルは6列目から9列目まで結合されており、ここの文字列を変数に入れ込みたいです。

同じような経験されたことが在る方、または当方のケアレスミスの可能性など(変数の参照違い以外で)考えられる原因をご指摘いただきたく質問致しました。
よろしくお願いします。

全体のコードはこちらです

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Dim EROW As Long
Dim Row As Long
Dim col As Long
Dim log As String

EROW = Cells(Rows.Count, 2).End(xlUp).Row
Row = Target.Row
col = Target.Column

 If Row <= EROW And Row > 5 And col = 2 And Cells(Row, 2).Interior.ColorIndex = 34 Then
 
  If Cells(Row, 5) = "" Then
   MsgBox "記録日が記載されていません。"
  End If
  Dim WB As String
  Dim path As String
  Dim cate As String
  Dim rowcate As Long
  Dim colcate As Long
  Dim Bcate As String
  Dim Bname As String
  Dim sename As String
  Dim zyaname As String
  Dim tgtsheet As String
  Dim PRow As Long
    
  path = ThisWorkbook.path
  WB = ThisWorkbook.name
  cate = Cells(Row, 2)
  tgtsheet = Cells(Row, 4)
   
   i = 1
  Do While Cells(i, 9) <> "規　　則"
   i = i + 1
  Loop
   rowcate = i - 1
  colcate = Cells(6, Columns.Count).End(xlToLeft).Column
  Bcate = Range(Cells(9, 9), Cells(rowcate, colcate)).Find(cate).Column
  zyaname = Range(Cells(6, Bcate), Cells(9, Bcate)).Value '’’’’’’’’’’’’’’’ここ質問中
  sename = "書籍一覧(" & zyaname & ")"
  Bname = "書籍記録(" & Cells(6, Bcate) & ")"
  
  Workbooks.Open path & "\" & Bname & ".xlsx" '指定のワークブックを開く
  
  Workbooks(WB).Worksheets(tgtsheet).Move After:=Workbooks(Bname).Sheets(Worksheets.Count) '読書データのブックを移動
  
  Workbooks(Bname).Close savechanges:=True '変更を保存して閉じる

  Workbooks(WB).Worksheets(sename).Activate '貼り付け先のシート
  PRow = Cells(Rows.Count, 2).End(xlUp).Row '貼り付け先のリスト最終行
  
  Worksheets("インデックス").Range(Cells(Row, 2), Cells(Row, 8)).Cut Destination:=Worksheets(sename).Cells(PRow + 1, 2) 'ｲﾝﾃﾞｯｸｽからコピペ
  
  Worksheets("インデックス").Activate
  Range(Cells(Row, 2), Cells(Row, 8)).Delete shift:=xlShiftUp '切り取った部分を削除して上にシフト
  
  Range(Cells(4, 1), Cells(105, 8)).Borders.LineStyle = xlContinuous '格子作成
  Range(Cells(4, 1), Cells(105, 8)).BorderAround Weight:=xlThick '周囲太枠
  
  End If

End Sub

今回質問しました箇所に「ここ質問中」というコメントが付いています。
よろしくお願いします。

m.ts10806

2021/01/20 11:05 編集

念のため、どの行で出るエラーなのか明示してもらえませんか？

m.ts10806

2021/01/20 11:06

あとそれぞれの変数定義の場所と、処理の位置関係コードは全体の流れが分かるように提示してください

ice930

2021/01/20 11:30

申し訳ありません。 zyanameの部分すべてで同様のエラーが発生しています。それぞれ書かせていただきました。 zyaname以外の変数については、ローカルウインドウで確認しています。型は全てLong型で特別な設定はしていません。

ice930

2021/01/20 11:36

全体のコードも最下部に添付致しました。よろしくお願いします。

行動規範の内容に同意します

回答1件

ベストアンサー

変数Bcateへは問題なく変数が入力されており（10）、目的の文字列が有るのはJ列です。
セルは6列目から9列目まで結合されており、ここの文字列を変数に入れ込みたいです。

太字の部分は、6行目から9行目 の間違いだとして回答します。

エラーに関係する部分だけ抜き出してみました。

vba
1Public Sub Test()
2   Dim Bcate As String
3   Dim zyaname As String
4   Dim sename As String
5   
6   Bcate = 10
7   
8   zyaname = Range(Cells(6, Bcate), Cells(9, Bcate)).Value
9   sename = "書籍一覧(" & zyaname & ")"
10End Sub

「アプリケーション定義またはオブジェクト定義のエラーです。」というエラーになります。

Bcate を String型（文字列）で宣言しています。
Cellsの引数は(行番号, 列番号) というように数値型でする必要がありますが、文字列にしているので上記のエラーになります。
Bcate を Long型（長整数）で宣言してください。

vba
1Public Sub Test()
2   Dim Bcate As Long
3   Dim zyaname As String
4   Dim sename As String
5   
6   Bcate = 10
7   
8   zyaname = Range(Cells(6, Bcate), Cells(9, Bcate)).Value
9   sename = "書籍一覧(" & zyaname & ")"
10End Sub

今度は、「型が一致しません。」というエラーに変わります。
Range(Cells(6, Bcate), Cells(9, Bcate)).Value は複数セルのValueですので配列になります。
配列を、文字列型の変数（zyaname）に代入しようとしてますので、上のエラーになります。
この場合のValueはバリアント型の配列ですので、zyanameはバリアント型で宣言する必要があります。

vba
1Public Sub Test()
2   Dim Bcate As Long
3   Dim zyaname As Variant
4   Dim sename As String
5   
6   Bcate = 10
7   
8   zyaname = Range(Cells(6, Bcate), Cells(9, Bcate)).Value
9   sename = "書籍一覧(" & zyaname & ")" 
10End Sub

今度は最後の行で「型が一致しません。」というエラーになります。
配列を文字列と & で連結しようとしているからです。

配列は複数の値を持っていますが、その最初の値を取り出す場合は、zyaname(1,1) とします。

vba
1Public Sub Test()
2   Dim Bcate As Long
3   Dim zyaname As Variant
4   Dim sename As String
5   
6   Bcate = 10
7   
8   zyaname = Range(Cells(6, Bcate), Cells(9, Bcate)).Value
9   sename = "書籍一覧(" & zyaname(1,1) & ")" 
10End Sub

これでエラーがなくなり、sename には結合セルの値を含む文字列が代入されます。

実は、結合セルは先頭のセル（左上のセル）に値が格納されており、他のセルは空欄になってます。
ですので、先頭のセルを最小すれば値を取得できます。
ということで下記でもエラーなく動作します。

vba
1Public Sub Test()
2   Dim Bcate As Long
3   Dim zyaname As String
4   Dim sename As String
5   
6   Bcate = 10
7   
8   zyaname = Cells(6, Bcate).Value
9   sename = "書籍一覧(" & zyaname & ")"
10End Sub