MATCH_MACRO
- depth:
- 0
前の投稿
-
次の投稿
|
親投稿
-
子投稿なし
|
投稿日時 2010-3-7 18:41
ゲスト
投稿数: 0
Private Sub CommandButton1_Click()
Call CHECK("", "", "", "", -4142, 1, "")
End Sub
Private Sub CommandButton2_Click()
Call CHECK("B", "F", "C", "G", 40, 2, "")
End Sub
Private Sub CommandButton3_Click()
Call CHECK("B", "J", "C", "K", 36, 3, "")
End Sub
Private Sub CommandButton4_Click()
Call CHECK("B", "N", "C", "O", 4, 4, "abc")
End Sub
Private Sub CommandButton5_Click()
Call CHECK("B", "S", "C", "T", 28, 5, "def")
End Sub
----------------------------------------------------
Sub CHECK(START_Column As String, THIRD_Column As String, SECOND_Column As String, FOUR_Column As String, CELL_COLOR As Integer, FLAG As Integer, KANKYO_NAME As String)
'☆変数宣言開始--------------------------------------------------------------------------------------
'メッセージ変数
Dim KANKYO_MSG As String
'オブジェクト変数宣言
Dim START_CELL As Object
Dim TRIM_CELL As Object
'カウント変数宣言
Dim Shori_Gokei As Integer
Dim NO_COUNT_CELL As Integer
'☆変数宣言終了--------------------------------------------------------------------------------------
'☆変数設定開始--------------------------------------------------------------------------------------
'メッセージ変数設定
KANKYO_MSG = ""
'オブジェクト変数設定
Set START_CELL = Range("B8")
'カウント変数設定
NO_COUNT_CELL = 7
'☆変数設定終了--------------------------------------------------------------------------------------
'処理開始------------------------------------
Application.ScreenUpdating = False
START_CELL.Select
Shori_Gokei = ActiveCell.End(xlDown).Row - NO_COUNT_CELL '処理の合計
If FLAG = 1 Then
Range(Cells(START_CELL.Row, START_CELL.Column), Cells(Shori_Gokei + NO_COUNT_CELL, START_CELL.Offset(0, 1).Column)).Select
Selection.Interior.ColorIndex = CELL_COLOR
START_CELL.Select
GoTo Shori_END:
End If
If FLAG = 2 Then
START_CELL.Select
TRIM_CNT = ActiveCell.End(xlDown).Row
Range(Cells(ActiveCell.Row, ActiveCell.Column), Cells(TRIM_CNT, ActiveCell.Offset(0, 1).Column)).Select
For Each TRIM_CELL In Selection
TRIM_CELL.Value = Replace(TRIM_CELL.Value, " ", "")
TRIM_CELL.Value = Replace(TRIM_CELL.Value, " ", "")
START_CELL.Select
Next
End If
For i = 1 To Shori_Gokei
If Cells(i + NO_COUNT_CELL, START_Column) = Cells(i + NO_COUNT_CELL, THIRD_Column) And _
Cells(i + NO_COUNT_CELL, SECOND_Column) = Cells(i + NO_COUNT_CELL, FOUR_Column) Then
Range(Cells(i + NO_COUNT_CELL, START_Column), Cells(i + NO_COUNT_CELL, SECOND_Column)).Interior.ColorIndex = CELL_COLOR
Range(Cells(i + NO_COUNT_CELL, THIRD_Column), Cells(i + NO_COUNT_CELL, FOUR_Column)).Interior.ColorIndex = CELL_COLOR
End If
Next i
If KANKYO_NAME <> "" Then
Cells(1 + NO_COUNT_CELL, FOUR_Column).Offset(0, 1).Select
KANKYO_FLAG = 0
s = 0
Do Until ActiveCell.Value = ""
If ActiveCell.Value <> KANKYO_NAME Then
KANKYO_MSG = KANKYO_MSG & vbCrLf & ActiveCell.Offset(0, -2).Value & " / " & _
ActiveCell.Offset(0, -1).Value & " / " & ActiveCell.Value
Else
End If
ActiveCell.Offset(1, 0).Select
Loop
If Len(KANKYO_MSG) > 0 Then
KANKYO_MSG = "下記のものが" & KANKYO_NAME & "環境ではありません。" & vbCrLf & vbCrLf & _
"オブジェクト名 / タイプ名 / 環境名" & vbCrLf & KANKYO_MSG
Else
KANKYO_MSG = "すべて" & KANKYO_NAME & "環境なので、問題ありません。"
End If
MsgBox KANKYO_MSG
Else
End If
Shori_END:
Application.ScreenUpdating = True
'処理終了------------------------------------
End Sub
Call CHECK("", "", "", "", -4142, 1, "")
End Sub
Private Sub CommandButton2_Click()
Call CHECK("B", "F", "C", "G", 40, 2, "")
End Sub
Private Sub CommandButton3_Click()
Call CHECK("B", "J", "C", "K", 36, 3, "")
End Sub
Private Sub CommandButton4_Click()
Call CHECK("B", "N", "C", "O", 4, 4, "abc")
End Sub
Private Sub CommandButton5_Click()
Call CHECK("B", "S", "C", "T", 28, 5, "def")
End Sub
----------------------------------------------------
Sub CHECK(START_Column As String, THIRD_Column As String, SECOND_Column As String, FOUR_Column As String, CELL_COLOR As Integer, FLAG As Integer, KANKYO_NAME As String)
'☆変数宣言開始--------------------------------------------------------------------------------------
'メッセージ変数
Dim KANKYO_MSG As String
'オブジェクト変数宣言
Dim START_CELL As Object
Dim TRIM_CELL As Object
'カウント変数宣言
Dim Shori_Gokei As Integer
Dim NO_COUNT_CELL As Integer
'☆変数宣言終了--------------------------------------------------------------------------------------
'☆変数設定開始--------------------------------------------------------------------------------------
'メッセージ変数設定
KANKYO_MSG = ""
'オブジェクト変数設定
Set START_CELL = Range("B8")
'カウント変数設定
NO_COUNT_CELL = 7
'☆変数設定終了--------------------------------------------------------------------------------------
'処理開始------------------------------------
Application.ScreenUpdating = False
START_CELL.Select
Shori_Gokei = ActiveCell.End(xlDown).Row - NO_COUNT_CELL '処理の合計
If FLAG = 1 Then
Range(Cells(START_CELL.Row, START_CELL.Column), Cells(Shori_Gokei + NO_COUNT_CELL, START_CELL.Offset(0, 1).Column)).Select
Selection.Interior.ColorIndex = CELL_COLOR
START_CELL.Select
GoTo Shori_END:
End If
If FLAG = 2 Then
START_CELL.Select
TRIM_CNT = ActiveCell.End(xlDown).Row
Range(Cells(ActiveCell.Row, ActiveCell.Column), Cells(TRIM_CNT, ActiveCell.Offset(0, 1).Column)).Select
For Each TRIM_CELL In Selection
TRIM_CELL.Value = Replace(TRIM_CELL.Value, " ", "")
TRIM_CELL.Value = Replace(TRIM_CELL.Value, " ", "")
START_CELL.Select
Next
End If
For i = 1 To Shori_Gokei
If Cells(i + NO_COUNT_CELL, START_Column) = Cells(i + NO_COUNT_CELL, THIRD_Column) And _
Cells(i + NO_COUNT_CELL, SECOND_Column) = Cells(i + NO_COUNT_CELL, FOUR_Column) Then
Range(Cells(i + NO_COUNT_CELL, START_Column), Cells(i + NO_COUNT_CELL, SECOND_Column)).Interior.ColorIndex = CELL_COLOR
Range(Cells(i + NO_COUNT_CELL, THIRD_Column), Cells(i + NO_COUNT_CELL, FOUR_Column)).Interior.ColorIndex = CELL_COLOR
End If
Next i
If KANKYO_NAME <> "" Then
Cells(1 + NO_COUNT_CELL, FOUR_Column).Offset(0, 1).Select
KANKYO_FLAG = 0
s = 0
Do Until ActiveCell.Value = ""
If ActiveCell.Value <> KANKYO_NAME Then
KANKYO_MSG = KANKYO_MSG & vbCrLf & ActiveCell.Offset(0, -2).Value & " / " & _
ActiveCell.Offset(0, -1).Value & " / " & ActiveCell.Value
Else
End If
ActiveCell.Offset(1, 0).Select
Loop
If Len(KANKYO_MSG) > 0 Then
KANKYO_MSG = "下記のものが" & KANKYO_NAME & "環境ではありません。" & vbCrLf & vbCrLf & _
"オブジェクト名 / タイプ名 / 環境名" & vbCrLf & KANKYO_MSG
Else
KANKYO_MSG = "すべて" & KANKYO_NAME & "環境なので、問題ありません。"
End If
MsgBox KANKYO_MSG
Else
End If
Shori_END:
Application.ScreenUpdating = True
'処理終了------------------------------------
End Sub
投票数:51
平均点:5.88
返信する
この投稿に返信する
投稿ツリー
-
MATCH_MACRO (ゲスト, 2010-3-7 18:41)