House Keeping Macro
Mar 26, 2007
I want to create a kind of "Housekeeping Macro" that will run based on one single piece of information a number.
I want an inputbox to ask me for a number.
Based on that number I want the macro to do the following -
Go to Ws1 - Membership
Seach down column A until it finds the number put into the Inputbox
When it finds it do the following
Go to Column N and enter todays date as a value.
Go to Column Q and Enter No
Delete the data in R:S and U:V and X:Y and AA:AB and AD:AE
Go to Column J This contains the name of another worksheet i.e. Data1
Go to the worksheet Data1
Search down Column C and find all instances of that the the number entered into the inputbox (there could be several).
Go to each instance and delete the number.
View 9 Replies
ADVERTISEMENT
Oct 27, 2008
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....
View 9 Replies
View Related
Jul 3, 2007
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.
View 11 Replies
View Related
Apr 5, 2013
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.
In Summary:-
F3 is Hammer Price
K3 is Commission (=(F3*.1)+1)
K3 should NOT state £1 if F3 is empty
View 6 Replies
View Related
Jan 29, 2014
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)
View 3 Replies
View Related
Feb 14, 2011
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.
View 9 Replies
View Related
Nov 13, 2008
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.......
View 9 Replies
View Related
Feb 17, 2009
I'm trying to create a VBA macro that will allow me to copy a formula from one sheet to another whilst keeping all the original references.
E.g.
If the formula on Sheet1 is:
= sum(A1:B6)
then the copied formula on Sheet2 would read
=sum(Sheet1!A1:Sheet1!B6)
You can do this by cuting the cell, but I don't want to do this, I want to leave the original cell unchanged.
I'm sure there is some simple VBA code to do this, but I can't seem to figure it out.
View 9 Replies
View Related
Mar 27, 2009
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?
View 2 Replies
View Related
May 28, 2013
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.
View 4 Replies
View Related
Apr 30, 2013
I actually have time entered as a number and decimal.
10.29 means 10 minutes and 29 seconds. How can I get that to only seconds 629?
View 3 Replies
View Related
Jan 9, 2009
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).
View 6 Replies
View Related
Sep 22, 2008
In a basic formula, I need to take one number from another. But I need to keep the answer to zero, not a negative number.
View 2 Replies
View Related
Apr 24, 2009
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?
View 10 Replies
View Related
Sep 29, 2009
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.
View 3 Replies
View Related
Jan 31, 2013
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.
View 3 Replies
View Related
May 13, 2013
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 ?
View 2 Replies
View Related
Jun 11, 2014
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?
View 8 Replies
View Related
Aug 15, 2014
How can I get this macro to run without removing the hyperlinks from the 1st line of each group?
Sub Transpose()
sn = Sheets("sheet1").UsedRange.Columns(1).Resize(, 4)
For J = 1 To UBound(sn)
sn((J - 1) UBound(sn, 2) + 1, (J - 1) Mod UBound(sn, 2) + 1) = sn(J, 1)
Next
Sheets("sheet2").Cells(1).Resize(UBound(sn) UBound(sn, 2) + 1, UBound(sn, 2)) = sn
End Sub
View 1 Replies
View Related
Aug 19, 2014
I have the following
[Code] .....
AK5 value will be 2 - 21.
I need it to display
A1D05
or
A1D21
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 ...
View 5 Replies
View Related
Nov 19, 2009
I'm looking for when 'Chart 1' appears in the code, I would like it to link to the current chart I've just created.
View 4 Replies
View Related
Jan 5, 2010
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?
View 8 Replies
View Related
Jan 30, 2012
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
View 5 Replies
View Related
Sep 20, 2012
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.
View 9 Replies
View Related
Oct 24, 2008
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:
View 9 Replies
View Related
May 16, 2008
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?
View 9 Replies
View Related
Feb 23, 2013
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.
Abby 16
Amy 15
Ben 14
Jenna
when I want all my data to stick together.
View 2 Replies
View Related
May 23, 2013
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.
View 4 Replies
View Related
Dec 5, 2013
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?
View 3 Replies
View Related
Jul 26, 2006
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?
View 10 Replies
View Related
