Excel vbaでアクティブなシート(sheet3)AM列のマイナスの数値を探し、その行のA列の値

Question

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

Qchan1962 · Accepted Answer

こんにちは、
回答が訂正を重ねてバラバラになってしまい分かり難くなってしまったようです。申し訳ない。
＞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行目の日付は実行の日付が自動で入力されますので
空白のまま実行してください。

Qchan1962 · Answer

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

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

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

めぐみん_ · Answer

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

Qchan1962様へ

検索対象列がＡＭ⇒ＡＸになられているような気がします。
質問文の解読ミスでなければ（朝日が昇ると寝ぼける）ですが。

Qchan1962 · Answer

#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(.～で取得したデータを出力しています。

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

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

見直して＃１のコードに問題がある事に気が付きました。
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

独り言
コードを書くよりデバッグが大事と言う事を改めて自身に言い聞かせます。

Qchan1962 · Answer

こんにちは、
出力行が不明です。
取得したデータは、纏めて出力すれば良いのでしょうか？
（ワークシート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に書き込むのが簡単かもしれません

Excel vbaでアクティブなシート(sheet3)AM列のマイナスの数値を探し、その行のA列の値

こんにちは、

こんにちは、

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

#1です

こんにちは、

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング