[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『エクセルのマクロを使用したコピペについて』（マクロって楽しいな〜）
エクセルのマクロを使用したコピペについて
いつもは、該当にシートに下記の様にしてコピペしてます。
Sub Macro1()
Range("A2").Value = "=B2*C2"
End Sub
Sub コピペ01()
Dim a%, s&, s1&, PLACE1 As String
Sheets("Sheet1").Select
Range("B2").Select
s = ActiveCell.Row
a = ActiveCell.Column
s1 = Cells(s, a).End(xlDown).Row
PLACE1 = "A2:" & "A" & s1
Range("A2").Copy
Range(PLACE1).PasteSpecial Paste:=xlPasteAllExceptBorders
Range("A2").Select
Application.CutCopyMode = False
Dim q%, w&, w1&, PLACE2 As String
Sheets("Sheet1").Select
Range("B2").Select
w = ActiveCell.Row
q = ActiveCell.Column
w1 = Cells(w, q).End(xlDown).Row
PLACE2 = "A2:" & "A" & w1
Range(PLACE2).Copy
Range(PLACE2).PasteSpecial Paste:=xlPasteValues
Range("A2").Select
Application.CutCopyMode = False
End Sub
上記であっているのか（無駄がないのか）は分かりません。
しかし、今回行いたい事が上記の「コピペ01」だとできないので教えて下さい。
マクロを２個に分けているのは自分で分かりやすくする為だけです。
A2をコピーしてB列の最終行にあたるA列にA2をコピペしたいと思っています。
上記のマクロだと、A3・A4・A5にも全て新しく関数が反映してしまいます。
A3・A4・A5は、個別に手入力で数字を変える可能性がある為
B列の最終行から張り付けたいと思っています。
そもそも、Macro1をわざわざA2に張り付けないでできるならそれに越した事はありません。
ただ、自分のレベルでは、A2セルをつぶして使う事ぐらいしか浮かばなかったのでこのようにしています。
解決できる方が居ましたらご教授お願い致します。
もちろん、答えのマクロでも全然ＯＫです
直ぐに理解できる程理解力が無いので徐々に覚えていこうと思っていますので
＜ 使用 Excel：Office365、使用 OS：Windows10 ＞
Sub test()
Dim lastRow As Long
lastRow = Cells(Rows.Count, "B").End(xlUp).Row
Range("A2").Copy Cells(lastRow, "A")
End Sub
こういうことですか？（シート指定は省略しています。）
（γ） 2022/10/11(火) 19:36:24
Sub Sample()
With Range("A2")
.Value = .Offset(, 1).Value * .Offset(, 2).Value
End With
With Range("B2").End(xlDown).Offset(, -1)
.Value = .Offset(, 1).Value * .Offset(, 2).Value
End With
End Sub
ただA2とB列合わせの最終行にB列×C列の値が入ればいいだけなら。
（下手の横好き） 2022/10/11(火) 20:36:20
＞ Ｂ列の最終行にではなくまでコピーです とのことですが、首尾一貫した発言にしてくださいよ。 ＞ A2をコピーしてB列の最終行にあたるA列にA2をコピペしたいと思っています。 ＞ 上記のマクロだと、A3・A4・A5にも全て新しく関数が反映してしまいます。 と書いてない？ まあ、いずれにしても自分でやってください。 （γ） 2022/10/12(水) 15:07:39
■１
Dim a%, s&, s1&, PLACE1 As String
↑のような書き方について、意味が分かって書いているのであればよいと思いますが、「a%って何型ですか？」と問われて即答できないのであればやめたほうがよいでしょう。
数か月後のご自身が、「アレこれってどういう意味だっけ？！」となる可能性が大です。
【参考】 https://www.adonetvb.com/Learning/OldBasicVariable.html
■２
VBAの世界では、基本的にシートやセルなど（オブジェクトといいます）をきちんと明示すれば、いちいちアクティブにしたり選択したりする必要はありません。
また【標準モジュール】でシートの指定を省略するとActiveSheetを指定したとみなされるルールです。
したがって、想定外の処理をしないためにも対象のオブジェクトをきちんと明示することや、可読性の向上を意識しActive○○やSelection.○○のような記述は避けることをことをお勧めします
■３
Sub 整理前1()
Dim a%, s&, s1&, PLACE1 As String
Sheets("Sheet1").Select
Range("B2").Select
s = ActiveCell.Row
a = ActiveCell.Column
s1 = Cells(s, a).End(xlDown).Row
End Sub
上記を踏まえて↑を整理するとこうなります。
Sub 整理1()
Dim a As Integer, s As Long, s1 As Long
s = Sheets("Sheet1").Range("B2").Row
a = Sheets("Sheet1").Range("B2").Column
s1 = Sheets("Sheet1").Cells(s, a).End(xlDown).Row
End Sub
さらに整理するとこうですね。
Sub 整理2()
Dim s1 As Long
s1 = Sheets("Sheet1").Range("B2").End(xlDown).Row
End Sub
さらに続けて↓も整理してみましょう
Sub 整理前2()
Dim s1&, PLACE1 As String
s1 = Sheets("Sheet1").Range("B2").End(xlDown).Row
PLACE1 = "A2:" & "A" & s1
Sheets("Sheet1").Select
Range("A2").Copy
Range(PLACE1).PasteSpecial Paste:=xlPasteAllExceptBorders
End Sub
あまり変わりませんが↓ですよね。
Sub 整理3()
Dim s1 As Long, PLACE1 As String
s1 = Sheets("Sheet1").Range("B2").End(xlDown).Row
PLACE1 = "A2:A" & s1
Sheets("Sheet1").Range("A2").Copy
Sheets("Sheet1").Range(PLACE1).PasteSpecial Paste:=xlPasteAllExceptBorders
End Sub
ここで「s1」「PLACE1」に注目してみてください。それぞれ１回しか出てこないですよね。
ということは、そのまま当てはめちゃってもよさそうです。
実際にやってみましょう。
Sub 整理4()
Sheets("Sheet1").Range("A2").Copy
Sheets("Sheet1").Range("A2:A" & Sheets("Sheet1").Range("B2").End(xlDown).Row).PasteSpecial _
Paste:=xlPasteAllExceptBorders
End Sub
ちょっと長くなってしまいましたが、実質２行になりました。
ただ、なんども「Sheets("Sheet1")」が出てきて見づらいですよね。
こういうときは【Withステートメント】の出番です。Withステートメントを使うと↓のようになります。
Sub 整理4_改()
With Sheets("Sheet1")
.Range("A2").Copy
.Range("A2:A" & .Range("B2").End(xlDown).Row).PasteSpecial Paste:=xlPasteAllExceptBorders
End With
End Sub
※Wihtってなんですか？という状態であれば↓を読んでください。
【参考】 http://officetanaka.net/excel/vba/beginner/16.htm
■４
さて、「整理4_改」と「Macro1」を合体させるとこうなるわけです。
Sub 合体()
With Sheets("Sheet1")
.Range("A2").Value = "=B2*C2"
.Range("A2").Copy
.Range("A2:A" & .Range("B2").End(xlDown).Row).PasteSpecial Paste:=xlPasteAllExceptBorders
End With
End Sub
だいぶすっきりしましたね。
まずは、こちらをたたき台にして何をやってるか、【ステップ実行】などを行って研究してみてはどうでしょうか？
なお、↓は間違ってるとはいいませんが、意味合い的には
.Range("A2").Value = "=B2*C2"
.Range("A2").Formula = "=B2*C2"
↑のように書いたほうがよいとおもいます。
■５
＞そもそも、Macro1をわざわざA2に張り付けないでできるならそれに越した事はありません。
上記に関連してますが、「Paste:=xlPasteAllExceptBorders」としているのに↑だと話の辻褄があっていないように思います。
単に、一括して数式を入れたいのであれば、極端な話↓でもよいでしょう。
Sub 極端な例1()
With Worksheets("Sheet1").Range("A2")
Application.Range(.Cells, .Offset(, 1).End(xlDown).Offset(, -1)).Formula = "=B2*C2"
End With
End Sub
'==========================================================
Sub 極端な例2()
With Worksheets("Sheet1")
.Range("A2", .Range("B2").End(xlDown).Offset(, -1)).Formula = "=B2*C2"
End With
End Sub
■６
＞コピーした後に値貼り付けも行いたいです。
「xlPasteAllExceptBorders」でなくてよいということが前提になりますが、数式を設定した範囲をコピーしてそのまま値貼り付けすればよいです。
Sub 極端な例2_改1()
With Worksheets("Sheet1")
With .Range("A2", .Range("B2").End(xlDown).Offset(, -1))
.Formula = "=B2*C2"
.Copy
.PasteSpecial Paste:=xlPasteValues
End With
End With
End Sub
よりVBA的なアプローチにトライしたいということであれば↓のような方法もありますね。
Sub 極端な例2_改2()
With Worksheets("Sheet1")
With .Range("A2", .Range("B2").End(xlDown).Offset(, -1))
.Formula = "=B2*C2"
.Value = .Value
End With
End With
End Sub
■７
すでに指摘がありますが、
＞＞ Ｂ列の最終行にではなくまでコピーです ＞＞ 上記のマクロだと、A3・A4・A5にも全て新しく関数が反映してしまいます。
例えば、B列最終行が10だったときに
(1) A2〜A10セルに 「=B2*C2」に相当する数式を入れたい (2) A10セルだけに 「=B10*C10」という数式を入れたい
↑のどっちなんですか？
仰る通り、コードを見るかぎりは(1)になっているわけですが、(2)がやりたいことだとすれば↓のような説明になりませんか？
Ｂ列の最終行に相当するＡ列のセル【まで】ではなく【に】コピーです
（もこな２） 2022/10/12(水) 18:53:03
ということで↓みたいにしたかったのではないでしょうか？
Sub さんぷる1()
With Worksheets("Sheet1")
.Range("A2").Formula = "=B2*C2"
.Range("B2").End(xlDown).Offset(, -1).FormulaR1C1 = .Range("A2").FormulaR1C1
End With
End Sub
そしてR1C1形式での数式が分かっていれば↓のように。A2セルに書きまなくても直接指定すればよいですね。
Sub さんぷる2()
Worksheets("Sheet1").Range("B2").End(xlDown).Offset(, -1).FormulaR1C1 = "=RC[1]*RC[2]"
End Sub
（もこな２） 2022/10/12(水) 21:49:15
?@元々は、A2セルなどに関数を入れてそれをＢ列の最終行まで「コピー」か「コピペ」したいと思っています
その為、今は書いたマクロを使用しています。（友人が作成したマクロを使ってます）
?Aそして、今回は、元のマクロだと上書きされてしますので
新しくデータを作成した際に新しく作成した場所からコピペをしたいので投稿しました。
?@の場合も?Aの場合ももっと関単にできたらと思っています。
（こんなことできたらいいな） 2022/10/13(木) 16:01:20
行いたい事は
(1) A2〜A10セルに 「=B2*C2」に相当する数式を入れたい これです
すみません、私の書き方が駄目で皆様に迷惑を掛けました。
（こんなことできたらいいな） 2022/10/13(木) 16:03:53
今回行いたいのは、明記しましたが。
１番最初は、教えて頂いたマクロを使いセルA2などからコピペでよかったのですが
データを継ぎ足していくうえで今までに張り付けた場所に手入力で数値を上書きしていきます。
そこで同じ様マクロを使用すると上書きされてしまうので、データの最終行+１行を取得して新しく張り付けたデータにのみに関数を張り付けたいと思っています。
説明が下手ですみません。
どうかよろしくお願い致します。
（こんなことできたらいいな） 2022/10/13(木) 16:25:00
Sub Sample()
Dim rng As Range
Dim Destination As Range
Set rng = Range("A2") '先頭セルを決める
Set rng = Range(rng, rng.Offset(, 1).End(xlDown).Offset(, -1)) '先頭セル右隣列を基準に先頭セル列最終行までの範囲を設定
On Error Resume Next 'エラー無視
Set Destination = rng.SpecialCells(xlCellTypeBlanks) 'セル範囲のうちの空白セルを貼り付け先にする（空白セルが無い場合にエラーになるのでエラー無視をしておく）
On Error GoTo 0 'エラー無視おわり
If Destination Is Nothing Then Exit Sub '貼り付け先範囲が無い場合は終了
rng(1).Copy '先頭セルの数式をコピー
Destination.PasteSpecial xlPasteFormulas '空白セルに数式貼り付け
Application.CutCopyMode = False 'コピーモード解除
rng.Value = rng.Value 'セル範囲の数式を結果値で更新（数式をなくす）
End Sub
まだよくわかっていませんが、先頭セルに書いた数式をその下の空白セルにだけ貼り付けて数式をなくすものを書いてみました。
（下手の横好き） 2022/10/13(木) 16:45:44
下記のマクロでＡ列に値を入れる場合は問題ないのですが
ＡＡ２からＢ列の最終行までデータを入れたい場合はどのようにすればいいのでしょうか？
下記の形で行うと、Ｂ列からＡＡ列まで=COUNTIF(H:H,H2)が値貼り付けされた状態で入ります。
Sub 極端な例2()
With Worksheets("一時貼付")
With .Range("AA2", .Range("B2").End(xlDown).Offset(, -1))
.Formula = "=COUNTIF(H:H,H2)"
.Copy
.PasteSpecial Paste:=xlPasteValues
End With
End With
End Sub
（こんなことできたらいいな） 2022/10/13(木) 17:00:08
何となくわかりました、
こういうことですね。
Sub ブランクセルに数式を書き込むマクロ()
With Worksheets("Sheet1")
.Range("B2:B" & .Cells(.Rows.Count, "B").End(xlUp).Row).Offset(, -1).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=RC[1]*RC[2]"
End With
End Sub
行列がわかるレイアウトを示して説明いただいたほうが状況がつかめたかもしれません。
（もこな２） 2022/10/13(木) 17:02:11
Sub ブランクセルに数式を書き込むマクロ_修正版()
With Worksheets("Sheet1")
.Range("A2:A" & .Cells(.Rows.Count, "B").End(xlUp).Row).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=RC[1]*RC[2]"
End With
End Sub
（もこな２） 2022/10/13(木) 17:04:09
これってR1C1形式じゃないとできないんですか？
それと、値は付けVERもあれば．．．(´;ω;｀)
それと、そもそもブランクセルに入れるには別な考え方が必要なのでしょうか？
今まで気にした事なかったので
（検索ボタン） 2022/10/13(木) 17:49:42
Sub Sample2()
Dim 基準列 As Range
Dim 基準行 As Range
Dim 貼付先 As Range
With Worksheets("一時貼付")
Set 基準列 = .Range("AA2").EntireColumn 'AA列を基準列とする
Set 基準行 = .Range("B2", .Range("B2").End(xlDown)).EntireRow 'B列基準で基準行を決める
With Intersect(基準列, 基準行) '基準列と基準行の交わった範囲を対象に
.Cells(1).ClearContents '先頭セルの値を消す（確実に先頭セルに数式を入力するため。これをしないと数式の対象セルが狂う）
Set 貼付先 = .SpecialCells(xlCellTypeBlanks) '空白セルを変数に入れる
貼付先.Value = "=COUNTIF(H:H,H2)" '空白セルに数式を入れる（数式は先頭セル基準のもの）
End With
End With
End Sub
対象範囲設定を見直しました。
（下手の横好き） 2022/10/13(木) 17:52:57
★１
＞これってR1C1形式じゃないとできないんですか？
まずは【ステップ実行】して、自己検証してみてください。
聞くまでもなく理解できるでしょう。
それでも理解できない場合は、どんな検証をしたらどうなったか教えてください
★２
＞それと、値は付けVERもあれば．．．(´;ω;｀)
【VBA】の綴り間違えですかね？
値貼付け（というか値に直す方法）が分からないということであればマクロの記録を取ってみてはいかがでしょうか？
（既に値になっている部分と数式の部分をまとめてコピーしてそのまま値貼付すればよいですよね）
★３
＞それと、そもそもブランクセルに入れるには別な考え方が必要なのでしょうか？
質問？疑問点？の意味がわかりません。
行・列が分かるレイアウトで例をあげていただけませんか？
（もこな２） 2022/10/13(木) 21:30:29
ブランクセルに数式を書き込むマクロに値貼り付けするにはこんな感じになっちゃうんでしょうか？
Sub ブランクセルに数式を書き込むマクロ_修正版()
With Worksheets("一時貼付")
.Range("AA2:AA" & .Cells(.Rows.Count, "B").End(xlUp).Row).SpecialCells(xlCellTypeBlanks).Formula = "=COUNTIF(H:H,H2)"
End With
Range("AA2:AA" & Range("B" & Cells.Rows.Count).End(xlUp).Row).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End Sub
（マクロって楽しいな〜） 2022/10/14(金) 11:00:18
さらに、こちらでいう話でもありませんが、あちらにも回答が付いていますから、ちゃんと対応すべきでしょう。
検証・研究中であっても、その旨のレスがなければ、どういう状況なのか回答者にはわかりません。
また、他人を装っても良いことはないと思いますのでニックネームは統一したほうがよいでしょう。
場合によっては何度も同じ説明をされることになりかねませんし、 回答側も同じことを説明するとなると手間ですからお互いに不利益です。
■９
With Worksheets("一時貼付")
.Range("AA2:AA" & .Cells(.Rows.Count, "B").End(xlUp).Row).SpecialCells(xlCellTypeBlanks).Formula = "=COUNTIF(H:H,H2)"
End With
↑について「★１」で」述べた通り自己検証してみましたか？
↓みたいなときに、想定外の結果になったりしませんか？
__B__ ... __AA__
1
2 1 0
3 1 0
4 1 0
5 1 0
6 1
7 1
8 1
9 1
10 1
■10
Range("AA2:AA" & Range("B" & Cells.Rows.Count).End(xlUp).Row).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
↑について「■２」を再読してください。
さらに、「With」の意味がわかっていないようなので「■３」も再読してください。
■11
★３のお返事はまだですか？
（もこな２） 2022/10/14(金) 19:47:37
> Range("AA2:AA" & Range("B" & Cells.Rows.Count).End(xlUp).Row).Select
AA2:AA？を選択
> Selection.Copy
選択したとこコピー
> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
選択した所に貼り付け
何か感じませんかね！ 中学レベルの英語 （英検3級） 2022/10/14(金) 20:38:43
★３
＞それと、そもそもブランクセルに入れるには別な考え方が必要なのでしょうか？
質問？疑問点？の意味がわかりません。
行・列が分かるレイアウトで例をあげていただけませんか？
これに関しては、セルがそもそもブランクだろうが値が入っていようが関係ない物だと思っていたので…
値が入っていると処理が起こらないというのは初めて知りました。
一応全て教えてもらったマクロをＦ８で実行して試してます。
（マクロって楽しいな〜） 2022/10/18(火) 13:31:24
関数を張り付けたい部分がたくさんあり
下記の様に関数を対象の最初の行に貼付てコピー値貼り付けする様に考えてました。
Sub 関数()
Range("A14").Value = "=A13+1" '関数コピペ01
Range("C13").Value = "=CONCATENATE(TRIM(F13),TRIM(H13),TRIM(K13))" '関数コピペ02
Range("D13").Value = "=COUNTBLANK(AE13:AG13)"
Range("AE13").Value = "=TRIM(F13)" '関数コピペ03
Range("AF13").Value = "=TRIM(G13)"
Range("AG13").Value = "=TRIM(H13)"
Range("AH13").Value = "=IF(D13=2,""通常"",IF(D13=0,""特値"",""""))"
Range("AI13").Value = "=IF(E13="""","""",TRIM(E13))"
Range("AJ13").Value = "=IF(TRIM(E13)="""","""",VLOOKUP(AO13,K:AF,21,0))"
Range("AK13").Value = "=IF(TRIM(E13)="""","""",VLOOKUP(AO13,K:AF,22,0))"
Range("AL13").Value = "=IF(H13="""","""",H13)"
Range("AM13").Value = "=IF(I13="""","""",I13)"
Range("AN13").Value = "=IF(J13="""","""",TRIM(J13))"
Range("AO13").Value = "=IF(TRIM(E13)="""","""",K13)"
Range("AP13").Value = "=IF(TRIM(E13)="""","""",TRIM(L13))"
Range("AQ13").Value = "=IF(TRIM(E13)="""","""",O13)"
Range("AR13").Value = "=IF(TRIM(E13)="""","""",N13)"
Range("AS13").Value = "=IF(TRIM(E13)="""","""",V13)"
Range("AT13").Value = "=IF(TRIM(E13)="""","""",W13)"
Range("AU13").Value = "=IF(TRIM(E13)="""","""",P13)"
Range("AV13").Value = "=IF(TRIM(E13)="""","""",Y13)"
Range("AW13").Value = "=IF(TRIM(E13)="""","""",Z13)"
Range("AX13").Value = "=IF(TRIM(E13)="""","""",Q13)"
Range("AY13").Value = "=IF(TRIM(E13)="""","""",(AX13-AU13)/AX13)"
Range("AZ13").Value = "=IF(TRIM(E13)="""","""",AV13/(0.99-AY13))"
Range("BA13").Value = "=IF(TRIM(E13)="""","""",(AZ13-AV13)/AZ13)"
Range("BE13").Value = "=IF(BD13<>"""",BD13,IF(BC13<>"""",BC13,IF(BB13<>"""",BB13,IF(BB13="""",""""))))" 'コピーのみ '関数コピペ04
Range("BF13").Value = "=IFERROR(IF(TRIM(E13)="""","""",LOOKUP(10^9,BB13:BD13)/AX13-1),"""")" 'コピーのみ
Range("BG13").Value = "=IF(BE13="""","""",BE13*AQ13)" 'コピーのみ
Range("BH13").Value = "=IF(BE13="""","""",BE13*AR13)" 'コピーのみ
Range("BJ13").Value = "=IF(BI13="""","""",(AX13*BI13)+AX13)" 'コピーのみ '関数コピペ05
Range("BK13").Value = "=IF(AC13="""","""",""有"")" '関数コピペ06
Range("BL13").Value = "=IF(AD13="""","""",IF(AD13=1,"""",""複数""))"
Range("BN13").Value = "=T13" '関数コピペ07
Range("BX13").Value = "=IF(M13="""","""",M13)" '関数コピペ08
Range("BY13").Value = "=IF(AO13="""","""",AO13)"
Range("BZ13").Value = "=IF(AP13="""","""",AP13)"
Range("CA13").Value = "=IF(AQ13="""","""",AQ13)"
Range("CB13").Value = "=IF(AR13="""","""",AR13)"
Range("CC13").Value = "=IF(AX13="""","""",AX13)"
Range("CD13").Value = "=IF(BE13="""","""",BE13)" 'コピーのみ '関数コピペ09
Range("CE13").Value = "=IF(BF13="""","""",BF13)" 'コピーのみ
Range("CF13").Value = "=IF(BK13="""","""",BK13)" '関数コピペ10
Range("CH13:CX13").Select 'コピーのみ '関数コピペ11
End Sub
しかしこれをすると過去に張り付けた場所に手入力で上書きした場合に更に上書きされてしまうので今回質問させて頂きました。
|[A] |[B] |[C] |[D] |[E] |[F] |[G] |[H] |[I] |[J] |[K] |[L] |[M] |[N] |[O] |[P] |[Q] |[R] |[S] |[T] |[U] |[V] |[W] |[X] |[Y] |[Z] |[AA] |[AB] |[AC]|[AD] |[AE] |[AF] |[AG] |[AH] |[AI] |[AJ] |[AK] |[AL] |[AM] |[AN] |[AO] |[AP] |[AQ] |[AR] |[AS] |[AT] |[AU] |[AV] |[AW] |[AX] |[AY] |[AZ] |[BA] |[BB] |[BC] |[BD] |[BE] |[BF] |[BG] |[BH] |[BI] |[BJ] |[BK] |[BL] |[BM] |[BN] |[BO] |[BP] |[BQ] |[BR] |[BS] |[BT] |[BU] |[BV]|[BW]|[BX] |[BY] |[BZ] |[CA] |[CB] |[CC] |[CD] |[CE] |[CF] |[CG]|[CH] |[CI] |[CJ] |[CK] |[CL] |[CM] |[CN] |[CO] |[CP] |[CQ] |[CR]|[CS] |[CT] |[CU] |[CV] |[CW] |[CX]
[12]|SEQ |見積ファイル名 |CON |区分 |担当者名 |仕入先コード|仕入先名 |得意先コード|宛名|得意先名 |商品コード|商品名 |客先商品ＣＤ |袋入数|ケース入数|仕入単価|契約単価|ランク|区分|備考 |数量１|旧商事
数量２|新商事
新数量２|数量３|新仕入|UP率 |新上代|特記事項|実績|複数口座| | | |区分 |担当 |仕入先 |仕入先名 |得意先 |宛名 |得意先名 |商品CD |商品名 |ケース
入数 |袋入数 |商事
旧仕入 |商事
新仕入 |旧
仕入 |新
仕入 |仕入
ＵＰ率 |旧
売価 |旧
粗利率 |仮想
新売価 |仮想
新粗利率 |新売価
（１回目）|新売価
（２回目）|新売価
（３回目）|決定
売価 |UP率 |ＣＳ
金額 |袋
金額 |％
算出|％算出
単価 |１３ケ月実績 |複数口座
登録あり |得意先実施日|備考 |メーカー |配信日|変更日|提出日|提出方法|登録変更日|チェック|備考| |客先商品ＣＤ |商品コード |商品名 |ケース入数 |袋入数 |旧売価 |新売価 |UP率 |１３ケ月実績 | |処理
区分|仕入先ｺｰﾄﾞ|得意先
ｺｰﾄﾞ|宛名
ｺｰﾄﾞ|商品コード|商品名|客先商品ＣＤ|仕入契約単価|売契約単価|ランク|区分|備考 |数量１|数量２|数量３|適用日|適用時
[13]|100000|【斎藤彦兵衛商店】価格改定 0401|=CONCATENATE(TRIM(F13),TRIM(H13),TRIM(K13))|=COUNTBLANK(AE13:AG13)| |MEQ44 |エフピコ商事（株）※ウツミリサイクル | | | | 00502514|卵４Ｈ 平面 ＳＫ（１１１） | | 2400| 2400| 3.58| 0| |* | | 0| 3.4| 3.9| 0| 4.11|=Y13/P13-1| | | | |=TRIM(F13)|=TRIM(G13)|=TRIM(H13)|=IF(D13=2,"通常",IF(D13=0,"特値",""))|=IF(E13="","",TRIM(E13))|=IF(TRIM(E13)="","",VLOOKUP(AO13,K:AF,21,0))|=IF(TRIM(E13)="","",VLOOKUP(AO13,K:AF,22,0))|=IF(H13="","",H13)|=IF(I13="","",I13)|=IF(J13="","",TRIM(J13))|=IF(TRIM(E13)="","",K13)|=IF(TRIM(E13)="","",TRIM(L13))|=IF(TRIM(E13)="","",O13)|=IF(TRIM(E13)="","",N13)|=IF(TRIM(E13)="","",V13)|=IF(TRIM(E13)="","",W13)|=IF(TRIM(E13)="","",P13)|=IF(TRIM(E13)="","",Y13)|=IF(TRIM(E13)="","",Z13)|=IF(TRIM(E13)="","",Q13)|=IF(TRIM(E13)="","",(AX13-AU13)/AX13)|=IF(TRIM(E13)="","",AV13/(0.99-AY13))|=IF(TRIM(E13)="","",(AZ13-AV13)/AZ13)| | | |=IF(BD13<>"",BD13,IF(BC13<>"",BC13,IF(BB13<>"",BB13,IF(BB13="",""))))|=IFERROR(IF(TRIM(E13)="","",LOOKUP(10^9,BB13:BD13)/AX13-1),"")|=IF(BE13="","",BE13*AQ13)|=IF(BE13="","",BE13*AR13)| |=IF(BI13="","",(AX13*BI13)+AX13)|=IF(AC13="","","有")|=IF(AD13="","",IF(AD13=1,"","複数"))| |=T13 | | 44809| 44839| 44824|持参 | | | | |=IF(M13="","",M13)|=IF(AO13="","",AO13)|=IF(AP13="","",AP13) |=IF(AQ13="","",AQ13)|=IF(AR13="","",AR13)|=IF(AX13="","",AX13)|=IF(BE13="","",BE13)|=IF(BF13="","",BF13)|=IF(BK13="","",BK13)| | |=F13 |=H13 |=I13 |=K13 |=L13 |=M13 |=Y13 |=CD13 |=R13 |=S13|=BN13|=U13 |=W13 |=X13 |=BQ13 |1：朝(AM 06:00)
[14]|=A13+1|【斎藤彦兵衛商店】価格改定 0401|M236600502514 | 2|小倉裕太 | | |M2366 |** |（有）吉野商店東久留米 | 00502514|卵４Ｈ 平面 ＳＫ（１１１） | | 2400| 2400| 3.58| 3.6| | |SCMEQ44 | 0| 0| 3.9| 0| 4.11|14.80% | | |実績| | | |M2366 |通常 |小倉裕太 |MEQ44 |エフピコ商事（株）※ウツミリサイクル |M2366 |** |（有）吉野商店東久留米 | 00502514|卵４Ｈ 平面 ＳＫ（１１１） | 2400| 2400| 0| 3.9| 3.58| 4.11|14.80% | 3.6|0.56% | 4.17 |1.56% | 10| 9| 8| 8|122.22% | 19200.00 | 19200.00 |15.00% | 4.14 |有 | |9月27日 |SCMEQ44 |ウツミリサイクル| | | | | | | | | | 00502514|卵４Ｈ 平面 ＳＫ（１１１）| 2400| 2400| 3.6| 8.00 |122.22% |有 | | | | | | | | | | | | | | | | | |
[15]| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
（マクロって楽しいな〜） 2022/10/18(火) 14:09:24
■９について
With Worksheets("一時貼付")
.Range("AA2:AA" & .Cells(.Rows.Count, "B").End(xlUp).Row).SpecialCells(xlCellTypeBlanks).Formula = "=COUNTIF(H:H,H2)"
End With
↑について「★１」で」述べた通り自己検証してみましたか？
↓みたいなときに、想定外の結果になったりしませんか？
__B__ ... __AA__
1
2 1 0
3 1 0
4 1 0
5 1 0
6 1
7 1
8 1
9 1
10 1
下記で行ったら問題なくできました。
特に問題は起きてません。
Sub ブランクセルに数式を書き込むマクロ_修正版()
With Worksheets("一時貼付")
.Range("AA2:AA" & .Cells(.Rows.Count, "B").End(xlUp).Row).SpecialCells(xlCellTypeBlanks).Formula = "=COUNTIF(H:H,H2)"
End With
Range("AA2:AA" & Range("B" & Cells.Rows.Count).End(xlUp).Row).Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
End Sub
■10に関しては、上記の「ブランクセルに数式を書き込むマクロ_修正版」で良いのでしょうか？
withについては一応の理解はできてるのですが（多分）
（マクロって楽しいな〜） 2022/10/18(火) 16:20:08
現状だと質問自体が理解出来てませんので、回答は保留します。
■13
＞withについては一応の理解はできてるのですが（多分）
では、聞きます。なんでSelection○○という書き方になっているのですか？
理解したなら↓になると思いますが・・・
Sub ブランクセルに数式を書き込むマクロ_修正版()
With Worksheets("一時貼付")
.Range("AA2:AA" & .Cells(.Rows.Count, "B").End(xlUp).Row).SpecialCells(xlCellTypeBlanks).Formula = "=COUNTIF(H:H,H2)"
.Range("AA2:AA" & .Range("B" & .Cells.Rows.Count).End(xlUp).Row).Copy
.Range("AA2:AA" & .Range("B" & .Cells.Rows.Count).End(xlUp).Row).PasteSpecial Paste:=xlPasteValues
End With
End Sub
でこれを整理するとこうなるわけです
Sub ブランクセルに数式を書き込むマクロ_修正版()
With Worksheets("一時貼付").Range("AA2:AA" & Worksheets("一時貼付").Cells(.Rows.Count, "B").End(xlUp).Row)
.SpecialCells(xlCellTypeBlanks).Formula = "=COUNTIF(H:H,H2)"
.Copy
.PasteSpecial Paste:=xlPasteValues
End With
End Sub
そこは理解出来ていたのですか？
※ただし、AA2セルがブランクでないと、上記コードは■14のとおり問題が発生するはず
■14
＞特に問題は起きてません。
そうですか。じゃあいいんじゃないですか？
「■９」でこちらが危惧したのは、例えば例示のようにAA2がブランクでないと、はじめのブランクセルであるAA6セルが基準となるから
本来 ：AA6セル =COUNTIF(H:H,H6) 危惧される状況：AA6セル =COUNTIF(H:H,H2)
のようになるだろうなと思ってのことです。問題無いならご自由にどうぞ。
一応、あくまでFormulaR1C1プロパティを使用しないことに拘るという場合を想定してこちらで用意しておいた答えを提示しておきます。
Sub 研究用()
With Worksheets("研究用")
.Range("AA2").Formula = "=COUNTIF(H:H,H2)"
.Range("AA2").Copy
With .Range("AA2:AA" & .Cells(.Rows.Count, "B").End(xlUp).Row)
.SpecialCells(xlCellTypeBlanks).PasteSpecial Paste:=xlPasteFormulas
.Copy
.PasteSpecial Paste:=xlPasteValues
End With
End With
End Sub
このように
(1) どこかのセルに(相対参照の)数式を入力しておいてから (2) それを【コピー】して (3) ブランクセルに【数式を貼り付けて】 (4) 全体を【コピー】して (5) 全体に【値を貼り付け】
とすればFormulaR1C1プロパティは使わなくても済みますね。
（代わりに【コピー】することなりますが）
（もこな２） 2022/10/18(火) 19:54:26
2022/10/18(火) 14:09:24 に提示されたコードを拝見すると、ほぼ13行目の数式設定をしているに過ぎませんから、使わない別の行（たとえば1行目）を数式保持用のセルにして、そこから数式コピぺするとか、FormulaR1C1プロパティを参照するとかしたらよいのではありませんか？
どちらも方法は提示済みです。
（もこな２） 2022/10/18(火) 20:49:28
■13については
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ここの部分をどのように短縮できるのか分からなかったので
そのまま使いました。
Selection.はWITHで囲めばいいんだろうとは思いましたが
シート移動をするわけではないので関係ないのかなって思ってました。
ただ整理して頂いたマクロだと.Rowsでとまり、不完全と表示されてしまいます。
■14
＞特に問題は起きてません。
すみません！
テストでタイトル行と４行目までデータを入れた状態で
ＡＡ２−ＡＡ４までのブランクにデータを入れてましたが
ＡＡ２−ＡＡ４までにデータを入れた状態で６行目まで作成したら
ＡＡ列にもデータが入るのですがＹ列にもデータが入ってしまいました。
Sub 研究用()で行ったらおかしな結果にはなりませんでした。
FormulaR1C1プロパティは、それこそ全く知らないので使わないでできないのかなって思ってました。
（マクロって楽しいな〜） 2022/10/19(水) 13:49:07
Sub 研究用()で行ったらおかしな結果にはなりませんでした。 おかしなってのは、Ｙ列には反映されないって事です。 （マクロって楽しいな〜） 2022/10/19(水) 13:56:51
Sub A()
With Worksheets("一時貼付")
.Range("AE2:AE" & .Cells(.Rows.Count, "B").End(xlUp).Row).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=TRIM(RC[-25])"
.Range("AF2:AF" & .Cells(.Rows.Count, "B").End(xlUp).Row).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=TRIM(RC[-25])"
.Range("AG2:AG" & .Cells(.Rows.Count, "B").End(xlUp).Row).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=TRIM(RC[-25])"
.Range("AH2:AH" & .Cells(.Rows.Count, "B").End(xlUp).Row).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=IF(RC[-30]=2,""通常"",IF(RC[-30]=0,""特値"",""""))"
.Range("AI2:AI" & .Cells(.Rows.Count, "B").End(xlUp).Row).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=IF(RC[-30]="""","""",TRIM(RC[-30]))"
.Range("AJ2:AJ" & .Cells(.Rows.Count, "B").End(xlUp).Row).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=IF(TRIM(RC[-31])="""","""",VLOOKUP(RC[5],C[-25]:C[-4],21,0))"
.Range("AK2:AK" & .Cells(.Rows.Count, "B").End(xlUp).Row).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=IF(TRIM(RC[-32])="""","""",VLOOKUP(RC[4],C[-26]:C[-5],22,0))"
.Range("AL2:AL" & .Cells(.Rows.Count, "B").End(xlUp).Row).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=IF(RC[-30]="""","""",RC[-30])"
.Range("AM2:AM" & .Cells(.Rows.Count, "B").End(xlUp).Row).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=IF(RC[-30]="""","""",RC[-30])"
.Range("AN2:AN" & .Cells(.Rows.Count, "B").End(xlUp).Row).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=IF(RC[-30]="""","""",TRIM(RC[-30]))"
.Range("AO2:AO" & .Cells(.Rows.Count, "B").End(xlUp).Row).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=IF(TRIM(RC[-36])="""","""",RC[-30])"
.Range("AP2:AP" & .Cells(.Rows.Count, "B").End(xlUp).Row).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=IF(TRIM(RC[-37])="""","""",TRIM(RC[-30]))"
.Range("AQ2:AQ" & .Cells(.Rows.Count, "B").End(xlUp).Row).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=IF(TRIM(RC[-38])="""","""",RC[-28])"
.Range("AR2:AR" & .Cells(.Rows.Count, "B").End(xlUp).Row).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=IF(TRIM(RC[-39])="""","""",RC[-30])"
.Range("AS2:AS" & .Cells(.Rows.Count, "B").End(xlUp).Row).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=IF(TRIM(RC[-40])="""","""",RC[-23])"
.Range("AT2:AT" & .Cells(.Rows.Count, "B").End(xlUp).Row).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=IF(TRIM(RC[-41])="""","""",RC[-23])"
.Range("AU2:AU" & .Cells(.Rows.Count, "B").End(xlUp).Row).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=IF(TRIM(RC[-42])="""","""",RC[-31])"
.Range("AV2:AV" & .Cells(.Rows.Count, "B").End(xlUp).Row).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=IF(TRIM(RC[-43])="""","""",RC[-23])"
.Range("AW2:AW" & .Cells(.Rows.Count, "B").End(xlUp).Row).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=IF(TRIM(RC[-44])="""","""",RC[-23])"
.Range("AX2:AX" & .Cells(.Rows.Count, "B").End(xlUp).Row).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=IF(TRIM(RC[-45])="""","""",RC[-33])"
.Range("AY2:AY" & .Cells(.Rows.Count, "B").End(xlUp).Row).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=IF(TRIM(RC[-46])="""","""",(RC[-1]-RC[-4])/RC[-1])"
.Range("AZ2:AZ" & .Cells(.Rows.Count, "B").End(xlUp).Row).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=IF(TRIM(RC[-47])="""","""",RC[-4]/(0.99-RC[-1]))"
.Range("BA2:BA" & .Cells(.Rows.Count, "B").End(xlUp).Row).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=IF(TRIM(RC[-48])="""","""",(RC[-1]-RC[-5])/RC[-1])"
.Range("AE2:BA" & .Range("B" & .Cells.Rows.Count).End(xlUp).Row).Copy
.Range("AE2:BA" & .Range("B" & .Cells.Rows.Count).End(xlUp).Row).PasteSpecial Paste:=xlPasteValues
End With
End Sub
（マクロって楽しいな〜） 2022/10/19(水) 17:59:29
■16
＞Selection.はWITHで囲めばいいんだろうとは思いましたが〜
再三になりますが、「■３」を再読してください。
■17
＞ＡＡ２−ＡＡ４までにデータを入れた状態で６行目まで作成したら
＞ＡＡ列にもデータが入るのですがＹ列にもデータが入ってしまいました。
実際のコードを見ないとわかりませんが、普通に考えればAA列にしか書き込んでいないならば、Y列まで操作されることにはなりません。
今一度、セル範囲の定義を確認されてみてはどうでしょうか。
■18
＞Sub 研究用()で行ったらおかしな結果にはなりませんでした。
懸念内容は「■14」で述べたとおりです。
無駄なやりとりになりますから、この部分については今後無視します。
■19
＞まとめて複数行に行うにはやはり下記の様になるのでしょうか？
別案を「2022/10/18(火) 20:49:28」に提示済みです。
"まとめて"は無理ですが、列ごとにループ処理すれば良いでしょう。
（もこな２） 2022/10/19(水) 20:00:10
とりあえず今まで教えて頂いた事を踏まえてエクセルを作成します。
ありがとうございました。
また、質問はさせて頂くと思います、よろしくお願い致します。
（マクロって楽しいな〜） 2022/10/20(木) 11:02:03
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.