Z列に関数を入力するマクロを組みたいです。
最初に、A-D列のみ表示し、E-Y列はグループ化して非表示にしており、
A、B、C、D、Z 列のみ表示されています。
セルZ1に、A1、B1を引数とする関数を入力します。
次に、グループ化を解除し、E-H列のみ表示し、A-D列、I-Y列をグループ化して非表示にし、
E、F、G、H、Z列のみ表示します。
マクロを実行すると、セルZ1に、E1、F1を引数とする関数が入力されるようにしたいです。
グループ化は4列ずつ行い、画面に表示されるのは常に5列のみで、
Z列には、「表示されている第1列と第2列」のセルを引数とする関数を入力するように
したいのです。
Z列から見て、表示されている列が変わっていくため、RC形式のセルの単純な相対表示では
あらわせません。
よろしくお願いいたします。
A 回答 (6件)
- 最新から表示
- 回答順に表示
No.6
- 回答日時:
#5です。
表現が誤解を招くかなと思いましたので、念のために注釈させていただきます。Const formulaTemplate As String = "=(RC[○])^2 + 3*(RC[□]) + 4"
の○、□は後でプログラムで数値に置換しますので、ここは○、□のままにしておいて下さい。
たとえば、単純な足し算なら
"=RC[○] + RC[□]"
といった様に式をアレンジして下さい。
"=RC[" & val1 & "] + RC[" & val2 & "]"
などとやるより、分かり易くて良いかなと思っています。
No.5
- 回答日時:
他の質問への回答で、R1C1形式の操作について試していたので、その延長線上で考えてみました。
列の表示/非表示は手動で行うものとします。マクロを実行すると、A列からスキャンして、最初の表示列と、二番目の表示列を引数とした数式を、Z列に入力します。数式はコード中の定数宣言のところで、ひな形を組み立てておきます。こんな理解でよろしいのでしょうか?なお、一番目と二番目の表示列が非隣接でも動くコードになっています。
Sub test()
Dim targetRange As Range, myColumn As Range
Dim firstColumn As Range, secondColumn As Range
Dim setFormulaColumn As Range
Dim myFormulaR1C1 As String
'ここにお好きな式を入れる。○の部分が最初の表示列、□の部分が2番目の表示列
Const formulaTemplate As String = "=(RC[○])^2 + 3*(RC[□]) + 4"
With Sheets(1)
'A~Y列の同じ行までびっちりデータが詰まっているとします
Set targetRange = Range(.Range("A1"), .Range("Y" & .Rows.Count).End(xlUp))
End With
Set setFormulaColumn = targetRange.Offset(0, targetRange.Columns.Count).Resize(, 1)
For Each myColumn In targetRange.Columns
If myColumn.Hidden = False Then
If firstColumn Is Nothing Then
Set firstColumn = myColumn
Else
Set secondColumn = myColumn
Exit For
End If
End If
Next myColumn
myFormulaR1C1 = Replace(formulaTemplate, "○", CStr(firstColumn.Column - setFormulaColumn.Column))
myFormulaR1C1 = Replace(myFormulaR1C1, "□", CStr(secondColumn.Column - setFormulaColumn.Column))
setFormulaColumn.FormulaR1C1 = myFormulaR1C1
End Sub
No.4
- 回答日時:
質問文を拝読し、受け取った印象で考えてみました。
つまり、
・A:Yの列を全部非表示
・選択した列だけ再表示
・選択した列の、先頭列を取る
・Z1セルに「=SUM(先頭セル:次のセル)」の式を入力
の順に処理していけば良さそうですね。
ただし、
> 次に、グループ化を解除し、E-H列のみ表示し
の記載しかないため、この「表示する列」を切り替えるタイミングが不明瞭です。
なので、図のように「表示する列を選択するセル(入力規則使用)」を作りました。
入力規則は、リスト「A:D,E:H,I:L,M:P,Q:T,U:X」としています。
更に、切り替えるマクロを実行させるボタンを勝手に作りました。
このボタンに以下のマクロを登録しておきます。
4列ずつ・・とする場合に、Y列は必ずあぶれるなぁ、とか少々思いつつ。
Sub TEST()
Dim TRange As String, TCol As String
Dim SRange As Range
Dim SCal As String
TRange = Range("Z4").Value ' 選択された「文字列」を格納
TCol = Mid(TRange, 1, 1) ' 選択された文字列の「1文字目」を格納
Set SRange = Range(TCol & "1") ' 格納された「1文字目」が示す列の1行目のセルを格納
Columns("A:Y").EntireColumn.Hidden = True ' A:Y列を非表示
Range(TRange).EntireColumn.Hidden = False ' 格納された文字列が示す列範囲を再表示
Range("Z1") = "=SUM(" & SRange.Address & ":" & SRange.Offset(0, 1).Address & ")"
' ↑Z1セルに
' =SUM(SRangeに格納されたセルの番地:その一つ右のセルの番地)
' となるように文字列を結合させて、式として代入
SRange.Select
Set SRange = Nothing
End Sub
質問文の範囲、
> 画面に表示されるのは常に5列のみで、
> Z列には、「表示されている第1列と第2列」のセルを引数とする関数を入力するようにしたい
だけで処理が終わるのであれば、こんな感じでいけます。
ただし、このコードは1行目しか対象として考えていません。
まぁ、他の行も対象にしたいよ、だとか、行も範囲で指定したいよ、だとかは
少々いじればそんなに難しくはありません。
ところで、
> RC形式のセルの単純な相対表示ではあらわせません。
いくらでも出来ますが、そう言い切られてしまったので、
使わない方法だけを紹介しました。
使わなくても出来ましたという事で、悪しからずご了承くださいませ。
No.3
- 回答日時:
使用条件にあわせて変形してください。
・A-D列とE-H列のどちらかが必ず非表示になっているなら、
B1 = ActiveSheet.Columns("B").Hidden * 1 でB列の状態を確認し、
もし表示なら Z1にA1、B1を引数とする関数(T1)をいれ
非表示なら Z1にE1、F1を引数とする関数(T2)をいれればいい。
if B1=0 then Sheets(1).Cells(1, 26).Formula = T1
else Sheets(2).Cells(1, 26).Formula = T2
・関数はできているのだろうから、
自動記録で、できている式を貼り付けると
ActiveCell.FormulaR1C1 = "=IF(RC[-9]=RC[-11],9,IF(RC[-9]=RC[-10],10,RC[-9]))"
のように表示された式を
T1="=IF(C2=A2,9,IF(C2=B2,10,C2))"
のように変形する。
(セル参照を見慣れた形に変形しただけ)
・オートフィルは
Range("Z1").Select
Selection.AutoFill Destination:=Range("Z2:Z29"), Type:=xlFillDefault
のようになる。
No.2
- 回答日時:
作業用シートを使います。
Sheet1を元のシート、Sheet2を作業シートとします。
Sheet1のZ1には↓の様に作業用シートのA列とB列の値を使用する式を入れておきます。
=MAX(Sheet2!A1+Sheet2!B1) *あくまでも例です
で、列の表示状態を変更したら↓のマクロを起動します。
Sub Sample()
Sheets("Sheet1").Columns("A:Y").SpecialCells(xlCellTypeVisible).Copy Sheets("Sheet2").Range("A1")
End Sub
これで作業シートのA列B列には元シートで表示列となっている1番目と2番目の値が入ります。
Z列の式はそのまま変更する必要ありません。
No.1
- 回答日時:
Sub Macro1()
For i = 1 To 22
If Not (Cells(1, i).EntireColumn.Hidden) Then
Exit For
End If
Next
Range("Z1").Value = Cells(1, i) + Cells(1, i + 1)'ここに実行したい式を
End Sub
単純に頭からセルが非表示か表示かを調べていき、表示状態の列を見つけたらループを抜けるだけです
さっそくのご回答、ありがとうございます!
なるほど、基本的な反復形式でいけるんですね。
始めたばかりで、構文は知ってはいましたが、実用にすぐに「これは反復でいける」となかなか思いつきません。
ありがとうございました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) マクロだと数式が表示される 2 2022/09/10 14:48
- Excel(エクセル) Excel2019、2021の日付、曜日の表示について 2 2022/11/29 15:01
- Excel(エクセル) エクセルでIF関数中にIFERROR関数を使いたいのですが???? 5 2022/04/08 13:24
- Excel(エクセル) Excelの関数について教えてください。 5 2023/07/28 11:27
- Excel(エクセル) 【再度】Excelの関数について教えてください。 4 2023/07/28 13:06
- Excel(エクセル) <スプレッドシート>IF関数の複数条件について 5 2022/10/27 14:38
- Excel(エクセル) Excelのテーブルについて 6 2023/07/07 08:37
- Excel(エクセル) 【Excel質問】 「本日の日付」から指定条件を満たす営業日経過後の日数を表示させる関数式 3 2022/06/06 23:28
- Excel(エクセル) エクセルで日付が入っているセルを一定の法則に従って違うセルに表示したい 2 2022/04/04 17:16
- Excel(エクセル) 画像のとおり先にB列に関数を入れ、 後からA列に任意の名前をA列セルに入れます。 後からA列に名前を 4 2022/05/08 02:44
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルで二つの数字の小さい...
-
エクセルで最初のスペースまで...
-
PowerPointで表の1つの列だけ...
-
2つのエクセルのデータを同じよ...
-
EXCELで 一桁の数値を二桁に
-
「B列が日曜の場合」C列に/...
-
エクセルで文字が混じった数字...
-
エクセルの項目軸を左寄せにしたい
-
Excelで半角の文字を含むセルを...
-
エクセル(勝手に太字になる)
-
VBAで文字列を数値に変換したい
-
エクセル 文字数 多い順 並...
-
エクセルの列を範囲選択しての...
-
50人を数回、グループ分けする...
-
エクセルで、列の空欄に隣の列...
-
オートフィルターをかけ、#N/A...
-
Excel、市から登録している住所...
-
【VBA】特定列に文字が入ってい...
-
エクセルのセル内の文字の一部...
-
エクセルの並び変えで、空白セ...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
PowerPointで表の1つの列だけ...
-
エクセルで二つの数字の小さい...
-
エクセル 同じ値を探して隣の...
-
2つのエクセルのデータを同じよ...
-
エクセルで最初のスペースまで...
-
エクセル 文字数 多い順 並...
-
エクセルの項目軸を左寄せにしたい
-
エクセル(勝手に太字になる)
-
EXCELで 一桁の数値を二桁に
-
Excel、市から登録している住所...
-
VBAで文字列を数値に変換したい
-
「B列が日曜の場合」C列に/...
-
Excelで半角の文字を含むセルを...
-
EXCEl VBA
-
エクセルの並び変えで、空白セ...
-
VBAで、サブフォルダにある複数...
-
エクセルで文字が混じった数字...
-
エクセルの表から正の数、負の...
-
エクセルの列を範囲選択しての...
-
エクセル 時間帯の重複の有無
おすすめ情報