Search each row for string, put result in last column
Hi All
I am hoping someone can help, as the more I look for a solution the more confused I get about loops and find().
Is it possible to use VBA to search through rows of data for a specific string (say 'Apples') and then insert a boolean result in the final column. It'll be for datasets no more than 300-1500 rows long, 15-20 columns wide
Picture2.png
Any advice would massively appreciated
All the best
Vicky
Will this work for you?
=ISNUMBER(MATCH("apples",C5:H5,0))
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
(Using your posted file for cell references) Another formula you can try...
=COUNTIF(C5:H5,"apples")>0
Thank you both for the fast replies.
Is there any way to achieve this with VBA so that there are no formulas on the worksheet?
Hi ! Try this demonstration …
Even for a dumb VBA way the better is to use a smart table formula :
PHP Code:Sub Demo1()
Dim C%, F$
With Sheets("Sheet1").ListObjects(1)
C = .ListColumns.Count
F = "=COUNTIF(" & .Name & "[@[" & .Range(2).Text & "]:[" & .Range(C - 1).Text & _
"]],""" & Replace(.Range(C).Text, "Has ", "") & """)>0"
With .DataBodyRange.Columns(C): .Formula = F: .Formula = .Value2: End With
End With
End Sub
► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄
Happy to help and thanks for the feedback
Oh wow! Thank you Marc
This is superb. So much more than I hoped for. It works no matter where the table is positioned and it searches for whatever word I put in the final column title. Amazing stuff.
But I'm having a little trouble implementing it in my workbook. After a bit of testing I realised the code doesn't like these characters in my column titles ":" and "[ ]". Is there any way to get around this without renaming the columns? Would double quote marks mark?
I know renaming the columns should be simpler but I have other VBA scripts that are reliant on the column name layouts (they update pivot table categories in 10 other sheets) so ideally trying not too. See attached revised doc
Picture2.png
Thanks again
All the best
V
Last edited by vwhite; 06-09-2020 at 07:29 AM.
(removed …)
Last edited by Marc L; 06-09-2020 at 08:05 AM.
Re: Search each row for string, put result in last column
To understand the Excel table basic syntax start to enter a COUNTIF formula then using the mouse
to select the first and the last cells so you must see how those characters are escaped with a quote.
For each header you can use the VBA Replace function to add a quote for those characters
or use the Address property of a cell rather than the Excel table syntax
or better just renaming the headers, yes I know, obviously too obvious …
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks