Message box before another macro
 

I need to add a message box to the front end of a search macro that
will trigger based on certain criteria and, in the absence of those
criteria, allow the macro to proceed. Sounds like a simple
"IF..Then..Else", but I cannot get the syntax to agree, even after
pouring through this group and using previously suggested solutions.

I need this:

If Range("Q55") = "True" Then
MsgBox"blah blah blah"

Else RunMacro1

VB kicks back a "Type Mismatch" every time on the very first statement
here. I have tried the True with and without quotes, same result.

Entire code is below. Help is appreciated.

Sub Look_Here1()

If Range("Q55") = "true" Then
MsgBox "Check Driver's License Expiration Date", 48, "Exprired
Driver's License"

Else
Dim FoundCell As Range
Dim WhatFor As Variant
WhatFor = ActiveSheet.Cells(7, 2).Value

Set FoundCell = Range("B8:B990").Find(What:=WhatFor,
after:=ActiveCell, _
SearchDirection:=xlNext, searchorder:=xlByRows,
_
MatchCase:=False)

If FoundCell Is Nothing Then
Range("A7").Select
ActiveCell.FormulaR1C1 = "X"
Range("D7").Select

Else

FoundCell.Offset(0, -1).Select
ActiveCell.FormulaR1C1 = "X"
Selection.Offset(0, 4).Select

End If
End If


End Sub

Message box before another macro
 

Try the True without the quotes to get the value as opposed to a string.


--
mrice

Research Scientist with many years of spreadsheet development experience
------------------------------------------------------------------------
mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931
View this thread: http://www.excelforum.com/showthread...hreadid=544103

Message box before another macro
 

Still give me a "Type Mismatch"

Thanks for looking.

Message box before another macro
 

What's in Q55?

maybe...

if lcase(range("q55").text) = "true" then

I'm guessing that you had an error #ref, #n/a, div/0, ... in that cell.

michaelberrier wrote:

I need to add a message box to the front end of a search macro that
will trigger based on certain criteria and, in the absence of those
criteria, allow the macro to proceed. Sounds like a simple
"IF..Then..Else", but I cannot get the syntax to agree, even after
pouring through this group and using previously suggested solutions.

I need this:

If Range("Q55") = "True" Then
MsgBox"blah blah blah"

Else RunMacro1

VB kicks back a "Type Mismatch" every time on the very first statement
here. I have tried the True with and without quotes, same result.

Entire code is below. Help is appreciated.

Sub Look_Here1()

If Range("Q55") = "true" Then
MsgBox "Check Driver's License Expiration Date", 48, "Exprired
Driver's License"

Else
Dim FoundCell As Range
Dim WhatFor As Variant
WhatFor = ActiveSheet.Cells(7, 2).Value

Set FoundCell = Range("B8:B990").Find(What:=WhatFor,
after:=ActiveCell, _
SearchDirection:=xlNext, searchorder:=xlByRows,
_
MatchCase:=False)

If FoundCell Is Nothing Then
Range("A7").Select
ActiveCell.FormulaR1C1 = "X"
Range("D7").Select

Else

FoundCell.Offset(0, -1).Select
ActiveCell.FormulaR1C1 = "X"
Selection.Offset(0, 4).Select

End If
End If


End Sub

--

Dave Peterson

Message box before another macro
 

Q55 is a True/False based on a date entered in another cell.

Basically, if that date is after today, then Q55 is false and the macro
completes. Conversely, if Q55 is True, then I want the Message Box to
display.

Thanks.

Message box before another macro
 

That code is looking at the activesheet.

Are you sure that's the one you wanted?

maybe adding:

msgbox range("q55").text

would help you debug the problem.

michaelberrier wrote:

Q55 is a True/False based on a date entered in another cell.

Basically, if that date is after today, then Q55 is false and the macro
completes. Conversely, if Q55 is True, then I want the Message Box to
display.

Thanks.

--

Dave Peterson

Message box before another macro
 

It is the activesheet that I want.

Where would I add msgbox range("Q55").text?

Message box before another macro
 

right before the if statement.

You'd be using it just to help debug the problem.

msgbox range("q55").text
if range("q55").value = True then
.....

michaelberrier wrote:

It is the activesheet that I want.

Where would I add msgbox range("Q55").text?

--

Dave Peterson

Message box before another macro
 

Neat trick I didn't know. Somehow the value in Q55 got corrupted.
Thanks for the tip. Of course, now I've jumped a hurdle into a snake
pit.

I'm sure you'll see another post from me soon.

Message box before another macro
 

I have mod'ed your code as indicated (red). My small test worked:
_________________________________
Sub Look_Here1()
If Range("Q55") = True Then 'no quotes
MsgBox "Check Driver's License Expiration Date", 48,
"ExpiredDriver's License"
Else
Dim FoundCell As Variant
Dim teststr As String 'for testing only
Dim WhatFor As Variant
WhatFor = ActiveSheet.Cells(7, 2).Value

With Worksheets(1).Range("B8:B990")
Set FoundCell = .Find(WhatFor, LookIn:=xlValues,
MatchCase:=False)
End With

'note: with the word "testing" in B33, FoundCell held the value
"testing" after the find operation, and not the address itself.
teststr = FoundCell.Address 'used to test only (teststr = $B$33, now)
If FoundCell Is Nothing Then
Range("A7").Select
ActiveCell.FormulaR1C1 = "X"
Range("D7").Select
Else
FoundCell.Offset(0, -1).Select
ActiveCell.FormulaR1C1 = "X"
Selection.Offset(0, 4).Select
End If
End If
End Sub


--
protonLeah
------------------------------------------------------------------------
protonLeah's Profile: http://www.excelforum.com/member.php...o&userid=32097
View this thread: http://www.excelforum.com/showthread...hreadid=544103