【アプリ版】サポートOS変更のお知らせ

Excel vbaでアクティブなシート(sheet3)AM列のマイナスの数値を探し、その行のA列の値、C列の値、そのマイナスの値をワークシート4のA列、B列、C列にそれぞれ転記したいです。
どなたかお力添えお願いします!
ワークシート4の1行目は日付けにしてあります。
また、次にその作業をした時には、シートのD列からといったように3列毎ずらしていきたいのですが、可能でしょうか?

gooドクター

A 回答 (5件)

こんにちは、


回答が訂正を重ねてバラバラになってしまい分かり難くなってしまったようです。申し訳ない。
>sheet4のA,Bには値が来るのですが、C列が空欄になってしまいます。(マイナス数値がはいりません)
AM列をAX列として書いてしまったのが原因かと
>次に作業した時にD列から、その次はG列から、といった様に入れられたらとおのうのですが、可能でしょうか?
>ワークシート4の1行目は日付けにしてあります。
日付が入っていれば、これはすでに行っていると思うのですが、、

日付もVBAで入れた方が良いのかも知れませんね。
と言う事で、書き直してみました。

Sub sample()
Dim i As Long, n As Long
Dim MaxRow As Long
Dim Ary()
Dim CHK_SH As Worksheet: Set CHK_SH = Sheets("Sheet3")
Dim OUT_SH As Worksheet: Set OUT_SH = Sheets("Sheet4")
MaxRow = CHK_SH.Cells(Rows.Count, "AM").End(xlUp).Row
If Application.CountIf _
(CHK_SH.Range("AM1:AM" & MaxRow), "<0") <> 0 Then
With CHK_SH
For i = 1 To MaxRow
If IsNumeric(.Cells(i, "AM").Value) And Sgn(.Cells(i, "AM").Value) = -1 Then
ReDim Preserve Ary(2, n)
Ary(0, n) = .Cells(i, "A").Value
Ary(1, n) = .Cells(i, "C").Value
Ary(2, n) = .Cells(i, "AM").Value
n = n + 1
End If
Next
End With
With OUT_SH
For i = 1 To .Cells(1, Columns.Count).Column Step 3
If .Cells(Rows.Count, i).End(xlUp).Row = 1 And .Cells(1, i) = "" Then
n = i
Exit For
End If
Next
.Cells(1, n).Value = Format(Date, "mm月dd日")
.Cells(2, n).Resize(UBound(Ary, 2) + 1, 3) = Application.Transpose(Ary)
End With
Else
MsgBox ("負の値は見つかりませんでした。")
End If
End Sub

sheet4の1行目の日付は実行の日付が自動で入力されますので
空白のまま実行してください。
    • good
    • 1
この回答へのお礼

助かりました

凄すぎます、もぅ脱帽です!!
完璧です!!
ありがとうございます!!
たった5年で肩をなんて、、、足元にでも何10年掛かるのか、、、。
この解読を勉強の良い教材とし、また次の問題に取り掛かりたいと思います。
弟子にとって頂き教えを乞いたいところですが、Qさんもお忙しいと心得ております。
またこのわがままな私の相談を聞いて下さい。もしよろしければ。
それではまたの機会に。
失礼致します。

お礼日時:2021/04/24 17:37

こんにちは、


あらびっくり、AX列でなくAM列ですね。質問文と整合しておりませんので
該当箇所をAMに訂正してください。

スレッドをお借りして
最近、脳内の不良クラスターが増えて強い思い込みや記憶不良のバグを確認しています。
シニアグラスの度を上げたり、サプリなどを投与しているのですが、中々改善されません。

いつも、めぐみん_様には貴重なアドバイスやご指摘を頂き
また回答されている内容を拝見して、独学で視野の狭い私は大変勉強になっております。
めぐみん_様
ご指摘ありがとうございました。
気になる点などございましたら、今後ともよろしくお願い申し上げます
    • good
    • 1
この回答へのお礼

ありがとう

おはようございます、すいません、やっとお返事する事が出来ます。
>#1にも書きましたが、出力先のSheet4は、印刷や在庫切れ発注業務などに使用されるのでしょうか?

A.ここのマイナスは在庫切れではなく、カンバンの出し忘れ、もしくはその遅れを指し示す指標となります。
各部品の収容数、各製品の生産数、各製品に対する部品の使用数をその日毎に確認する事で、人的ミス(カンバンの出し忘れによるライン停止)を防止したいと考えております。
IOTの時代になんとアナログなことを、、、この様なシステムを作ってくれる会社も沢山あるとは思いますが。
まだ試作段階ですが、私にとっておよそ初めてとなる試みにワクワクして楽しく、充実した毎日を送っております。VBA,関数、毎日勉強しているなかで、Qさまや、めぐみんさまやその他たくさんの方々のお力をお借り(ご迷惑をおかけし)しておりまして、本当に感謝の言葉しかございません!!

>VBA関数のSgnは、シート関数のSIGN関数と同じだったと思いますが

A.頂いたコードで完璧でした!!

私が実行しますと、sheet4のA,Bには値が来るのですが、C列が空欄になってしまいます。(マイナス数値がはいりません)。私のコードに間違いがあるのだと思うのですが、どこなのか、、、。
これもほんとうに私の伝える能力不足なのですが、次に作業した時にD列から、その次はG列から、といった様に入れられたらとおのうのですが、可能でしょうか?

セル範囲指定の基本がまだまだ理解出来ておりません。すいません、精進して参ります。5年真剣に打ち込んでここの皆さまと肩を並べる事が出来たらと、、、。

お体ご自愛下さい。

お礼日時:2021/04/24 08:43

ちょっとお邪魔致します。



Qchan1962様へ

検索対象列がAM⇒AXになられているような気がします。
質問文の解読ミスでなければ(朝日が昇ると寝ぼける)ですが。
    • good
    • 2
この回答へのお礼

あなたに会えてよかった

いつもありがとうございます!
ほんとにど素人のくせにやりたい事ばかりで皆様にご迷惑をお掛けしてしまっているかと思いますが、頑張って行きたいと思います。

お礼日時:2021/04/23 17:55

#1です


コピペで良く確認せずに投稿してしまいました。
お詫びいたします。
With ActiveSheet Sheets("Sheet3") については
With Sheets("Sheet3") です。

以下 End Withまで . オブジェクトなどは、Sheets("Sheet3")シートのオブジェクトなどになります。

With Sheets("Sheet3") 内はデータの取得部分になります。

If IsNumeric(.Cells(i, "AX").Value) And Sgn(.Cells(i, "AX").Value) = -1 Then
AX列の値が数値に出来る値で且つ負の値ならば
配列Aryに代入しています。

次の With Sheets("ワークシート4") 
これは、With Sheets("Sheet4")のようですね。

With Sheets("Sheet4")
For i = 1 To .Cells(1, Columns.Count).End(xlToLeft).Column Step 3
If .Cells(Rows.Count, i).End(xlUp).Row = 1 Then
n = i
Exit For
End If
Next

は、1行目に日付があるとの事なので一番左列まで1列目(A列)から3つ飛びに最終行を取得して1が返ってきた場合、その行は1行目が使われている、もしくは何も入力されていない列と判断してループを抜けて、その時変数nにiの値(列番号)を代入して出力先を取得しています。

If UBound(Ary, 2) > 0 Then 以下は出力部分になります。
取得した列番号の2行目にIf IsNumeric(.~で取得したデータを出力しています。

#1にも書きましたが、出力先のSheet4は、印刷や在庫切れ発注業務などに使用されるのでしょうか?その場合、示したコードで良いと思いますが、、
あ、A列品番を出力しているのですから、纏めて出力でOKかな

VBA関数のSgnは、シート関数のSIGN関数と同じだったと思いますが
整数の時 1  負の数値の時 -1 それ以外の時0だったかと
この辺は調べてみてください。
私の説明より、正確でよりわかり易い参考サイトが沢山ありますので
Transpose関数、配列については2次配列で調べてみてくださいね

見直して#1のコードに問題がある事に気が付きました。
If UBound(Ary, 2) > 0 Then は、機能しない訳のわからないコードです。

With Sheets("Sheet4")以下最後までを書きに書き換えて下さい。

With Sheets("Sheet4")
For i = 1 To .Cells(1, Columns.Count).End(xlToLeft).Column Step 3
If .Cells(Rows.Count, i).End(xlUp).Row = 1 Then
n = i
Exit For
End If
Next
On Error GoTo Ary_Err
.Cells(2, n).Resize(UBound(Ary, 2) + 1, 3) = Application.Transpose(Ary)
End With
Exit Sub
Ary_Err:
MsgBox ("在庫切れデータを取得できませんでした。")
End Sub

独り言
コードを書くよりデバッグが大事と言う事を改めて自身に言い聞かせます。
    • good
    • 1
この回答へのお礼

あなたに会えてよかった

こんにちは。
いつもお世話になっております。
まだ仕事中でこの頂きましたコードを書けておりません、しかしながらやはり僕には、と言うか、皆さんエキスパートはこのレベルなのでしょうか、、、。
ファンタジスタとお呼びしたい位です!
ほんとうに、いつもお助け頂きありがとうございます!
自分でも書ける様になりたいと思いながら、、、。
また確認後と言うことで、すいません失礼致します!

お礼日時:2021/04/23 17:54

こんにちは、


出力行が不明です。
取得したデータは、纏めて出力すれば良いのでしょうか?
(ワークシート4の2,3,・・・行)

取敢えずサンプル

Sub a()
'Excel vbaでアクティブなシート(sheet3)AM列のマイナスの数値を探し、
'その行のA列の値、C列の値、そのマイナスの値をワークシート4のA列、B列、C列にそれぞれ転記したいです。

Dim i As Long, n As Long
Dim MaxRow As Long
Dim Ary()
With ActiveSheet 'Sheets("Sheet3")
With .UsedRange
MaxRow = .Rows(.Rows.Count).Row
End With
For i = 1 To MaxRow
If IsNumeric(.Cells(i, "AX").Value) And Sgn(.Cells(i, "AX").Value) = -1 Then
ReDim Preserve Ary(2, n)
Ary(0, n) = .Cells(i, "A").Value
Ary(1, n) = .Cells(i, "C").Value
Ary(2, n) = .Cells(i, "AX").Value
n = n + 1
End If
Next
End With
With Sheets("ワークシート4")
For i = 1 To .Cells(1, Columns.Count).End(xlToLeft).Column Step 3
If .Cells(Rows.Count, i).End(xlUp).Row = 1 Then
n = i
Exit For
End If
Next
If UBound(Ary, 2) > 0 Then
.Cells(2, n).Resize(UBound(Ary, 2) + 1, 3) = Application.Transpose(Ary)
End If
End With
End Sub

アクティブなシート(sheet3)でない場合、問題が生じるので
With ActiveSheet
をWith ActiveSheet Sheets("Sheet3") としてください

取得行、出力行を同じにする場合は、配列に値を入れるところで
(取得時に)ワークシート4に書き込むのが簡単かもしれません
    • good
    • 1
この回答へのお礼

こんにちは。お待ちしておりました、Qさん。
すいません、いつも説明不足で、私のコードに対する理解不足であると反省しております。日々勉強をしていますが(今日は1日中インターネット喫茶で勉強していまいた)、どーも容量不足の様です。。。
sheet3のA列には品番、C列には品名、AM列にはその在庫数となります。
在庫がマイナスとなった品番、品名、在庫の値をその作業(集計作業)をした日付毎にsheet4に残したいのです。オートシェイプにこのマクロを登録し使用したいと考えています。
sheet4のA列に品番、B列に品名、C列にその在庫数もっと良い管理方法もあるかなとも思う所でもありますが。
(”シート4”)だけが("sheet4")の誤りでしたので、その修正をし
With ActiveSheet 'Sheets("Sheet3") の ’ を削除しコンパイルすると
With ActiveSheet Sheets("Sheet3")
の部分が構文エラーになってしまいます。
どうか、お力添えをお願いしたいのですが。。。

お礼日時:2021/04/22 19:45

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

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

gooドクター

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

人気Q&Aランキング