記憶マクロしかできないのでお恥ずかしいのですが
式をコピーして貼り付ける際の最終行の取得の仕方について教えてください。

B2にVLOOKの式を入れて、この表ならB2:K7
に式を貼り付けたいです。

初めは、品番をB2:K7に全て貼り付けて、その上に式を重ねてから
Range(Selection, Selection.End(xlDown)).Select
にしていましたがもし品番が1つしかなかった場合は最終行が取得できずうまく動きませんでした。

 
Range("B2").Select
Selection.Copy
Range("B2:K2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Range("B2:K7").Select
ActiveSheet.Paste
Application.CutCopyMode = False


どこかにRowをくっつけたらいいのでしょうか?
使い方もわからないので、どのようにしたらいいか教えて頂けると助かります。

「コピー貼り付け際の最終行取得について」の質問画像

A 回答 (2件)

こんにちは!



B2セルには数式が入っているのですよね?
(絶対参照にされていて、B2セルの数式をフィル&コピーできる状態になっている!)

というコトであれば

Sub Sample1()
Dim lastRow As Long
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range(Cells(2, "B"), Cells(lastRow, "K")).Formula = Range("B2").Formula
End Sub

程度で大丈夫だと思います。m(_ _)m
    • good
    • 0
この回答へのお礼

助かりました

ひとつずつ意味を調べながらなんとなく内容把握しました。
VBAだとこんなにシンプルに仕上がるんですね!うまく稼動できました。
ありがとうございます。非常に助かりました。

お礼日時:2017/07/11 15:34

B2:K7に名前をつけておいてその名前で参照すればいいのでは?

    • good
    • 0
この回答へのお礼

ありがとう

名前の参照だと範囲が変わった時が怖いので今回は辞めておきますが
思いつきませんでした!今後の参考にさせて頂きます。ありがとうございます。

お礼日時:2017/07/11 15:34

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

このQ&Aと関連する良く見られている質問

Q【VBA】IF文 複数(ネスト)の時の処理について

こんにちは。
if文についておしえてください。
以下のようなマクロがあるとします。

変数 tensuuに-1をいれて実行すると①→②のように動作し「入力エラー」と表示されます。
tensuuに120を入れて実行すると①´→②´の順に動作し「入力エラー1」と表示されます。

どして、-1のときは入力エラー1にはいかず入力エラーにいくのでしょうか?
120のときは入力エラーにはいかず入力エラー1にいくのでしょうか?

動きがよくわかりません。
IF文とELSEはどういう紐づけがされているのでしょうか?

よろしくおねがいいたします。
   
Sub t()
tensuu = -1
If tensuu >= 0 Then '①
If tensuu <= 100 Then '①´
If tensuu >= 80 Then
MsgBox "合格"
Else
MsgBox "不合格"
End If
Else
MsgBox "入力エラー1" '②´
End If
Else
MsgBox "入力エラー" '②
End If
End Sub

こんにちは。
if文についておしえてください。
以下のようなマクロがあるとします。

変数 tensuuに-1をいれて実行すると①→②のように動作し「入力エラー」と表示されます。
tensuuに120を入れて実行すると①´→②´の順に動作し「入力エラー1」と表示されます。

どして、-1のときは入力エラー1にはいかず入力エラーにいくのでしょうか?
120のときは入力エラーにはいかず入力エラー1にいくのでしょうか?

動きがよくわかりません。
IF文とELSEはどういう紐づけがされているのでしょうか?

よろし...続きを読む

Aベストアンサー

If 〜 Then 〜 Else 〜 End If
で1セットです。

ネスト(入れ子)になったIF文というのは、 Then 〜 とか Else 〜 の〜の部分にIf文がくるものです。
ですから、外のIfを越えてしまうことはありません。
よって、一番内側から見ていけば、構造がはっきりします。


一番内側から見ます。

If tensuu >= 80 Then
MsgBox "合格"
Else
MsgBox "不合格"
End If

が1セットです。
これを 「文1」とすると元のプログラムは

If tensuu >= 0 Then '①
If tensuu <= 100 Then '①´
「文1」
Else
MsgBox "入力エラー1" '②´
End If
Else
MsgBox "入力エラー" '②
End If

となります。この状態で「一番内側」を見ると

If tensuu <= 100 Then '①´
「文1」
Else
MsgBox "入力エラー1" '②´
End If
です。これを「文2」とすると

If tensuu >= 0 Then '①
「文2」
Else
MsgBox "入力エラー" '②
End If


余談ですが
この例の場合、外側2つは、判定内容と処理とが離れてしまい、見辛いのは確かです。
if 条件 Then A Else B は if not条件 Then B Else A と同じ、ということから、Thenでの処理とElseでの処理を入れかえれば、
条件の直ぐ下の処理が来るので、見易さが格段によくなります。

If tensuu < 0 Then '① ' tensuu<0 は not (tensuu>=0)と同じ
MsgBox "入力エラー" '②
ElseIf tensuu > 100 Then '①´
MsgBox "入力エラー1" '②´
ElseIf tensuu >= 80 Then
MsgBox "合格"
Else
MsgBox "不合格"
End If ' ElseIfで継いでいるので、ネストにはなっていない

If 〜 Then 〜 Else 〜 End If
で1セットです。

ネスト(入れ子)になったIF文というのは、 Then 〜 とか Else 〜 の〜の部分にIf文がくるものです。
ですから、外のIfを越えてしまうことはありません。
よって、一番内側から見ていけば、構造がはっきりします。


一番内側から見ます。

If tensuu >= 80 Then
MsgBox "合格"
Else
MsgBox "不合格"
End If

が1セットです。
これを 「文1」とすると元のプログラムは

If tensuu >= 0 Then '①
If tensuu <= 100 Then '①´
「文1」
Else
MsgBox "入力エラー1" '②´
...続きを読む

QEXCEL VBAでVLOOKUPを実行するとFALSEなのに、おかしな数字をもってきてしまいます。

EXCEL VBA 超初心者です。
以下のマクロを作成しました。VLOOKUPを使って、SHEET2にあるデータから、同じ№であるなら、SHEET1のE列に売上を持ってくるようにしたはずなのですが、一部うまく作動できずに困っています。
E17の黄色く塗りつぶした箇所ですが、SHEET2に№1523のデータがないので、0になるはずが、何故か№1515の値を持ってきてしまいます。
以下のマクロを実行した後、F列にVLOOKUPの関数を当ててみたところ、発覚しました。
E15やE22の緑の箇所は、データがないので、0になっているのでOKなのに、何故一部おかしい数値をもってくるのかわかりません。300くらいのデータの内、10件くらいは、おかしい数値をもってきてしまっており、結局VLOOKUPの関数を後から使って、データを修正するという手間になってしまっています。
マクロのどこがおかしいのでしょうか?
On Error Resume Nextの使い方が間違っていますか?
VLOOKUPでFALSEにしてあるのに訳が分からずおて上げ状態です。

わかりにくくて申し訳ありませんが、どなたか教えてください。
どうぞ宜しくお願い致します。

Sub 売上マクロ()

Dim i As Long
Dim Uriage As Long: Uriage = 0
Dim MyNum As Long
Dim MyData As Range

Set MyData = Worksheets(2).Range("A1").CurrentRegion

For i = 2 To Worksheets(1).Range("A1").End(xlDown).Row

MyNum = Worksheets(1).Cells(i, 3).Value

On Error Resume Next
Uriage = Application.WorksheetFunction.VLookup(MyNum, MyData, 3, False)
On Error GoTo 0
Worksheets(1).Cells(i, 5).Value = Uriage

Next i

End Sub

EXCEL VBA 超初心者です。
以下のマクロを作成しました。VLOOKUPを使って、SHEET2にあるデータから、同じ№であるなら、SHEET1のE列に売上を持ってくるようにしたはずなのですが、一部うまく作動できずに困っています。
E17の黄色く塗りつぶした箇所ですが、SHEET2に№1523のデータがないので、0になるはずが、何故か№1515の値を持ってきてしまいます。
以下のマクロを実行した後、F列にVLOOKUPの関数を当ててみたところ、発覚しました。
E15やE22の緑の箇所は、データがないので、0になっているのでOKなのに、...続きを読む

Aベストアンサー

本マクロでの状態ではVLOOKUPでエラー発生時(#N/Aのケース)で、そのまま続行しています。
そうすると、エラー発生時、Uriageにはなにもセットされない為、前回の値が残ります。
VLOOKUPの直前でUriageをクリアしてください。
----------------------------------
On Error Resume Next
Uriage = 0 '・・・①
Uriage = Application.WorksheetFunction.VLookup(MyNum, MyData, 3, False)
On Error GoTo 0
----------------------------------
①を追加してください。

Qエクセル VBA 条件を複数指定したいのですが。。

Dim buf As Single
With sh1
For i = 6 To 200 'Sheet1の6行目から200行目まで'
If InStr(Range("F" & i), "空室,") > 0 Then
buf = buf + Range("K" & i)
End If

Next i

Range("J200") = "使用量"
Range("J201") = "空室等"
Range("J202") = "合計"
Range("K202").Formula = "=SUM(K3:K197)" '合計
Range("K201") = buf '空室等
Range("K200") = Range("K202") - Range("K201") '控除
For i = 1 To 3
Range("K" & i).NumberFormatLocal = "0.0"

Next i

三点、質問させていただきます。

1、空室 という文字が含まれる行の数値を合計させるために上記のようにしています。そこに、”共用部”や”管理外”など、条件の単語を追加したいのですが、
”空室,共用部,管理外” ではダメなんですね、、
どのように記述すれば、複数の単語を対象に出来ますか?
それとも、行をコピーしておのおの指定するべきでしょうか?

2、6行目から200行目 ではなく、「6行目から下方向、データがある行まで」
というような、指定は出来ますでしょうか??

3、マクロ実行後、セルの数値を手で打ち変えた際に、合計の値も連動して変わるようにするには、bufを使わないでやればいいのでしょうか?

'Range("AK243").Formula = "=SUM(AL3:AL239)" '空室合計計算
'Range("AK244").Formula = "=SUM(AK3:AK239)" '合計計算
'Range("AK242") = Range("AK244") - Range("AK243") '差引合計計算

上記の行を使うと、後でセルの数値を変更した場合連動して合計値なども変わってくれるのですが・・

添削をお願いできませんでしょうか。。
宜しくお願いいたします。

Dim buf As Single
With sh1
For i = 6 To 200 'Sheet1の6行目から200行目まで'
If InStr(Range("F" & i), "空室,") > 0 Then
buf = buf + Range("K" & i)
End If

Next i

Range("J200") = "使用量"
Range("J201") = "空室等"
Range("J202") = "合計"
Range("K202").Formula = "=SUM(K3:K197)" '合計
Range("K201") = buf '空室等
Range("K200") = Range("K202") - Range("K201") '控除
For i = 1 To 3
Range("K" & i).NumberFormatLocal ...続きを読む

Aベストアンサー

1.以下のように変えてください。
If InStr(Range("F" & i), "空室,") > 0 Or InStr(Range("F" & i), "共用部,") > 0 Or InStr(Range("F" & i), "管理外,") > 0 Then

2.Sheet1の最大行をもとめておき、そこまで行うようにします。
Dim maxrow as Long
maxrow = sh1.Cells(Rows.Count, "A").End(xlUp).Row '・・・・①
として
For i = 6 To maxrow
・・・・
next i
とします。
尚、①は、最大行を持つ列がAの場合です。もし、他の列が最大行を持っているならその列を指定して下さい。

3.
>マクロ実行後、セルの数値を手で打ち変えた際に、合計の値も連動して変わるようにするには、bufを使わないでやればいいのでしょうか?
>上記の行を使うと、後でセルの数値を変更した場合連動して合計値なども変わってくれるのですが・・
質問の意味がよくわかりませんが、上記の行を使ってうまく行くならそれを使用すればよいと思います。

1.以下のように変えてください。
If InStr(Range("F" & i), "空室,") > 0 Or InStr(Range("F" & i), "共用部,") > 0 Or InStr(Range("F" & i), "管理外,") > 0 Then

2.Sheet1の最大行をもとめておき、そこまで行うようにします。
Dim maxrow as Long
maxrow = sh1.Cells(Rows.Count, "A").End(xlUp).Row '・・・・①
として
For i = 6 To maxrow
・・・・
next i
とします。
尚、①は、最大行を持つ列がAの場合です。もし、他の列が最大行を持っているならその列を指定して下さい。

3.
>マクロ実行後、セ...続きを読む

Qマクロを組んだエクセルのデータを別のエクセルにコピペしたことが原因で・・・

win7使用してます。以前に,アクティブセルの行と列に十文字のラインが入るようにネットで調べたマクロを入力してみました。そのせいなのだと思うのですが,そのデータを別のエクセルにコピペして使うのですが,そのコピペ先のエクセルシートのセルがランダムに青くなるようになりました。
範囲選択をしてその青い部分を覆うと,消えたり消えなかったりしたり,いったん画面外にスクロールし戻ると消えていたり・・そのままも。枠だけが水色(この色はコピペ元のシートで入力したマクロで指定した色)になったりもします。これも同様,消えたり現れたりを繰り返します。恐らくマクロを組んだシートのデータなので,何か余分なものまで持ってきてしまっているような状態なのだと思います。いろいろ調べていますが同じ症例が見当たらず,改善策を教えて頂きたく質問させて頂きました。
有識の方よろしくお願いします。

Aベストアンサー

No.1 の追補

標準モジュールではなくシートモジュールやワークブックモジュールにコードは残っていませんか?
下図の赤く囲まれた部分をクリックしてみてください。

Q至急で申し訳ございません。Excelマクロ教えて下さい。

●  A     B     C     D     E     F     G

1  2/1    10:00   10     0    入荷    出荷   リンゴa
2  2/1    11:00   10     2    入荷    出荷   イチゴb
3  2/1    12:00   10     1    入荷    出荷   リンゴa
4  2/1    12:00   10     1    入荷    出荷   リンゴa
5  2/1    13:00   10     0    入荷    出荷   リンゴa
6
7
8  2/1    14:00   10     2    入荷    出荷   リンゴc
9  2/1    15:00   10     0    入荷    出荷   イチゴa
10  2/1    16:00   10     10    入荷    出荷   リンゴb
11  2/1    17:00   10     0     入荷    出荷   リンゴa
12 2/1    18:00   10      3    入荷    出荷   ミカンc
13 2/1    19:00   10      1    入荷    出荷   リンゴb
14 2/1    19:30   10      0    入荷    出荷   リンゴa
15 2/1    20:00   10      5    入荷    出荷   リンゴa

至急で申し訳ございません。
うまく記載できなかったのですが、上記のような表があり、(最終行は約2000行くらいです)
マクロで処理を行いたいのですが、構文がよくわかりません。
どなたか、エクセルのマクロにお詳しい方、ご教授をお願い致します。

G列の´リンゴ*′の時にD列の′1′以外の数値分だけ空白行を直上に挿入させたいのです。
8行目のように直上に2行の空白行を挿入させたいです。
10行目の直上にD列の´10´に従い空白行を直上に挿入させたり、
15行目の直上にD列の´5´に従い空白行を直上に挿入させたいのです。

行数が多く、人間の目で確認していると間違ってしまう気がします。

何卒よろしくお願いいたします。教えてください。
教えてください。お願い致します。

●  A     B     C     D     E     F     G

1  2/1    10:00   10     0    入荷    出荷   リンゴa
2  2/1    11:00   10     2    入荷    出荷   イチゴb
3  2/1    12:00   10     1    入荷    出荷   リンゴa
4  2/1    12:00   10     1    入荷    出荷   リンゴa
5  2/1    13:00   10     0    入荷    出荷   リンゴa
6
7
...続きを読む

Aベストアンサー

こんにちは!

D列の数だけ行挿入すればよいのですね?
D列はプラスの整数が入っているとします。
一例です。

Sub Sample1()
Dim i As Long
For i = Cells(Rows.Count, "A").End(xlUp).Row To 1 Step -1
If Cells(i, "G") Like "リンゴ*" Then
If Cells(i, "D") > 1 Then
Rows(i & ":" & i + Cells(i, "D") - 1).Insert
End If
End If
Next i
End Sub

こんな感じではどうでしょうか?m(_ _)m

Q表と関数

壁にぶつかり、再び質問しに参りました。
複数の質問がありますが、一度に全部を質問して混乱するといけないので
分けて質問したいと思います。
まず最初の質問分だけお伺いさせて頂きます。


1.A1に年月をいれます。(書式設定で2017/5/1をyyyy"年"m"月"と設定)。(N1~Y1)も同じ。
2.B列~F列にそれぞれ番号、名前、フリガナ、日付、金額のデータを入力(重複あり)
3.B列~F列のデータから個人(番号を基準)ごとに合計額を集計

ここで、excel入門レベルの私は、

N2に

=IF(N$1=$A$1,SUMIF($B$2:$F$1000,$L2,$F$2:$F$1000),"")

入れて、コピードラッグして作りました。(個人ごとの合計額を集計)


そして、ここで機能を加えることにしました。
「入金が未納状態である」ことを表の中に表示させようと思いました。
添付画像の赤文字が「入金が未納状態である」を示しています。

契約者の中には、色んなケースがあります。
「月払」「年払」の方がおり、また契約を途中で打ち切る方がおられます。

機能を追加したことで、ここで一旦、要件を整理したいと思います。

上記の1~3の他に

4.契約終了あり
5.契約形態として「月払」「年払」あり(J列)
6.「年払」契約者の更新月(K列)
7.契約者の入金予定額(I列) -未納と判定されると、この額が未納額と同額となります。
              契約終了者は、「契約終了」と表示されています
8.「未納判定の要件」
  ・「月払契約者の未納判定」
  納付すべき月払契約者(契約終了を除く)が、月替わりの直後(毎月1日-判定日)に行われる。
  前月分を納めていない(表の中では、「空白」)者を「未納認定」
  
  ・「年払契約者の未納判定」
  納付すべき年払契約者(契約終了を除く)が、契約者「更新月」(K列)の月替わりの
  直後(毎日1日-判定日)に行われる。判定日において前月(更新月)分を
  納めていない(表の中では、「空白」)者を「未納判定」


それでは
添付画像で例題を説明したいと思います。

●2017年4月(A1)の表-「未納判定日前」

2017年4月(正確には30日)の時点です

0052 A様(月払)は、未納金を残して契約終了
0493 B様(月払)は、3月に未納、4月は4月30日になっても入金確認ナシ → 「空白」状態(Q3)
0538 C様(月払)は、契約開始から滞りなく納める
0730 D様(月払)は、4月は4月30日になっても入金確認ナシ → 「空白」状態(Q5)
2965 E様(年払)は、更新月の2月から未納が続いている
3821 F様(年払)は、更新月の1月に納めた




●2017年5月(A1)の表-「未納判定日」

月が変わり、2017年5月(正確には1日)の時点です。この日は「未納判定日」。

0052 A様(月払)は、未納金を残しての契約終了ですが、もはや契約終了してますので
「未納判定」から除外。

0493 B様(月払)は、2017年5月1日に3月に続き4月も入金確認は認められなかったので
2017年4月30日から2017年5月1日になった時点で
(P3)に赤文字の-2,500があるにもかかわらず、「空白」(Q3)から
赤文字の-2,500(Q3)と表示が変わる

0538 C様(月払)は、先月(4月)も納めているので問題ナシ

0730 D様(月払)は、2017年5月1日に前月(4月)分は未納状態(Q5 空白)なので
2017年4月30日から2017年5月1日になった時点で正式に「未納」と判定され、
「空白」(Q5)から赤文字の-3,000(Q5)と表示が変わる

2965 E様(年払)は、更新月の2月に納められず、3月に入って「未納」と判定される。
しかし、(年払)なので3月1日(未納判定日)以降のセルは「空白」でも
未納判定を行っていない。
「年払契約者の未納判定」は、毎年、「更新月」(K列)の月替わりの
直後(毎日1日認定日)に行われる

3821 F様(年払)は、更新月の1月に一括して納めたので、「更新月」以降のセルが
「空白」でも年払契約者なので未納判定を行っていない。



このように表中から「未納判定日」において、「未納」と判定した時、その相当する
セル(N~EC)に、(I列)の入金予定額を未納額(マイナス数値)として表示させる
関数式を教えてください。


★注意事項

・VBAは使用しません
・作業列を設けても構いません
・本来、B列~F列のデータは月別に別シートに記録されています。質問しやすいように
このようなかたちにしました。
・B列~F列のデータは最大1000件
・月間データ(N~Y)は、最大10年(N~EC)
・月間データは、その都度別シートに記録されています。(個人ごとの入金合計額)のみ。
・質問内容に循環参照のおそれがある場合には、その解決策も併せて提示してください


脱字・脱語・質問内容に不備がありましたらご容赦ください。
よろしくお願い致します。

壁にぶつかり、再び質問しに参りました。
複数の質問がありますが、一度に全部を質問して混乱するといけないので
分けて質問したいと思います。
まず最初の質問分だけお伺いさせて頂きます。


1.A1に年月をいれます。(書式設定で2017/5/1をyyyy"年"m"月"と設定)。(N1~Y1)も同じ。
2.B列~F列にそれぞれ番号、名前、フリガナ、日付、金額のデータを入力(重複あり)
3.B列~F列のデータから個人(番号を基準)ごとに合計額を集計

ここで、excel入門レベルの私は、

N2に

=IF(N$1=$A$1,SUMIF($B$2:$F$...続きを読む

Aベストアンサー

ふむふむ。
No1への返信で
>>年払で未納だった人が次の月に支払った場合は、更新月ではないですが入金があったので、その額を表示しますか?
>上記の理由により、この件も次回に質問させて頂きます。
とありましたので、とりあえず更新月以外は全て空白でよいものと判断してしまいました。
未納判定はしないけれど、入金があれば記載する。ということですね。

であれば条件の一部を変える必要があります。
>年払で更新月と一致していなければ、空白を表示します。
という部分が、
年払いで更新月と一致していない場合、入金額が0なら空白、それ以外はそのままの数字を表示する。
というわけですね。
式は次のようになります。

=IF(O2="契約終了","契約終了",IF(O2="","",IF($J2="年払",IF($K2=N$1,IF(P2<O2,IF(Q$1>$A$1,"",-O2),P2),IF(P2=0,"",P2)),IF(P2<O2,IF(Q$1>$A$1,"",-O2),P2))))

あと、O列の入力式に訂正があります。
空白の時にも0が表示される式になっておりましたので、N3,N5にも0が表示されてしまったものと思います。
O2=IF(INDIRECT(YEAR(N$1)&"年"&MONTH(N$1)&"月!I"&ROW())="","",INDIRECT(YEAR(N$1)&"年"&MONTH(N$1)&"月!I"&ROW()))
としてください。
各シートでのN列が空白であった場合は空白を、それ以外はそのまま表示する。となっています。
(そのまま表示するの場合でも空白は0と変換されるので)
P列の方は実際の入金が無いというだけなので0と表示されても問題はないはずです。

いかがでしょう?

ふむふむ。
No1への返信で
>>年払で未納だった人が次の月に支払った場合は、更新月ではないですが入金があったので、その額を表示しますか?
>上記の理由により、この件も次回に質問させて頂きます。
とありましたので、とりあえず更新月以外は全て空白でよいものと判断してしまいました。
未納判定はしないけれど、入金があれば記載する。ということですね。

であれば条件の一部を変える必要があります。
>年払で更新月と一致していなければ、空白を表示します。
という部分が、
年払いで更新月と一致していな...続きを読む

QVBA DO LOOP Do loopをつかって行を空欄まで見に行き、ある条件の時は行を削除します。

VBA DO LOOP

Do loopをつかって行を空欄まで見に行き、ある条件の時は行を削除します。ただdo loopでさくじょすると行が動くので(四行目を削除すると五行目が四行目になり、四行目が条件対象であっても四行目はループしているため、プログラムが見に行かない。)うまくいきません。何か他の方法でうまくいきませんか?

Aベストアンサー

No.2さんと同じ考えかたです。
A列をA1から見ていって、1の行を削除します。

Sub Sample()
  Dim row As Long
  
  row = 1
  Do While Cells(row, 1).Value <> ""
    If Cells(row, 1).Value = 1 Then
      Cells(row, 1).EntireRow.Delete
      row = row - 1
    End If
    row = row + 1
  Loop
End Sub

Q(Excel)データのカウントの仕方

データが2万件近くあり、添付画像のように9桁の商品番号が
バラバラにならんでおり、商品番号の数を「=COUNTIF」でだしたのですが
必要なのはその隣の注文数でした。

商品番号も30種類くらいあり、関数がわからないので
教えて頂けませんか?

Aベストアンサー

》 「=COUNTIF」でだしたのですが…
あなたが「だした」結果も載せるべきでしたね。
それから、行番号、列記号も添付図に示すべきでした。式を作成する上で必要!

添付図が参考になりますか?
E2: =COUNTIF(B:B,D2)
F2: =SUMIFS(A:A,B:B,D2)

Qエクセルについて質問です

仕事で仕入先からの交渉データを作成していて見やすいように
追加しようと考えていますが一部分からないところがあります。

■現状
シート1(件名一覧表)
シート2(交渉内訳)

■追加シート3(個別件名の確認用シート)

やりたいことはシート2(交渉内訳)に
C列に注文番号のコード
D列にメーカーコード
E列に仕入先名
F列にメーカーごとの定価金額
H列に担当者が交渉したメーカーごとの原価金額
K列に私が交渉したメーカーごとの原価金額
があります。
C列の注文番号を入力すれば
個別件名シートで見れるように

上記データを元に
追加シート3(個別件名の確認用シート)に
交渉内訳シートのC列、D列、E列、F列、H列、K列が
反映されるようにしたいです。

=VLOOKUP(A2,交渉内訳!B7:E2049,3,0)と
入力すると全てが同一仕入先になってしまい
各仕入先が表示されません。

なかなか文章で説明が難しく詳細もコンプライアンス上なかなか
表示出来ませんので答えにくいかも

Aベストアンサー

=VLOOKUP(A2,交渉内訳!B7:E2049,3,0)で、
A列には検索したい日付が入っているなら、
VLOOKUPの中身としては、交渉内訳シートのB列でその日付を検索し、3番目=D列のデータを表示。
(D列=メーカーのコード)

>全てが同一仕入先になってしまい各仕入先が表示されません。
上記式で表示されるのはメーカーのコードであり、仕入先名ではないですよ?
メーカー=仕入先なのでしたら、同一日に複数の案件があり、全ての日に全てのメーカーのデータがあるとか?

>正:B列に注文番号のコード
B列の中からA2(日付)を検索しているのに、B列には注文番号が入っているのですか?
それでしたら、一致するものがない為、近似として扱われたデータが表示されているのかもしれません。
完全一致で検索するとして、VLOOKUPの最後の0(TRUE扱い)を1(個人的には1よりFALSEと書く方が見た時わかりやすいので推奨)にしてみてください。
VLOOKUPの場合、近似よりも完全一致で検索する場合の方が多いと思いますし、それだと検索範囲を昇順に並べ替える必要もなくなります。

>注文番号を入力すれば個別件名シートで見れるように
7~2049行にデータがあるとして、
=VLOOKUP($AA2,交渉内訳!$B$7:$K$2049,2,FALSE)
AA2は検索したい注文番号の入力されたセルの事です。該当するセルに変更してください。
B列の1~6行目に注文番号と重複するデータが確実にないのであれば、
=VLOOKUP($AA2,交渉内訳!$B:$K,2,FALSE)
とした方が良いです。(データが増えても範囲を変更せずに済むので。

とりあえず、これはB列を1列目として2列目=C列のデータを表示させる式です。
C列は結局何が入っているのかわかりませんが、とりあえずC列、D列、E列、F列、H列、K列を表示させる式を書きますね。

C列:=VLOOKUP($AA2,交渉内訳!$B:$K,2,FALSE)
D列:=VLOOKUP($AA2,交渉内訳!$B:$K,3,FALSE)
E列:=VLOOKUP($AA2,交渉内訳!$B:$K,4,FALSE)
F列:=VLOOKUP($AA2,交渉内訳!$B:$K,5,FALSE)
H列:=VLOOKUP($AA2,交渉内訳!$B:$K,7,FALSE)
K列:=VLOOKUP($AA2,交渉内訳!$B:$K,10,FALSE)
となります。
元の表とシートの様式を同じにしていれば、
B列に注文番号のコードを入力するとして、
C2=VLOOKUP($B2,交渉内訳!$B:$K,COLUMN()-1,FALSE)
これをコピーするだけで済みます。
G,I,J列が不要であれば非表示にすればいいのです。1列1列何列目を抽出するかを数える必要はなくなります。

文章だけで間違いも含め可能な限り読み砕いたつもりです。参考まで

=VLOOKUP(A2,交渉内訳!B7:E2049,3,0)で、
A列には検索したい日付が入っているなら、
VLOOKUPの中身としては、交渉内訳シートのB列でその日付を検索し、3番目=D列のデータを表示。
(D列=メーカーのコード)

>全てが同一仕入先になってしまい各仕入先が表示されません。
上記式で表示されるのはメーカーのコードであり、仕入先名ではないですよ?
メーカー=仕入先なのでしたら、同一日に複数の案件があり、全ての日に全てのメーカーのデータがあるとか?

>正:B列に注文番号のコード
B列の中からA2(日付)を検索...続きを読む

QVBA コンパイルエラーの解消法

皆様、いつもお世話になっております。
初心者なりに考えてVBAを組んでみたのですが、「Sub または Fanction が定義されていません」とエラー表示が出てきます。
自分なりにコードに間違いがないか検索ながらやってみたのですが、うまくいきません。

どなたか、知恵をお貸しいただけないでしょうか。



テーブルの入っているSheet1から、シート”施工体制台帳~”の該当するセルにデータのコピーを行うためのVBAです。
業者の数だけループするように組んだつもりなのですが、エラー表示のため実行ができません。
ご指摘、よろしくお願いいたします。

Sub kopipe1() '施工体制台帳
Dim sh As Worksheet
Dim sh1 As Worksheet
Dim sb As Long
Dim m As Long
Dim p As Long
Dim c As Long
Dim i As Long
Dim end1 As Long

Set sh = Worksheet("sheet1")
Set sh1 = Worksheet("施工体制台帳 (下請け) ")


end1 = sh.Range(".Cells(Rows.Count, 2)").End(xlUp)
sb = 1
p = 1

For i = 2 To end1 Step 1
sb = sb + 1
c = cell(p, 9)

sh.cell(sb, 2).Copy Destination:=.sh1.Range(c).Offset(2, 1) '会社名
sh.cell(sb, 3).Copy Destination:=.sh1.Range(c).Offset(2, 27) '代表者名
sh.cell(sb, 4).Copy Destination:=.sh1.Range(c).Offset(4, 1) '郵便番号
sh.cell(sb, 5).Copy Destination:=.sh1.Range(c).Offset(5, 1) '住所
sh.cell(sb, 6).Copy Destination:=.sh1.Range(c).Offset(6, 24) '電話番号
sh.cell(sb, 7).Copy Destination:=.sh1.Range(c).Offset(13) '業種1
sh.cell(sb, 8).Copy Destination:=.sh1.Range(c).Offset(14, 12) '許可者1
sh.cell(sb, 9).Copy Destination:=.sh1.Range(c).Offset(14, 15) '区分1
sh.cell(sb, 10).Copy Destination:=.sh1.Range(c).Offset(14, 17) '許可1-1
sh.cell(sb, 11).Copy Destination:=.sh1.Range(c).Offset(14, 20) '許可1-2
sh.cell(sb, 12).Copy Destination:=.sh1.Range(c).Offset(14, 27) '許可年月日
sh.cell(sb, 13).Copy Destination:=.sh1.Range(c).Offset(16) '業種2
sh.cell(sb, 14).Copy Destination:=.sh1.Range(c).Offset(17, 12) '許可者2
sh.cell(sb, 15).Copy Destination:=.sh1.Range(c).Offset(17, 15) '区分2
sh.cell(sb, 16).Copy Destination:=.sh1.Range(c).Offset(17, 17) '許可2-1
sh.cell(sb, 17).Copy Destination:=.sh1.Range(c).Offset(17, 20) '許可2-2
sh.cell(sb, 18).Copy Destination:=.sh1.Range(c).Offset(17, 27) '許可年月日2
sh.cell(sb, 19).Copy Destination:=.sh1.Range(c).Offset(21, 28) '健康保険
sh.cell(sb, 20).Copy Destination:=.sh1.Range(c).Offset(22, 28) '厚生年金保険
sh.cell(sb, 21).Copy Destination:=.sh1.Range(c).Offset(23, 28) '雇用保険
sh.cell(sb, 22).Copy Destination:=.sh1.Range(c).Offset(25, 3) '現場代理人指名
sh.cell(sb, 23).Copy Destination:=.sh1.Range(c).Offset(29, 7) '主任技術者氏名
sh.cell(sb, 24).Copy Destination:=.sh1.Range(c).Offset(31, 3) '資格内容
sh.cell(sb, 25).Copy Destination:=.sh1.Range(c).Offset(33, 3) '安全衛生責任者
sh.cell(sb, 26).Copy Destination:=.sh1.Range(c).Offset(25, 26) '安全衛生推進者
sh.cell(sb, 27).Copy Destination:=.sh1.Range(c).Offset(27, 26) '雇用管理責任者
sh.cell(sb, 28).Copy Destination:=.sh1.Range(c).Offset(29, 26) '専門技術者名
sh.cell(sb, 29).Copy Destination:=.sh1.Range(c).Offset(31, 26) '技術資格内容

p = p + 62
Exit For
Next i
End Sub

皆様、いつもお世話になっております。
初心者なりに考えてVBAを組んでみたのですが、「Sub または Fanction が定義されていません」とエラー表示が出てきます。
自分なりにコードに間違いがないか検索ながらやってみたのですが、うまくいきません。

どなたか、知恵をお貸しいただけないでしょうか。



テーブルの入っているSheet1から、シート”施工体制台帳~”の該当するセルにデータのコピーを行うためのVBAです。
業者の数だけループするように組んだつもりなのですが、エラー表示...続きを読む

Aベストアンサー

今のところ、変数に代入する以外のコードはほとんど間違っているわけですから、どれがどうと言えないと思います。
他の方との重複を含みます。
・Worksheet(---) ->Worksheets(----)
・sh.Range(".Cells(Rows.Count, 2)").End(xlUp)
  ↓
sh.Cells(Rows.Count, 2).End(xlUp).Row
・c = cell(p, 9)
  ↓
 c = Cells(p, 9) ただし、最後に、p = p + 62の後、Exit For では、1回キリでおしまいになってしまいます。

おそらくは、Dim c As String --Cells(p, 9).Address(0,0)
のはずです。
 
・sh.cell(sb, 2).Copy Destination:=.sh1.Range(c).Offset(2, 1) '会社名
とりあえず、Cells と sh1 のコンマは取るけれども、
sh1.Range(c).Offset(2, 1) これでは可読性を落とすばかりで分かりません。

これ自体を直すよりは、最初から作り直したほうが早そうです。
たぶん、一覧で横に並んでいるものを、別のシートの各場所に振り替えていくわけで、それが、ページごとになっているというわけでしょう。

まず、 sh1.cells(sb, 2).Copy ですが、
その列の2 を変数にすべきですね。

受ける側が、Offset で書かれてしまうと、手がつけられなくなってしまいます。

sh1.Range("J3,AJ3,J5,J6,AG7,I14,U15,X15,Z15,AC15,AJ15,I17," & _
 "U18,X18,Z18,AC18,AJ18,AK22,AK23,AK24,L26,P30,L32,L34," & _
 "AI26,AI28,AI30,AI32")

このようにまとめてしまい、以下のように纏めたものから呼び出すようにします。
しかし、実際にやってみると不具合が生じるので、それを配列で渡すようにします。

注:以下は、私の想像で書かれたものであって、実際に合っているのかは分かりません。
このようなスタイルにしてみたらという、あくでもこちらの提案です。

'//
Sub Test1()
 Dim Rng As Range
 Dim i As Long, j As Long, sb As Long
 Dim c As Variant
 Dim sh As Worksheet, sh1 As Worksheet
 Set sh = Worksheets("sheet1")
 Set sh1 = Worksheets("施工体制台帳 (下請け)")

 
 Set Rng = sh1.Range("J3,AJ3,J5,J6,AG7,I14,U15,X15,Z15,AC15,AJ15,I17," & _
 "U18,X18,Z18,AC18,AJ18,AK22,AK23,AK24,L26,P30,L32,L34," & _
 "AI26,AI28,AI30,AI32")
 
 end1 = sh.Cells(Rows.Count, 2).End(xlUp).row
 
 i = 2
 sb = 2
 For j = 1 To end1
 a = (j - 1) * 62 + 1
  For Each c In Rng.Offset((j - 1) * 62 + 1)
   c.Value = sh.Cells(sb, i).Value
   DoEvents
   i = i + 1
  Next
  i = 2
  sb = sb + 1
 Next
End Sub

今のところ、変数に代入する以外のコードはほとんど間違っているわけですから、どれがどうと言えないと思います。
他の方との重複を含みます。
・Worksheet(---) ->Worksheets(----)
・sh.Range(".Cells(Rows.Count, 2)").End(xlUp)
  ↓
sh.Cells(Rows.Count, 2).End(xlUp).Row
・c = cell(p, 9)
  ↓
 c = Cells(p, 9) ただし、最後に、p = p + 62の後、Exit For では、1回キリでおしまいになってしまいます。

おそらくは、Dim c As String --Cells(p, 9).Address(0,0)
のはずです。
 
・sh.cell(sb, 2).C...続きを読む


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング