branfont Posted July 2 Share Posted July 2 I'm not a programmer, so I can't figure out what to do, but I can describe it if someone knows how to make programs that work directly in Excel. Link to comment https://www.neowin.net/forum/topic/1443387-anyone-know-how-to-program-for-excel/ Share on other sites More sharing options...
1 Dick Montage Posted July 2 Share Posted July 2 I have worked with VSTOs within the office suite. But I think you’ll be best served by telling people what you want to achieve and dropping the vagueness. zeke009 1 Share Link to comment https://www.neowin.net/forum/topic/1443387-anyone-know-how-to-program-for-excel/#findComment-598913910 Share on other sites More sharing options...
0 +virtorio MVC Posted July 2 MVC Share Posted July 2 You can write scripts within Excel using Visual Basic for Applications (VBA). You have to go into Options -> Customize Ribbon and ensure the Developer tab is enabled to get access to those tools. What are you looking to accomplish? Link to comment https://www.neowin.net/forum/topic/1443387-anyone-know-how-to-program-for-excel/#findComment-598913909 Share on other sites More sharing options...
0 branfont Posted July 3 Author Share Posted July 3 On 02/07/2024 at 16:52, Dick Montage said: I have worked with VSTOs within the office suite. But I think you’ll be best served by telling people what you want to achieve and dropping the vagueness. I wasn't being vague. I was doing the initial post as fast as I could because the bus was coming, then I got a little busy. Now I have time to give a description of what I need. Link to comment https://www.neowin.net/forum/topic/1443387-anyone-know-how-to-program-for-excel/#findComment-598913940 Share on other sites More sharing options...
0 branfont Posted July 3 Author Share Posted July 3 A friend & I are doing some private sales -- nothing illegal, if anyone was wondering -- and we need a way to track sales to a conclusion. What I'm looking for are 2 columns, let's call them A & B for now, a currently unknown number of rows. We need a place for a fixed monetary amount that will decrease as we add to the columns, but will also add a total amount at the bottom of each column. So, basically: (Fixed Dollar Amount) Column A...........Column B Row 1 2 3 4 5 6 7 8 9 10 and so on. (Earned Amount) I don't know if I explained it well. Link to comment https://www.neowin.net/forum/topic/1443387-anyone-know-how-to-program-for-excel/#findComment-598913943 Share on other sites More sharing options...
0 trogenda Posted July 3 Share Posted July 3 (edited) On 03/07/2024 at 04:34, branfont said: A friend & I are doing some private sales -- nothing illegal, if anyone was wondering -- and we need a way to track sales to a conclusion. What I'm looking for are 2 columns, let's call them A & B for now, a currently unknown number of rows. We need a place for a fixed monetary amount that will decrease as we add to the columns, but will also add a total amount at the bottom of each column. So, basically: (Fixed Dollar Amount) Column A...........Column B Row 1 2 3 4 5 6 7 8 9 10 and so on. (Earned Amount) I don't know if I explained it well. Sure, I understand your requirement. You want a VBA script that will help you track sales in two columns, with a fixed monetary amount at the top, and the total earned amount at the bottom of each column. Here's a VBA script that accomplishes this: 1. It initializes a fixed amount at the top of columns A and B. 2. As you enter sales amounts in columns A and B, it updates the total earned amount at the bottom of each column. ```vba Sub TrackSales() Dim ws As Worksheet Dim fixedAmount As Double Dim lastRowA As Long, lastRowB As Long Dim totalA As Double, totalB As Double ' Set the worksheet Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your sheet's name ' Set the fixed amount fixedAmount = 1000 ' Change this to your desired fixed amount ' Place the fixed amount at the top of columns A and B ws.Range("A1").Value = fixedAmount ws.Range("B1").Value = fixedAmount ' Find the last row in columns A and B lastRowA = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row lastRowB = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row ' Calculate the total earned amount in columns A and B totalA = Application.WorksheetFunction.Sum(ws.Range("A2:A" & lastRowA)) totalB = Application.WorksheetFunction.Sum(ws.Range("B2:B" & lastRowB)) ' Place the total earned amount at the bottom of columns A and B ws.Range("A" & lastRowA + 1).Value = totalA ws.Range("B" & lastRowB + 1).Value = totalB End Sub Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Me.Range("A2:B" & Me.Rows.Count)) Is Nothing Then Call TrackSales End If End Sub ``` ### Explanation: 1. **TrackSales Subroutine**: - This subroutine sets a fixed amount at the top of columns A and B (you can change the fixed amount as needed). - It finds the last used row in each column. - It calculates the total earned amount in each column (excluding the fixed amount at the top). - It places the total earned amount at the bottom of each column. 2. **Worksheet_Change Event**: - This event triggers whenever a change is made in columns A or B. - It calls the `TrackSales` subroutine to update the total earned amount. ### Usage: 1. Open your Excel workbook and press `Alt + F11` to open the VBA editor. 2. Insert a new module and copy the `TrackSales` subroutine into it. 3. Go to the sheet where you want to track sales, right-click the sheet tab, select `View Code`, and paste the `Worksheet_Change` event into the sheet module. 4. Adjust the sheet name and fixed amount as needed in the `TrackSales` subroutine. 5. Save and close the VBA editor. Now, whenever you add or change values in columns A or B, the total earned amount will be updated automatically at the bottom of each column. lordpake and Jim K 2 Share Link to comment https://www.neowin.net/forum/topic/1443387-anyone-know-how-to-program-for-excel/#findComment-598914007 Share on other sites More sharing options...
0 branfont Posted July 3 Author Share Posted July 3 I forgot to mention that I'll be using Excel on my Samsung Galaxy S23 Ultra. +virtorio 1 Share Link to comment https://www.neowin.net/forum/topic/1443387-anyone-know-how-to-program-for-excel/#findComment-598914014 Share on other sites More sharing options...
0 tsupersonic Posted July 3 Share Posted July 3 On 02/07/2024 at 21:34, branfont said: A friend & I are doing some private sales -- nothing illegal, if anyone was wondering -- and we need a way to track sales to a conclusion. What I'm looking for are 2 columns, let's call them A & B for now, a currently unknown number of rows. We need a place for a fixed monetary amount that will decrease as we add to the columns, but will also add a total amount at the bottom of each column. So, basically: (Fixed Dollar Amount) Column A...........Column B Row 1 2 3 4 5 6 7 8 9 10 and so on. (Earned Amount) I don't know if I explained it well. You don't need to program anything - this is exactly what Excel is built for. Just start entering the data... It's a simple math formula - should take all of 5 minutes to get this going at most. If you want to get fancy, put in pivot tables/charts. Also, if you need to actually write a script or code, just ask an AI assistant like ChatGPT / CoPilot / Gemini. Dick Montage, Jim K and winrez 3 Share Link to comment https://www.neowin.net/forum/topic/1443387-anyone-know-how-to-program-for-excel/#findComment-598914020 Share on other sites More sharing options...
0 pmrd Posted July 3 Share Posted July 3 On 02/07/2024 at 21:34, branfont said: nothing illegal, if anyone was wondering definitely illegal Jim K and tsupersonic 2 Share Link to comment https://www.neowin.net/forum/topic/1443387-anyone-know-how-to-program-for-excel/#findComment-598914026 Share on other sites More sharing options...
0 Jim K Global Moderator Posted July 3 Global Moderator Share Posted July 3 On 03/07/2024 at 05:30, trogenda said: Sure, I understand your requirement. You want a VBA script that will help you track sales in two columns, with a fixed monetary amount at the top, and the total earned amount at the bottom of each column. Here's a VBA script that accomplishes this: 1. It initializes a fixed amount at the top of columns A and B. 2. As you enter sales amounts in columns A and B, it updates the total earned amount at the bottom of each column. ```vba Sub TrackSales() Dim ws As Worksheet Dim fixedAmount As Double Dim lastRowA As Long, lastRowB As Long Dim totalA As Double, totalB As Double ' Set the worksheet Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your sheet's name ' Set the fixed amount fixedAmount = 1000 ' Change this to your desired fixed amount ' Place the fixed amount at the top of columns A and B ws.Range("A1").Value = fixedAmount ws.Range("B1").Value = fixedAmount ' Find the last row in columns A and B lastRowA = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row lastRowB = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row ' Calculate the total earned amount in columns A and B totalA = Application.WorksheetFunction.Sum(ws.Range("A2:A" & lastRowA)) totalB = Application.WorksheetFunction.Sum(ws.Range("B2:B" & lastRowB)) ' Place the total earned amount at the bottom of columns A and B ws.Range("A" & lastRowA + 1).Value = totalA ws.Range("B" & lastRowB + 1).Value = totalB End Sub Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Me.Range("A2:B" & Me.Rows.Count)) Is Nothing Then Call TrackSales End If End Sub ``` ### Explanation: 1. **TrackSales Subroutine**: - This subroutine sets a fixed amount at the top of columns A and B (you can change the fixed amount as needed). - It finds the last used row in each column. - It calculates the total earned amount in each column (excluding the fixed amount at the top). - It places the total earned amount at the bottom of each column. 2. **Worksheet_Change Event**: - This event triggers whenever a change is made in columns A or B. - It calls the `TrackSales` subroutine to update the total earned amount. ### Usage: 1. Open your Excel workbook and press `Alt + F11` to open the VBA editor. 2. Insert a new module and copy the `TrackSales` subroutine into it. 3. Go to the sheet where you want to track sales, right-click the sheet tab, select `View Code`, and paste the `Worksheet_Change` event into the sheet module. 4. Adjust the sheet name and fixed amount as needed in the `TrackSales` subroutine. 5. Save and close the VBA editor. Now, whenever you add or change values in columns A or B, the total earned amount will be updated automatically at the bottom of each column. ...all that work only to later find out... On 03/07/2024 at 06:50, branfont said: I forgot to mention that I'll be using Excel on my Samsung Galaxy S23 Ultra. adrynalyne 1 Share Link to comment https://www.neowin.net/forum/topic/1443387-anyone-know-how-to-program-for-excel/#findComment-598914027 Share on other sites More sharing options...
0 branfont Posted July 3 Author Share Posted July 3 On 03/07/2024 at 06:30, pmrd said: definitely illegal I never break any laws, why would I start now?! Link to comment https://www.neowin.net/forum/topic/1443387-anyone-know-how-to-program-for-excel/#findComment-598914029 Share on other sites More sharing options...
0 tsupersonic Posted July 3 Share Posted July 3 On 03/07/2024 at 08:44, branfont said: I never break any laws, why would I start now?! really sus when you said it was not for anything illegal. Why would we think that in the first place? But now that you said it's not illegal, it's definitely illegal 😛 Link to comment https://www.neowin.net/forum/topic/1443387-anyone-know-how-to-program-for-excel/#findComment-598914033 Share on other sites More sharing options...
0 branfont Posted July 3 Author Share Posted July 3 On 03/07/2024 at 06:57, tsupersonic said: really sus when you said it was not for anything illegal. Why would we think that in the first place? But now that you said it's not illegal, it's definitely illegal 😛 Because, that's what most people think, especially when you're not white, so yes, I have to add that, even when talking with people in person. winrez, Jose_49 and pmrd 3 Share Link to comment https://www.neowin.net/forum/topic/1443387-anyone-know-how-to-program-for-excel/#findComment-598914037 Share on other sites More sharing options...
0 tsupersonic Posted July 3 Share Posted July 3 On 03/07/2024 at 09:28, branfont said: Because, that's what most people think, especially when you're not white, so yes, I have to add that, even when talking with people in person. 🤦♂️ who said anything about color of skin/race?! respectfully, we're just trying to help you out. +hedleigh and Jose_49 2 Share Link to comment https://www.neowin.net/forum/topic/1443387-anyone-know-how-to-program-for-excel/#findComment-598914040 Share on other sites More sharing options...
0 adrynalyne Posted July 3 Share Posted July 3 On 03/07/2024 at 06:28, branfont said: Because, that's what most people think, especially when you're not white, so yes, I have to add that, even when talking with people in person. 🤦🏻 pmrd 1 Share Link to comment https://www.neowin.net/forum/topic/1443387-anyone-know-how-to-program-for-excel/#findComment-598914048 Share on other sites More sharing options...
0 Kelxin Posted July 4 Share Posted July 4 1. What you're trying to do is literally what spreadsheet applications are designed for and "programming" isn't necessary. 2. If you're running it on your phone, just use Google sheets instead of anything Microsoft. 3. This thread literally explains so much that wasn't asked but never really gets to the point of what was asked. a. We know you're black. b. We know you don't know much about spreadsheets c. We know you assume that everyone else automatically assumes lesser of you. (I mean, how the hell would we have known anything about you without you telling us?) d. You assume everyone out there is white and racist. e. You haven't asked many questions online. Just get to the point of what you need and more than likely we'll post a solution and get on with our day feeling like we just did something good. f. Don't treat Neowin like Reddit and you won't get Reddit responses. g. You haven't used any of the new AI systems like chatGPT, bings integration or Google integration. Most of those would have spit out an answer to your question in under 3 seconds. "how do I make a spreadsheet that.. blah blah blah" Link to comment https://www.neowin.net/forum/topic/1443387-anyone-know-how-to-program-for-excel/#findComment-598914191 Share on other sites More sharing options...
0 branfont Posted July 5 Author Share Posted July 5 I tried that and it's not working. A1: Name =SUM(A2:A62) B1: Name =SUM(B2:B62) C1: Amount =C1-(A62+B62) I put them in their required place, but then I get the following error: Link to comment https://www.neowin.net/forum/topic/1443387-anyone-know-how-to-program-for-excel/#findComment-598914376 Share on other sites More sharing options...
0 Jose_49 Posted July 5 Share Posted July 5 On 05/07/2024 at 15:25, branfont said: I tried that and it's not working. A1: Name =SUM(A2:A62) B1: Name =SUM(B2:B62) C1: Amount =C1-(A62+B62) I put them in their required place, but then I get the following error: Hi Branfont 🤗. Where are you placing the SUM? This error usually happens when a cell is referring to another cell that refers to the cell you were referring. Imagine that you have cell A1 `B1`, and in B1, you have `A1`. That's a circular reference. Link to comment https://www.neowin.net/forum/topic/1443387-anyone-know-how-to-program-for-excel/#findComment-598914382 Share on other sites More sharing options...
0 branfont Posted July 6 Author Share Posted July 6 On 05/07/2024 at 07:59, Jose_49 said: Hi Branfont 🤗. Where are you placing the SUM? This error usually happens when a cell is referring to another cell that refers to the cell you were referring. Imagine that you have cell A1 `B1`, and in B1, you have `A1`. That's a circular reference. The sums are put in their respective column. Link to comment https://www.neowin.net/forum/topic/1443387-anyone-know-how-to-program-for-excel/#findComment-598914509 Share on other sites More sharing options...
Question
branfont
I'm not a programmer, so I can't figure out what to do, but I can describe it if someone knows how to make programs that work directly in Excel.
Link to comment
https://www.neowin.net/forum/topic/1443387-anyone-know-how-to-program-for-excel/Share on other sites
18 answers to this question
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now