Select Case (Choosing Columns)

[Cydney]

I have a process I am developing that should highlight a certain column
(user's choice) and highlight (shade) it indicating the current week. I want
it to shade that column PLUS the column (week) next to it. I also have a
total column that sums across the weeks of the month. This is repeated for
the entire year.

The "Case" shown below (I thought) should eliminate the selection of the
total columns plus the first 3 descriptive columns. It does not.. After I get
it to eliminate that, I need it to move over to the right one column and
highlight that column instead.

How can I write this line so that it does NOT allow these columns to be
selected (highlighted)?

Select Case MyColNum
Case Is <> 1, 2, 3, 9, 15, 21, 27, 33, 39, 45, 51, 57, 63, 69, Is < 75
GoTo RunMyMonths


--
Thank you, cs
~~~~~~~~~~~~~~~~~~~~~~~~
"What lies behind us and what lies before us are tiny matters compared to
what lies within us."
~ Ralph Waldo Emerson

 

[JLGWhiz]

I am guessing that you have a user doing something that supplies a value to
myColNum, like an InputBox or some other control method. If I guessed right
then
put a label just before that line of code like this:

TRYAGAIN:

'The line of code that assigns the value to myColNum
Select Case MyColNum
Case Is <> 1, 2, 3, 9, 15, 21, 27, 33, 39, 45, 51, 57, 63, 69, Is <
75
MsgBox "You cannot select that column"
GoTo TRYAGAIN:

This creates a control loop that forces the user to provide an acceptable
column number. You could include the prohibited column numbers in the text
of the message box so the user does not have to keep guessing.

 

[Cydney]

Yes, I've done the input box as you guessed and the user is aware of which
columns not to select. The delima lies more in the highlighting of the
column. I'm having a hard time getting it to pass over the month total column.

Does "Is < 75" override the selected column numbers in the CASE statement?
--
Thank you, cs
~~~~~~~~~~~~~~~~~~~~~~~~
"What lies behind us and what lies before us are tiny matters compared to
what lies within us."
~ Ralph Waldo Emerson

 

[JLGWhiz]

You can set the case statementment up so that if the case is true and the
column number is off limits, it will ask for an acceptable column number. If
a different case is true and the column number is acceptable then it will
continue to execute the next line of code.

You can set your case criteria (column numbers as less than <, greater than

, from -to 3 To 6, etc. Case is pretty liberal like that. You do not need

to use Is < 75, just < 75 will include 1 To 74. The comma in the case
criteria acts as an "Or" operator.

I'm not sure if I am answering the question, but if you still can't get to
work, post back.

 

[OssieMac]

Try testing the other way. That is test if it does NOT meet all conditions
and then use Else as meets all conditions. Note You used < 75 as invalid
therfore I assume 74 is valid so I used > 74 so that 74 remains valid.

Select Case MyColNum
Case 1, 2, 3, 9, 15, 21, 27, 33, 39, 45, 51, 57, 63, 69, Is > 74
MsgBox MyColNum & " does NOT meet column criteria"
Case Else
MsgBox MyColNum & " does meet column criteria"
End Select

 

[JLGWhiz]

correction on the < 75, the Is operator is needed, but if you don't put it in
then VBA will insert it for you.

 

[Cydney]

I think I was trying to make it too complicated. Here's what I ended up with
that seems to work.

If MyColNum < 4 Or MyColNum > 74 Then
MyResp = MsgBox("Wrong column selected. Try again.",17)
If MyResp = vbCancel Then GoTo ExitMyMonths
If MyResp = vbOK Then GoTo SelColumn
End If

Select Case MyColNum
Case 9, 15, 21, 27, 33, 39, 45, 51, 57, 63, 69
MyResp = MsgBox("Wrong column selected. Try again",17) = vbCancel
If MyResp = True Then GoTo ExitMyMonths
If MyResp = False Then GoTo SelColumn
Case Else
GoTo RunMyMonths
End Select
--
Thank you, cs
~~~~~~~~~~~~~~~~~~~~~~~~
"What lies behind us and what lies before us are tiny matters compared to
what lies within us."
~ Ralph Waldo Emerson

 

[Dana DeLouis]

Case Is <> 1, ...., Is < 75

Hi. This is just a logic error, as you are allowing "All" numbers.
(The union of these two statements alone allow all numbers)

The response from a MsgBox being True/False could be a little confusing
since these are not typical return values.
Perhaps a slightly different way...

Const Msg1 As String = "Wrong column selected. Try again."

Select Case MyColNum
Case Is < 4, 9, 15, 21, 27, 33, 39, 45, 51, 57, 63, 69, Is > 74
MyResp = MsgBox(Msg1, vbCritical + vbOKCancel)
If MyResp = vbOK Then GoTo SelColumn
If MyResp = vbCancel Then GoTo ExitMyMonths
Case Else
GoTo RunMyMonths
End Select

- - -
HTH
Dana DeLouis

 

[OssieMac]

It's always interesting to read to various responses to questions. Now that I
understand what the OP really wants to do, I think that I would have opted
for something like this and make use of the vbRetry.

Dim MyColNum As Long
Dim MyResp As Integer

Do

'Application.InputBox has parameter to control type of data such as numeric
MyColNum = Application.InputBox("Enter column number", , 0, , , , , 1)

'Following line gives user an out if required.
If MyColNum = 0 Then Exit Do 'Entered Zero or Cancel

Select Case MyColNum
Case 9, 15, 21, 27, 33, 39, 45, 51, 57, 63, 69, Is > 74, Is < 4
'Responding with Retry will invoke loop while cancel will exit sub
MyResp = MsgBox("Wrong column selected.", vbRetryCancel)
Case Else
GoTo RunMyMonths
End Select

Loop While MyResp = vbRetry

MsgBox "Invalid number: " & MyColNum
Exit Sub

RunMyMonths:
MsgBox "Valid number: " & MyColNum

 

[Cydney]

That was very helpful and I've come up with the following code. Let me know
if you think I can streamline it further...

[...some code...]
Application.ScreenUpdating = False
ActiveWorkbook.Worksheets(7).Select
SelColumn:
Do
MyCol = Application.InputBox("Which column letter represents the
CURRENT WEEK? (e.g.: D, E, F, G...)", "Shade Columns")
v = Cells(4, MyCol).Value
If MyCol = False Or MyCol = "" Or v = "" Then
MyResp = MsgBox("Wrong column selected.", vbRetryCancel, "Column
Highlight")
Else
m = Columns(MyCol).Column
Select Case m
Case 9, 15, 21, 27, 33, 39, 45, 51, 57, 63, 69, Is > 74, Is
< 4
MyResp = MsgBox("Wrong column selected.", vbRetryCancel)
Case Else
GoTo RunMyEmplShtMonths
End Select
End If
Loop While MyResp = vbRetry
If MyResp = vbCancel Then GoTo ExitMyMonths

RunMyEmplShtMonths:
For SheetCnt = 7 To SheetTtl
[....code continues on...]

ExitMyMonths:
Application.ScreenUpdating = True
End Sub

--
Thank you, cs
~~~~~~~~~~~~~~~~~~~~~~~~
"What lies behind us and what lies before us are tiny matters compared to
what lies within us."
~ Ralph Waldo Emerson

 

[Cydney]

Same macro.. new aspect..

I need to search for a specific date (chosen previously via the
user-selected column) in a different sheet. I used the following code, but it
gives me the "Object variable or With block variable not set" error message.

(v=11/24/2008)

Cells.Find(What:=v, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart,
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False,
SearchFormat:=False).Activate

Also, I assume I can change "activate" to "column" to just get the column
number?
--
Thank you, cs
~~~~~~~~~~~~~~~~~~~~~~~~
"What lies behind us and what lies before us are tiny matters compared to
what lies within us."
~ Ralph Waldo Emerson

That was very helpful and I've come up with the following code. Let me know
if you think I can streamline it further...

[...some code...]
Application.ScreenUpdating = False
ActiveWorkbook.Worksheets(7).Select
SelColumn:
Do
MyCol = Application.InputBox("Which column letter represents the
CURRENT WEEK? (e.g.: D, E, F, G...)", "Shade Columns")
v = Cells(4, MyCol).Value
If MyCol = False Or MyCol = "" Or v = "" Then
MyResp = MsgBox("Wrong column selected.", vbRetryCancel, "Column
Highlight")
Else
m = Columns(MyCol).Column
Select Case m
Case 9, 15, 21, 27, 33, 39, 45, 51, 57, 63, 69, Is > 74, Is
< 4
MyResp = MsgBox("Wrong column selected.", vbRetryCancel)
Case Else
GoTo RunMyEmplShtMonths
End Select
End If
Loop While MyResp = vbRetry
If MyResp = vbCancel Then GoTo ExitMyMonths

RunMyEmplShtMonths:
For SheetCnt = 7 To SheetTtl
[....code continues on...]

ExitMyMonths:
Application.ScreenUpdating = True
End Sub

--
Thank you, cs
~~~~~~~~~~~~~~~~~~~~~~~~
"What lies behind us and what lies before us are tiny matters compared to
what lies within us."
~ Ralph Waldo Emerson

It's always interesting to read to various responses to questions. Now that I
understand what the OP really wants to do, I think that I would have opted
for something like this and make use of the vbRetry.

Dim MyColNum As Long
Dim MyResp As Integer

Do

'Application.InputBox has parameter to control type of data such as numeric
MyColNum = Application.InputBox("Enter column number", , 0, , , , , 1)

'Following line gives user an out if required.
If MyColNum = 0 Then Exit Do 'Entered Zero or Cancel

Select Case MyColNum
Case 9, 15, 21, 27, 33, 39, 45, 51, 57, 63, 69, Is > 74, Is < 4
'Responding with Retry will invoke loop while cancel will exit sub
MyResp = MsgBox("Wrong column selected.", vbRetryCancel)
Case Else
GoTo RunMyMonths
End Select

Loop While MyResp = vbRetry

MsgBox "Invalid number: " & MyColNum
Exit Sub

RunMyMonths:
MsgBox "Valid number: " & MyColNum

 

[Cydney]

I'm thinking it might have something to do with the fact that the actual
value (based on the previous date value +7) is the number "24" and not the
full date. Do I need to convert "v" to a serial date first? And will it find
it if I do? I can't seem to get the "v" converted to serial anyway...
help..!??
--
Thank you, cs
~~~~~~~~~~~~~~~~~~~~~~~~
"What lies behind us and what lies before us are tiny matters compared to
what lies within us."
~ Ralph Waldo Emerson

Same macro.. new aspect..

I need to search for a specific date (chosen previously via the
user-selected column) in a different sheet. I used the following code, but it
gives me the "Object variable or With block variable not set" error message.

(v=11/24/2008)

Cells.Find(What:=v, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart,
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False,
SearchFormat:=False).Activate

Also, I assume I can change "activate" to "column" to just get the column
number?
--
Thank you, cs
~~~~~~~~~~~~~~~~~~~~~~~~
"What lies behind us and what lies before us are tiny matters compared to
what lies within us."
~ Ralph Waldo Emerson

That was very helpful and I've come up with the following code. Let me know
if you think I can streamline it further...

[...some code...]
Application.ScreenUpdating = False
ActiveWorkbook.Worksheets(7).Select
SelColumn:
Do
MyCol = Application.InputBox("Which column letter represents the
CURRENT WEEK? (e.g.: D, E, F, G...)", "Shade Columns")
v = Cells(4, MyCol).Value
If MyCol = False Or MyCol = "" Or v = "" Then
MyResp = MsgBox("Wrong column selected.", vbRetryCancel, "Column
Highlight")
Else
m = Columns(MyCol).Column
Select Case m
Case 9, 15, 21, 27, 33, 39, 45, 51, 57, 63, 69, Is > 74, Is
< 4
MyResp = MsgBox("Wrong column selected.", vbRetryCancel)
Case Else
GoTo RunMyEmplShtMonths
End Select
End If
Loop While MyResp = vbRetry
If MyResp = vbCancel Then GoTo ExitMyMonths

RunMyEmplShtMonths:
For SheetCnt = 7 To SheetTtl
[....code continues on...]

ExitMyMonths:
Application.ScreenUpdating = True
End Sub

--
Thank you, cs
~~~~~~~~~~~~~~~~~~~~~~~~
"What lies behind us and what lies before us are tiny matters compared to
what lies within us."
~ Ralph Waldo Emerson

It's always interesting to read to various responses to questions. Now that I
understand what the OP really wants to do, I think that I would have opted
for something like this and make use of the vbRetry.

Dim MyColNum As Long
Dim MyResp As Integer

Do

'Application.InputBox has parameter to control type of data such as numeric
MyColNum = Application.InputBox("Enter column number", , 0, , , , , 1)

'Following line gives user an out if required.
If MyColNum = 0 Then Exit Do 'Entered Zero or Cancel

Select Case MyColNum
Case 9, 15, 21, 27, 33, 39, 45, 51, 57, 63, 69, Is > 74, Is < 4
'Responding with Retry will invoke loop while cancel will exit sub
MyResp = MsgBox("Wrong column selected.", vbRetryCancel)
Case Else
GoTo RunMyMonths
End Select

Loop While MyResp = vbRetry

MsgBox "Invalid number: " & MyColNum
Exit Sub

RunMyMonths:
MsgBox "Valid number: " & MyColNum

 

[Cydney]

ok.. So I finally answered my own question.. (you knew I would eventually get
there, didn't you...) Apparently the answer is in changing the number format
to the date that I need to find prior to doing the search.

Range("D3:CJ3").Select
Selection.NumberFormat = "m/d/yyyy;@"

Selection.Find(What:=v, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

o = ActiveCell.Column
Selection.NumberFormat = "dd"


--
Thank you, cs
~~~~~~~~~~~~~~~~~~~~~~~~
"What lies behind us and what lies before us are tiny matters compared to
what lies within us."
~ Ralph Waldo Emerson

I'm thinking it might have something to do with the fact that the actual
value (based on the previous date value +7) is the number "24" and not the
full date. Do I need to convert "v" to a serial date first? And will it find
it if I do? I can't seem to get the "v" converted to serial anyway...
help..!??
--
Thank you, cs
~~~~~~~~~~~~~~~~~~~~~~~~
"What lies behind us and what lies before us are tiny matters compared to
what lies within us."
~ Ralph Waldo Emerson

Same macro.. new aspect..

I need to search for a specific date (chosen previously via the
user-selected column) in a different sheet. I used the following code, but it
gives me the "Object variable or With block variable not set" error message.

(v=11/24/2008)

Cells.Find(What:=v, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart,
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False,
SearchFormat:=False).Activate

Also, I assume I can change "activate" to "column" to just get the column
number?
--
Thank you, cs
~~~~~~~~~~~~~~~~~~~~~~~~
"What lies behind us and what lies before us are tiny matters compared to
what lies within us."
~ Ralph Waldo Emerson

That was very helpful and I've come up with the following code. Let me know
if you think I can streamline it further...

[...some code...]
Application.ScreenUpdating = False
ActiveWorkbook.Worksheets(7).Select
SelColumn:
Do
MyCol = Application.InputBox("Which column letter represents the
CURRENT WEEK? (e.g.: D, E, F, G...)", "Shade Columns")
v = Cells(4, MyCol).Value
If MyCol = False Or MyCol = "" Or v = "" Then
MyResp = MsgBox("Wrong column selected.", vbRetryCancel, "Column
Highlight")
Else
m = Columns(MyCol).Column
Select Case m
Case 9, 15, 21, 27, 33, 39, 45, 51, 57, 63, 69, Is > 74, Is
< 4
MyResp = MsgBox("Wrong column selected.", vbRetryCancel)
Case Else
GoTo RunMyEmplShtMonths
End Select
End If
Loop While MyResp = vbRetry
If MyResp = vbCancel Then GoTo ExitMyMonths

RunMyEmplShtMonths:
For SheetCnt = 7 To SheetTtl
[....code continues on...]

ExitMyMonths:
Application.ScreenUpdating = True
End Sub

--
Thank you, cs
~~~~~~~~~~~~~~~~~~~~~~~~
"What lies behind us and what lies before us are tiny matters compared to
what lies within us."
~ Ralph Waldo Emerson


:

It's always interesting to read to various responses to questions. Now that I
understand what the OP really wants to do, I think that I would have opted
for something like this and make use of the vbRetry.

Dim MyColNum As Long
Dim MyResp As Integer

Do

'Application.InputBox has parameter to control type of data such as numeric
MyColNum = Application.InputBox("Enter column number", , 0, , , , , 1)

'Following line gives user an out if required.
If MyColNum = 0 Then Exit Do 'Entered Zero or Cancel

Select Case MyColNum
Case 9, 15, 21, 27, 33, 39, 45, 51, 57, 63, 69, Is > 74, Is < 4
'Responding with Retry will invoke loop while cancel will exit sub
MyResp = MsgBox("Wrong column selected.", vbRetryCancel)
Case Else
GoTo RunMyMonths
End Select

Loop While MyResp = vbRetry

MsgBox "Invalid number: " & MyColNum
Exit Sub

RunMyMonths:
MsgBox "Valid number: " & MyColNum

 

[OssieMac]

I didn't get back to you sooner because I was working. Your Case code looks
OK. Users will often relate better to the column alpha character because they
do not want to be counting the columns and are less likely to make an error
with the alpha characters.

The Find code would be better like the following example. It allows you to
handle NOT found which otherwise produces an error because you cannot
activate a cell that was not found.

The code firstly assigns the 'look in' range to a variable. It then finds an
object (in this case the object is a cell) that contains the value it is
looking for. You can then extract all sorts of information about that object
that really represents the cell in which the data was found. The MsgBox lines
demonstrate this.

objFoundCell.Address returns the address in absolute. eg $D$35
parameters can be added to return the address as non absolute or partial
absolute.
objFoundCell.Address(0, 0) returns D35
objFoundCell.Address(0, 1) returns $D35
objFoundCell.Address(1, 0) returns D$35


Example find code:

Dim dateToFind As Date
Dim rngToSearch As Range
Dim objFoundCell As Object

With Sheets("Sheet1")
'Note Set in following line
Set rngToSearch = Range("D3400")
End With

'NO Set in following line and date enclosed in #'s
dateToFind = #11/24/2008#

'Note Set in following line and No Activate at end
'After:=ActiveCell is optional. I left it out.
Set objFoundCell = rngToSearch. _
Find(What:=dateToFind, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If objFoundCell Is Nothing Then
MsgBox "Date " & dateToFind & " Not found"
Else
'Examples of code to extract info from objFoundCell.
MsgBox "Address of found cell = " & objFoundCell.Address(1, 0)
MsgBox "Value of foundcell = " & objFoundCell.Value
MsgBox "Column number of found cell = " & objFoundCell.Column
MsgBox "Row number of found cell = " & objFoundCell.Row
End If

You can also assign the ActiveCell to dateTofind if you want the user to
simply select the cell first before running the macro. The cell needs to be
formatted as a date.

Example:
dateToFind = ActiveCell


Feel free to get back again if still having problems.