Auto increment a specific cell value based on a different but related cell value

bradderst

New Member
Joined
Nov 20, 2011
Messages
1
Ok, I'm a VBA novice so please bear with me!

In my excel workbook, I have a customer table and invoice sheet among many others.

As part of my system, there is functionality to grant new customers with a discount on the first purchase. Within my customer table, the last two columns are "Number of Purchases" and "Customer Type" (either single/multiple depending on no. of purchases), which are then used to determine whether the discount is valid or not on the invoice.

Once an invoice has been created, archived and refreshed with the customer selected (via a Customer ID), I would like some code to auto increment the number of purchases on the Customer table for that specific customer to +1.

For example, customer called Bob (Bob-1);
Number of purchases = 1
Customer Type = Single
Discount = Yes

Invoice then created for Bob (using Bob-1 as the unique value), sent off and refreshed. New figures should be:

Number of purchases = 2
Customer Type = Multiple (can be achieved by using IF statement on No. of purchases)
Discount = No

Hope that makes sense and thanks in advance!
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

bensonsearch

Well-known Member
Joined
May 26, 2011
Messages
844
Hi
can easily be done, im assuming your invoicing is in vba?

the vba to make it 1 more would be

Code:
'all your code for invoices
dim count as integer
 
count = sheets("Sheet1").range("A1").value 
 
count = count + 1
 
sheets("Sheet1").range("A1").value = count

change the Sheet1 to your customer sheet and the range A1 to the cell that has the invoice number in it
 

Forum statistics

Threads
1,143,654
Messages
5,720,094
Members
422,266
Latest member
Mattyw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top