VBAを初めて2カ月の初心者です。
顧客マスタという名のシートがあり、そのシートに顧客の情報が1000レコードくらいあります。
そのデータにあるF2(市町村名)、G2(住所1)、H2(住所2)に()内の情報があり
その値を連結したものを別シート(作成一覧という名のシート)のH3セルに貼り付けたいと思っています。
以下のコードを記述して実行しました。結果、問題なくコピーできたのですがそこから先に進むことができません。
顧客マスタにあるデータをF2(市町村名)、G2(住所1)、H2(住所2)から1行ずつ3,4,5・・・
とカウントアップしていき、その値を作成一覧シートのH3から1行ずつ4,5,6とカウントアップしたセルに値を貼付したいです。ちなみに最終行の次のセルにendと入れているのでendのセル手前まで値を取得して貼り付けしたいです。
以上、宜しくお願いします。
Dim var_顧客マスタ As Variant
Dim str_作成シート1 As String
Dim str_作成一覧 As String
Dim str_住所 As String
Dim rng_住所範囲 As Range
Sheets("顧客マスタ").Select
str_住所 = ""
For Each rng_住所範囲 In Range("F2:H2")
str_住所 = str_住所 & rng_住所範囲.Text
Next rng_住所範囲
Sheets("作成一覧").Range("H3") = str_住所
No.6ベストアンサー
- 回答日時:
ん~~~
コードが長すぎて細かいところは見ていませんが・・・
結局列ごとコピー&ペーストではなく、ループで操作したい!というコトですね。
Sub Sample3()
Dim i As Long, j As Long, cnt As Long
Dim wS As Worksheet
Set wS = Worksheets("生産者マスタ")
With Worksheets("作成一覧")
.Cells.Delete
Worksheets("作成シート1").Cells.Copy .Range("A1")
cnt = 2
For i = 2 To wS.Cells(Rows.Count, "A").End(xlUp).Row
cnt = cnt + 1
With .Cells(cnt, "B")
.Value = wS.Cells(i, "A")
.Offset(0, 1) = wS.Cells(i, "B")
.Offset(0, 2) = wS.Cells(i, "N")
.Offset(0, 3) = wS.Cells(i, "I")
.Offset(0, 4) = wS.Cells(i, "J")
.Offset(0, 5) = wS.Cells(i, "K")
For j = 6 To 8
With .Offset(0, 6)
.Value = .Value & wS.Cells(i, j)
End With
Next j
End With
Next i
.Activate
End With
MsgBox "完了"
End Sub
こんな感じではどうでしょうか?
※ コードの説明はご勘弁を!m(_ _)m
tom04様
ご連絡が遅れて申し訳ありません。
ありがとうございました。
いろいろご迷惑をおかけして申し訳ありませんでした。
tom04様の優しさに感謝いたします。
No.5
- 回答日時:
>.Range("C3").PasteSpecial Paste:=xlPasteValues ですが・・・
Withステートメントになります。
>.Range("C3").PasteSpecial Paste:=xlPasteValues
とは
Worksheets("作成一覧").Range("C3").PasteSpecial Paste:=xlPasteValues
の意味です。
この「Worksheets("作成一覧")」を何度も入力するのが面倒なので
>With Worksheets("作成一覧")
としておけば、あとは
.○○・・・
で「Worksheets("作成一覧")」の○○・・・
と主語が省略ができます。
当方が文章で説明するより↓のサイトが参考になると思います。m(_ _)m
http://officetanaka.net/excel/vba/beginner/16.htm
tom04様
返信が遅れて申し訳ありません。
ご連絡ありがとうございました。
とても分かりやすく教えていただき感謝いたします。
最後に3つ教えて頂きたいのですが
質問①
最初に教えて頂いた↓のコードですが、
If wS.Cells(i, "F") = "end" Then Exit For
F列がendになったらループから抜ける設定になっています。
A,B,N,I,J,Kの列はA列で最終行を取得しています。
"顧客マスタ"A列の最終行=すべての対象列の最終行
なのでF列に"end"を入れずにA列の最終行を参照して
セルの値の連結(F,G,H)は可能でしょうか?
質問②
作成一覧シートに貼り付けた値に
A3セルから最終行まで1、2、3・・・・と数字を入れることは可能ですか?
A3には1 A4には2 A5には3というかんじです・・
質問③
列ごとコピーする方法はなんとなく理解できましたが、
列ではなく値をコピーして貼り付ける方法(ループ)も
教えて頂けないでしょうか?
今現在のコードを貼り付け致します。おかしい部分があれば
ご指摘いただければ幸いです。
前半と後半に分けて送ります。
Sub リスト作成()
Dim i As Long, lastRow As Long
Dim wS As Worksheet
Set wS = Worksheets("生産者マスタ")
With Worksheets("作成一覧")
'作成一覧シートの初期化
Sheets("作成一覧").Select
Cells.Delete Shift:=xlUp
'作成シート1のテンプレートをコピーして作成一覧に貼付
Sheets("作成シート1").Cells.Copy
Sheets("作成一覧").Select
Cells.Select
ActiveSheet.Paste
Application.CutCopyMode = False
後半へ続く
No.4
- 回答日時:
「作成一覧」シートに「顧客マスタ」シートと「生産者マスタ」シートのデータを
「作成一覧」シートのB~E列にコピー&ペーストする必要があるのですね。
(H列の操作はしない?)
単にデータのコピーだけであればお示しのように1行ずつループさせるより
列ごとのコピーが速いかもしれません。
ただお示しのコードは書式もそのままコピー&ペーストされるので
「値」の貼り付けの方が少し時間短縮できるかも・・・
(各シートを「Select」しない方が時間短縮できます)
Sub Sample2()
Dim i As Long, lastRow As Long
Dim wS1 As Worksheet, wS2 As Worksheet
Set wS1 = Worksheets("顧客マスタ")
Set wS2 = Worksheets("生産者マスタ")
With Worksheets("作成一覧")
'//▼「作成一覧」シートのB~E列データを一旦消去//
lastRow = .Cells(Rows.Count, "B").End(xlUp).Row
If lastRow > 2 Then
Range(.Cells(3, "B"), .Cells(lastRow, "E")).ClearContents
End If
'//▼両シートの最終行を取得し、「作成一覧」シートのB~E列に値の貼り付け//
lastRow = wS1.Cells(Rows.Count, "A").End(xlUp).Row '//←「顧客マスタシート」の最終行はA列で取得//
Range(wS1.Cells(2, "A"), wS1.Cells(lastRow, "A")).Copy
.Range("B3").PasteSpecial Paste:=xlPasteValues
Range(wS1.Cells(2, "N"), wS1.Cells(lastRow, "N")).Copy
.Range("D3").PasteSpecial Paste:=xlPasteValues
Range(wS1.Cells(2, "I"), wS1.Cells(lastRow, "I")).Copy
.Range("E3").PasteSpecial Paste:=xlPasteValues
lastRow = wS2.Cells(Rows.Count, "B").End(xlUp).Row '//←「生産者マスタシート」の最終行はB列で取得//
Range(wS2.Cells(2, "B"), wS2.Cells(lastRow, "B")).Copy
.Range("C3").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
.Activate
.Range("B3").Select
End With
End Sub
これでお示しのコードの部分は同じ動きになると思います。
その後H列操作が必要な場合は
その操作を追加すればよいと思います。m(_ _)m
tom04様 ありがとうございます。
H列のコードを最後に追加してできました。
ちなみに生産者マスタシートは全く別の管理しているもので正しくは顧客マスタの誤りでした。←「生産者マスタシート」の最終行はB列で取得//のコードの部分を削除して使用しています。
各列の値を作成一覧シートに貼り付けるコード
.Range("C3").PasteSpecial Paste:=xlPasteValues ですがどこにも"作成一覧"が記述されていないのですがどうして.Range("C3")だけで指定できるのですか?
差支えなければ教えて頂けないでしょうか?
宜しくお願いします。
No.3
- 回答日時:
>ちなみに顧客マスタのA2の値を作成一覧のB3へ
>顧客マスタのB2の値を作成一覧のC3へ貼付
2セルだけの操作で良いのですね。
.Range("B3:C3").Value = wS.Range("A2:B2").Value
の1行を
>cnt = 2
の行の前に追加してみてください。
ループに入る前かループの後であれば、
wSの宣言と、With Worksheets("作成一覧") の後であればどこでも構いません。m(_ _)m
ご連絡遅れて申し訳ありません。
ありがとうございます。
実は住所の連結以外は、下記コードで作成していましたが、
素人なので下記のコードしか思いつかなくてループ機能を使えればもっとすっきりした
コードになると思いますがなかなかできません。先ほどtom04様に作っていただいたコードはあくまでも最初の顧客データのコードと名前の貼付です。実際には1000人以上顧客がいて不定期で新規顧客が追加されます。
今は1500人程度なので2000行までをコピーした形で貼り付けていますが作りたいものは、各対象列最終行の次の行にendと入力しているのでそこまでのデータをコピーしたいです。実際にはA列の最終行=各列の最終行になるのでendは必要ないと思いますがA列以外の最終行は空白になる場合があります。このような場合、プロはどういうコードを記述するのでしょうか?
お時間があれば宜しくお願い致します。
'顧客マスタの顧客Codeをコピーして作成一覧に貼付
Sheets("顧客マスタ").Select
Range("A2:A2000").Copy
Sheets("作成一覧").Select
Range("B3").Select
ActiveSheet.Paste
'顧客マスタの顧客名をコピーして作成一覧に貼付
Sheets("生産者マスタ").Select
Range("B2:B2000").Copy
Sheets("作成一覧").Select
Range("C3").Select
ActiveSheet.Paste
'顧客マスタの支部名をコピーして作成一覧に貼付
Sheets("顧客マスタ").Select
Range("N2:N2000").Copy
Sheets("作成一覧").Select
Range("D3").Select
ActiveSheet.Paste
'顧客マスタの電話番号1をコピーして作成一覧に貼付
Sheets("顧客マスタ").Select
Range("I2:I2000").Copy
Sheets("作成一覧").Select
Range("E3").Select
ActiveSheet.Paste
Dim i As Long, j As Long, cnt As Long
.............
.............
No.2
- 回答日時:
No.1です。
コードの解説というコトですので、
そのままコードにコメントを入れておきます。
Sub Sample1()
'//変数の宣言//
Dim i As Long, j As Long, cnt As Long
Dim lastRow As Long, wS As Worksheet
'//「顧客マスタ」シートを「wS」に格納//
Set wS = Worksheets("顧客マスタ")
With Worksheets("作成一覧") '//「作成一覧」シートの・・・//
lastRow = .Cells(Rows.Count, "H").End(xlUp).Row '//最終行取得//
'//▼「作成一覧」シートのH列データを一旦消去//
If lastRow > 2 Then '//最終行が3行以上あれば・・・//
Range(.Cells(3, "H"), .Cells(lastRow, "H")).ClearContents '//H3~H列最終行までを消去//
End If
cnt = 2 '//まず「cnt」に 2 を格納しておく//
For i = 2 To wS.Cells(Rows.Count, "F").End(xlUp).Row '//「顧客マスタ」シートの2行目~最終行までループ//
cnt = cnt + 1 '//←「cnt」が 3 になる(作成一覧シートの3行目から始めるため★)//
If wS.Cells(i, "F") = "end" Then Exit For '//F列データが「end」の場合はループから抜ける//
For j = 6 To 8 '//←F列~H列まで//
With .Cells(cnt, "H") '//「作成一覧」シートのcnt行,H列は・・・(★の行でループするたびにcntは1ずつ増える)
.Value = .Value & wS.Cells(i, j) '//「作成一覧」シートのcnt行、H列の値に「顧客マスタ」シートi行のF~G列値をつなげていく//
End With
Next j '//「顧客マスタ」シートの次の列へ(F → G → H 列へ)//
Next i '//「顧客マスタ」シートの次の行へ//
.Activate
MsgBox "完了"
End With
End Sub
※ ごちゃこちゃしてしまいましたが
ある程度は理解してもらえると思います。m(_ _)m
とても細かく解説していただき有難うございます。
ちなみに顧客マスタのA2の値を作成一覧のB3へ
顧客マスタのB2の値を作成一覧のC3へ貼付というのも簡単な記述でできますか?
No.1
- 回答日時:
こんにちは!
>最終行の次のセルにendと入れているので・・・
「end」と入っているのはF列だとします。
Sub Sample1()
Dim i As Long, j As Long, cnt As Long
Dim lastRow As Long, wS As Worksheet
Set wS = Worksheets("顧客マスタ")
With Worksheets("作成一覧")
lastRow = .Cells(Rows.Count, "H").End(xlUp).Row
'//▼「作成一覧」シートのH列データを一旦消去//
If lastRow > 2 Then
Range(.Cells(3, "H"), .Cells(lastRow, "H")).ClearContents
End If
cnt = 2
For i = 2 To wS.Cells(Rows.Count, "F").End(xlUp).Row
cnt = cnt + 1
If wS.Cells(i, "F") = "end" Then Exit For
For j = 6 To 8 '//←F列~H列まで//
With .Cells(cnt, "H")
.Value = .Value & wS.Cells(i, j)
End With
Next j
Next i
.Activate
MsgBox "完了"
End With
End Sub
こんな感じで良いのでしょうか?m(_ _)m
tom04様
ご連絡ありがとうございます。
思っていた通りの処理ができました。
出来れば1行ごとの意味を理解して次のステップに
つなげていきたいのですが差支えなければ解説をお願いできないでしょうか?
宜しくお願い致します。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) 複数シート一括作成後に、特定範囲の数式は値で貼り付けしたい 3 2022/10/07 11:18
- Visual Basic(VBA) ExcelVBAに関する質問 3 2023/02/17 10:47
- Excel(エクセル) エクセルVBAでオブジェクトが必要です 2 2022/09/10 16:37
- Visual Basic(VBA) 複数csvを横に追加していくマクロについて 2 2023/04/25 09:19
- Visual Basic(VBA) 別シートのデータを参照して値を入れたい。 まとめデータシートのC列D列の値を商品一覧シートのコードが 7 2022/08/17 13:20
- Excel(エクセル) VBA でvlookup エラーなどは削除したい 8 2022/12/30 04:03
- Visual Basic(VBA) Sheet1のA列にコードB列にメアド、Sheet2のB列にコード一覧とD列にメアド一覧があり、Sh 3 2022/10/19 11:57
- Visual Basic(VBA) コード名シートA列と集計シートA列のコードが一致したら、コード名シートA5からk12の範囲をコピーし 1 2022/08/29 23:46
- Visual Basic(VBA) エクセル VBA 処理スピードを上げたいのですが。 6 2023/03/31 20:52
- Visual Basic(VBA) 形式を選択して貼り付け 以下のコードで「元」シートと「先」シートのA列に同じ値があったら指定範囲をコ 5 2022/11/11 07:30
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルの保護で、列の表示や...
-
ExcelのVlookup関数の制限について
-
文字の色も参照 VLOOKUP
-
エクセルの列の限界は255列以上...
-
VBAで繰り返しコピーしながら下...
-
【VBA】複数のシートの指定した...
-
SUMPRODUCTにて別シートのデー...
-
VLOOKアップ関数の結果の...
-
エクセルの複数シートにあるデ...
-
Excel VBA ピボットテーブルに...
-
エクセル マクロ 標準モジュー...
-
【条件付き書式】countifsで複...
-
ある数値に対して、値を返す数...
-
Excel の複数シートの列幅を同...
-
エクセルで横並びの複数データ...
-
エクセル 日報売上を月報に展開...
-
スプレッドシートでindexとIMPO...
-
【VBA】ピボットテーブルを既存...
-
【VBA】シート名と見出しが一致...
-
アンケート集計をエクセルで行...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
ExcelのVlookup関数の制限について
-
文字の色も参照 VLOOKUP
-
オートフィルタ使用時にCOUNTIF...
-
エクセルの保護で、列の表示や...
-
VBAで繰り返しコピーしながら下...
-
エクセル関数に詳しい方、教え...
-
【条件付き書式】countifsで複...
-
Excel の複数シートの列幅を同...
-
エクセル マクロ 標準モジュー...
-
エクセルで横並びの複数データ...
-
エクセルの列の限界は255列以上...
-
Excelでの並べ替えを全シートま...
-
VLOOKアップ関数の結果の...
-
SUMPRODUCTにて別シートのデー...
-
エクセルで、チェックボックス...
-
Excel VBA ピボットテーブルに...
-
【エクセル】1列のデータを交...
-
エクセルVBAで、ある文字を含ん...
-
エクセルのブック分割マクロを...
-
excel 複数のシートの同じ場所...
おすすめ情報
後半1
'//▼生産者マスタシートの最終行を取得し、「作成一覧」シートのB~G列に値の貼り付け//
lastRow = wS.Cells(Rows.Count, "A").End(xlUp).Row '//←「顧客マスタシート」の最終行はA列で取得//
Range(wS.Cells(2, "A"), wS.Cells(lastRow, "A")).Copy
.Range("B3").PasteSpecial Paste:=xlPasteValues
Range(wS.Cells(2, "B"), wS.Cells(lastRow, "B")).Copy
.Range("C3").PasteSpecial Paste:=xlPasteValues
Range(wS.Cells(2, "N"), wS.Cells(lastRow, "N")).Copy
.Range("D3").PasteSpecial Paste:=xlPasteValues
Range(wS.Cells(2, "I"), wS.Cells(lastRow, "I")).Copy
.Range("E3").PasteSpecial Paste:=xlPasteValues
Range(wS.Cells(2, "J"), wS.Cells(lastRow, "J")).Copy
.Range("F3").PasteSpecial Paste:=xlPasteValues
Range(wS.Cells(2, "K"), wS.Cells(lastRow, "K")).Copy
.Range("G3").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
.Activate
.Range("B3").Select
End With
'生産者マスタの市町村名、住所1、住所2を連結し作成一覧に貼付
Dim j As Long, cnt As Long
Set wS = Worksheets("生産者マスタ")
With Worksheets("作成一覧")
lastRow = .Cells(Rows.Count, "H").End(xlUp).Row
cnt = 2
For i = 2 To wS.Cells(Rows.Count, "F").End(xlUp).Row
cnt = cnt + 1
If wS.Cells(i, "F") = "end" Then Exit For
For j = 6 To 8 '//←F列~H列まで//
With .Cells(cnt, "H")
.Value = .Value & wS.Cells(i, j)
End With
Next j
Next i
.Activate
MsgBox "完了"
End With
Application.CutCopyMode = False
Sheets("作成一覧").Select
Cells(1, 1).Select
End Sub
以上になります。変更点は【顧客マスタ】から
【生産者マスタ】へシート名を変更いたしました。
宜しくお願いします。