UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Simple VBA Help, Office 2003    
 
   
davidgk
post Feb 1 2018, 02:32 PM
Post#1



Posts: 7
Joined: 27-May 15



Hi
I just need a simple [not simple for me...] VBA
Worksheet is as follows
Column 1 = items
Column 2 = qty
Column 3 = price
Column 4 = percent off price [will vary by item]
Column 5 = final price after the percent discount in column 4
Column 6 = total qty x price
Column 7 = total of qty 6 with the discount
Would like to be able to click a button that would do the following

Calculate the price for column 4

Put in the row following the last row [always a different row depending on how many items]
The sum for all columns
Thanks
Also
I need to have this on toolbar or anywhere to be accessed on all excel files
Go to the top of the page
 
kfield7
post Feb 1 2018, 02:39 PM
Post#2



Posts: 858
Joined: 12-November 03
From: Iowa Lot


Why not just use Excel formulas in the cells?
For the summing row, leave an empty row between the last row and the summing row, make the totals include the empty row, make the empty row very skinny or fill with a color for a line, then any insertions above that line will be included in the sum formula ranges.

No VBA needed.

Also, why would you want this button available on "all excel files"?
Go to the top of the page
 
kfield7
post Feb 1 2018, 02:42 PM
Post#3



Posts: 858
Joined: 12-November 03
From: Iowa Lot


Also, you want to calculate column 4 -- from what? C4 = (C3 - C5)/C3 ??
Go to the top of the page
 
davidgk
post Feb 2 2018, 11:22 AM
Post#4



Posts: 7
Joined: 27-May 15



HI

Sorry I meant column 5 should be calculated for the final price [regular price less the percent discount]
The reason I need this in VBA is because I’m getting these sheets sent to me from different sources maybe 15 - 30 in a week
and VBA will be much more time efficient than putting in the formulas and dragging down etc etc ,
Any help will be appreciated
Thanks
Go to the top of the page
 
dflak
post Feb 5 2018, 02:24 PM
Post#5


Utter Access VIP
Posts: 6,149
Joined: 22-June 04
From: North Carolina


Attach a sample workbook. You will probably have to put your code into your Pesonal.xls file (or whatever it is for Excel 2003). Otherwise, you'll have to find a way to import it or you can put the code in another workbook and have that book read the "transient" workbooks as a data source.

Do you have any need to consolidate the data or keep a history?

--------------------
Dan

One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
Please zip and attach samples. It makes life easier for those who have to figure out what you are trying to do. Thanks
Go to the top of the page
 
davidgk
post Feb 6 2018, 11:53 AM
Post#6



Posts: 7
Joined: 27-May 15



Hi
Please see attached sample excel file
any help would be appreciated
Thanks
Attached File(s)
Attached File  SAMPLE_CALCULATION_1.zip ( 3.22K )Number of downloads: 2
Attached File  SAMPLE_CALCULATION_1.zip ( 3.22K )Number of downloads: 0
 
Go to the top of the page
 
bazza
post Feb 13 2018, 11:00 AM
Post#7



Posts: 339
Joined: 13-February 03



Ignore my very sloppy code, I'm very new to this myself

This should do what you want.

It finds the last rows with data and works from there. As a result you have to reset the data each time you do a calculation and it only works with the formatting as you provided in your example.
Attached File(s)
Attached File  Copy_of_SAMPLE_CALCULATION.zip ( 15.71K )Number of downloads: 3
 
Go to the top of the page
 
davidgk
post Feb 14 2018, 11:53 AM
Post#8



Posts: 7
Joined: 27-May 15



Hi bazza
thanks a lot for your help
it works fine
The only thing that i will to do is make it work even without the word sum [or any entry] in the bottom of the columns
Go to the top of the page
 
dflak
post Feb 15 2018, 11:57 AM
Post#9


Utter Access VIP
Posts: 6,149
Joined: 22-June 04
From: North Carolina


David, what version of Excel do you have? If it's 2007 or later, there may be a table solution.

Also, have you considered putting the sums at the top of the data? This way the data can extend down indefinitely without having to push the sum row down.

--------------------
Dan

One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
Please zip and attach samples. It makes life easier for those who have to figure out what you are trying to do. Thanks
Go to the top of the page
 
davidgk
post Feb 15 2018, 12:45 PM
Post#10



Posts: 7
Joined: 27-May 15



Hi
The reason I need this in a button or actually in the toolbar/menu bar globally with VBA , because I get these send to me a few times a week with the main info filled item , qty, price, disc, and I want to do the calculation on “that” sheet [row numbers can vary significantly 30 or sometimes 1500 etc. etc.]
Thanks
Go to the top of the page
 
dflak
post Feb 15 2018, 12:52 PM
Post#11


Utter Access VIP
Posts: 6,149
Joined: 22-June 04
From: North Carolina


Have you considered this: have a spreadsheet that opens the report does the work in the report and then saves the report? The report itself does not have any VBA code in it. All the work is done with the "outside" spreadsheet. All you need to do is tell the outside spreadsheet where to find the report (path to it) and what it is called. In fact, if the name of the report is standard except for a date stamp, you could even "calculate" the file name.

Another option is, if you want to keep a history of these reports, is to put the incoming reports in an "IN" directory, process them and then move them to an "OUT" directory. This way, they only get processed once, and it doesn't matter what the file name is.


--------------------
Dan

One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
Please zip and attach samples. It makes life easier for those who have to figure out what you are trying to do. Thanks
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    22nd June 2018 - 08:16 AM