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: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 :
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: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" wrote in message
.com...
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 :
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