PDA

View Full Version : [SOLVED] Intersect method question



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

Norie
09-30-2005, 01:28 PM
Joseph

How can a range on 1 sheet intersect with a range on another?

malik641
09-30-2005, 01:37 PM
Joseph

How can a range on 1 sheet intersect with a range on another?Sorry, I guess I wasn't too clear.

There is a Private Sub Worksheet_Change on a sheet named "ClinPath" that calls a procedure that does some checks to some ranges on a sheet named "Freezer Diagrams". And on that sheet "Freezer Diagrams" is where I am trying to intersect the two ranges.

The Two ranges Being:

1. The entire column of whatever cell is being check in the for loop in ranges(CP026, CP027, CP055) that I specified in the beginning of the code.

2. The entire row #106 on the "Freezer Diagrams" sheet.

The intersect is just a cell with text, which I want for the message box.

Bob Phillips
09-30-2005, 09:15 PM
Surely, you just reference the sheet?

For example



With Worksheets("Sheet2")
If Not Intersect(.Range("A1:H10"), .Range("A5")) Is Nothing Then
MsgBox "hello"
End If
End With

malik641
10-01-2005, 07:20 AM
Surely, you just reference the sheet?

For example



With Worksheets("Sheet2")
If Not Intersect(.Range("A1:H10"), .Range("A5")) Is Nothing Then
MsgBox "hello"
End If
End With

xld,
Awesome, that works great :clap:
I'm surpised I didn't think of that, but I guess looking at my project yesterday for 8 hours trying to make it run faster kinda fried my brain.

Thanks again!