Multi tool use

Clash Royale CLAN TAG

Excel - Search entire worksheet for a cell contain a string of text (formula)

Hopefully this is a fairly easy one.

I have an Excel file which has a Summary tab at the start, followed by several tabs which contain weekly information. As the weeks progress, I may add additional rows to the weekly sheet, meaning that the total is not always in the same cell on each sheet.

I am looking for a formula that will search a specified weekly sheet for the words "Grand Total" and return it's address, which I can then build into other formulas, so that the Summary sheet will populate the correct values regardless of whether additional rows have been added.

2 Answers
2

Hi, your question should always contain your current efforts and ideally some code as well. But given it's a fairly basic question to answer and you're new here I decided to answer it

With that aside...:

Option Explicit
Private Function get_total () As Range
Dim ws As Worksheet: Set ws = Sheets("Your Sheet Name") 'set your own
Dim lc as Long
lc = ws.Cells(1, Columns.Count).End(xlToLeft).Column
' determines last column with data

'some other useful variables
Dim lr as Long 'last row
Dim temp as Range 'find result
Dim i as Long ' loop index

For i = 1 to lc
lr = ws.Cells(Rows.Count, i).End(xlUp).Row
Set temp = ws.Range(Cells(1, i), Cells(lr, i)).Find("Grand Total", lookin:=xlValues)
If Not temp Is Nothing Then 'if we found grand total, return it
Set get_total = temp 'return range of result
End If
Next i

Exit Function 'if no Range found, exit function ("return;")

End Function


Technically, that alone is the answer, but for practical use, you might want to visualize it

So with this function you will loop through all the columns and it will stop upon Grand Total is found, or alternatively will "return" void if not found any

return

Obviously, this only is a function, so if you wanted to print out the result, you could create a procedure like this:

Private Sub print_grand_total()
Dim res as Range
Set res = get_total
If Not res Is Nothing Then
MsgBox("Found in Cell[" & res.row & "," & res.Column & "]")
Else
MsgBox("Grand Total not found!")
End If
End Sub


So, I've set an example sheet:

After running the procedure, it returns the result, successfully:

Note: Small implementation foresight: This will not work in cases, where the first row is empty (because it can't detect last column properly then). Edit the lc code depending on where your data range begins

lc

Forgive me if this is no help, but here is a similar post that could possibly be of use:
search entire Excel workbook for text string and highlight cell

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.