i have a spreadsheet with data that is exported to Excel via our in house investment system, the report looks something like below, though real data consists of 2000 rows of data. Where we have O/S in Bank this means these entries are all physical bank entries i.e statement credit and statement debit, and where we have O/S not in Bank these are all accounting entries, i.e. Ledger Debit and Ledger Debit.
What i am after is a macro that will insert a column next to Team and then input SDR SCR on all statement entries and LDR and LCR on all Ledger entries, the final report should look like the second spreadsheet....
I basically need to add the ongoing totals for each house team-so when scores are inputted, the house teams score is automatically being added up.....I think I would need to mail someone the sheet to show how far I have got as I dont think it is a simple count formula.
I think I'm just missing something obvious here but I want to calculate commission based on £1 Plus 10% of Hammer Price. That bit's easy but I need it to ignore the hammer price if blank. In other words if I leave the hammer price empty, at the moment the sheet adds £1 to the commission cell.
I have a string of numbers and characters and I have a Macro that I copy-pasted from somewhere online that REMOVES any character that is not a number.
Is a great Macro that transforms: "-a209 03 . 1 - 22 46" to 2090312246 I can send the code on request.
However the problem comes when running the macro into that string excel transforms it DIRECTLY into a Numeric. That is we are missing the first two, or first characters when they are zeros.
For example: "007 01 1.32.08" becomes 70113208 in stead of: 0070113208
And this is a problem.
I have tried to define the cell as text before running the macro. Fails. I have tried to re-define the cell afterwards as text but the zeros do not appear. Fails.
Including in the Macro a last minute section that forces the cell to remain as text...(seems easy! but I have do not know how to program in VBA)
I have a spreedsheet with 17,000 rows in it. In column D I have ID numbers and in column B I have the dates. There are multiple entries for each ID in column D which gives multiples dates each entry was given in column B. I need a VBA Macro that will identify duplicate entries in Column D and keep only the most recent entry by date in column B.
I have the following Macro that I run assigned to a Button.
Sub AskAndDo() If MsgBox("Have you Finished collecting data ?", vbYesNo + vbQuestion) = vbNo Then Exit Sub Else If MsgBox("Have you Printed the Reports ?", vbYesNo + vbQuestion) = vbNo Then Exit Sub Else ActiveWorkbook.SaveAs Filename:="\gamingntcompanyFLOAT MASTERFloat_Sheet_" & Format(Date, "_YY_MM_DD") End If End If Application.Quit End Sub.......
After all the awesome macros I've obtained with the help of all of you, I now have over 30 macros, each in its own module. I have tried without success to re-name the modules with no luck. How is everyone organizing these?
I have a big clunky spreadsheet that I thought I'd tidy up by adding a userform to keep the info
There's a combination of textboxes, option buttons, listboxes and a multipage. Everything seems to load correctly, and selecting an item in a listbox correctly fills the textboxes, but when I change the value of the textboxes, only 2 out of 4 holds that value and sends it to the correct cell.
The code that is supposed to send all the values is;
VB: Private Sub CommandButton1_Click() 'check that a row has been selected If Range("K3") = "" Then
[Code].....
The culprits are TextBox3 and TextBox4.
I've tried changing the names, I've checked that there are no other textboxes pasted on top, I've even tried adding in a MsgBox line to show the value of the textbox; if the form loads and the box grabs a value of "ABC" from the spreadsheet, and I change it to "DEF", when I hit the button to execute the above code the textbox goes straight back to "ABC".
But it's not an issue with TextBox2 ...
Other parts of the code include a UserForm_Activate block and 5 x ListBox_Click's.
I have a coumn starting from A2 onwards that holds data with various characters, all that i require is the 1st 9 characters (These are always numbers).
i am a builder and i want to make a spread sheet to keep track of what i have been paid and the labour costs i have paid out
so i am working on 20 houses that all have 7 stages of payment each, and 5 to 8 men working on them who receive a price for each stage, but are paid a portion of that price each week while they are working on that stage
what i am trying to do is set up a sheet to keep track of what has been paid and the balance remaining for each stage and what has been received
i have tried doing it a few different ways but im none to clever with spread sheets and what is causing me the problem is next to each payment made i need to put the date and the name of the person who was paid so it always seems to just look a cluttered mess
have any of you got any ideas of how i could simplify this and make it look neat and easy
or alternatively is there some other software that may suit this application better?
I have a spreadsheet that has numbers in column A (example = 31, 15, 159) I want to count the number (159) each time it occurs and keep a running count of it in say column B... example..... A1 = 159, A2 = 31, A3 = 159, so B3 would show (2) as the number 159 has appeared 2 times thus far.
I'm using the hidden-row trick to auto-fill cells beneath the hidden rows.
For this to work, every cell that is to be auto-filled in must have a character in every cell above it.
How can I make sure that my unfilled cells always have a character in them? If a user deletes the contents of the cell, I want a single character to stay in the cell (I want a backslash). This way, there will always be either a backslash, or the user-filled data in the cell. It can never be empty.
my spreadsheet I have a column(7.65462E+11) it's a upc code.I can format the column to read as a number (value) but when I save it I does not save the number format.It's really interesting that there are some rows that read exactly the same and those rows keep the number formatting.Going nuts.Also when I try to save a date format(2013-mm-dd) It won't save,I've read that it's system default in U.S,is there a way around that ?
I'm trying to make it so that a lastrow I defined in one module will still be defined in another macro.
My background for this - my macro is too large for one module, therefore, I split it in two. However, the new module uses defined variables (multiple lastrow definitions) from the original module.
So, is there a way to make it so when I originally define a lastrow in one module, other modules will recognize it?
or whatever the number is after A1D, but when AK8 = a value of 1 or 2 or 3...... It comes back as A1D1 or A1D2... How ca I keep the two digits in AK8 to stay and put the value the way I want in AE5 ...
When I copy/paste an Excel 2003 file containing macros into an email and send and open into another computer, the macros are gone. How can I ensure the macros go with the file?
I have a formula in column A, =AVERAGE(C2:Z2). I have a macro that moves columns C to Z over one column to the right every day. How do I keep the above formula the same as it is now. At present the range also moves one column. I have added $ but it still changes
I have dumped main frame data into excel. It is customer info that is in three rows of data each. I need to know if its possible to group or somehow keep three rows of data together for multiple sorts. The first row is the customer name, the second row is their first line of address, the third row is the city, state and zip. I need to add columns to this dumped data, but I will need to do a number of sorts for different views of the data. But as I've stated every 3 rows go together. I've tried different ways to group (for lack of a better word) the three rows, but nothing I've tried works. I've also done multiple searches, but either I'm not searching on the right word or this can't be done.
I have groups of information I need to stay together, but I'd like to be able to sort them by last name. Today, Tomorrow and Long Term are conditionally formatted (=ISBLANK is green and =ISTEXT is red). As long as the information stays together in a group, that won't be a problem. I have a hidden column A where every cell next to the form refers back to the cell with the last name in it. But when I tried to sort by that, it still seemed to mangle my form. There are no merged cells here. There will eventually be dates, locations (booked) and absent, etc.. filled in. I just want to be able to sort this entire block, without changing anything in it, by last name. There are about 100 of these blocks of info.
LAST NAMEFIRST NAMETodayTomorrowLong TermDATEBOOKEDReason for SubAbsent EmployeeHours is WorkingDayHappyPhone Number:555-5555Cell Number:555-1234Special Skills:Notes:
I have a spreadsheet that I want to keep the blank rows that seperate the data for asthetic reasons. Can I sort just the rows with data in them and still keep the blank rows where they are?
I have two excel sheets in the same workbook. I've linked column B2 in Sheet1 with column A1 in Sheet2. It works great!
The only problem is that when I add a row in Sheet1, it appears in Sheet2, but the rest of the data on that sheet doesn't move alongside the rest of the rows. So if Sheet2 looks like this:
Abby 16 Amy 15 Jenna 14
And I add in another name in Sheet1, lets say Ben, then Sheet2 will look like this.
I'm trying to sort by City first, then by Report #, but keeping the highlighted rows together. If I use the custom sort it will through my D2's at the bottom and not keep them with their city, report, and D1. How do I get them to stay together? I have attached my document example.
I have two columns, the left of which consists of merged cells. I got the document in this format, but to work with it, I need to unmerge the cells in the left column - simple enough.
However, when I do so, only the top cell in the group contains any actual data. What I get is this:
Age 18-20
21-25
26-30
And I would like it to look this way:
Age 18-20
Age 21-25
Age 26-30
Is there a simple way to get this result automatically when unmerging the cells? And if not, what is the fastest solution to avoid tons of manual copying?
There is one crucial feature to the 2007 Excel that has been overlooked.
Throughout all versions of Excel there has always been the feature of converting text to table, however there is no way to do this in reverse.
There is no way to merge two columns of data and to keep all of the data without one column overwriting the other. If only there were an automatic way to merge two columns of data and to be able to place a delimited character in-between, just like the “Convert Text to Columns Wizard”, except in reverse. Currently, the only way to merge two columns of data is to manually go row-by-row and cut and paste them together. However, for 500,000 rows of data… this is impossible. Or to use a function to merge two columns, however this requires that the original two columns remain. This is also unacceptable. If Microsoft really wants to make Excel more functional, how can this vast improvement be overlooked?
