ken********

2022/6/23 19:01

55回答

Excel VBAで、あるセル範囲に一気に数式を入れる方法がわかりません。 詳しい方、どうぞ教えてください。 やりたいことは↓です。

Excel VBAで、あるセル範囲に一気に数式を入れる方法がわかりません。 詳しい方、どうぞ教えてください。 やりたいことは↓です。 Sheet1のRange(Cells(2 , Ca) , Cells(2 + N , Ca))に入力されている値を 小数点以下3桁に丸めて Sheet2のRange(Cells(2 , Cb) , Cells(2 + N , Cb))に表示させたいです。 Ca、Cb、Nは変数です。 X = Sheets("Sheet1").Cells(2 + N - 1, Ca).Value Sheets("Sheet2").Cells(2 , Cb).Value = WorksheetFunction.Round(X, 3) として、For～Nextでセル1つずつ設定していたのですが、 Nが1から150、CaとCbが1から100まで変化するので、 処理に時間がかかって困っています。 VBAを使わずに関数で処理した場合のイメージは画像参照ください。 なお、Sheet1の数値は、行方向には連続しますが、 列方向は飛び飛びなので、一気に選択することができません。 よろしくお願いいたします。

画像

Visual Basic | Excel・218閲覧

ベストアンサー

0
tak********

2022/6/23 20:41

提示のシートレイアウトであるのなら、以下のようなものでしょうか？ ・sheet1で実施される前提です。 ・セル値のクリアや値設定が必要ないのであれば、コメントアウトまたは削除してください。 ・「Private Sub CommandButton1_Click()」はACTIVEXのボタンの場合です。実施環境にあわせてください。 ・データ量の影響でレスポンスが遅かったら、ごめんなさい。 Private Sub CommandButton1_Click() Dim ws As Worksheet, iRowsCount As Long ''シート変数を設定する。 Set ws = Worksheets("sheet2") ''以前のセル値をクリアする。 ※必要？ ws.Range("F2:J2").Resize(Rows.CountLarge - 1).Clear ''２行目（最初のデータ行）に式を設定する。 ws.Range("F2").Formula = "=ROUND(sheet1!C2,3)" ws.Range("G2").Formula = "=ROUND(sheet1!F2,3)" ws.Range("H2").Formula = "=ROUND(sheet1!E2,3)" ws.Range("I2").Formula = "=ROUND(sheet1!K2,3)" ws.Range("J2").Formula = "=ROUND(sheet1!H2,3)" ''セル値を丸めてコピーする。 iRowsCount = Range("C2", Cells(Rows.CountLarge, "C").End(xlUp)).Rows.CountLarge ''３行目以降に式をコピーする。 If iRowsCount > 2 Then ws.Range("F2:J2").Copy ws.Range("F3").Resize(iRowsCount - 1) End If ''２行以降のセル値を値にする。※必要？ With ws.Range("F2:J2").Resize(iRowsCount) .Value = .Value End With ''シート変数を開放する。 Set ws = Nothing End Sub

ken********

質問者2022/6/24 9:48

ご教示ありがとうございます。 "=ROUND(sheet1!C2,3)"のC2のC(列)も変数になるため、 R1C1で記述しようと画策していますが上手くいきません…。 お手数ですが、良い方法をご存じでしたら教えてくださいm(__)m

ThanksImg質問者からのお礼コメント

皆さんの良いとこ取りをさせていただいたのですが、 最終形がtak********さんのものに1番近かったので ベストアンサーとさせていただきました。 皆様、ありがとうございました。

お礼日時：6/24 15:45

その他の回答（4件）

0
bbw********

2022/6/24 14:33

あなたのしたいことはセルに数式をいれたいのですよね？ あなたのプログラムは数式でなく値を入れているようですが、それはあなたのしたいことなのですか？

ken********

質問者2022/6/24 15:25

書き方が下手で申し訳ありません。 Sheet2には小数点以下3桁に丸めた後の「値」を入れたいです。 画像はあくまでも > VBAを使わずに関数で処理した場合のイメージ でした。

0
1150246010

2022/6/24 14:03

添付図のような結果でどうでしょうか。 マクロの実行は、シート２で実行ください。 Sub 数値の丸め転記() With Worksheets("Sheet1") Dim Ca As Integer Dim Cb As Integer Dim n As Integer For Ca = 3 To .Cells(1, Columns.Count).End(xlToLeft).Column If .Cells(1, Ca).Value <> "" Then For Cb = 5 To Cells(1, Columns.Count).End(xlToLeft).Column If .Cells(1, Ca).Value = Cells(1, Cb).Value Then For n = 2 To .Cells(Rows.Count, Ca).End(xlUp).Row Cells(n, Cb).Value = WorksheetFunction.Round(.Cells(n, Ca).Value, 3) Next End If Next End If Next End With End Sub

画像
0
PSX

2022/6/24 4:21

Sub a() 'Sheet1,Sheet2のどちらのシートからでも実行可 't1,t2,t3・・・はどちらのシートも１行目 'シート２にあるt1,t2,t3・・・はシート１にもすべて存在していることが前提です Dim c As Range Dim myCol As Long Dim eRow As Long Dim ws1 As Worksheet Dim ws2 As Worksheet Set ws1 = Worksheets("Sheet1") Set ws2 = Worksheets("Sheet2") For Each c In ws2.Rows(1).SpecialCells(xlCellTypeConstants, xlTextValues) On Error Resume Next myCol = WorksheetFunction.Match(c.Value, ws1.Rows(1), 0) On Error GoTo 0 If myCol <> 0 Then eRow = ws1.Cells(Rows.Count, myCol).End(xlUp).Row With c.Offset(1, 0).Resize(eRow - 1, 1) .FormulaR1C1 = "=ROUND(Sheet1!RC[" & myCol - c.Column & "],3)" .Value = .Value End With End If Next c End Sub

ken********

質問者2022/6/24 8:55

ご教示ありがとうございます。 With関数の .FormulaR1C1 = "=ROUND(Sheet1!RC[" & myCol - c.Column & "],3)" のところでデバッグが発生してしまいました。 (値の更新を求められ、ファイルを開くダイアログが立ち上がる。) 実際はSheet1の名前はSheet1ではなく、 Worksheets("グラフ用データ")なので ws1にすれば良いのか？と変えてみましたが、改善されませんでした…。

0
1150992942

2022/6/23 19:43

ExcelのVBAはシートへのアクセス処理がとても重いので、できる限り一括にまとめると早くなります。 Variant型変数にSheet1のセル範囲のデータを丸々格納して、 Variant型変数をFor文で回してデータ編集（桁丸め処理）をして、 Variant型変数をSheet2のセル範囲に貼り付けます。