I want it to copy and paste whatever the cell is;
Andrew Smith
not
ANDREW SMITH
Code:
Sub Replacing()
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Cursor = xlWait
Application.DisplayStatusBar = True
Application.StatusBar = "Generating DM Pack, please wait!"
[Code] .......
Is there anyway (without using VBA) of creating validation within a cell so that it will only accept capital letters?
In addition, can this also be done to accept either capitals and numbers in a set format?
eg C####C
Where C is a letter (in capitals) and # is a number
I have a column with thousads of names
i.e. JohnSmith
Is there a custom way to delimited the text by Capitol letter.
Or, to have the text string searched and then insert a space in front of every caps it finds to get the output.
I run a catalogue of music (thousands of records) made in excel
where I have in the first cell the name of the band + album + format,
something like this:
Pearl Jam - name of the album - CD
how can I turn automatically all names of bands into CAPITALS, in
example:
PEAR JAM - name of the album - CD
Is there a way to turn in CAPITALS all text from the beginning of a cell 'till the symbol with spaces on both sides ( - ) of each cell ?
I know how to turn the entire cell into CAPITALS using the formula =upper(column nº) but not just part of the cell.
if it possible to produce a letter in excel like you would in word by mail merging.
i dont want to open word as i dont have it on my work pc.
im wondering if it possible to do something like this
dear ( sheet 1 cell 1 )
thank you for your order of ( sheet 1 cell 2 ) we wish to deliver to you on ( sheet 1 cell 3 )
I know it has something to do with the way I am trying to complete the loop.
I have tried next i, I have tried if's instead of the do until but can't get it to work.
I want it to cycle through the x and if a value is found then paste into the corresponding i row. If the x value is blank then exit the loop.
I would like to use the following code to produce a message with two numbers in it, both showing an exact golf handicap to one decimal point. If a number is exactly 6 I want it to show as 6.0.
All works well for the number I'm collecting from the user and storing in newh. But I can't retain/produce the trailing zero from oldh which is formatted in the spreadsheet as Custom 0.0.
I am looking for a macro or a formula that can give me multiple tabs, what i need is jan 01 to april 30,the next 2 books i could do by copying of course i have looked at the macros on here and no nothing about them ....
View 14 Replies View RelatedI want to make a little chart for easy reference that tells me due dates for projects, based on estimated completion times.
I'm already using NETWORKDAYS to find the amount of working days between today and a due date, but I want to flip the formula around, and I'm having trouble getting the syntax right.
For example, column A reads:
0
1
2
3
5
7
10
15
20
Estimated completion times for various projects.
So I want column B to read the date that this would render. A1, value 0, would always produce today's date for B1. B2 would always read one business day into the future, B3 would read as 2 business days into the future, B4 as 3 business days into the future. Does that make sense, and B5 as 5 bd into future.
[Code] ......
Trying to get columns 10, 11 & 13 to join so it says "Mr Noddy Bigshoes" (Salutation, Forename & Surname).
s/s is very large and the need is to transfer formulas from one column to another. Column L contains formulas in cells L7:L45 (attachment) - (L7:L326415 in working s/s). Some of these formulas result in a value being given. I need to transfer formulas only from those cells having values to cells three columns to the left on the same row. I do have code which I was using for another application. This puts a formula in the correct places but it is the wrong formula and I don't know how to amend it so that it carries out the required action. Small attachment enclosed for better understanding of what is involved. Or could anyone change the line ".formula = ......" in the following code to make it work??
View 5 Replies View RelatedI have a workbook that if I use just the array formula for the totals it works fine and if I use data validation and vlookup and the array formula for the totals it is giving me a #Value! instead. I have tried a couple of things and did find that as long as I do not use the vlookup in the one column (column d) the array formula at the bottom works but as soon as I add the vlookup to column D I got the error.
I want to use the vlookup to get the values for items on a different page - along with the data validation and then use the array formula to get a total for multiple items in the above columns. I have attached a workbook.
My objective is to create a "guide or cheat sheet" for the correct combination of various tools.
My user has to choose (1) Mother Tool and (2) If the tool is to be used uphole or downhole.
I need the spreadsheet to automatically limit the choices for Combinability that corresponds to uphole or downhole.
After selecting Combinability, the 4th ComboBox must automatically limit the choices specific to subs that fall under the tool chosen under combinability.
I can create a spreadsheet and color-code the combinations however it will be very busy as the list is very long.
I wanted to use ControlBoxes but I can't get it to work - my brain can't seem to connect the dots in VBA.
I want to define a varible named MonthEnd that I will use in more than one project. In a normal example the variable would look like this:
Dim MonthEnd As String
MonthEnd = Format(Sheet1.Range("C3"), "MMYY")
The problem is that I will be using this more than one time so I figured I could define this a Public constant like
Public Const MontEnd As String = Format(Sheet1.Range("C3"), "MMYY")
This is kind of an extension to a previously solved post. For a similar problem I used the attatched file (which someone from here so kindly came up with - but to be honest i dont fully understand),
but now im getting onto numbers from 1 to 100 so could get very confusing in the table!
RANDOM TABLE2.xlsx
Basically I want to produced a random number, (which I have managed) then another number, but it cant have any common factors as the first.
So 1st = 10, 2nd = 13 is acceptable
but 1st = 22, 2nd = 12 is not.
I have managed to do it with smaller values by just typing then out, but this is a very long winded technique, but is there a formula that can do this?
I have a set of data with index numbers and the percentage of their occurrence. I want to use this percentage to weight the occurrence of the index numbers and create a random list of say 500 occurrences.
Index %
1 7.95
2 3.28
3 7.37
4 38.45
5 28.62
6 14.12
I have created a macro some time ago that is an integrated part of an XLA. The Xla has worked fine but now, for some reason, the macro fails to import the specified text, it doesn't fail but nothing gets imported. I have tried solving this myself, but alas I am not bright enough
The code is:
Sub GetWorksheet()
Dim filetoopen As String
Dim wb As Workbook
filetoopen = Application _
. GetOpenFilename("XL Files (*.xls), *.xls")
On Error Resume Next
I'm trying to do a simple conditional format where the date is highlighted in red if the difference between that date and Todays date is greater than 90 days.
I've done the formula as =(TODAY()>$B5)>90, which appears to work in the sheet and shows either True or False but when inputting that formula into the conditional format it highlights every single cells.
Are conditional formatting formulas different?
I need to produce a summary of the monthly spend with parts suppliers for a number of vehicles, with each vehicle having it's own worksheet. The suppliers' names are in column C with the costs in column H.
So what I need to do is sum the results of column H on each worksheet when the supplier's name (which is on the same row) matches "Triple 7" and it's in the same month. The idea is to produce a sheet which displays the total spend each month with our suppliers.
I have a table of yearly totals for the amount spent by x. I also have a growth rate for each month so for example in 2001 in jan the growth rate might have been 0.3% and feb 0.5% What I want to do is for each month based on the growth rate and the total produce a value for each month which sum to the total amount. It's also important to note that it restarts each year.
Link for excel file is here: [URL] ...........
Trying to use Excel Data List to create a database style report. IE. Originally blank sheet, which is only populated by data containing data matching "filters" input into cells ( say A1 & A2 )
I.e. A1 = Delivery week to be filtered by, and B1 Manufacturer Name
So if I type week "1" into A1 & Manufacturer "Microsoft" into A2, it will show a table only containing data Microsoft, Week 1, and associated data for those lines across the screen.
Week 1
Microsoft
PO number : Date Ordered: Address 1, 2 3 etc....
0011 01/01/13 Somewhere
0015 02/01/13 Anywhere
0213 05/01/13 Nowhere
I know this is much easier with a database, however my manager insists a database cannot be used, and it must be in a spreadsheet format !
I am trying to use a combination of Concatenate and IF formula to produce an email.
My input.
D4: First Name:
D5: Middle Name:
D6: Last Name:
D8: Organization:
Once all these are filled, I want the formula to produce a result like
FirstName.M.LastName@Organization.com
I have used the following formula.
[Code] .....
Problem is if there is a middle name the formula works fine, but in case where there is no middle name, it produces the following result.
FirstName..LastName@Organization.com
How do I remove the additional (.) in cases where there is no middle name.
Attached File : Email Generator.xlsx‎
I am trying to make a save&close workbook macro.
I found several examples on google, but unfortunatly it conflicts with another macro I use for forceing users to enable macros (hide all sheets except one if macros are disabled).
The attached file is an example contaning the save&close code and the show/hide sheets depending on macros enabled.
If the file is opened with macros disabled then only one sheet will be visible.
If the file is opened with macros enabled other sheets are visible.
The problem if that this code uses a custom save, witch makes the save&close not save... (in module1 and in ThisWorkbook)
The pourpose of the save&close is to make sure some users don't forget the excel open and thus block access to it. So if a certain idele time passes excel has to save and close without any confirmation messages.
I'm using a CALL Macro to split up a HUGE macro into different pieces:
Code:
Sub RSLDASHBOARDV2()
'Macro recorded 12/14/2010 by Ryan R. Koleno, Pharm.D.
'Last Updated 7/10/12 by Ryan R. Koleno, Pharm.D.
'Do Not Modify Code Unless Given Proper Privileges to do so.
Dim APPSPD As Worksheet
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
[code]...
The first few macros dealing with page setup and what not work fine but when it hits the Pivot table code for the STATSPIVOT macro it errors out stating: "Run-time error '1004': Unable to get the PivotItems property of PivotField class' at this point in the code:
Code:
objField2.PivotItems( _
"TRC").Position = 1
objField.PivotItems( _
"MEDCO MAIL OR AOB").Position = 2
When this macro is not split up it worked fine as written. Am I overlooking something in the Call Macro's code or is there a variable I'm not aware of. I have included the Pivot Table code that errors out as well.
Code:
Sub STATSPIVOT()
'STATS PAGE BASED ON STATS DATA TAB
Sheets("STATS DATA").Select
Dim objTable As PivotTable, objField As PivotField
ActiveWorkbook.Sheets("STATS DATA").Select
Range("A1").Select
[code]...
I am making a spreadsheet that sorts and pastes, but I need to know if I can add a code to the Sort and Paste Macro that will open the second spread sheet needed without just already having it open and using the
Windows("estimate sheet one.xls").Activate
I have a macro where I manually select a cell then the macro kicks in to copy and paste the contents into the Find function. From there it goes to another work sheet, clicks on a cell in column one and searches for matching cell contents. Then If false it manually goes back to the original worksheet/cell and then I input a N in the left adjacent cell. If true I enter a Y. Right now I can only run this for the specific cell the macro was recorded for. I would like to expand this.
What I would like to do create a loop in the macro that waits for my cell choice input then continues with the with the Find function. If false the macro should just loop back to the original worksheet/cell and wait for input, ie the next cell selected. However, if true a worksheet/cell is selected, a Y is input and it loops back to the original cell and waits for input.
This is the code I use to call a macro when the macro Im calling is in the same workbook.
[Code].....
However, I would like to call this same macro when using another workbook. I copied the macro "Clearformating" and pasted into a personal macro workbook module. However when I add this code to the sheet tab it will not run the macro.
I also tried this code.
[Code] .....
I have a macro that I found somewhere on the net to look within a folder and list all the files of a certain file extension.
The macro to do this is in the attached example and is called 'Get_File_Names_Within_Dir_ext'.
I have created a basic userform outline, 'UF1' for the user to define:
Select File Extension
Select Folder to Search
Destination Sheet
I just don't have any idea how to sync the two.
If you type 'exe' into 'TB1_File_Extension' of 'UF1' the macro should search for '*.exe' files within the specified folder.
The search folder 'RefEdit1' box should open a windows explorer box (or some such) so that the user can select the directory in which to search for the previously specified file extension.
'TB2_Destination_Sheet' is a text box for the user to type the sheet within the workbook in which to list the files found within the specified directory.
'CB1_Find_Files' should activate the macro to find any files for the specified criteria.
There is also a Button 'Find File Types' in Sheet1 of the file which should activate the userform 'UF1'.
I have a worksheet in which I have a worksheet_change macro. This worksheet_change macro makes sure that a few cells will keep their colors, even if the user copies and pastes a new value to that cell. This worksheet_change macro runs each time there is a change on the worksheet. Now my problem is that on the same sheet I have an update list macro which updates around 20.000 rows and two columns (which is alltogether around 40.000 values) and it takes a while to run. So.. it takes a loooooooooot of time (too much) when these two macros both run.
My question is that can I somehow disable the worksheet_change macro while the update list macro runs. I mean something like when I start the update list macro to disable worksheet_change macro and when the update list macro finishes, then reenable worksheet_change macro?
I need a macro to get the values from cells D29 and H24 in the Resource Calculator sheet and populate it into cells N8 and O8 in the Input form.
Users will then be able to change the information in the calculator and click the macro again to populate N9 and O9 and so on.
Is there a way to do this?
I've attached the file for you to see.