VBA「実行時エラー ‘1004’」コピーができないエラーについて

前提・実現したいこと

エクセルで表を作る際、書式のみを行または列ごとに貼り付けるマクロを作成しております。

発生している問題・エラーメッセージ

実行時エラー ‘1004’

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

◆エラーが出るコード

            Range(Cells(row, Col1st), Cells(row, ColEnd)).Copy

変数の値入れ漏れもなく、シートは1シートのみで複数シートにまたがらず、
ほぼ同じ内容のElse文以降だと動きます。

該当のソースコード

Sub 書式貼り付け_自動縦横判定()

    Dim buf_from As Range, buf_To As Range
    Set buf_from = Application.InputBox(Prompt:="書式取得元セルを選択してください。", Type:=8)
    Set buf_To = Application.InputBox(Prompt:="書式貼付先セルを選択してください。", Type:=8)

    Dim from_add As String, To_add As String
    from_add = buf_from.Address(, , xlR1C1) 'R4C14:R20C14
  　To_add = buf_To.Address(, , xlR1C1)     'R4C14:R20C17
        
    Dim Col1st As Variant, ColEnd As Variant, row1st As Variant, rowEnd As Variant
    row1st = Mid(To_add, InStr(To_add, "R") + 1, InStr(To_add, "C") - InStr(To_add, "R") - 1)
    Col1st = Mid(To_add, InStr(To_add, "C") + 1, InStr(To_add, ":") - InStr(To_add, "C") - 1)
    rowEnd = Mid(To_add, InStr(InStr(To_add, ":"), To_add, "R") + 1, InStr(To_add, "C") - InStr(To_add, "R"))
    ColEnd = Mid(To_add, InStr(InStr(To_add, ":"), To_add, "C") + 1, Len(To_add) - InStr(To_add, "C") - 1)
    
'　行貼り付け／列貼り付け の判定
    If ColEnd = Mid(from_add, InStr(InStr(from_add, ":"), from_add, "C") + 1, Len(from_add) - InStr(from_add, "C") - 1) Then
        
        Dim row As Long    '　行貼り付け
        For row = row1st To rowEnd - 1
            Range(Cells(row, Col1st), Cells(row, ColEnd)).Copy
            Range(Cells(row + 1, Col1st), Cells(row + 1, ColEnd)).PasteSpecial Paste:=xlPasteFormats
        Next row
    
    Else
    
        Dim col As Long    '　列貼り付け
        For col = Col1st To ColEnd - 1
            Range(Cells(row1st, col), Cells(rowEnd, col)).Copy
            Range(Cells(row1st, col + 1), Cells(rowEnd, col + 1)).PasteSpecial Paste:=xlPasteFormats
        Next col
    
    End If
    Application.CutCopyMode = False

End Sub

試したこと

エラー名で検索し、記事等読んでみたのですが、
原因不明エラーのため理由の特定につながりませんでした...
https://www.sejuku.net/blog/71121　等

宜しくお願い致します。

tosi

2020/08/26 05:16

エラーの前行で値を確かめて見てどうですか。ゼロとか入ってませんか。 Debug.Print row & " " & Col1st & " " & row & " " & ColEnd (イミディエイトウィンドウで見る)

mattuwan

2020/08/26 07:30

やりたいことは、コピー元のセル範囲が縦長なら、列ごとにコピー、横長なら行ことにコピーですか？長々とした数式だと後で読んで何してるのかわかるのかなぁ。。。。もっと簡潔に表現できないですかねぇ？

mai._123

2020/08/26 09:37

@tosi さんイミディエイトウィンドウにいただいた式を入れてみたのですが、数値はあっているようです。。。

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

回答2件

ベストアンサー

宣言を変更してみてください。

VBA
1Dim Col1st As Long, ColEnd As Long, row1st As Long, rowEnd As Long

Col1stとColEndにスペースが含まれており、文字列扱いになっているのでエラーになっています。
⇒スペースは入っていませんでした！すいません！見間違いでした。

ローカルウィンドウの画像です。これを見間違いました。
row1stとrowEndもStringですが、うまくいくのは謎です。
Col1StとColEndがStringの場合のみエラーとなっているようです。
予め数値で宣言しておけば問題ないのでそうしていただけたら助かります。

あと、これではまずいでしょうか？

VBA
1Sub 書式貼り付け_自動縦横判定()
2
3    Dim buf_from As Range, buf_To As Range
4    Set buf_from = Application.InputBox(Prompt:="書式取得元セルを選択してください。", Type:=8)
5    Set buf_To = Application.InputBox(Prompt:="書式貼付先セルを選択してください。", Type:=8)
6
7    buf_from.Copy
8    buf_To.PasteSpecial xlPasteFormats
9
10    Application.CutCopyMode = False
11
12End Sub