Problem with Search macro + data connections

S

smichael

New Member
Joined
Jul 15, 2011
Messages
38
Well, I have a search macro that has been working great. There is a formula in the last column that is either "TRUE" or "FALSE" depending on if the row has a word matching the search. This is filtered through the macro and allows for only the "TRUE" rows to show up.

The problem I am having is that I would like to update this data through a connection I've set up, but it seems that this can only be executed by means of a table. With that, I was wondering if this code can be altered to allow it to search through a table without converting the data into a range.

The code is as follows:
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A3")) Is Nothing Then
    If Range("A3") = "" Then
        On Error Resume Next ' in case autofilter not applied
        ActiveSheet.ShowAllData
    Else
        Range("A7:E1764").AutoFilter Field:=6, Criteria1:="TRUE"
    End If
End If
End Sub

Thanks for the help!
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Sort by date Sort by votes
this issue is linked to previous thread http://www.mrexcel.com/forum/showthread.php?t=565010

I understand the issue to be that you want the search filter tool to work on an XL07 table instead of a defined range that has autofilter applied to it. I don't use data connections myself so I don't know any other implications caused by them, and can't help with that side of things

I wiped out the autofilter section, inserted a 6-column XL07 table (which by default is named "table1" by XL) and used this. I had to change how it was referred to in the VBA code, and also how the autofilter was removed from it, because showalldata doesn't work. I used the same T/F formula approach in column 6

My amended code is now:
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A3")) Is Nothing Then
    If Range("A3") = "" Then
        ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=6
    Else
        ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=6, Criteria1:="TRUE"
    End If
End If
End Sub

To work out how XL was naming and using my table within the VBA, I simply recorded a macro, where I filtered the table once or twice and reset it, then used the appropriate piece of code. You can also use the names box on the top left of the screen, to identify table names
 
Upvote 0
smichael said:
is it possible to run this macro through a button? i can't seem to get it to work
Click to expand...

Please can you post further questions to the original thread, so other users get the benefit of any responses, Thanks

This is easy enough to do, but you need to change several things.

The original code is an event that is trigggered to run when the worksheet is changed, you need to change that. The first thing it does is to see if it should be run, based on what is being changed - that becomes unnecessary because you are running it manually. The auto event uses an object called "Target" as the range, currently we are using "A3", to see if this overlaps (intersects) with target - we don't need that test, we just use the range itself

We then configure this code to be run from a button, the method varies depending what type of button you use

Other improvements: I would use a named range instead of "A3", and I would move my formula to column 1 instead of column 6, as this enables easier update to more columns of data (col 6 might move, col 1 probably wont)

code becomes
Code: 
Sub searchFilter()
    If Range("searchBox") = "" Then
        ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1
    Else
        ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:="TRUE"
    End If
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Search/Filter a Spreadsheet by User Entered Value
Replies
3
Views
197
jbennett01
J
J
  • Solved
VBA code is not working
2
Replies
11
Views
493
smozgur
smozgur
J
  • Solved
VBA to search or autofilter
Replies
2
Views
375
Jagat Pavasia
J
G
  • Solved
Help needed with simple macro
2
Replies
15
Views
211
MARK858
MARK858
A
  • Question Question
VBA Copy/Paste New Data Only
Replies
0
Views
438
ah2024_2024
A

Forum statistics

Threads
1,224,521
Messages
6,179,285
Members
452,902
Latest member
Knuddeluff

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top