can't get a macro to work using a range name

[still learning]

Hi.
I'm having a problem getting the following macro to work.
It's for a Userform.
The user enters a name in the Text box and and I will have a macro use it to do an advance search.
I will work on that after I get this to work. I don't for see any problems because I can copy and paste a lot of it from another book and modify it to suit (I may be back, but I'll use a different thread)

I keep getting an error at the line >>> If Range("checkresults").Value = "" Then <<
I used F5 and the range "checkresults" is correct. ( I checked the spelling and the 's' on the end...i've made that mistake before)
I changed the line to >>> If Range("BA3").Value = "" Then <<
and it works.
I tried to change the line to the actual cells and that didn't work. ("AP2:AU2")

the results will always be a text.

Private Sub CommandButton1_Click()
    With TextBox2  'name
        ActiveSheet.Range("aw1").Value = .Text
   End With
 Me.Hide
  Range("A1").Select
Unload Me
'If Range("checkresults").Value = "" Then
'If Range("BA3").Value = "" Then
   MsgBox "       NO RESULTS": Exit Sub
    End If
End Sub

mike

 

[JoeMo]

What is the error message you get? What is the scope of the named range, workbook level or sheet level?

 

[still learning]

Hi JoeMo
I get "Error 13---type mismatch." that line is highlighted
I tired both the range name and the cell addresses

I have both in the question so you can see what i had typed in. I removed one of the ' to try it. then the other.

I put the code in the command box, and not the text box2 because that is where the macro is in another userform in a different book that works

The range is in a worksheet that is in a workbook that has 3 sheets in it. I bring up F3 and it shows--$AP$2:$AU$2 of the proper worksheet and will take me there when I click on edit.

What I'm trying to do :
the user brings up a userform with a text box to put in a name. that name goes to AW1 ( that works perfect)
Then i will have a macro go to the range name "checkresults" (which is the criteria's ranges of the advance filter. it's the second line) and clear it
I will have 3 check boxes in the userform.
Then it will copy what is in AW1 to one of the search criteria ranges (depending on which check box is clicked) and do >>Selection.Value = Selection.Value <<<
( don't know if I have to get rid of the formula...+AW1..but it is easy to add the line)
Then it will do an advance filter and the cursor will move the that part of the sheet.
I want the user to see that either the spelling is wrong or the name does not exist. if there are no results, instead of just a blank line

I'm working on the the macro to see which check box is clicked and put what is in AW1 in the proper criteria range.

I bet I'll have to come back and ask more questions, but it'll go into a new question.

 

[Skyybot]

Hi.
I'm having a problem getting the following macro to work.
It's for a Userform.
The user enters a name in the Text box and and I will have a macro use it to do an advance search.
I will work on that after I get this to work. I don't for see any problems because I can copy and paste a lot of it from another book and modify it to suit (I may be back, but I'll use a different thread)

I keep getting an error at the line >>> If Range("checkresults").Value = "" Then <<
I used F5 and the range "checkresults" is correct. ( I checked the spelling and the 's' on the end...i've made that mistake before)
I changed the line to >>> If Range("BA3").Value = "" Then <<
and it works.
I tried to change the line to the actual cells and that didn't work. ("AP2:AU2")

the results will always be a text.

Private Sub CommandButton1_Click()
    With TextBox2  'name
        ActiveSheet.Range("aw1").Value = .Text
   End With
 Me.Hide
  Range("A1").Select
Unload Me
'If Range("checkresults").Value = "" Then
'If Range("BA3").Value = "" Then
   MsgBox "       NO RESULTS": Exit Sub
    End If
End Sub

mike

Try Commenting out the Unload.Me line and let me know what happens.

 

[still learning]

He Joe

In writing the last reply. I see where I might be All SCREWED UP
I'm trying to have a mgs box come up if the range "checkresults" is blank.
II should have the range "extract" be dependent on the msg box. that is the results of the Querry. If that is blank, either the name is wrong of there is no such name.
I'll change it and get back

mike

 

[still learning]

hI Skyybot
I changed the range that i want to have a msg box come up if there are no results. (B2:K2)
That is first line under the extract range of the three check boxes querry..........I might be wrong using Querry instead of advance filter.......
I still get the debug box
I took out the Unload me. didn't work
I changed the range to a name. didn't work
I change the range to a single cell and IT WORKS
Wonder if I should have a helper cell to copy any results to it and if IT IS blank, then bring up th mg box

mike

 

[MARK858]

Try....

If WorksheetFunction.CountA(Range("B2:K2")) = 0 Then

 

[still learning]

Yea...it works...it also works if I use a range name.
Thank you MARK858
Thank you Skyybot
Thank you JoeMo

Wonder why I had to use a function for multiple cells or a range

Question...is the syntax for msg correct>>>> MsgBox " NO RESULTS": Exit Sub <<<<
(from my first post)
I didn't use quotation marks. but it still works.

It's late here {9:35}, so I'll get back tomorrow.

Thanks again for all your help


mike

 

[MARK858]

You're welcome

Wonder why I had to use a function for multiple cells or a range

Because it is multiple cells, therefore multiple tests