[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『マクロを使用して関数を張り付けたい』（殿様ぺんぎん）
マクロの関数で計算式を貼り付けを行いたい
貼付場所シートにデータを取得して
提出場所シートに関数を張り付けて表示したい
Range("D3").Value = "=IF(B3="""","""",IF(貼付場所!E44=""-"",D2,貼付場所!E44))"
から
Range("D165").Value = "=IF(B165="""","""",IF(貼付場所!E530=""-"",D164,貼付場所!E530))"
エクセル上に関数で上記を張り付けたいのですが
E列は、行が増える毎にE44・E47・E50と３づつ増えていきます。
最初のD列は、D3・D4・D5
最後のD列は、D2・D3・D4
という風に１行ごとに１行増える形になります。
「貼付場所」シートにデータを張り付けて最大でE列に41行目から532行目までデータが張り付きます。
41行目の上にもデータは張り付いてますが、明細は41行目から始まります。
しかも、データが532行目までない場合でも
D3〜D165までを張り付ける事になるのですごく処理が重くなります。
B列からW列までこんな風な関数が２１種類ぐらいある為
全てを作成すると165×21で3500ぐらいの関数を作成しないとダメなので…
データが532行目までない場合は、データのある最終行までに張り付ける様にしたい
これをマクロの式で簡単にする方法はありますか？
Range("D3").Value = "=IF(B3="""","""",IF(貼付場所!E44=""-"",D2,貼付場所!E44))"
を１個用意して、そこに関数でどうにか162まで繰り返すみたいな．．．
どなたかご存じの方が居ましたら教えてくださいませ
計算式を繰り返す様なマクロを教えてください
＜ 使用 Excel：Excel2019、使用 OS：Windows10 ＞
ちょっと何がしたいのか詳細が読み取れないのですが、
式を手入力する作業を、マクロの記録してみてください。 セルの FormulaR1C1 プロパティを使ってることがわかると思います。 R1C1形式を調べてください。 （´・ω・`） 2021/05/26(水) 16:38
Sub FillFormulae()
Dim RW As Long, TgtRW, lastRW, Delta As Long
Delta = 3
Range("D3").Value = "Dummy"
lastRW = Columns("D").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Range("D3:D165").ClearContents
For RW = 3 To lastRW
TgtRW = 44 + (RW - 3) * Delta
Range("D" & RW).FormulaLocal = "=IF(B" & RW & "="""","""",IF(貼付場所!E" & TgtRW & "=""-"",D" & RW - 1 & ",貼付場所!E" & TgtRW & "))"
Next RW
End Sub
（半平太） 2021/05/26(水) 16:55
貼付場所シートのＤ列の４１行目から５３０行目までデータが入ります。
Ｄ列には４１行目から上にもデータがありますが
明細は４１行目から始まります。
最高で５３０行目というだけで１００行目ぐらいで終わる場合もあります。
そのデータを加味して作成して頂いたマクロを実行した場合に
上記の教えて頂いた関数を張付場所シートとは別のシートのＤ列に張り付けて良く事は可能でしょうか？
ちょうど、Ｄ列がかぶってしまって申し訳御座いません。
（殿様ぺんぎん） 2021/05/27(木) 11:27
ありゃ、D列を混同してしまった。
ところで「明細は４１行目から始まります」との事ですが、
このサンプルからすると、44行目からが意味のあるものですよね？
↓
＞Range("D3").Value = "=IF(B3="""","""",IF(貼付場所!E44=""-"",D2,貼付場所!E44))"
’修正後
Sub FillFormulae()
Dim RW, TgtRW, lastRwSrc, numDATA, Delta, FormlaAry()
Delta = 3
lastRwSrc = Sheets("貼付場所").Columns("D").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
lastRwSrc = Application.Max(lastRwSrc, 41)
numDATA = lastRwSrc - 40
With Sheets("提出場所")
.Range("D3:D165").ClearContents
ReDim FormlaAry(1 To numDATA, 1 To 1)
For RW = 3 To numDATA + 2
TgtRW = 44 + (RW - 3) * Delta
FormlaAry(RW - 2, 1) = "=IF(B" & RW & "="""","""",IF(貼付場所!E" & _
TgtRW & "=""-"",D" & RW - 1 & ",貼付場所!E" & TgtRW & "))"
Next RW
'数式を一括書き出し
.Range("D3").Resize(numDATA).FormulaLocal = FormlaAry
End With
End Sub
（半平太） 2021/05/27(木) 13:03
これらすべてを
Range("D3").Value = "=IF(B3="""","""",IF(貼付場所!E44=""-"",D2,貼付場所!E44))"
から
Range("D165").Value = "=IF(B165="""","""",IF(貼付場所!E530=""-"",D164,貼付場所!E530))"
って感じで作成すると地獄なので…
上の式の様に簡単にできれば楽だな〜って思って
（殿様ぺんぎん） 2021/05/27(木) 13:37
上記のマクロを使用すると別にそこまでの支障はないのですが
最終が
=IF(B493="","",IF(貼付場所!E1514="-",D492,貼付場所!E1514))
１５１４行まで見てる感じになるのですが（何度試しても同じ場所）
これは何で設定しているのでしょうか？
で試しに、別のエクセルで貼付場所のD41からD264までにデータを付けて（1・2・3的な）
実行しました。
Ｄ3〜Ｄ226まで張り付き
=IF(B226="","",IF(貼付場所!E713="-",D225,貼付場所!E713))
がラストでした。
これはどういう範囲の選択になっているのでしょうか？
（殿様ぺんぎん） 2021/05/27(木) 13:50
＞これはどういう範囲の選択になっているのでしょうか？
D列のデータ行数で数式の入力行数も決めたので、およそ３倍も数式が入ってしまいました。m(__)m
D列は44スタートで3行ごとだったので、こうなるべきでした。
' numDATA = lastRwSrc - 40 ' ↓ numDATA = Int((lastRwSrc - 44) / Delta) + 1 ’(正)
（半平太） 2021/05/27(木) 14:38
ほとんどわからない事だらけですが、今後に生かせる様にしたいので…
Dim RW, TgtRW, lastRwSrc, numDATA, Delta, FormlaAr これは、別にＡでもＢでも自分が分かれば何でも大丈夫ですよね？
Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious).Row
早速これが何をしているのか分かりません…
ってかこっからずっと分かりません。
lastRwSrc = Application.Max(lastRwSrc, 41) MAXって書いてありますが、スタートの意味ですか？
numDATA = Int((lastRwSrc - 44) / Delta) + 1 スタート マイナス 44 割る ３ ＋１ですか？
こっからは予想すらできません…
ReDim FormlaAry(1 To numDATA, 1 To 1)
For RW = 3 To numDATA + 2
TgtRW = 44 + (RW - 3) * Delta
FormlaAry(RW - 2, 1) = "=IF(B" & RW & "="""","""",IF(貼付場所!E" & _
TgtRW & "=""-"",D" & RW - 1 & ",貼付場所!E" & TgtRW & "))"
Next RW
'数式を一括書き出し
.Range("D3").Resize(numDATA).FormulaLocal = FormlaAry
End With
助けてもらった上に質問攻めですみません…
（殿様ぺんぎん） 2021/05/27(木) 16:47
＞ Dim RW, TgtRW, lastRwSrc, numDATA, Delta, FormlaAr ＞これは、別にＡでもＢでも自分が分かれば何でも大丈夫ですよね？ OKです。データ型の指定をするのが正式ですが、全部バリアント型で差し支えないです。
＞Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious).Row D列中で有効なデータが入っているセルの中で最下段の行番号を取得しています。
＞lastRwSrc = Application.Max(lastRwSrc, 41) MAXって書いてありますが、スタートの意味ですか？ D列にデータがないと困るので、その場合は41行に仮決めするという意味です (まぁ、実際はあり得ない事態なんでしょうけどね）
＞numDATA = Int((lastRwSrc - 44) / Delta) + 1 スタート マイナス 44 割る ３ ＋１ですか？ その通りです。 数式を何行書き込めばいいか、その行数を計算している所です。
＞ReDim FormlaAry(1 To numDATA, 1 To 1) 1セルずつ数式を入れているとその都度再計算になると時間の無駄なので、 後で一気に入れる為、作った数式をため込んでおく入れ物です。
For RW = 3 To numDATA + 2 、 ’数式の数だけループさせる(スタートは3行目からとする）
TgtRW = 44 + (RW - 3) * Delta ’E列の行番号を計算しているところです。
＞FormlaAry(RW - 2, 1) = "=IF(B" & RW & "="""","""",IF(貼付場所!E" & _ ＞TgtRW & "=""-"",D" & RW - 1 & ",貼付場所!E" & TgtRW & "))" 数式を文字列で作っているところです。
Next RW ’RWがnumDATA+2を超えるまで、1ずつカウントアップして、繰り返す。
＞'数式を一括書き出し
＞ .Range("D3").Resize(numDATA).FormulaLocal = FormlaAry
配列中に溜めて置いた数式の集まりを、一気にセルに書き込んでいるところです。
（半平太） 2021/05/27(木) 17:06
＞一応、関数がこんだけあります。
簡単そうなのもありますが、全部となると結構大変ですよね。
マクロで、そのまんまの数式を再構築させて、行の数だけ作るのは大変なので、 コピーに耐えられる数式を各列1種類考案して、 それを下にコピーする方式にすれば少しは楽かも知れません。 (式は違うが、同じ結果を返してくる数式を考案する)
いずれにしても、各列たった１つの数式では、その下の数式がどう変化すべきなのか分かりません。 (規則性が読めないので）
提示された各数式の１行下の数式も貼り付けてください。
（半平太） 2021/05/27(木) 19:40
半平太様、最高です!
完全に自分の求めていた物です！
下手な説明から汲み取って頂き感謝です。
これで、今後楽な日々が訪れそうです。
本当にありがとうございました。
（殿様ぺんぎん） 2021/05/28(金) 11:19
あれ？
「こんだけ関数があります」の話は、もういいのですか？
全部解決して、楽しましょうよ。
（半平太） 2021/05/28(金) 13:29
１行目がタイトルで
２行目は、他の規則的なのと違う関数なので
３行目と４行目を明記します。
基本的に、４０行目から始まる数字からは、３行ごと増えていきます。
それ以外の場所は１行ごとに増えていきます…
Range("B3").Value = "=IF(貼付場所!J45="""","""",B2)"
Range("B4").Value = "=IF(貼付場所!J48="""","""",B3)"
Range("C3").Value = "=IF(B3="""","""","" ..."")"
Range("C4").Value = "=IF(B4="""","""","" ..."")"
Range("D3").Value = "=IF(B3="""","""",IF(貼付場所!E44=""-"",D2,貼付場所!E44))"
Range("D4").Value = "=IF(B4="""","""",IF(貼付場所!E47=""-"",D3,貼付場所!E47))"
Range("E3").Value = "=IF(B3="""","""",IF(貼付場所!E45=""-"",E2,貼付場所!E45))"
Range("E4").Value = "=IF(B4="""","""",IF(貼付場所!E48=""-"",E3,貼付場所!E48))"
Range("F3").Value = "=IF(貼付場所!J44="""","""",貼付場所!J44)"
Range("F4").Value = "=IF(貼付場所!J47="""","""",貼付場所!J47)"
Range("G3").Value = "=IF(B3="""","""",""..."")"
Range("G4").Value = "=IF(B4="""","""",""..."")"
Range("H3").Value = "=IF(貼付場所!K45="""","""",CONCATENATE(貼付場所!K45,""×"",貼付場所!L45,貼付場所!M45))"
Range("H4").Value = "=IF(貼付場所!K48="""","""",CONCATENATE(貼付場所!K48,""×"",貼付場所!L48,貼付場所!M48))"
Range("I3").Value = "=IF(貼付場所!N45="""","""",貼付場所!N45)"
Range("I4").Value = "=IF(貼付場所!N48="""","""",貼付場所!N48)"
Range("J3").Value = "=IF(貼付場所!Q44="""","""",貼付場所!Q44)"
Range("J4").Value = "=IF(貼付場所!Q47="""","""",貼付場所!Q47)"
Range("K3").Value = "=IF(貼付場所!R44="""","""",貼付場所!R44)"
Range("K4").Value = "=IF(貼付場所!R47="""","""",貼付場所!R47)"
Range("L3").Value = "=IF(貼付場所!U44="""","""",貼付場所!U44)"
Range("L4").Value = "=IF(貼付場所!U47="""","""",貼付場所!U47)"
Range("M3").Value = "=IF(貼付場所!V44="""","""",貼付場所!V44)"
Range("M4").Value = "=IF(貼付場所!V47="""","""",貼付場所!V47)"
Range("N3").Value = "=IF(貼付場所!W45="""","""",貼付場所!W45)"
Range("N4").Value = "=IF(貼付場所!W48="""","""",貼付場所!W48)"
Range("O3").Value = "=IF(貼付場所!Y45="""","""",貼付場所!Y45)"
Range("O4").Value = "=IF(貼付場所!Y48="""","""",貼付場所!Y48)"
Range("P3").Value = "=IF(B3="""","""",TRIM(CONCATENATE("" "",貼付場所!Y44)))"
Range("P4").Value = "=IF(B4="""","""",TRIM(CONCATENATE("" "",貼付場所!Y47)))"
Range("R3").Value = "=IF(貼付場所!T45="""","""",貼付場所!T45)"
Range("R4").Value = "=IF(貼付場所!T48="""","""",貼付場所!T48)"
Range("S3").Value = "=IF(B3="""","""",TRIM(CONCATENATE("" "",貼付場所!T44)))"
Range("S4").Value = "=IF(B4="""","""",TRIM(CONCATENATE("" "",貼付場所!T47)))"
Range("T3").Value = "=IF(MID(貼付場所!F45,1,5)=""-"","""",MID(貼付場所!F45,1,5))"
Range("T4").Value = "=IF(MID(貼付場所!F48,1,5)=""-"","""",MID(貼付場所!F48,1,5))"
Range("U3").Value = "=IF(T3="""","""",VLOOKUP(T3,Sheet1!B:I,2,0))"
Range("U4").Value = "=IF(T4="""","""",VLOOKUP(T4,Sheet1!B:I,2,0))"
Range("V3").Value = "=IF(MID(貼付場所!F45,6,2)=""-"","""",MID(貼付場所!F45,6,2))"
Range("V4").Value = "=IF(MID(貼付場所!F48,6,2)=""-"","""",MID(貼付場所!F48,6,2))"
Range("W3").Value = "=IF(VLOOKUP(CONCATENATE(T3,""-"",V3),Sheet1!A:I,5,0)="""","""",VLOOKUP(CONCATENATE(T3,""-"",V3),Sheet1!A:I,5,0))"
Range("W4").Value = "=IF(VLOOKUP(CONCATENATE(T4,""-"",V4),Sheet1!A:I,5,0)="""","""",VLOOKUP(CONCATENATE(T4,""-"",V4),Sheet1!A:I,5,0))"
（殿様ぺんぎん） 2021/05/28(金) 13:38
'標準モジュールに貼り付けて、FillMultを実行する
Private BaseFmlAry(1 To 21) Private TopAdr
Sub FillMult()
Dim RW, TgtRW, lastRwSrc, numDATA, Delta, FormlaAry()
Dim i As Long, TopCell As Range, WshToWrite As Worksheet
Dim adjust4445 As Long, adjust23 As Long, fmlaToApply
'基本データをセットする
storeBaseFormulae
Delta = 3
'D列最終行番号を求める
lastRwSrc = Sheets("貼付場所").Columns("D").Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
lastRwSrc = Application.Max(lastRwSrc, 41)
numDATA = Int((lastRwSrc - 44) / Delta) + 1 '数式の所要行数を求める
'21種の数式を順次3行目から埋める
Set WshToWrite = Sheets("提出場所")
For i = 1 To 21
Set TopCell = WshToWrite.Range(TopAdr(i))
TopCell.Resize(163).ClearContents
Select Case i
Case 1 To 4 '単純コピーでいい数式
TopCell.Resize(numDATA).FormulaLocal = BaseFmlAry(i)
Case Else
ReDim FormlaAry(1 To numDATA, 1 To 1)
For RW = 3 To numDATA + 2
adjust4445 = (RW - 3) * Delta
adjust23 = RW - 3
fmlaToApply = Replace(BaseFmlAry(i), "♪2", (2 + adjust23))
fmlaToApply = Replace(fmlaToApply, "♪3", (3 + adjust23))
fmlaToApply = Replace(fmlaToApply, "♪44", (44 + adjust4445))
fmlaToApply = Replace(fmlaToApply, "♪45", (45 + adjust4445))
FormlaAry(RW - 2, 1) = fmlaToApply
Next RW
TopCell.Resize(numDATA).FormulaLocal = FormlaAry
End Select
Next i
End Sub
Private Sub storeBaseFormulae()
TopAdr = Split("0,C3,G3,U3,W3,B3,D3,E3,F3,H3,I3,J3,K3,L3,M3,N3,O3,P3,R3,S3,T3,V3", ",")
'単純コピー4列
BaseFmlAry(1) = "=IF(B3="""","""","" ..."")"
BaseFmlAry(2) = "=IF(B3="""","""",""..."")"
BaseFmlAry(3) = "=IF(T3="""","""",VLOOKUP(T3,Sheet1!B:I,2,0))"
BaseFmlAry(4) = "=IF(VLOOKUP(CONCATENATE(T3,""-"",V3),Sheet1!A:I,5,0)="""","""",VLOOKUP(CONCATENATE(T3,""-"",V3),Sheet1!A:I,5,0))"
'飛ばし調整17列
BaseFmlAry(5) = "=IF(貼付場所!J♪45="""","""",B♪2)"
BaseFmlAry(6) = "=IF(B♪3="""","""",IF(貼付場所!E♪44=""-"",D♪2,貼付場所!E♪44))"
BaseFmlAry(7) = "=IF(B♪3="""","""",IF(貼付場所!E♪45=""-"",E♪2,貼付場所!E♪45))"
BaseFmlAry(8) = "=IF(貼付場所!J♪44="""","""",貼付場所!J♪44)"
BaseFmlAry(9) = "=IF(貼付場所!K♪45="""","""",CONCATENATE(貼付場所!K♪45,""×"",貼付場所!L♪45,貼付場所!M♪45))"
BaseFmlAry(10) = "=IF(貼付場所!N♪45="""","""",貼付場所!N♪45)"
BaseFmlAry(11) = "=IF(貼付場所!Q♪44="""","""",貼付場所!Q♪44)"
BaseFmlAry(12) = "=IF(貼付場所!R♪44="""","""",貼付場所!R♪44)"
BaseFmlAry(13) = "=IF(貼付場所!U♪44="""","""",貼付場所!U♪44)"
BaseFmlAry(14) = "=IF(貼付場所!V♪44="""","""",貼付場所!V♪44)"
BaseFmlAry(15) = "=IF(貼付場所!W♪45="""","""",貼付場所!W♪45)"
BaseFmlAry(16) = "=IF(貼付場所!Y♪45="""","""",貼付場所!Y♪45)"
BaseFmlAry(17) = "=IF(B♪3="""","""",TRIM(CONCATENATE("" "",貼付場所!Y♪44)))"
BaseFmlAry(18) = "=IF(貼付場所!T♪45="""","""",貼付場所!T♪45)"
BaseFmlAry(19) = "=IF(B♪3="""","""",TRIM(CONCATENATE("" "",貼付場所!T♪44)))"
BaseFmlAry(20) = "=IF(MID(貼付場所!F♪45,1,5)=""-"","""",MID(貼付場所!F♪45,1,5))"
BaseFmlAry(21) = "=IF(MID(貼付場所!F♪45,6,2)=""-"","""",MID(貼付場所!F♪45,6,2))"
End Sub
（半平太） 2021/05/28(金) 19:26
完璧すぎる．．．
意味が分からなすぎる．．．
このマクロって
Private BaseFmlAry(1 To 21) Private TopAdr
と
Sub FillMult() End Sub
と
Private Sub storeBaseFormulae() End Sub と ３個に分かれてますが… そういうもの何ですか？ callで呼び出す的な感じですか？
最初「♪」が文字化けかと思いました…
「♪」は何なんですか？
そして、やはりここの意味が分からないです。
もしも、良ければ教えて下さい。
For i = 1 To 21
Set TopCell = WshToWrite.Range(TopAdr(i))
TopCell.Resize(163).ClearContents
Select Case i
Case 1 To 4 '単純コピーでいい数式
TopCell.Resize(numDATA).FormulaLocal = BaseFmlAry(i)
Case Else
ReDim FormlaAry(1 To numDATA, 1 To 1)
For RW = 3 To numDATA + 2
adjust4445 = (RW - 3) * Delta
adjust23 = RW - 3
fmlaToApply = Replace(BaseFmlAry(i), "♪2", (2 + adjust23))
fmlaToApply = Replace(fmlaToApply, "♪3", (3 + adjust23))
fmlaToApply = Replace(fmlaToApply, "♪44", (44 + adjust4445))
fmlaToApply = Replace(fmlaToApply, "♪45", (45 + adjust4445))
FormlaAry(RW - 2, 1) = fmlaToApply
Next RW
TopCell.Resize(numDATA).FormulaLocal = FormlaAry
End Select
Next i
End Sub
Private Sub storeBaseFormulae()
TopAdr = Split("0,C3,G3,U3,W3,B3,D3,E3,F3,H3,I3,J3,K3,L3,M3,N3,O3,P3,R3,S3,T3,V3", ",")
今回は勉強にもなりましたが、高度すぎてまだ実になってない部分も多いです。
でも、求めていた完全な物を作成頂きありがとうございます♪
（殿様ぺんぎん） 2021/05/29(土) 09:21
＞３個に分かれてますが… ＞そういうものなんですか？ いや、一つのプロシージャでも書けますが、 長ったらしくなるので準備工程と本番工程を分けただけです。
＞callで呼び出す的な感じですか？ Callと書いてないですが、Callそのものです。 Callは省略できるので、・・と言うか省略するのは極普通です。
＞「♪」は何なんですか？ 数式ひな形に書き込んでおくもので、そこから行番号が変動しますよと言う目印です。 こう言うのは、解決の構想の立て方次第なので、色んなアプローチがあると思います。
数式の規則性は2種類。 単純に1行ずつ増やすもの と 3行ずつ増やすもの(その中に1行ずつ増やすものも含む）です。 前者は単純コピーで処理可能。 後者は♪2、♪3の目印は1行ずつ増やす、♪44、♪45は3行ずつ増やすことになります。
沢山あるので、ループで処理できるように「先頭セルのアドレス」と 「数式ひな形」の順番を合わせて配列に格納して置く必要があります。
数式ひな形の配列は1からスタートしますが、
先頭セルは、同じように格納するコードを書くのは面倒なので、
Split関数を使って変数(TopAdr）に配列格納することにしたのですが、
その配列は0スタートのものであり、数式ひな形配列と対応関係がズレる為、
0番目にダミーの0が埋まるようにしてあります。
↓
＞TopAdr = Split("0,C3,G3,U3,W3,B3,D3,E3,F3,H3,I3,J3,K3,L3,M3,N3,O3,P3,R3,S3,T3,V3", ",")
あとは前回と同じ考えなんですけど。 ひな形の♪を目印に、行番号を修正して正規の数式文字に変えているだけです。
ループ処理なので、１個あっても、10個あっても同じこと。 まぁ、そう出来るように、どうお膳立てするかが工夫のしどころとも言えます。
（半平太） 2021/05/29(土) 12:43
＞Callは省略できるので、・・と言うか省略するのは極普通です。
って
Sub APGM()
Call Macro1
Call Macro2
End Sub
が
Sub APGM()
Macro1
Macro2
End Sub
で良いって事ですか？
（殿様ぺんぎん） 2021/05/31(月) 11:31
貼付に関してどうですか？
（殿様ぺんぎん） 2021/06/01(火) 14:56
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.