VBAで倉庫管理をしたいのですが……

質問

シート１に次のようなランダムな入力を行って、
それを倉庫に模したシート２に表示したいと思っています。
（下図参照）

色々な人のアドバイスを受けて、関数で表示することはできました。
シート２のセルに入力した関数
=IF(COUNTIF(入力帳票!$H$1:INDIRECT("入力帳票!F"&IF($M$1<MIN(入力帳票!$A:$A),1,MATCH($M$1,入力帳票!$A:$A,1))),ADDRESS(ROW(),COLUMN(),4)&"*")=0,"",INDEX(入力帳票!$B:$B,MATCH(ADDRESS(ROW(),COLUMN(),4)&"*",入力帳票!$F:$F,0)))

今度は、シート１で指定した範囲に、色をつけたいと思います。
そこで、以下のように書いてみましたがエラーになります。
どう修正すればいいのでしょうか？
よろしくお願いします。

Sheet2をアクティブにして
Sub 品番色分け()
For n = 2 To 1000
Range("WorksheetFunction.INDIRECT(Sheet1!H & Format(n))").Select
If Worksheet("Sheet1").Cells(n, 2).varue Lilke "*A" or "*H"Then
Selection.Interior
.ColorIndex = 19
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
ElseIf Worksheet("Sheet1").Cells(n, 2).varue Lilke "*B" Then
Selection.Interior
.ColorIndex = 40
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
ElseIf Worksheet("Sheet1").Cells(n, 2).varue Lilke "*F" Then
Selection.Interior
.ColorIndex = 38
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
ElseIf Worksheet("Sheet1").Cells(n, 2).varue Lilke "*C" Then
Selection.Interior
.ColorIndex = 35
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End If
Next

End Sub

最近VBAやら関数やらの本を読み始めたばかりで、混乱しています。
よろしくお願いします。

回答No.2

mt2008 · ベストアンサー

いっぺんに作りこまず、少しづつ動作を確認しながら作っていく方が良いですよ。
もとのコードだと、最初のRange().Selctの箇所でエラーになっている筈です。
こんな感じに作り直してみました。

Sub 品番色分け()
　　For n = 2 To 1000
　　　　sSelectAddress = Sheets("Sheet1").Cells(n, 5).Text 'Sheet1 H列の文字列
　　　　If sSelectAddress <> "" Then
　　　　　　Range(sSelectAddress).Select
　　　　　　nColor = 0 'ColorIndex用変数初期化
　　　　　　sItem = Right(Sheets("Sheet1").Cells(n, 2), 1) '商品名の右の1文字
　　　　　　Select Case sItem
　　　　　　　　Case "A", "H"
　　　　　　　　　　nColor = 19
　　　　　　　　Case "B"
　　　　　　　　　　nColor = 40
　　　　　　　　Case "F"
　　　　　　　　　　nColor = 38
　　　　　　　　Case "C"
　　　　　　　　　　nColor = 35
　　　　　　End Select

　　　　　　If nColor <> 0 Then 'ColorIndex用変数が0以外の時に塗りつぶし
　　　　　　　　With Selection.Interior
　　　　　　　　　　.ColorIndex = nColor
　　　　　　　　　　.Pattern = xlSolid
　　　　　　　　　　.PatternColorIndex = xlAutomatic
　　　　　　　　End With
　　　　　　End If
　　　　End If
　　Next
End Sub

補足

mt2008さん、ありがとございます。
なるほど、変数とはこのように使うものなのですね。
まだためしてはいませんが、本当に参考になります。
ごらんのように、なにしろ文法も何も全く知らずに
ネットを参考に、あれやこれや書いてみているだけなので
ひどいものです。
かなり恥ずかしくなってきました。
さっそく試してみたいと思います。
ところで、このままコピペじゃだめですよね？
コメントは、コメントアウトしてからですよね？

回答No.4

mt2008

ANo.2です。
一応コピペでも動くはずです。コメントは消す必要ありません。

蛇足ですが、VBAの場合は「'」以降はコメントとなり、コードの実行には無関係となります。
また「コメントアウト」とは、プログラムコードを部分的・一時的に実行したくない場合にそのコードの先頭に「'」を付けてコメント化する事を言います。

お礼

mt2008さん　ありがとうございます。
うまく動きました。
ついでに、関数でやっていた商品名の表示も一緒にやってしまおうと
以下のようなコードにしてみました。

　　Dim strAddr As String
　　Dim comName As String
　　Dim sItem As String
　　Dim nColor As Long

Sub 色分け2()
　　For n = 2 To 1000
　　　　strAddr = Sheets("Sheet1").Cells(n, 8).Text 'Sheet1 H列の文字列
　　　　comName = Sheets("Sheet1").Cells(n, 2).Value

　　　　If strAddr <> "" Then
　　　　　　Sheets("Sheet2").Range(strAddr).Select
　　　　　　Sheets("Sheet2").Range(strAddr).Value = comName
　　　　　　nColor = 0 'ColorIndex用変数初期化
　　　　　　sItem = Left(comName, 1) '商品名の左の1文字
　　　　　　Select Case sItem
　　　　　　　　Case "M", "P"
　　　　　　　　　　nColor = 19
　　　　　　　　Case "J"
　　　　　　　　　　nColor = 40
　　　　　　　　Case "A"
　　　　　　　　　　nColor = 38
　　　　　　　　Case "S"
　　　　　　　　　　nColor = 35
　　　　　　　　Case "K"
　　　　　　　　　　nColor = 24
　　　　　　　End Select

　　　　　　If nColor <> 0 Then 'ColorIndex用変数が0以外の時に塗りつぶし
　　　　　　　　With Selection.Interior
　　　　　　　　　　.ColorIndex = nColor
　　　　　　　　　　.Pattern = xlSolid
　　　　　　　　　　.PatternColorIndex = xlAutomatic
　　　　　　　　End With
　　　　　　End If
　　　　End If
　　Next
End Sub

本当にありがとうございました。
ところで、これにさらに、Sheet1のＡ列にある日付で管理したいと思っています。
Sheet2のどこかに（例えばＡ１に）12/5などと入力すると
それ以前のデータが参照されるというふうにすれば、
過去の倉庫配置も復元できる事になります。
その場合、

　Dim logDate AS Date
logDate = Sheets("Sheet2").Range("A1").value
IF logDate > Sheets("Sheet1").Cells(n, 1).value Then

として、これを先ほどのコードのどこに書けばいいのでしょうか？
　どうもうまく動かないのです。

回答No.3

tsubuyuki

回答番号１番です。

> Range("WorksheetFunction.INDIRECT(Sheet1!H & Format(n))").Select

あと、気になるのはやはりココですね。
単純に、
　　Range(Sheets("Sheet1").Range("H" & n).Value).Select
これでもきっと上手くいきますよ。
Ｈｎセルの内容が全角でも半角でもＯＫです。

ただし、質問の添付図を見る限りＨ列は全部空白ですから、
どちらにしてもエラーで止まります。
「Ｈ列が空白じゃなかったら」と言う条件付けも必要かもしれませんね。

同様に、Ｈ列が「セル番地を示す文字列」ではない場合もエラーで止まります。
これは上記条件では処理できません。
なので、Ｈ列が空白だったり、セル番地じゃなかったりしてエラーが出てしまったら
その行をスキップするような仕組みも欲しいところですね。

他にも色々・・例えば、
転記のミスかもしれませんが、
誤）Worksheet("Sheet1")
正）Worksheets("Sheet1")

誤）Lilke
正）Like
この辺りだとか、

> If Worksheets("Sheet1").Cells(n, 2).Value Like "*A" Or "*H" Then
ここもこのままでは通りませんので、
　　　　 If Worksheets("Sheet1").Cells(n, 2).Value Like "*A" Or Worksheets("Sheet1").Cells(n, 2).Value Like "*H" Then
こうする必要がありますね。

> Selection.Interior
> .ColorIndex = 35
> .Pattern = xlSolid
> .PatternColorIndex = xlAutomatic
ここもこれだけでは不完全ですので、Withを補って
　　　　With Selection.Interior
　　　　　　　　.ColorIndex = 35
　　　　　　　　.Pattern = xlSolid
　　　　　　　　.PatternColorIndex = xlAutomatic
　　　　End With
こうしないと上手く反応してくれません。

欲を言い出すとキリがありませんが、
例えば、こんな感じにするとちょっと早くなるかもしれません。
ザックリとしたモノで申し訳ないですが・・・

Sub 品番色分け_2()
Dim n As Integer, CI As Integer
Dim MyRange As Range　　　　
'以上、変数型を宣言

On Error Resume Next　　　　'エラーの時は次の処理

　　　　For n = 2 To 1000
　　　　　　　　Set MyRange = Range(Worksheets("Sheet1").Range("H" & n).Value)
　　　　　　　　If Not MyRange Is Nothing Then
　　　　　　　　　　　　If Worksheets("Sheet1").Cells(n, 2).Value Like "*A" Then
　　　　　　　　　　　　　　　　CI = 19
　　　　　　　　　　　　ElseIf Worksheets("Sheet1").Cells(n, 2).Value Like "*H" Then
　　　　　　　　　　　　　　　　CI = 19
　　　　　　　　　　　　ElseIf Worksheets("Sheet1").Cells(n, 2).Value Like "*B" Then
　　　　　　　　　　　　　　　　CI = 40
　　　　　　　　　　　　ElseIf Worksheets("Sheet1").Cells(n, 2).Value Like "*F" Then
　　　　　　　　　　　　　　　　CI = 38
　　　　　　　　　　　　ElseIf Worksheets("Sheet1").Cells(n, 2).Value Like "*C" Then
　　　　　　　　　　　　　　　　CI = 35
　　　　　　　　　　　　End If

　　　　　　　　　　　　With MyRange.Interior
　　　　　　　　　　　　　　　　.ColorIndex = CI
　　　　　　　　　　　　　　　　.Pattern = xlSolid
　　　　　　　　　　　　　　　　.PatternColorIndex = xlAutomatic
　　　　　　　　　　　　End With

　　　　　　　　　　　　Set MyRange = Nothing
　　　　　　　　End If
　　　　Next
End Sub

もちろん、これがベストではありません。
参考程度に、違いを見て頂ければ良いかと思います。

お礼

ありがとうございました
無事動きました。
コードはいかようにも書けるのですね。
tsubuyukiさんのコードは、当初の私のコードに近いので
理解しやすく思えました。
Set　ですとか、勉強しなければならないものも増えました。
しかし、大変参考になりました。
重ねてお礼を申し上げます。
ありがとうございました。

補足

tsubuyukiさん、重ねてのご回答、ありがとうございます。
ご指摘のＨ列ですが、Ｅ列の間違いです(^^;
スペルミスや転記ミスどころか、文法も何もまったくわからずに
無謀にも書いてしまったコードで、本当にお恥ずかしい。
ただ、必要に迫られていて、焦っております。
若い頃にちゃんと勉強しておけばと今さらながら反省です。
しかし、本当に参考になります。
さっそく試してみようと思います。
それからまた補足質問をしたいと思いますので、よろしくお願いします。

回答No.1

tsubuyuki

コードはよく見ていませんし、どの辺りでエラーが出るのかわからないのですが。

誤）varue
正）Value

これだけの問題だったりしませんか？

ところで、この程度なら条件付き書式を使った方が早いのでは？とも思いますが、
その辺りはどうなのでしょう？