I want to generate a text column for a mail merge which will add the correct ordinal suffix to dates eg st to 1 as in 1st or 21st or 31st but nd to 2nd 22nd also rd and th where appropriate.
I have a macro that will add the next letter to a number as a suffix but this will only work for A - Z. But i need variations of upto 150, so i need the macro to add another letter to the variation once Z has been reached. For example at the moment 1000A the next variation would be 1000B up until 1000Z but once this has been reached the macro then starts to use symbols as the code is written using ASCII numbers. So i need the next variation to be 1000AA then 1000AB etc the code i currently have is
Public Function NewVarianceLetter(lngQuoteNum As Long, Optional rngQuote As Range) As String Dim a As String Dim i As Long Dim rngFind As Range
Each day I recieve a spreadsheet with thousands of row. In col A there is data that has "-NCA1" or "NCA2" as the suffix. I have to "delete" these rows before proceding.
I'd like to know if there is a way to do this with a macro and ideally delete a wildcard suffix with "NCA*" in case any 3...4..5 etc are added.
I find, when performing a 'save as', xl puts in a default filename with a suffix (1), or (2), or (3) and so on, to the existing filename, after I have opened a file from MS Outlook. I had assumed it was because there was already an existing file in the default directory, and so it was creating another version, but no other files with the same name exist in the default directory being saved to, or on the remainder of my computer, so I don't understand why this occurs.
I have a piece of code that runs and creates an output Excel sheet that currently has the run date as a suffix for the automatically generated file name. However, I now sometimes have to run this report multiple times in one day, which currently means overwriting the previously run spreadsheet (whereas I would like to keep both versions of the ruin output).
Is there a way that I can create some kind of counter for the number of times the report is run and add that as a suffix for the file name? At the moment the only way I can think to do this is to set up code that looks at the file name containing the date field and adds 1 to every number that is added as a suffix, but this doesn't seem very efficient...
I am using (in Column "B") the formula: =IF(A2=A3."",1), copied down as required, to uniquely identify entries in Column "A". In this instance "10", "10A", "10B" and "10C" located in "A5" through "A11" are viewed as the same. Is it possible to have the formula recognize the enties in "A5:A11" as a unique entry and therfore have "1" entered into cell "B11"? The suffix, if used will always be A thorugh Z.
I have a column filled with 5 digit numbers and a suffix. (Ex: 12345-001) I need to delete all of the '-001' from all of the numbers in the column. There are over 11,000 numbers. Is there a formula or function that I can apply to the entire column that will delete the suffix?
From an Excel 2003 workbook I generate some product labels. Each label has a 5 digit job number and a quantity that are passed to variables. If the quantity is 1, then I have no problems in printing my label. My problem is where the quantity is greater than 1. If a job has more than 1 item (can be up to 40 or 50) I need to add a series of letter (or letters) to the end of the job number.
For example:
Job number 12345 qty 1
number on label 12345
Job number 12346 qty 4
numbers on labels 12346A, 12346B, 12346C, 1234D
so that each printed label has a unique job number
I am looking for a replacement to the function COUNTIFS that will work with Excel 97-03. I have attached a sample of the document which will hopefully demonstrate what I am trying to achieve. On the sheet labelled 'Data' there is four headings (MerRefs, JCPRefs, Self Refs and Other) for the results beneath these headings I have used the Countifs function. I have attempted to use the SUMIF as an array formula but couldn't get this to work.
Also in the hope of killing two birds with one stone, I created this in Excel 2007 and in the past when I have saved documents as 97-03 documents it used to warn me of any incompatible functions included within the doc and now it doesent. Where can I switch that setting on?
I'm trying to take a random sample without replacement. The sample is also partially representative, e.g. if a certain proportion of the data are Class A and a certain proportion is Class B, that proportion is maintained in the sample. After the proportions are set, the observations within each class are randomized.
Is there anyway to sample a number of rows, where the number of rows is based on the value in another cell, without having to use a lookup function or index match? Sorry if the question is a little unclear; please let me know if you need any clarification.
I have several walking routes that are labeled A through H. Each walking route has a different distance. .18 miles, 1.45 miles, etc. What i would like to do is be able to enter what routes are walked in a day and have it display the milage in a total cell. Example......
In the attached spreadsheet, I want to do a running average of the "To" column (column G) for each company throughout the 8 year period. The only problem is I want to do it with replacement based on column D, meaning if the same brokerage firm comes out with a new announcement, it will replace the value of the old one in the running average calculation.
Here is an illustration of what I mean:
Looking at Company A between row 2 and 12, the value for Consensus in row 2 should be the average of Column G in row 2, row 3 is the average of Column G in row 3...row 11 is the average of Column G in row 2 to row 11, but row 12 will be the average of Column G in row 2 to 12 be excluding column 5's value because both column 5 and 12 are issued by the same brokerage.
I want excel to replace new recommendations by brokerages over the old ones in the running average calculation.
I have a cell with a url in it, Say in A1 I have the following url: mrexcel.com/forum/forumdisplay.php?f=10
I want Cell B1 To copy what's in cell A1 but delete everthing after the backslash"/" In B1 want it to show: mrexcel.com
What I have now doesn't work, which is: SUBSTITUTE(A1,"/*","")
An attempt to use a wildcard (*) to remove everything after the backslash. Doesn't work What's the correct formula that will allow me to do this automatically?
I have some code to insert a picture based on the value of a cell. The user would select the name of the item by selecting the cell and then clicking a macro button.
In my code I need to delete the previous picture first. I used the following Sub get_system_picture()
Dim pic As Shape Dim picname As String
picname = ActiveCell.Value On Error Resume Next
'exit sub if selection is not a system number If IsEmpty(ActiveCell) Then MsgBox "Please select a system number first!" Exit Sub End If
If Not ActiveCell.Column = 2 Then MsgBox "Please select a system number first!" Exit Sub End If
The problem is this also deletes my macro button! Any suggestions?
I compile ratings for racehorses and am currently extracting the data I need using an array formula. Unfortunately there are are many thousands of lines of data and it can take quite some time for the data to pull through.
Here is a sample of the formula's I am currently using. I cannot take credit for the formulas by the way I found them on the web.
Basically I have 2 sheets (Database 2012, Database 2011) with the horse names with ratings next to them for each run, One sheet for this season one for last season.
These are sorted in date order. then I have a ratings sheet that shows the horse name and then to the right it has columns for each of its last 4 ratings.
The formulas I use look for the horse name on the database sheet then put the rating in latest rating in the first column then the 2nd latest in the next column and so on until the last 4 ratings are shown. If there are not 4 ratings to find the is an iserror at the beginning of the formula to populate that cell with a blank so that the errors dont show.
What I want to know is there a way of doing what the formula does using VBA and more importantly would it be quicker than using array formulas which are very slow to recalculate when there are 1000's of lines.
My boss has made it clear I can't require our people to use the add-in.
I have searched under WORKDAY as well as under Barry Houdini, since someone said he has come up with plenty of replacements for the Analysis add-in... but I am not finding it.. can someone help?
I am trying to search a folder that is selected by the user for all .jpg files, and then place all those pictures onto a certain spreadsheet within the excel file. It also formats the pictures. Following is the Private Sub BatchProcessThumb2x3() Msg = "Select a file containing the photos you want to insert." Directory = GetDirectory(Msg) If Directory = "" Then Exit Sub If Right(Directory, 1) "" Then Directory = Directory & ""
On Error Resume Next With Application.FileSearch .NewSearch .LookIn = Directory .Filename = "*.jpg" .SearchSubFolders = False .Execute
' Select begining range Worksheets("Thumbnail (2x3)").Select Range("B4").Select ' Loop through all files and process them
I was wondering if is possible to replace SUMPRODUCT with code. Right now I have formulas like these throughout my sheet and I think they are causing it to be slow:
I also have the following code in my worksheet. I don't know if anything here is causing it to take so long to "calculate cells." Also my code just stopped coloring the row ranges and I not sure what cause it to stop working.
Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "O:O" If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target 'Begin coloring row ranges based on these requirements If .Row > 3 Then If Me.Cells(.Row, "O").Value = "" Or Me.Cells(.Row, "O").Value = "O" Or Me.Cells(.Row, "O").Value = "H" Then Me.Cells(.Row, "A").Resize(, 26).Interior.ColorIndex = 0 End If.................
Okay so I'm trying to us the SUMIFS formula to add values on a different sheet based on criteria on that sheet but I want to use a text string on the sheet with the SUMIFS formula to reference the sheet with the data on it.
However INDEX(A2:A2,1)&"'!K4:K500" returns the value "1251062 EP.EL+CB-A10'!K4:K500" which wont calculate properly because of the quotation marks but excel is auto inserting them.
In the following thread, richphillips wonders about Application.Filesearch Application.filesearch In Office 2007
Is there a replacement for this? I have several macros that use Application.Filesearch and I would like it to work not only with Excel 2003 but also with Excel 2007. This is the function
Function CreateFileList(FileFilter As String, _ IncludeSubFolder As Boolean) As Variant ' returns the full filename for files matching ' the filter criteria in the current folder Dim FileList() As String, FileCount As Long CreateFileList = "" Erase FileList If FileFilter = "" Then FileFilter = "*.*" ' all files Debug.Print CurDir strFolder = BrowseForFolderShell(, , , 0) If strFolder = "" Then MsgBox "You Cancelled" Exit Function End If............
If InStr( Cells(i, 3).Value, "Other") > 0 Then _ Cells(i, 3).Replace What:="Other", Replacement:=Cells(i, 4).Value
This seems to work fine for the most part. However, if the value in Cells(i, 4) is too long, I seem to get a Run-time error '13': Type mismatch. Is there any way to rework this code so it can replace even if the string in Cells(i, 4).Value is too long?
Im trying to work out how to write some code to find and replace from a list.
I have key data in column P on sheet 1, and a list on sheet 2 with the same key data in column C.
Basicially, every month the data on sheet 1 changes (there are usually 100 rows of data each month) and what i want to do is, write some code to look down column P on sheet 1 and for every cell, find the corrosponding value on sheet 2 (column C). When it finds the value, copy and paste it onto sheet 3 then delete the row from sheet 2.
I hve written some code that can do the first part (copy and paste) and a second macro to do the deleting....but it is only for 1 value at a time...