webdevqa.jp.net

数式をセルに追加するVBA

3つのセルにヘッダーテキストを追加し、最後の行まで数式を入力するVBAを作成しようとしています。私は以下を書きました、それはヘッダーを問題なく書きます、しかしそれが私の最初の.Formulaに達するとき、それは投げます

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

このマクロを正常に実行するには、何を変更する必要がありますか? (数式はセル内の数式から直接取得されたため、少なくとも「フロントエンド」では有効な数式であることがわかります)

Function Gre()
    Range("E2").Select
    ActiveCell.FormulaR1C1 = "Under"
    Range("F2").Select
    ActiveCell.FormulaR1C1 = "Over"
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "Result"

    With Range("E2:E" & Cells(Rows.Count, "C").End(xlUp).Row)
        .Formula = "=IF(C2<B2,B2-C2,"")"
    End With
    With Range("F2:F" & Cells(Rows.Count, "C").End(xlUp).Row)
        .Formula = "=IF(C2>B2,C2-B2,0)"
    End With
    With Range("G2:G" & Cells(Rows.Count, "C").End(xlUp).Row)
        .Formula = "=IF(F2>0,'Issue',"")"
    End With
End Function

問題は、数式で引用符をエスケープしている可能性があります。

必要なものは次のとおりです。

.Formula = "=IF(C2>B2,B2-C2,"""")"

たとえば、最初のもののために。他の引用符も2倍にする必要があります。

補足として、作業中のシートを次のように指定することもお勧めします。

 Dim ws as worksheet
 Set ws = Sheets("mySheet")
 ws.Range("E2").FormulaR1C1 = "Under"

等.

これを行わないと、コードの実行中にエラーが発生する場合があります。

4
OpiesDad
  1. OpiesDadが提案しているように、あいまいさを最小限に抑えるために、ActiveCellなどは避けてください。
  2. Selectを使用すると、セルに直接割り当てる場合に比べてパフォーマンスが大幅に低下します。
  3. 引用符を2倍にして、VBA内のExcel数式の引用符をエスケープする必要があると確信しているので、通常の空の文字列は_""""_になります。また、数式には一重引用符で囲まれたIssueがありますが、これはExcelでエラーになると確信しています。エスケープされた二重引用符も含める必要があります。
  4. Range("E2:E" & Cells(Rows.Count, "C").End(xlUp).Row)が実際に何をするのか理解するのに苦労していますが、シートの最後に使用された行に対して_E2_を選択したいようです。 _Rows.Count_を避けたり、一般的にシートの行を参照したりしないでください。行10 ^ 31に移動します。 _Worksheet.UsedRange_を使用して、コンテンツを含む最初の行と列から、コンテンツを含む最後の行と列までの範囲を取得します。これには空の文字列も含まれ、少し注意が必要な場合もありますが、通常は数千の余分な行を処理するよりも優れています。

また、

  1. ステートメントを1つだけ囲む場合は、問題は発生しませんが、Withを使用する必要はありません。

  2. 理由がない限り、_Range.Formula_と_Range.FormulaR1C1_の使用を混在させません。

    _Function Gre() 
    
        Dim ws as Worksheet
        Set ws = ActiveSheet
    
        Dim used as Range
        Set used = ws.UsedRange
    
        Dim lastRow as Integer
        lastRow = used.Row + used.Rows.Count - 1
    
        ws.Range("E2").Formula = "Under"
        ws.Range("F2").Formula = "Over"
        ws.Range("G2").Formula = "Result"
    
        ws.Range("E2:E" & lastRow).Formula = "IF(C2<B2, C2-B2, """")"
        ws.Range("F2:F" & lastRow).Formula = "IF(C2<B2, C2-B2, 0)"
        ws.Range("G2:G" & lastRow).Formula = "IF(F2>0, ""Issue"", """")"
    
    End Function
    _
2
JamesFaix
  1. 最初の問題はセルの選択です。これには、マクロがセルを選択してから、セルアドレスを決定する必要があります。実際にセルを選択する必要がある場合は、_Application.ScreenUpdating = False_を使用してください。その場合、マクロはセルのカーソル選択を表示する必要はありません。選択を削除し、以下のように範囲を数式割り当てコード行に組み込むと、ある程度の速度/効率が得られます。

    Range("E2").FormulaR1C1 = "Under"

  2. Range("E2:E" & Cells(Rows.Count, "C").End(xlUp).Row)は、空白の列(行1048576)の最後のセルを選択し、CtrlキーとUpキーを使用して、最も低い/最後に使用されたセルを決定するコードバージョンです。これにより、列が空白になるたびに行数が1になります。あなたは最後の行を探しているので。上からカウントダウンする方が速い場合があります。このための私のお気に入りの方法はループです。最後の行を探しながら、ループ内で変数をインクリメントします。次に、ボトムアップ戦略の代わりに変数を使用できます。

    _t = 0
    Do Until Range("C2").Offset(t, 0).Value = ""
    t = t + 1
    Loop
    
    With Range("E2:E" & t)
    .Formula = "=IF(C2<B2,B2-C2,"""")"
    End With`
    _
  3. TSQLと同様に、引用符には独自の引用符が必要です。

    _.Formula = "=IF(C2<B2,B2-C2,"""")"
    _
  4. この場合、範囲塗りつぶしVBA関数を使用して、Excelの数式参照の相対性を考慮して、下からすべてのセルに共通の数式を入力できます。以下のコードは、ループカウンターから取得した範囲から始まります。次に、Excelの合計行からカウンター行に対応する行を引いたものに等しい変数を設定します。次に、必要な行で元の領域のサイズを変更し、FillDown関数を使用して最初の数式をコピーします。
  5. 結果のコードは次のとおりです。これにより、Excelの最後の行から始まる範囲が埋められます。

    _ Sub Gre()
     Range("E2").FormulaR1C1 = "Under"
     Range("F2").FormulaR1C1 = "Over"
     Range("G2").FormulaR1C1 = "Result"
    
     Do While Range("e2").Offset(t, 0).Value <> ""
     t = t + 1
     Loop
     Range("E2").Offset(t, 0).Formula = "=IF(C2<B2,B2-C2,"""")"
     r1 = Range("e2").EntireColumn.Rows.Count
     r2 = Range("E2").Offset(t, 0).Row
     Range("E2").Offset(t, 0).Resize(r1 - r2, 1).FillDown
     Range("F2").Offset(t, 0).Formula = "=IF(C2>B2,C2-B2,0)"
     Range("F2").Offset(t, 0).Resize(r1 - r2, 1).FillDown
     Range("G2").Offset(t, 0).Formula = "=IF(F2>0,""Issue"","""")"
     Range("G2").Offset(t, 0).Resize(r1 - r2, 1).FillDown
     End Sub
    _
0
Geoffrey Fuller