可視セルを関数に組み込むマクロ

質問

Z列に関数を入力するマクロを組みたいです。

最初に、A-D列のみ表示し、E-Y列はグループ化して非表示にしており、
A、B、C、D、Z　列のみ表示されています。

セルZ1に、A1、B1を引数とする関数を入力します。

次に、グループ化を解除し、E-H列のみ表示し、A-D列、I-Y列をグループ化して非表示にし、
E、F、G、H、Z列のみ表示します。
マクロを実行すると、セルZ１に、E１、F１を引数とする関数が入力されるようにしたいです。

グループ化は4列ずつ行い、画面に表示されるのは常に５列のみで、
Z列には、「表示されている第1列と第2列」のセルを引数とする関数を入力するように
したいのです。

Z列から見て、表示されている列が変わっていくため、RC形式のセルの単純な相対表示では
あらわせません。
よろしくお願いいたします。

回答No.6

mitarashi

#5です。表現が誤解を招くかなと思いましたので、念のために注釈させていただきます。
Const formulaTemplate As String = "=(RC[○])^2 + 3*(RC[□]) + 4"
の○、□は後でプログラムで数値に置換しますので、ここは○、□のままにしておいて下さい。
たとえば、単純な足し算なら
"=RC[○] + RC[□]"
といった様に式をアレンジして下さい。
"=RC[" & val1 & "] + RC[" & val2 & "]"
などとやるより、分かり易くて良いかなと思っています。

回答No.5

mitarashi

他の質問への回答で、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

tsubuyuki

質問文を拝読し、受け取った印象で考えてみました。

つまり、
・Ａ：Ｙの列を全部非表示
・選択した列だけ再表示
・選択した列の、先頭列を取る
・Ｚ１セルに「=SUM(先頭セル：次のセル)」の式を入力
の順に処理していけば良さそうですね。

ただし、
> 次に、グループ化を解除し、E-H列のみ表示し
の記載しかないため、この「表示する列」を切り替えるタイミングが不明瞭です。
なので、図のように「表示する列を選択するセル（入力規則使用）」を作りました。
入力規則は、リスト「A:D,E:H,I:L,M:P,Q:T,U:X」としています。
更に、切り替えるマクロを実行させるボタンを勝手に作りました。
このボタンに以下のマクロを登録しておきます。
４列ずつ・・とする場合に、Ｙ列は必ずあぶれるなぁ、とか少々思いつつ。

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)　　　　　' 選択された文字列の「１文字目」を格納
Set SRange = Range(TCol & "1")　　' 格納された「１文字目」が示す列の１行目のセルを格納

Columns("A:Y").EntireColumn.Hidden = True　' Ａ：Ｙ列を非表示
Range(TRange).EntireColumn.Hidden = False　' 格納された文字列が示す列範囲を再表示
Range("Z1") = "=SUM(" & SRange.Address & ":" & SRange.Offset(0, 1).Address & ")"
' ↑Ｚ１セルに
' 　　　=SUM(SRangeに格納されたセルの番地：その一つ右のセルの番地)
' となるように文字列を結合させて、式として代入

SRange.Select
Set SRange = Nothing
End Sub

質問文の範囲、
> 画面に表示されるのは常に５列のみで、
> Z列には、「表示されている第1列と第2列」のセルを引数とする関数を入力するようにしたい
だけで処理が終わるのであれば、こんな感じでいけます。

ただし、このコードは１行目しか対象として考えていません。
まぁ、他の行も対象にしたいよ、だとか、行も範囲で指定したいよ、だとかは
少々いじればそんなに難しくはありません。

ところで、
> RC形式のセルの単純な相対表示ではあらわせません。
いくらでも出来ますが、そう言い切られてしまったので、
使わない方法だけを紹介しました。
使わなくても出来ましたという事で、悪しからずご了承くださいませ。

回答No.3

basic_gg

使用条件にあわせて変形してください。
・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

mt2008

作業用シートを使います。
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

web2525

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

単純に頭からセルが非表示か表示かを調べていき、表示状態の列を見つけたらループを抜けるだけです

お礼

さっそくのご回答、ありがとうございます！

なるほど、基本的な反復形式でいけるんですね。
始めたばかりで、構文は知ってはいましたが、実用にすぐに「これは反復でいける」となかなか思いつきません。

ありがとうございました。