Search through Cells Containing String using VBA Excel Programming

hey everyone in this video I'm going to show how we can programmatically search a column going row by row using VBA I mean I know we can do formulas in order to count certain items in here but what I'm going to do is programmatically show you how you can count every single item in row a that contains a particular string so we're going to be using a string function as well as the VBA programming I already have some words set up in column a and it goes all the way down to I believe row 400 so we go down to row 400 and I'm going to go to my developers tab in my developers tab I'm going to go to my insert and I'm going to insert an ActiveX control the one I'm putting in here is just the command button and just right click that properties just rename it to to your liking I'm going to want to put scan online under caption scan and go on and right now we're in design mode I'm going to double click it and I'm going to do a variable call it row number and I'm going to call it 1 put it as 0 and the first thing I'm going to do is just do a loop through it do do events maduro number is equal to row number plus 1 that way that way we get it goes to row 1 and then every repetition is going to increment by 1 now we're going to do loop until I'm going to put another variable first let me let me do item in review is equal to sheet and I'm going to do this that way as a quote this will be the name of the sheet right here in the bottom this can be sheet 1 or whatever the title is dot range now we're going to be looking at column a and then the row number so we're going to loop until item and review is equal to blank and just so you can so we can show you where we are at Monday messagebox row number I'm going to toggle back to my visual basic editor I'm sorry I'm going to toggle back to Microsoft Excel take us out of design mode I just hit scan so this is all this does is it's counting one by one until we're at the end of the sheet where it's blank and they'll give us a return the value 401 but what we're going to do is create we're going to put in a little bit of programming some more code in here so that we can find a certain string and I'll just put f3 as where we can insert a search string someone type in search the string and we'll plug in the value right here let's just say we want to look for the lip for the string oh you and right now this is f3 so I'm going to go back into design mode double click scan and item and review all this what mitem in review is is data that's being scanned so what we can do is put if I n STR and we'll do item enroute in review if it contains the string oh you then work the code that's between the if and the end if is the code I'll be executed so let's just do a count count of string is equal to zero we'll keep count of how many times the oh you occurs so in Purcell so count of string is equal to count of string plus one and we can put a message box the string occurred and we'll do and the count of string space times so let's try playing this code take it out of design mode and hit scan so the string occurred 15 times now right now I am out looking at the cell f3 if you notice in the visual basic environment all I did was happen oh you so to kind of just expand that way we're only looking at the search string right here we'll put search string is equal to sheets that's going to be sheet 1 range and we're going to be looking at the range f3 and we're going to copy that variable and all we're going to do is change oh you to search string so an item review we're looking for a search string and actually this is supposed to be greater than 0 because what I NS TR is going to do in this case it's going to tell us the position of the search string as an integer so in this case we're looking for oh you it's going to return the the integer of oh I mean it's going to return the integer of Oh us to where it occurs in the word amount so there show you a real quick example of this I'm going to insert another ActiveX control I'm going to place this code over here I'm just going to name it test I and STR show you how this works real quick this is just one way on how to use this function I'm going to message box we're going to I n STR the word amount and looking for the string owe you so if we're to play that code right there taste a design mode and play it that's telling you that oh you is that is the third position so a.m. and then O being the third letter so now that we've set up the scan work let's try this out again when I run it the string occurred 15 times if we were to put change this out to let's say um scan it that's string occurred six times and that concludes this video guys I will put this code as to whatever will fit into the YouTube description if you'd like to copy the code thank you for watching

Channel: Alex C

Views: 93,941

Rating: 4.716814 out of 5

Keywords: Excel VBA Programming, VBA Programming, Excel 2010, Macro Programming, Search, through, Cells, and, count, the, cells, Containing, certain, String, using, VBA, Excel, Programming.

Id: cIBazCfmbWE

Length: 6min 40sec (400 seconds)

Published: Wed Nov 07 2012