Look for a word in one column and update the value of the cell in another column

[andyexcel1]

Hi everyone,

I'm looking for some advice on what the best excel function is to use. If you look at the attached screenshot, I'm looking to insert a search term into 'Coupon code' H2 and a 'Percentage' into H3.

I'm then looking to Search column D for the code and then alter the value in column E by the percentage specified in H3.

If someone could put me on the right path in regards to the best functions to use then I'll go about seeing if I can get it to work how I want.

Thanks!

Screenshot_10_07_2015_11_53.png

[SPARKY347]

If your ok with it this can be done very easily with VBA. The best way is to just post a sample of your work book and Someone here will modify it. By writing the code. Would you be ok with a command button? To start the process to search and change the coding

[FDibbins]

Andy, welcome to the forum

If the data in column E is manually entered (or just values), then you wont be able to do this with regular formulas, you will need VBA (programming) for this.

If, however, column E contains formulas, then we can probably work something out for you with a formula.

I suggest that you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

[andyexcel1]

Thanks Ford.

I'll have to use VBA instead then as the values in column E are already populated and I just want to alter them based on the percentage value.

I've attached a workbook with the basic info on that I took a screenshot of.

I essentially want to enter a code into H3 and a percentage into H4. Then run the macro to search column D for any matches of the code name and then multiply the value in the corresponding cell in column E by the percentage that is set.

I've started learning and applying macros recently so I'll have a shot at it later and see how it goes.

Thanks everyone.

Test-workbook.xlsx

[SPARKY347]

here I attached a copy of a workbook with VBA, hope it solves your problems

[andyexcel1]

Thanks for this however I can't open it as I'm using excel 2011 on mac and activex doesn't appear to be supported. I'll see if I can get hold of a pc emulator or find a pc to open this on. Thanks

[andyexcel1]

Thanks for the help Sparky. I've been playing about with the code and trying to introduce a few more functions to it.

I've changed the way it works slightly so that it adjusts the value of one cell based on the percentage (Coupon value) and then deducts this from the cell in the (Line Total) column, then highlights the edit.

Now I'm trying to introduce another test before performing this where it checks column A (ID) for the first instance of a particular number and then only applies the function to the first instance it finds, if that makes sense.

I'm experimenting with the code and have added in a few lines:

Please Login or Register  to view this content.

I've added an AND function after the 'testcomp' to identify if the code exists and in the above instance have added '2002' to only apply the change if the code matches and the ID is 2002. Here is where I'm stuck though. I'd like it to check if the ID in column A is the first instance of a duplicate (or more) values.

E.g. in column A there are two instances of ID '2001' in A2 and A3 where the code is Code 1 for each of them. I only want to adjust the value of the Line total in the first instance where the ID matches and ignore the second row A3. Hopefully that makes sense. If someone could point me in the right direction it would be much appreciated.

I've attached the workbook. Thanks.

[SPARKY347]

Sorry I won't be at a computer till day's end but If all you want to do is do the call to the FIRST row in the loop. Add exit sub right below the end if. And I will kick the sub out on the first loop that the if statement is true