Array配列について教えてください。

質問

重複を防止する為に当サイトにて伝授頂きました。
付きましては、下記コードの

c_in = Array("A1", "A2", "A3", "A4", "A5") '//処理対照セル名を列記（入力セル）
c_cmp = Array("A1", "A2", "A3", "A4", "A5") '//比較対照セル名を列記（参照セル）

の　Array("A1"・・・) 部分を　A1:A500　若しくは　Ａ列全体　としたいのですが、方法はありますでしょうか？
宜しくお願いいたします。

・・・コード・・・

Sub Code_Check(s_ad As String)
Dim st As Worksheet, rng As Range, flag As Boolean
Dim i As Long, s, c_in, c_cmp
Set rng = ActiveSheet.Range(s_ad)
On Error Resume Next
If rng.Value = "" Then Exit Sub
On Error GoTo 0

c_in = Array("A1", "A2", "A3", "A4", "A5") '//処理対照セル名を列記（入力セル）
c_cmp = Array("A1", "A2", "A3", "A4", "A5") '//比較対照セル名を列記（参照セル）
flag = True

i = LBound(c_in)
'//処理対照セルかどうかを判定
While flag And (i <= UBound(c_in))
If rng.Address = Range(c_in(i)).Address Then flag = False
i = i + 1
Wend
If flag Then Exit Sub
'//ブック内の全シートについて比較
For Each st In Worksheets
For Each s In c_cmp
If st.Range(s).Value = rng.Value Then
If (st.Name <> ActiveSheet.Name) Or (st.Range(s).Address <> rng.Address) Then
MsgBox ("重複エラー！！" & Chr(13) & "" & Chr(13) & "入力した受注番号は当BOOK内に既に存在します！確認して下さい！")
Exit Sub
End If
End If
Next s
Next st
End Sub

回答No.3

xls88 · ベストアンサー

当方ではエラーは再現できませんでしたが不具合がありました。

Set c_in = Range("A1:A500,C1:C500") '//処理対照セル名を列記（入力セル） '★
Set c_cmp = Range("A1:A500,C1:C500") '//比較対照セル名を列記（参照セル） '★

と、指定しても
下記のところでは、A1:A500からC1:C500とならずに、A1:A1000となるようです。

i = 1 '★
'//処理対照セルかどうかを判定
While flag And (i <= c_in.Count) '★
　　If rng.Address = c_in(i).Address Then flag = False
　　i = i + 1
Wend

折角のRange型参照を活用して、下記のようにしてください。
希望が叶うと思います。

'//処理対照セルかどうかを判定
For Each s In c_cmp
　　If rng.Address = s.Address Then flag = False: Exit For
Next

お礼

色々とありがとうございました。
本当に助かりました。
デバックエラーの件は下記のようにVALUE→TEXTに置き換えることで
回避出来ました。

→　If st.Range(s).Text = rng.Text Then

ありがとうございました。

回答No.2

xls88

＞回答番号：No.1 この回答への補足

Excelには「マクロの記録」という素晴らしい道具があります。
A1セルを選択、Shiftキー＋A10セルをクリック
Ctrlキー＋C1セルをクリック
Shiftキー＋C10セルをクリック
といった操作を「マクロの記録」すれば答えが解ると思います。

補足

すみません・・・(>_<)
お恥ずかしい限りです。ありがとうございました。
ところが実行すると下記コードでデバックエラーが出るのですが
原因が何とも判りません。
→　If st.Range(s.Address).Value = rng.Value Then '★
宜しくお願い致します。

回答No.1

xls88

Range型に直します。

Sub Code_Check(s_ad As String)
　　Dim st As Worksheet, rng As Range, flag As Boolean
　　Dim i As Long, s
　　Dim c_in As Range, c_cmp As Range '★

　　Set rng = ActiveSheet.Range(s_ad)
　　On Error Resume Next
　　If rng.Value = "" Then Exit Sub
　　On Error GoTo 0

　　Set c_in = Range("A1:A500") '//処理対照セル名を列記（入力セル） '★
　　Set c_cmp = Range("A1:A500") '//比較対照セル名を列記（参照セル） '★
　　flag = True

　　i = 1 '★
　　'//処理対照セルかどうかを判定
　　While flag And (i <= c_in.Count) '★
　　　　If rng.Address = c_in(i).Address Then flag = False
　　　　i = i + 1
　　Wend
　　If flag Then Exit Sub
　　'//ブック内の全シートについて比較
　　For Each st In Worksheets
　　　　For Each s In c_cmp
　　　　　　If st.Range(s.Address).Value = rng.Value Then '★
　　　　　　　　If (st.Name <> ActiveSheet.Name) Or (st.Range(s.Address).Address <> rng.Address) Then '★
　　　　　　　　　　MsgBox ("重複エラー！！" & Chr(13) & "" & Chr(13) & _
　　　　　　　　　　　　"入力した受注番号は当BOOK内に既に存在します！確認して下さい！")
　　　　　　　　　　Exit Sub
　　　　　　　　End If
　　　　　　End If
　　　　Next s
　　Next st
End Sub

補足

xls88さん
返信ありがとうございます。
バッチリです！助かりました。ありがとうございます。
もうひとつだけ聞きたいのですが・・・
Range("A1:A500") 　←　連続した対象範囲が複数の時は
Range("A1:A500","C1:C500")　←　ここの所をどのようにすれば
いいのでしょうか？
すみませんが、どうか宜しくお願い致します。