Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Vba code for find and replace cells in next column


I need help in this.

For example in sheet 1 there is a database where,

In column a there is name of items like apple, orange, pineapple, banana etc.

In colun b stock of that item is present. For example 10, 22, 33 etc

Now coming to sheet 2 (sale)

In a cell (a2) i will write banana and in cell (b2) i write a numeric value for example 5

There will be a button, when i will press the button i want the numerical value entered in b2 to get deducted from my sheet 1's banana stock.




 Hello debashisy2k2016 and welcome to the forum.

You didn't upload a sample file to show what you are trying to do but you did provide some good explanation. We typically don't provide solutions unless the poster has already tried to do it themselves, but this sounded like a fun little exercise.

I've attached my file so you can see how it works. You will need to make modifications to suit your needs, but this will give you a starting point. There are other ways to achieve this, but I opted for a simple/basic method.

Hope this helps.

Cheers   :-)



Hi Willie.

Nice solution but looks like this is another user who fails to see the solution here and/or fails to respond in any way (which seems to be an increasing trend sadly). Sometimes I wonder why we bother...

John_Ru (rep: 6142) Nov 30, '22 at 10:45 am
Add to Discussion

As a non-VBA alternative, you can use a regular cell formula using the SUMIF function. 

In the attached file, sales are entered in Sheet2, one per line with the item (e.g. Apples) in column A and the quantity in column B. 

In sheet 2, this formula in cell C2 uses SUMIF to  add all rows in Sheet2 where the value in column A matches that in Sheet1 column A and subtracts it from an initial stock in column B:


where the argument in bold points to the quantities in column B.

This formula can be copied down in Sheet1 since the $ signs "lock" the ranges. You'd need to increase the $B$100 bits if you expect more than 99 sales to be recorded.

Hope this helps. 



Did you try the answer from Willie or mine? 
John_Ru (rep: 6142) Dec 15, '22 at 8:07 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login