Solved Display contents of all cells in a range that contain text

March 7, 2018 at 19:49:13
Specs: iPhone
I have a sheet called ‘July’ that looks like a calendar with data in a range from A1:G5

In any given cell I may have a entry such as C1 “Team01 vs Team08 July 1 6pm ”. Each day I have a different game listed.

How can I get a list of all games that only Team01 plays? So I want to search for any cell in the range that contains “Team01” and display the full cell contents. I should end up with a list of cell contents.

I am sorry if this is not clear , I can try to post an example if that is needed.


See More: Display contents of all cells in a range that contain text

Reply ↓  Report •

#1
March 8, 2018 at 06:18:10
An example.. How can I get a list for any cell that contains "Team1" from the range A1:C4 ?

So the list would be:
Team1-Date1
Team1-Date2
Team1-Date3
Team1-Date4

 
              A                      B                        C     
1        Team1-Date1            Team2-Date1               Team3-Date1
2        Team3-Date2            Team1-Date2               Team1-Date3
3        Team1-Date4            Team3-Date3               Team2-Date2
4        Team2-Date3            Team3-Date4               Team2-Date4


Reply ↓  Report •

#2
March 8, 2018 at 07:15:45
✔ Best Answer
The following macro will accept a team name via an Input box and output the data to Sheet2!A1. You didn't specify where you wanted the output, so I had to choose a location.

There is one caveat to be aware of. The code uses "Find" to locate the team name, but the team name is not the only text in the cells. Therefore, we have to tell the code to do a "partial search" meaning that if the string Team1 is found anywhere in the cell, then it's a valid hit. As written, Team1, Team10, MyTeam1Time, etc. are all valid hits because the search is basically "*Team1*". The same thing will happen with Team2 i.e. the code will find Team2, MyTeam20, etc.

Now, I would not be surprised to hear that Team1, Team2, etc. are not the actual team names, but I still wanted to point that at because I only have your information to work with and have to write the code to match the info I have.

Let me know what needs to be changed, because I'm sure that this is not the final solution.

Sub TeamExtract()
Dim teamName
Dim firstAddress As String
Dim t As Range
Dim nxtRw As Long

'Get Team Name from User, Exit if Canceled
getTeam:
    teamName = Application.InputBox("Enter Team Name:", "Team Name")
      If teamName = False Then Exit Sub
      
'Search range for team name, output to Sheet 2
    With Range("A1:C4")
       Set t = .Find(teamName, lookat:=xlPart, after:=Range("C4"))
         If Not t Is Nothing Then
            firstAddress = t.Address
          Do
            nxtRw = nxtRw + 1
             Sheets(2).Range("A" & nxtRw) = t
             Set t = .FindNext(t)
          Loop While Not t Is Nothing And t.Address <> firstAddress
         Else:
             MsgBox teamName & " Not Found" & vbCrLf & vbCrLf & _
                   "Please Click OK To Try Again or Cancel"
             GoTo getTeam
         End If
    End With
End Sub

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

message edited by DerbyDad03


Reply ↓  Report •

#3
March 8, 2018 at 11:24:15
Thanks, this definitely works. The teams will actually be team1 ... team8 so I won't have team1 matching any other teams, unless I add up to 10, in that case I will change it to team01...

This is my first experience with VB , so I modified your code to run one time, and update the sheets "Team1 List", "Team2 List" and "Team3 List" all with one run. This will work for my purposes.

Two questions:
1. Can I have this run anytime I change my "master" sheet?
2. Can I use this in google sheets?

Sub TeamExtract()
Dim teamName
Dim teamSheet
Dim firstAddress As String
Dim t As Range
Dim nxtRw As Long
Dim teamNum As Integer

teamNum = 1
'Search range for team name, output to appropriate sheet
    While teamNum < 4
        teamName = "Team" & teamNum
        teamSheet = teamName & " List"
        'clear the sheet from any previous runs
        Sheets(teamSheet).Cells.ClearContents
        With Range("A1:C4")
           Set t = .Find(teamName, lookat:=xlPart, after:=Range("C4"))
             If Not t Is Nothing Then
                firstAddress = t.Address
              Do
                nxtRw = nxtRw + 1
                 Sheets(teamSheet).Range("A" & nxtRw) = t
                 Set t = .FindNext(t)
              Loop While Not t Is Nothing And t.Address <> firstAddress
             Else:
                 MsgBox teamName & " Not Found" & vbCrLf & vbCrLf & _
                       "Please Click OK To Try Again or Cancel"
             End If
        End With
        teamNum = teamNum + 1
        'reset the row to 1
        nxtRw = 0
    Wend
End Sub


Reply ↓  Report •

Related Solutions

#4
March 8, 2018 at 12:52:42
re: "This is my first experience with VB"

Technically, this is VBA, not VB. "Visual Basic for Applications"

re: "Can I have this run anytime I change my "master" sheet?"

Yes, you could change this to be a Worksheet_Change macro which would fire on any change to the master sheet.

If you really want it to re-build the lists anytime you change the master sheet, then the modification is simple. Just replace the first (Sub) line with the following and then store the code in the worksheet module for the master sheet.

Private Sub Worksheet_Change(ByVal Target As Range)

If you would rather limit the list builds to only those times when you change something within the Range("A1:C4"), you can. You would add an If to determine if the Target (the cell that was changed) is within that range:

Private Sub Worksheet_Change(ByVal Target As Range)
'Existing Dim Statements

'Determine if change was made to Team section
  If Not Intersect(Target, Range("A1:C4")) Is Nothing Then
     teamNum = 1

...The rest of your code...

  End If
End Sub

The key thing to remember here is that a Worksheet_Change macro will technically run with any change to the sheet. However, you can control what it does by setting criteria within the code itself. In other words, the code will run within any change to the master sheet, but it will only execute the "list build" instructions when the If that determines the Target cell's location is True. In your case, it may not make any difference if it rebuilds the lists every time, but it many cases it makes a huge difference. e.g. If someone wanted to copy the current row to another sheet when Yes is chosen from a drop-down in Column F, they could limit the time that the "copy section" of the code executes to only when the Target is in Column F and the Target = "Yes". Again, the macro would still run with every change, but nothing much would happen if the If's weren't True.

As an aside, if you aren't using an InputBox to get the team name, you don't need the Else: MsgBox section of the code.

re: "Can I use this in google sheets?"

Because one of my many skills is using Google, I am able to answer that question. ;-)

"You can run macros in Google Sheets, as long as they're written in Google Apps Script, a JavaScript-based language built into Google Apps. You cannot directly run Excel macros in Google Sheets, however."

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


Reply ↓  Report •

#5
March 8, 2018 at 13:36:07
I appreciate the help, and with some googling was able to convert to Google Apps Script.. posted here for anyone that may ever come across this:

function myFunction() {
  
 var teamNum = 1
 while (teamNum < 4) {
        var teamName = "Team" + teamNum;
        var teamSheet = teamName + " List";
        var sheet = SpreadsheetApp.getActive().getSheetByName(teamSheet);
        var master = SpreadsheetApp.getActive().getSheetByName('master');

        sheet.clearContents();
   
        var range = master.getRange("A1:C4");
   
        var numRows = range.getNumRows();
        var numCols = range.getNumColumns();
        for (var i = 1; i <= numRows; i++) {
           for (var j = 1; j <= numCols; j++) {
              var currentValue = range.getCell(i,j).getValue();
              if (currentValue.indexOf(teamName) > -1) {
                 // we have a hit
                 sheet.appendRow([currentValue]);
              }   
           }
        }
        var teamNum = teamNum + 1;
   }

  
}


Reply ↓  Report •

Ask Question