Solved Loop Sheet1 names and Find each on matrix in Sheet2 & then

September 12, 2019 at 14:09:45
Specs: Windows 10
I have an order fulfillment worksheet (Sheet1) with 700 names. In same workbook, there are 10 other worksheets that match orders from various customers being sent in for personalized goods with some of the names on Sheet1. There is an input cell (Q3) on Sheet1 for the user to identify which order form (Sheet2, Sheet3, etc.) is received from the customer (matching the names on Sheet2 through Sheet11). I need vba help to select each name on Sheet1 (each name is in Col A, beginning at cell A9) and if cell Q3 has number 1 in it, search Sheet2, if number 2, search Sheet3., etc., for an exact match of that name. If name is not found, the row with the name being searched on Sheet1 should be hidden. If name is found, go to next row and find that name on Sheet2, through all the names in column A of Sheet1. When concluded, the unhidden rows with names on Sheet1 should match the 100 to 300 names on Sheet1 or whichever other Sheet number is being searched and matched to Sheet1.

See More: Loop Sheet1 names and Find each on matrix in Sheet2 & then

Reply ↓  Report •

✔ Best Answer
September 17, 2019 at 07:40:06
The first macro in this post is an updated version of the Drop Down Build macro that includes a View All choice to unhide all rows.

The second macro in this post should be placed in the Sheet Module for the Summary sheet. Since it is a Worksheet_Change macro, it will run the "hide rows" code as soon as a Q3 choice is made. That version also deals with the "Total" issue by not including that row as part of the Name search.

This is all I have time for right now. I'll check back in tonight (EST)

DropDown Build Version 2

Sub DropDownBuild_v2()
Dim shtNum As Long
Dim tmpList As String, shtList As String

'Build List for DropDown
  For shtNum = 2 To Sheets.Count
    tmpList = tmpList & Sheets(shtNum).Name & ","
  Next
    shtList = "View All" & "," & Left(tmpList, Len(tmpList) - 1)
    
'Assign Data Validation to Summary!Q3
    With Sheets("Summary").Range("Q3").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                   xlBetween, Formula1:=shtList
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With
End Sub

Search and Hide

Private Sub Worksheet_Change(ByVal Target As Range)

'Search and Hide

Dim LastRw As Long
Dim shtNum As Long
Dim nxtName As Long
Dim c As Range

 If Target = Cells(3, "Q") Then

   With Sheets("Summary")

'Unhide all Rows in Columns A
     .Rows.Hidden = False
   
'Determine which Sheet to search or View All Names
      If .Cells(3, "Q") = "View All" Then Exit Sub
      shtName = .Cells(3, "Q")
    
'Determine last row with names in Summary
      LastRw = .Cells(Rows.Count, 1).End(xlUp).Row - 1
   
   End With

'Loop through Names, hiding row if Name not found
   For nxtName = 9 To LastRw
      With Sheets(shtName).Cells
        Set c = .Find(Sheets("Summary").Cells(nxtName, "A"), lookat:=xlWhole)
          If c Is Nothing Then
            Sheets("Summary").Cells(nxtName, "A").EntireRow.Hidden = True
          End If
      End With
   Next
   
 End If

End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03



#1
September 13, 2019 at 21:37:02
I need some clarification on a few things:

re: "If name is found, go to next row and find that name on Sheet2, through all the names in column A of Sheet1."

I assume you mean "find that name on the Sheet referenced by the number in Sheet1!Q3". In other words, not necessarily Sheet2 in all cases.

re: "When concluded, the unhidden rows with names on Sheet1 should match the 100 to 300 names on Sheet1 or whichever other Sheet number is being searched..."

Are you sure that that is correct?

Of course the unhidden rows on Sheet1 will match the names on Sheet1. They are on Sheet1.

Don't you mean "...on Sheet2 or whichever other Sheet number is being searched..."?

re: "find that name on Sheet(x)"

Is there a specific Column or Row to be searched or could the name be found anywhere on the sheet being searched?

message edited by DerbyDad03


Reply ↓  Report •

#2
September 15, 2019 at 10:28:54
Sorry for the misstatement, the correct statement should have been, " When concluded, the unhidden rows with names on Sheet1 should match the 100 to 300 names on the sheet indicated in cell Q3 (Sheets2 through Sheet11)". You were right in your catch. There are no specific columns or rows on the sheet being searched; each sheet is different, but most sheets have the names in columns a,d,g,j,m,and p, but 1 is different and others added in the future may be different.

Thank you so much for your response.

message edited by lgillman


Reply ↓  Report •

#3
September 15, 2019 at 11:37:51
Your other two assumptions are correct.

Reply ↓  Report •

Related Solutions

#4
September 15, 2019 at 17:18:27
Try this code.

I included an instruction to unhide all Rows prior to searching so the user doesn't have to do that each time.

I also added a check to see if an invalid number was entered. e.g. if there are 10 Sheets to be searched, the max number in Q3 should be 10. Anything above that would Reference a sheet that doesn't exist.

Sub FindNames()
Dim LastRw As Long
Dim shtNum As Long
Dim nxtName As Long
Dim c As Range

'Unhide all Rows in Columns A
   Sheets(1).Rows.Hidden = False
   
   'Determine which Sheet to search, Check for valid sheet
   shtNum = Sheets(1).Cells(3, "Q") + 1
    If shtNum > Sheets.Count Then
     MsgBox "Sheet #" & shtNum & " does not exist. Please try again."
     Exit Sub
    End If
    
'Determine last row with names in Sheet(1) Column A
   LastRw = Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row

'Loop through Names, hiding row if Name not found
   For nxtName = 9 To LastRw
      With Sheets(shtNum).Cells
        Set c = .Find(Sheets(1).Cells(nxtName, "A"), lookat:=xlWhole)
          If c Is Nothing Then
            Sheets(1).Cells(nxtName, "A").EntireRow.Hidden = True
          End If
      End With
   Next
   
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#5
September 16, 2019 at 10:33:34
Wow, that is amazing. It is not yet perfect, but I can get it to work if I change the With Sheets(shtNum).cells to the actual name of the first sheet after Sheet1; which is With Sheets("1 Name List").Cells... it will hide all but the 288 names on the sheet named "1 Name List". I thought without giving you all the sheet names, I could just change the code to go to the correct name of the list instead of Sheet2, Sheet3, etc. But I see it is more complex than that. So now I will explain that there is a number in Cell Q3: 1 through 10. That number is on each sheet after Sheet1 as part of the name. 1 would be the first sheet after Sheet1, 2 would be the second sheet after Sheet1, etc., but they actually have names: "1 Name List", 2 Bowfin", "3 Dutch_Span", "4 E-Pa", "5 M-of-A". "6 Plano", "7 Orlndo", "8 D-S Last_Names", "9 Chandler" and "10 F&S". With a little tweaking to get "Sht Num" to select the correct sheet, it should be perfect. Sheet1 also has a name, "Summary", if that is important. Thank you, Derby Dad 03, for this awesome help.

message edited by lgillman


Reply ↓  Report •

#6
September 16, 2019 at 10:38:58
One additional thing that would be helpful. If it comes to a name that is "Total" on Sheet1 ("Summary") then stop hiding lines. If difficult, I can work around that.

Reply ↓  Report •

#7
September 16, 2019 at 12:01:37
I would like to ask that you supply all of your requirements at once so that we don't need to go and forth tweaking the code for each new requirement. Sometimes the additions are easy but sometimes the "bolt ons" get so extensive that the code is no longer as efficient as it should be and we end up starting from scratch. That is a waste of your time and mine, especially since I have to set up test workbooks to test the code against. Based on your latest requirements, the workbook I set up last night is no longer going to work.

That said, I once again need clarification.

It is no longer clear to me which sheet contains the full list of 700 names, which sheet the infamous Q3 is on or which sheets are supposed to be searched. Please clarify that for me.

re: "If it comes to a name that is "Total" on Sheet1 ("Summary")"

Maybe this will become clear when you answer my previous question, but for now I don't know what you mean by comes to a name that is "Total". The code doesn't "come to a name" when searching a sheet, it literally finds it by using Excel's built in Find feature. Unless it is searching for the name "Total" it will never find it. It's not testing each cell one at a time, so it'll never "come to" Total unless it was told to Find it.

Please keep in mind that I can't see your workbook from where I'm sitting, so you have to be very clear when describing both the workbook and your requirements.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#8
September 16, 2019 at 12:24:45
I am very sorry for my inadequacy in defining the workbook and its contents. The sheet with the 700 names is the worksheet that I originally referred to as Sheet1 that has an actual name of "Summary" (hereafter I will call it "Summary". The Q3 is also on "Summary", The sheets to be searched are the second through 11th sheets in the workbook, and they have names as I stated above ("1 Name List", 2 Bowfin", "3 Dutch_Span", "4 E-Pa", "5 M-of-A". "6 Plano", "7 Orlndo", "8 D-S Last_Names", "9 Chandler" and "10 F&S"). the numbers are actually part of the name for each worksheet. The names are exactly as shown in quotes. The name "Total" is on the "Summary" sheet and it is the last row of the sheet and I would prefer that it not be hidden. But it just occurred to me Ican put the word Total on the searched sheets and it won't hide that line

message edited by lgillman


Reply ↓  Report •

#9
September 17, 2019 at 07:07:37
Edit: See my next post before you repond to this one

Would you be willing to put the actual sheet names in a DropDown list in Q3?

It would be much easier to assign the Sheet name to an actual string than to parse the leading character(s). 1 - 9 wouldn't be that hard, but the 10 means that there are 2 sheets whose first character is 1. That adds a couple of extra instructions.

A DropDown would be easier on the user also. No need to use the keyboard, just a click of the mouse.

This code should make it easy to build that DropDown. Let me know if that is acceptable or if I still have to fight with the numbers 1 - 10.

Sub DropDownBuild()

'Build List for DropDown
  For shtNum = 2 To Sheets.Count
    tmpList = tmpList & Sheets(shtNum).Name & ","
  Next
    shtList = Left(tmpList, Len(tmpList) - 1)
    
'Assign Data Validation to Summary!Q3
    With Sheets("Summary").Range("Q3").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                   xlBetween, Formula1:=shtList
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

#10
September 17, 2019 at 07:40:06
✔ Best Answer
The first macro in this post is an updated version of the Drop Down Build macro that includes a View All choice to unhide all rows.

The second macro in this post should be placed in the Sheet Module for the Summary sheet. Since it is a Worksheet_Change macro, it will run the "hide rows" code as soon as a Q3 choice is made. That version also deals with the "Total" issue by not including that row as part of the Name search.

This is all I have time for right now. I'll check back in tonight (EST)

DropDown Build Version 2

Sub DropDownBuild_v2()
Dim shtNum As Long
Dim tmpList As String, shtList As String

'Build List for DropDown
  For shtNum = 2 To Sheets.Count
    tmpList = tmpList & Sheets(shtNum).Name & ","
  Next
    shtList = "View All" & "," & Left(tmpList, Len(tmpList) - 1)
    
'Assign Data Validation to Summary!Q3
    With Sheets("Summary").Range("Q3").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                   xlBetween, Formula1:=shtList
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With
End Sub

Search and Hide

Private Sub Worksheet_Change(ByVal Target As Range)

'Search and Hide

Dim LastRw As Long
Dim shtNum As Long
Dim nxtName As Long
Dim c As Range

 If Target = Cells(3, "Q") Then

   With Sheets("Summary")

'Unhide all Rows in Columns A
     .Rows.Hidden = False
   
'Determine which Sheet to search or View All Names
      If .Cells(3, "Q") = "View All" Then Exit Sub
      shtName = .Cells(3, "Q")
    
'Determine last row with names in Summary
      LastRw = .Cells(Rows.Count, 1).End(xlUp).Row - 1
   
   End With

'Loop through Names, hiding row if Name not found
   For nxtName = 9 To LastRw
      With Sheets(shtName).Cells
        Set c = .Find(Sheets("Summary").Cells(nxtName, "A"), lookat:=xlWhole)
          If c Is Nothing Then
            Sheets("Summary").Cells(nxtName, "A").EntireRow.Hidden = True
          End If
      End With
   Next
   
 End If

End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

#11
September 17, 2019 at 15:15:27
YEA!! Both Macros worked great! I have to figure out a solution to one little problem, however. I have a macro with an execution button which inserts a column beginning at row Q6 through Q677 on "Summary" sheet. it does the insert and shifts everything to the right, copies R6 through R677 and pastes into the inserted column; then clears Q6 through Q676, leaving the total formula in Q677. When I try to run that Macro, it triggers the running of the worksheet Macro as if I have targeted Q3, and it errors out when it gets to "If Target = Cells(3, "Q") Then". It should not trigger the running of the new Macro, like changing Q3 does. Any ideas of how to fix that? I also have a couple of other Macros: 1 to Unhide all rows that can be run by a button and 1 to go to last row. These macros do not trigger the new hide rows macro.
THANK YOU, THANK YOU, THANK YOU! This speeds up data entry onto the Summary sheet very much.

Reply ↓  Report •

#12
September 17, 2019 at 16:36:14
re: "I have a macro with an execution button which inserts a column beginning at row Q6 through Q677 on "Summary" sheet."

There is no such thing as "row Q6". Row 6 or cell Q6, yes, but "row Q6" doesn't exist.

In addition, you can't insert a "column" between Q6 and Q677. Those cells are in the same column so how can you insert a column between them? You can only insert a column between 2 columns or as the first in a sheet.

I can't fix my code until I understand what you mean by those 2 items.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#13
September 17, 2019 at 16:54:51
Below is the VBA code that does the insert function. Maybe this will help. Your are right, I should have said that in column Q, it inserts cells from row 6 down to row 677.

Sub Insert_New_Sales_Order_Cells()
'
Range("Q6:Q677").Select
Selection.Copy
Range("Q6").Select
Selection.Insert Shift:=xlToRight
Range("Q6:Q676").Select
Range("Q676").Activate
Application.CutCopyMode = False
Selection.ClearContents
End Sub

message edited by lgillman


Reply ↓  Report •

#14
September 17, 2019 at 18:34:00
First, a posting tip:

Please click on the How-To link at the end of this post and read the instructions on how to post VBA code in this forum so that it is easier for us to read. Then edit/repost your code so that it looks similar to the example found via that link.

As far as the Worksheet_Change code running when your macro runs, that was my error. I was working too fast. Replace this instruction:

 If Target = Cells(3, "Q") Then

with this:

 If Target.Address = ("$Q$3") Then

Your insert code was obviously recorded. That would explain all of the .Select instructions. It's perfectly fine to use the Macro Recorder to create a shell to get you started (I do it quite often) but you really should edit the code to clean it up and make it more efficient. (You should also add comments)

Rarely do you need to Select an object in order to perform an operation on it. VBA can almost always work directly on the object. I don't have a copy of your workbook, but I believe that your code can be condensed down to this:

Sub Insert_New_Sales_Order_Cells()
'
  Range("Q6:Q677").Copy
  Range("Q6").Insert Shift:=xlToRight
  Range("Q6:Q676").ClearContents
  Range("Q676").Activate
End Sub

I'm not sure if you really need to Activate Q676 or if that was just left over from the recorder, so I left that instruction alone.

One last item:

That code is still going to trigger the Worksheet_Change macro, but it shouldn't error out anymore. (Again, I'm not working with your workbook, so I can't say for sure.) However, if you want to be adventurous (see my comment after this code example), you can prevent the Change macro from running by Disabling events while the Copy/Insert code runs. You just need to make sure that you enable events again when the code is done.

Sub Insert_New_Sales_Order_Cells()
'
'Disable Events
 Application.EnableEvents = False
 
'Copy/Insert
  Range("Q6:Q677").Copy
  Range("Q6").Insert Shift:=xlToRight
  Range("Q6:Q676").ClearContents
  Range("Q676").Activate

'Re-enable Events
 Application.EnableEvents = True
End Sub

The danger of this method is that Events can become Disabled accidentally and never get enabled again.

Let's say that you are single stepping though the code and the Application.EnableEvents = False instruction executes. Then you stop running the macro or the code crashes before the Application.EnableEvents = True instructions executes. In that case, Events will remain Disabled and the Worksheet_Change code will never run. Since this an Application wide instruction, no event code, in any workbook, will run until you either re-enable events or you close/open Excel. Since there is no indication as to whether Events are enabled or not, it can get confusing when suddenly none of your event macros work anymore.

There are error handling routines that can prevent this from happening. You can find examples via a Google search.

(You do know about Single-Stepping, don't you?)

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#15
September 19, 2019 at 20:38:27
There is a problem in column Q after the Search and Hide routine has run, starting in row 9, which is used for data input. When one any of the cells are selected and data is entered and the sheet immediately jumps down to the total line at row 677 and does not permit input in the cell selected. It even does this if a cell is selected with no data entered and then another cell is selected right after. When this happens the little "in process" circle comes up for just an instant like it is processing something. I don't know if this is causing the problem, but could we maybe set up the entire macro to run on a button instead of running automatically when the target sheet is entered in Cell Q3?

message edited by lgillman


Reply ↓  Report •

#16
September 20, 2019 at 08:36:39
Since I did not see any response related to my post #14, I do not know if you made the correction that I suggested. Did you do that?

In that post I also asked: "You do know about Single-Stepping, don't you?" Since I did not get an answer to the question, I don't know your level of skill when it comes to debugging VBA code. If you are just letting the code run on it's own and hoping for the best, instead of using VBA's Debugging tools (Single Stepping, Breakpoints, Watches, etc.) to determine why it fails, then finding the root cause of the problem is going to be difficult. Are you in fact using any of the Debugging tools that the VBA editor makes available?

In addition, as I mentioned earlier, I do not have a copy of your workbook to test against, so I'm flying slightly blind here. You've already told me that you have existing macros in this workbook and there's no way for me to know how the various macros interact without having the full picture. If you'd be willing to email the workbook to me (after removing/replacing any personal and/or confidential data) then I would be able dig a little deeper into the situation.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#17
September 20, 2019 at 10:08:07
#15 above can be ignored. I figured out my problem. It had nothing to do with the Macro. Thank you again, DerbyDad03. You have been a blessing.

Reply ↓  Report •

#18
September 24, 2019 at 07:39:40
In answer to #16, I did respond and answered that I was familiar with but do not see the response here, so I guess it fell through the computer cracks. I am familiar with and use Single-Stepping, but I have very limited knowledge with VBA terminology. I often use the recorder to do simple macros, and I have gleaned several from various websites. I have effectively changed some of them to meet my individual needs. You are one of the most knowledgeable and helpful VBA specialists I have had the opportunity to communicate with. Thank you very much. Your macro is alive and well in my system and is performing well.

Reply ↓  Report •

#19
September 24, 2019 at 08:08:20
I'm glad I could be of assistance.

This might help with your future VBA endeavors:

https://www.computing.net/howtos/sh...

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#20
September 27, 2019 at 11:43:06
Thanks. I will research that.

Reply ↓  Report •

Ask Question