So what I am looking for is to put a macro behind button 1 which will do the following
Enter A Default Value Of 123 In The Yellow Boxes (B7:K7) If They Are Blank When The User Clicks The Button Performs A Search And Replace To Replace The Values 01-01-1990 With B8, 02-01-1990 With C8 Etc Until K8 Output The Contents Of Sheet 2 To A Text File (In XAI Format) In The Following Order A1:A100, B1:B100, C1:C100
Working for a dutch company and using a lot of excel and access it is sometimes a pain that when you are looking for functions. for example "left(b3;3)" in dutch is "links(b3;3)" now this is an easy one but some are hard to guess.
I wrote some neat code which condionally formats cell's in an Excel range and dependend on a value in column "Q" (= status) then sets the color of the field. it works fine
However when working with an english version of Excel I need to use "left" instead of "links"
how to check the local settings for Excel?
[code] 'Conditional Format status cell's .Range("G4:P" & (xRcount + 3)).Interior.ColorIndex = 4 iRow = 4 iRow2 = 4
One Excel file with two columns that is populated with a description code and a description. The total number of lines is 30 000 and the description is on the Languages of English, Spanish and Portuguese.
I need to indicate in the third column all the descriptions that are not English.
I am currently creating a massive bilingual report using Excel based on data gathered from a customer visit, as most of the engineers are non-native English speakers, and most everywhere else in the world wants reports in English. Currently I am using this roundabout method:
I'm currently studying japanese and I need to make a flashcard system so that I can memorise words a lot faster. An old friend told me that it is possible to make a flashcard system in excel (or maybe Access due to its database system - I cant remember which sorry). I'm looking at a basic system that first shows me the japanese - then i click it and it reveals the english, click the english and it then moves onto the next japanese word etc etc. Though, I have no idea where to start in building such a system, my excel knowledge is not great but it I'll give anything a try
Situation: I have two drop down lists 1) Country and 2) States/province
Country has list: [US, Canada]
If "US" is selected, [Arizona ,Florida, NewYork] is listed in the 2nd drop down list If "Canada" is selected, [Alberta, Ontario, Quebec] is listed in the 2nd drop down list
Scenario: First, I select "US" and choose the states to "Florida" Next I change the country to "Canada" and forgot to choose province Then, the 2nd dropdown list is changed to [Alberta, Ontario, Quebec], BUT the current value is still "Florida"
Probem: Now I have "Canada" and "Florida" selected in the sheet
Question: If the primary list is changed, can I make the 2nd drop down list to show a default value (e.g. blank or the first entry i.e. Alberta)?
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?
Is it possible to have a worksheet code that would underline a row if a certain value is selected in a dropdown list?
I have a worksheet with data populated across colums A to K, in Column I there is a dropdown list. What I wanted was to be able to understrike the Columns A to K of a particular row is the value in the dropdown list in Column I ="Cancelled". I cannot use Conditional Formatting as I have already used the maximum 3 formats for other features.
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 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.
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 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.
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...