Checking a cell's (Row,Column)

[Craig]

Hi there,

Within my cmdInsert code: when I click the cmdInsert i wish the code to
check to see if the activecell is one of 30 predifined cell address's. The
method I know would creat a huge if( and(. I wonder if there is a easier
way.

Example:
If activecell.address = D7 or F7 or H7 or J7 or L7 or D12 or F12 or H12 or
J12 or L12 or D17 or F17 or H17 or J17 or L17 or D22 or F22 or H22 or J22 or
L22 or D27 or F27 or H27 or J27 or L27 or D32 or F32 or H32 or J32 or L32
Then Activecell.value = "Craig"
elseif activecell.address = D8 or F8 or H8 or J8 or..... then
Activecell.value = "Doug"
elseif activecell.address = D9 or F9 or H9 or J9 or..... then
Activecell.value = "Michael"
Is there an easier way to test 7 sets of 30 cells ?


Thanks Craig

 

[keepITcool]

check out select case in VBA help

Also it may be an idea to name the (multiarea) ranges
your going to check.

then again.. following would work

Sub InsertCheck()
Dim rCol As Range
Dim rRow As Range
Dim n As Integer

Set rCol = Range("A:A,F:F,H:H,J:J,L:L")
Set rRow = Range("7:7,12:12,17:17,22:22,27:27,32:32")
n = -1

If Not Intersect(ActiveCell, rCol) Is Nothing Then
For n = 0 To 4
If Not Intersect(ActiveCell, rRow.Offset(n)) Is Nothing Then
Exit For
End If
Next
End If
Select Case n
Case 0: ActiveCell = "Craig"
Case 1: ActiveCell = "Doug"
Case 2: ActiveCell = "Mike"
Case 3: ActiveCell = "Pete"
Case 4: ActiveCell = "Jane"
Case Else: Beep
End Select
End Sub



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Craig wrote :

 

[Craig]

Your code works great... I modified it to include a second set of columns
and if a case was true to offset 2 column, and if it hit a certain column to
go back to "D" or "E"... this is working on a calendar and I'm using it to
schedule staff holidays.
Did I modify this OK... or is it rewritable... Thanks Again for your help,
you save me from using a lot of sloppy code!

Private Sub cmdInsert_Click()

Dim rCol As Range
Dim rRow As Range
Dim n As Integer
Dim I As Integer
For I = 1 To 2
If I = 1 Then Set rCol = Range("D,F:F,H:H,J:J,L:L")
If I = 2 Then Set rCol = Range("E:E,G:G,I:I,K:K,M:M")
Set rRow = Range("7:7,12:12,17:17,22:22,27:27,32:32")
n = -1

If Not Intersect(ActiveCell, rCol) Is Nothing Then
For n = 0 To 3
If Not Intersect(ActiveCell, rRow.Offset(n)) Is Nothing Then
Exit For
End If
Next
End If
If I = 1 Then
Select Case n
Case 0: ActiveCell = "Craig"
Case 1: ActiveCell = "Ron"
Case 2: ActiveCell = "Hemu"
Case 3: ActiveCell = "Gurinder"
'Case Else: Beep
End Select
If ActiveCell <> "" Then ActiveCell.Offset(0, 2).Select
If ActiveCell.Column = 14 Then ActiveCell.Offset(5, -10).Select
ElseIf I = 2 Then
Select Case n
Case 0: ActiveCell = "Debbie"
Case 1: ActiveCell = "Evan"
Case 2: ActiveCell = "Frank"
Case 3: ActiveCell = "George"
'Case Else: Beep
End Select
If ActiveCell <> "" Then ActiveCell.Offset(0, 2).Select
If ActiveCell.Column = 15 Then ActiveCell.Offset(5, -10).Select
End If
Next I
End Sub

 

[keepITcool]

Craig,

further streamlining..
similar to rows use an offset for the columns..
combining iRow and Icol in 1 selectcase..

greetz! Jurgen aka keepITcool.


Private Sub cmdInsert_Click()

Dim rCol As Range
Dim rRow As Range

Dim iRow As Integer
Dim iCol As Integer

Set rCol = Range("D,F:F,H:H,J:J,L:L")
Set rRow = Range("7:7,12:12,17:17,22:22,27:27,32:32")

With ActiveCell
For iCol = 0 To 1
If Not Intersect(.Cells, rCol.Offset(, iCol)) Is Nothing Then
For iRow = 0 To 3
If Not Intersect(.Cells, rRow.Offset(iRow)) Is Nothing Then
GoTo Gotcha
End If
Next
End If
Next
Beep 'or maybe reposition?
Exit Sub

Gotcha:
Select Case iCol * 10 + iRow
Case 0: .Value = "Craig"
Case 1: .Value = "Ron"
Case 2: .Value = "Hemu"
Case 3: .Value = "Gurinder"
Case 10: .Value = "Debbie"
Case 11: .Value = "Evan"
Case 12: .Value = "Frank"
Case 13: .Value = "George"
Case Else: Stop
End Select

.Offset( _
IIf(.Column < 12, 0, IIf(.Row < 32, 5, -25)), _
IIf(.Column < 12, 2, -8)).Activate

End With

End Sub

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Craig wrote :