malik641
09-30-2005, 11:43 AM
How do you use the intersection method if your macro is referencing another worksheet in the workbook?
I keep getting an Application Defined error OR 'Range' of '_Global' object failed OR Object Required OR Method of 'Intersect' of '_Global' failed for each attempt I make...:banghead: :banghead:
Here's what I got:
Option Explicit
Sub OverFlow()
Dim cell As Range
Dim ShelfNum As Variant
Dim FreezerRow As Variant
Dim FreezerCol As Variant
Dim Msg As Variant
Static CP026 As Range
Static CP027 As Range
Static CP055 As Range
'Ranges for calculated fields for each Freezer
Set CP026 = Sheets("Freezer Diagrams").Range("B49:H82")
Set CP027 = Sheets("Freezer Diagrams").Range("K49:Q82")
Set CP055 = Sheets("Freezer Diagrams").Range("B152:BC154")
'Searches for OverFlow of Study #s and Displays a message stating which Freezer is full and where
For Each cell In CP026
Msg = ""
Msg = "There are too many study #s in freezer:" & vbCrLf & "AF004" & vbCrLf & vbCrLf & "Location:" & vbCrLf
If cell.Value > 20 Then
FreezerRow = cell.Row
FreezerCol = cell.Column
Select Case FreezerRow
Case Is = 49: Msg = Msg & "1st Shelf" & vbCrLf
Case Is = 60: Msg = Msg & "2nd Shelf" & vbCrLf
Case Is = 71: Msg = Msg & "3rd Shelf" & vbCrLf
Case Is = 82: Msg = Msg & "4th Shelf" & vbCrLf
End Select
Select Case FreezerCol
Case Is = 2: Msg = Msg & "1st Column"
Case Is = 4: Msg = Msg & "2nd Column"
Case Is = 6: Msg = Msg & "3rd Column"
Case Is = 8: Msg = Msg & "4th Column"
End Select
MsgBox Msg, vbOKOnly + vbExclamation, "Study # Overflow"
End If
Next cell
For Each cell In CP027
Msg = ""
Msg = "There are too many study #s in freezer:" & vbCrLf & "AF005" & vbCrLf & vbCrLf & "Location:" & vbCrLf
If cell.Value > 20 Then
FreezerRow = cell.Row
FreezerCol = cell.Column
Select Case FreezerRow
Case Is = 49: Msg = Msg & "1st Shelf" & vbCrLf
Case Is = 60: Msg = Msg & "2nd Shelf" & vbCrLf
Case Is = 71: Msg = Msg & "3rd Shelf" & vbCrLf
Case Is = 82: Msg = Msg & "4th Shelf" & vbCrLf
End Select
Select Case FreezerCol
Case Is = 11: Msg = Msg & "1st Column"
Case Is = 13: Msg = Msg & "2nd Column"
Case Is = 15: Msg = Msg & "3rd Column"
Case Is = 17: Msg = Msg & "4th Column"
End Select
MsgBox Msg, vbOKOnly + vbExclamation, "Study # Overflow"
End If
Next cell
For Each cell In CP055
Msg = ""
Msg = "There are too many study #s in freezer:" & vbCrLf & "CP055" & vbCrLf & vbCrLf & "Location:" & vbCrLf
If cell.Value > 56 Then
FreezerRow = cell.Row
FreezerCol = cell.Column
'ShelfNum = Intersect(cell.EntireColumn, Range("106:106")).Value 'First Attempt: Method of Intersect Failed
'ShelfNum = Intersect(cell.EntireColumn, Sheets("Freezer Diagrams").Range("106:106")).Value 'Second Attemp:
'No error, just doesn't work for second attempt
If FreezerCol >= 3 And FreezerCol <= 19 Then
Select Case FreezerRow
Case Is = 152: Msg = Msg & "Rack 1" & vbCrLf & ShelfNum
Case Is = 153: Msg = Msg & "Rack 2" & vbCrLf & ShelfNum
Case Is = 154: Msg = Msg & "Rack 3" & vbCrLf & ShelfNum
End Select
End If
If FreezerCol >= 25 And FreezerCol <= 37 Then
Select Case FreezerRow
Case Is = 152: Msg = Msg & "Rack 4" & vbCrLf & ShelfNum
Case Is = 153: Msg = Msg & "Rack 5" & vbCrLf & ShelfNum
End Select
End If
If FreezerCol >= 43 And FreezerCol <= 55 Then
Select Case FreezerRow
Case Is = 152: Msg = Msg & "Rack 6" & vbCrLf & ShelfNum
Case Is = 153: Msg = Msg & "Rack 7" & vbCrLf & ShelfNum
End Select
End If
MsgBox Msg, vbOKOnly + vbExclamation, "Study # Overflow"
End If
Next cell
End Sub
This macro is called from a different worksheet than what it is checking, if that matters.
Anybody have any suggestions??? :dunno
I keep getting an Application Defined error OR 'Range' of '_Global' object failed OR Object Required OR Method of 'Intersect' of '_Global' failed for each attempt I make...:banghead: :banghead:
Here's what I got:
Option Explicit
Sub OverFlow()
Dim cell As Range
Dim ShelfNum As Variant
Dim FreezerRow As Variant
Dim FreezerCol As Variant
Dim Msg As Variant
Static CP026 As Range
Static CP027 As Range
Static CP055 As Range
'Ranges for calculated fields for each Freezer
Set CP026 = Sheets("Freezer Diagrams").Range("B49:H82")
Set CP027 = Sheets("Freezer Diagrams").Range("K49:Q82")
Set CP055 = Sheets("Freezer Diagrams").Range("B152:BC154")
'Searches for OverFlow of Study #s and Displays a message stating which Freezer is full and where
For Each cell In CP026
Msg = ""
Msg = "There are too many study #s in freezer:" & vbCrLf & "AF004" & vbCrLf & vbCrLf & "Location:" & vbCrLf
If cell.Value > 20 Then
FreezerRow = cell.Row
FreezerCol = cell.Column
Select Case FreezerRow
Case Is = 49: Msg = Msg & "1st Shelf" & vbCrLf
Case Is = 60: Msg = Msg & "2nd Shelf" & vbCrLf
Case Is = 71: Msg = Msg & "3rd Shelf" & vbCrLf
Case Is = 82: Msg = Msg & "4th Shelf" & vbCrLf
End Select
Select Case FreezerCol
Case Is = 2: Msg = Msg & "1st Column"
Case Is = 4: Msg = Msg & "2nd Column"
Case Is = 6: Msg = Msg & "3rd Column"
Case Is = 8: Msg = Msg & "4th Column"
End Select
MsgBox Msg, vbOKOnly + vbExclamation, "Study # Overflow"
End If
Next cell
For Each cell In CP027
Msg = ""
Msg = "There are too many study #s in freezer:" & vbCrLf & "AF005" & vbCrLf & vbCrLf & "Location:" & vbCrLf
If cell.Value > 20 Then
FreezerRow = cell.Row
FreezerCol = cell.Column
Select Case FreezerRow
Case Is = 49: Msg = Msg & "1st Shelf" & vbCrLf
Case Is = 60: Msg = Msg & "2nd Shelf" & vbCrLf
Case Is = 71: Msg = Msg & "3rd Shelf" & vbCrLf
Case Is = 82: Msg = Msg & "4th Shelf" & vbCrLf
End Select
Select Case FreezerCol
Case Is = 11: Msg = Msg & "1st Column"
Case Is = 13: Msg = Msg & "2nd Column"
Case Is = 15: Msg = Msg & "3rd Column"
Case Is = 17: Msg = Msg & "4th Column"
End Select
MsgBox Msg, vbOKOnly + vbExclamation, "Study # Overflow"
End If
Next cell
For Each cell In CP055
Msg = ""
Msg = "There are too many study #s in freezer:" & vbCrLf & "CP055" & vbCrLf & vbCrLf & "Location:" & vbCrLf
If cell.Value > 56 Then
FreezerRow = cell.Row
FreezerCol = cell.Column
'ShelfNum = Intersect(cell.EntireColumn, Range("106:106")).Value 'First Attempt: Method of Intersect Failed
'ShelfNum = Intersect(cell.EntireColumn, Sheets("Freezer Diagrams").Range("106:106")).Value 'Second Attemp:
'No error, just doesn't work for second attempt
If FreezerCol >= 3 And FreezerCol <= 19 Then
Select Case FreezerRow
Case Is = 152: Msg = Msg & "Rack 1" & vbCrLf & ShelfNum
Case Is = 153: Msg = Msg & "Rack 2" & vbCrLf & ShelfNum
Case Is = 154: Msg = Msg & "Rack 3" & vbCrLf & ShelfNum
End Select
End If
If FreezerCol >= 25 And FreezerCol <= 37 Then
Select Case FreezerRow
Case Is = 152: Msg = Msg & "Rack 4" & vbCrLf & ShelfNum
Case Is = 153: Msg = Msg & "Rack 5" & vbCrLf & ShelfNum
End Select
End If
If FreezerCol >= 43 And FreezerCol <= 55 Then
Select Case FreezerRow
Case Is = 152: Msg = Msg & "Rack 6" & vbCrLf & ShelfNum
Case Is = 153: Msg = Msg & "Rack 7" & vbCrLf & ShelfNum
End Select
End If
MsgBox Msg, vbOKOnly + vbExclamation, "Study # Overflow"
End If
Next cell
End Sub
This macro is called from a different worksheet than what it is checking, if that matters.
Anybody have any suggestions??? :dunno