[[20160902164225]]　『相対参照に変換』（くろ）　ページの最後に飛ぶ

『相対参照に変換』（くろ）

いつもお世話になってます。
2点質問があります。

1.相対参照に変換する（下記マクロ）と「実行エラー13　型が一致しません」と出るのですが「J/K/L]列のみエラーになります。
原因は何でしょうか？

2.相対参照にしたい行がこの他（4:34)に
　41：71
　78：108
　115：145
　152：182
　189：219
　226：256
　263：293
　300：330
　337：367
　374：404
　411：441
にも同じ処理をしたいのですが飛び飛びの行の場合どのように書いたらいいのでしょうか？

Option Explicit

Function 書式設定()

    Dim myDate As Date
    Dim i As Integer
    Dim w As Integer
    Dim e As Integer
    Dim crItem2 As Date
    Dim copyFrom As Range
    Dim pos As Range
    Dim sh1 As Worksheet
    Dim sh2 As Worksheet
    Dim sh3 As Worksheet

    Set sh1 = Sheets("設定")
    Set sh2 = Sheets("原本")
    Set sh3 = Sheets("集計")

        With Sheets("原本")
        With .Range("4:34")
            .Columns("H").Resize(.Rows.Count).Formula = Application.ConvertFormula(.Columns("H").Resize(.Rows.Count).Formula, xlA1, xlA1, xlRelative)
            .Columns("J").Resize(.Rows.Count).Formula = Application.ConvertFormula(.Columns("J").Resize(.Rows.Count).Formula, xlA1, xlA1, xlRelative)
            .Columns("K").Resize(.Rows.Count).Formula = Application.ConvertFormula(.Columns("K").Resize(.Rows.Count).Formula, xlA1, xlA1, xlRelative)
            .Columns("L").Resize(.Rows.Count).Formula = Application.ConvertFormula(.Columns("L").Resize(.Rows.Count).Formula, xlA1, xlA1, xlRelative)
            .Columns("AK").Resize(.Rows.Count).Formula = Application.ConvertFormula(.Columns("AK").Resize(.Rows.Count).Formula, xlA1, xlA1, xlRelative)
            .Columns("AM").Resize(.Rows.Count).Formula = Application.ConvertFormula(.Columns("AM").Resize(.Rows.Count).Formula, xlA1, xlA1, xlRelative)
            .Columns("AO").Resize(.Rows.Count).Formula = Application.ConvertFormula(.Columns("AO").Resize(.Rows.Count).Formula, xlA1, xlA1, xlRelative)
            .Columns("AQ").Resize(.Rows.Count).Formula = Application.ConvertFormula(.Columns("AQ").Resize(.Rows.Count).Formula, xlA1, xlA1, xlRelative)
            .Columns("AT").Resize(.Rows.Count).Formula = Application.ConvertFormula(.Columns("AT").Resize(.Rows.Count).Formula, xlA1, xlA1, xlRelative)
            .Columns("AV").Resize(.Rows.Count).Formula = Application.ConvertFormula(.Columns("AV").Resize(.Rows.Count).Formula, xlA1, xlA1, xlRelative)
            .Columns("AY").Resize(.Rows.Count).Formula = Application.ConvertFormula(.Columns("AY").Resize(.Rows.Count).Formula, xlA1, xlA1, xlRelative)
        End With
        End With

End Function

＜使用 Excel：Excel2013、使用 OS：Windows7 ＞

1．データシートを見ないと何とも…。セル連結とか、空欄とか？
2．規則性があるようなので、For文で Step 37 として範囲文字列を作るとか？
（???） 2016/09/02(金) 17:37

（???）さん
回答ありがとうございます。

＞＞1．データシートを見ないと何とも…。セル連結とか、空欄とか？

結合とか空欄はないのですが。。。
関数が長いからとか影響はありますか？

因みに関数は

 With Sheets("原本")
　　With .Range("4:34")
　　　　　　.Columns("J").Resize(.Rows.Count).Formula = "=IF(COUNT(C4:D4)<2,"""",IF(OR(COUNTIF(祝日,A4)=1,WEEKDAY(A4)=1,WEEKDAY(A4)=7),"""",IF(AND(C4>$AJ$2,OR(E4<>"""",F4<>"""")),TEXT(MAX(0,I4-S4),""h:mm"")*1,TEXT(MAX(0,MIN(D4+(C4>D4),$AJ$3)-MAX(C4,$AJ$2))+MAX(0,MIN(D4+(C4>D4),$AL$3)-MAX(C4,$AL$2)),""h:mm"")*1)))"
            .Columns("K").Resize(.Rows.Count).Formula = "=IF(COUNT(C4:D4)<2,"""",IF(OR(COUNTIF(祝日,A4)=1,WEEKDAY(A4)=1,WEEKDAY(A4)=7),"""",IF(AND(C4>$AJ$2,OR(E4<>"""",F4<>"""")),IF(D4+(C4>D4)-C4>=""20:15""*1,TEXT(I4-J4-L4,""h:mm"")*1,TEXT(MIN(I4-J4,""4:""*1),""h:mm"")*1),TEXT(MAX(0,MIN(D4+(C4>D4),$AN$3)-MAX(C4,$AN$2))+MAX(0,MIN(D4+(C4>D4),$AS$3)-MAX(C4,$AS$2))+MAX(0,MIN(D4+(D4>D4),$AX$3)-MAX(C4,$AX$2)),""h:mm"")*1)))"
            .Columns("L").Resize(.Rows.Count).Formula = "=IF(COUNT(C4:D4)<2,"""",IF(OR(COUNTIF(祝日,A4)=1,WEEKDAY(A4)=1,WEEKDAY(A4)=7),"""",IF(AND(C4>$AJ$2,OR(E4<>"""",F4<>"""")),IF(D4+(C4>D4)-C4>=""20:15""*1,TEXT(MIN(I4-J4,""5:""*1),""h:mm"")*1,TEXT(I4-J4-K4,""h:mm"")*1),TEXT(MAX(0,MIN(D4+(C4>D4),$AP$3)-MAX(C4,$AP$2))+MAX(0,MIN(D4+(C4>D4),$AR$3)-MAX(C4,$AR$2))+MAX(0,MIN(D4+(C4>D4),$AU$3)-MAX(C4,$AU$2))+MAX(0,MIN(D4+(C4>D4),$AW$3)-MAX(C4,$AW$2)),""h:mm"")*1)))"

　End With
End With

＞＞2．規則性があるようなので、For文で Step 37 として範囲文字列を作るとか？

その場合「With .Range("4:34")」をどのようにループしたらいいのか分からないのですが
ご教授よろしくお願いします。

（くろ） 2016/09/02(金) 19:09

 こんばんわ。

 1に関しては、範囲が5つくらいのセルまでならエラーにならないんですね。

 あまり詳しくないので推測になりますが、数式が長いので1度に処理できる文字数制限などに引っかかってるんでは無いでしょうか？

 ご提示のコードなら結果は＄のみを消すだけになると思うので、以下のようにしても同じ結果になります。
 以下ならご提示の式ではエラーになりません。

 Range("4:34").Columns("J:L").Replace What:="$", Replacement:="", LookAt:=xlPart

 後全ての箇所の　.Resize(.Rows.Count)　は必要ないです。

 2に関しては、以下のようなループにすれば良いと思います。

 Dim i As Long

 For i = 4 To 411 Step 37
    With Rows(i).Resize(31)
        .Columns ～のコード
    End With     
 Next i

（sy） 2016/09/02(金) 20:48

（sy）さん

回答ありがとうございます。

$を消すだけだから置換でも可能なんですね！
盲点でした。

ループのコードもありがとうございます。
セルの指定の仕方がたくさんあって中々覚えられてません。

質問項目ではない
「.Resize(.Rows.Count)は不要」のアドバイスもありがとうございます。

Resize　Range　の使い方をもう一度復習したいと思います。

すべて解決しました。
ありがとうございました。
また機会があればよろしくお願いします。
（くろ） 2016/09/03(土) 10:02

解決済ですが、検証してみました。

エラーの原因は、数式の長さみたいです。

(2016/09/02(金) 19:09)のコードを実行すると

J4の数式の文字数は243
k4の数式の文字数は344

J4の数式をConvertFormulaで変換することはできますが
k4の数式を同じように変換しようとするとエラーになります。

以下のコードは、J4,K4の数式を取得して
ConvertFormulaで変換したものをメッセージボックスに
表示します。

Sub MACRO()
Dim myF1 As String
Dim myF2 As String
myF1 = Cells(4, "j").Formula
myF2 = Cells(4, "k").Formula

MsgBox _
Application.ConvertFormula(myF1, xlA1, xlA1, xlRelative)
'↑変換元の数式をmyF1からmyF2に変更するとエラーになります。
End Sub
（カイル） 2016/09/04(日) 10:16

↓のマクロ2は、数式を変換した結果がエラー値なのかを
表示します。

Sub マクロ2()
Dim myF1 As String
Dim myF2 As String
Dim flg1, flg2
myF1 = Cells(4, "j").Formula
myF2 = Cells(4, "k").Formula

flg1 = _
Application.ConvertFormula(myF1, xlA1, xlA1, xlRelative)
flg2 = _
Application.ConvertFormula(myF2, xlA1, xlA1, xlRelative)

MsgBox IsError(flg1) & vbCrLf & IsError(flg2)

End Sub

（カイル） 2016/09/04(日) 12:48

コメント返信：

[ 一覧(最新更新順) ]

[[20160902164225]] 『相対参照に変換』（くろ） ページの最後に飛ぶ

[[20160902164225]]　『相対参照に変換』（くろ）　ページの最後に飛ぶ