I'd like to create a macro that searches a column within a changing, variable range.
For example, I have a roster with a list of teams. Each team is separated by a blank row. In each team, there are a varying number of team members, and one team leader. Column I has the position code, which designates whether the person is the team leader (SL), or just a team member (RD).
What I'd like to do, is search each team for the team leader, then check another column (Col A) just within that team for another value, and if that value is present return the last name of the team leader as well as the name of the member with the value in Col A.
The problem is, I'm not sure how to search a varying range in Col I. Teams can vary in size from 3-11 members (including leader), and the number of teams varies each day. I don't have the option of changing the format or layout of the roster - it's automatically generated by some other program and given to me in a CSV list.
For example, from this sample data of two teams
Testing Program : DKSFS
Scoring Center : Concord
Rater Schedule Date : 07/29/2013
[Code]....
how to define that range in Col I which changes with each new team. My approach is to find a way to search a range (essentially the values between blank cells ) in Col I for the value "SL", then search that same range in Col A for the value "VM". If there's a value of VM within that range (team), then copy Cols A-E, write them to a list, and insert that person's team leader last name at the right.
how to define a dynamic range in Col I? I already have a working macro written which generates that last time, sans team leader name, just trying to figure out how to search that changing range in Col I for each team to find team leader name.
Note, not all teams will have a member flagged with that VM marker in Col A.
code for a button that will search for a particular date that below 6 months from the current date today? Just like a notification that there is a date below 6 months from now. I will put this code where already have a code for my workbook.
I need a VBA code that will insert a column next to A, search column A for a string of say 50 cities, and then enter the classification of that city into column B. example below.
Before A B 1 Boston Good 2 Atlanta Good 3 Georgia Bad 4 NewYork Bad
After A B C 1 Boston MA Good 2 Atlanta GA Good 3 Cleveland OH Bad 4 NewYork NY Bad
I created a user form that has two textboxes on it. One is to enter a person's name that gets put into column A, starting at row 4, and the other is to enter a number into column B, starting at row 4.
What I would like to do is for the form to search the names that have been entered in column A for duplicates in the same range. If the name is not there already, then I would like it to operate as normal. If it already exists, then I would like the number in the text box to go into column C in the same row as the name.
Here is the code I have already on the Submit button:
Private Sub SubmitButton_click()
Dim emptyRow As Long Sheet1.Activate emptyRow = WorksheetFunction.CountA(Range("A:A")) + 4 Cells(emptyRow, 1).Value = Guestname.Value Cells(emptyRow, 2).Value = Roomnum.Value Unload Name_usrfrm End Sub
Is this possible? I have to make it more complicated, but once I get the basics, I might be able to figure it out myself.
I'm currently working with a largish file of about 300 columns and 40K+ rows.
1. To populate the cells, I have to find a match in column A first.define all of its entries as a range and make Excel's Find dialog box look for matches only within this range?Because right now I have to either constantly select column A before doing the search or move the cursor manually to the top cell before every search to prevent Excel from looking elsewhere... This is turning into a real drag. How to speed things up a bit?
2. After I've found a match in column A, I need to find the right column. All of them have a unique entry in the second row (B2, C2, etc.). The huge problem is there's a c..p load of these columns and identifiers aren't arranged according to any recognizable pattern. So, every time I have to do another search on row 2 to find the column I need. All of this is mind-numbingly, excruciatingly slow.
I'm currently working with a largish file of about 300 columns and 40K+ rows. So two questions:
1. To populate the cells, I have to find a match in column A first.
Is there a way to define all of its entries as a range and make Excel's Find dialog box look for matches only within this range?
Because right now I have to either constantly select column A before doing the search or move the cursor manually to the top cell before every search to prevent Excel from looking elsewhere... This is turning into a real drag.
2. After I've found a match in column A, I need to find the right column. All of them have a unique entry in the second row (B2, C2, etc.). The huge problem is there's a c..p load of these columns and identifiers aren't arranged according to any recognizable pattern. So, every time I have to do another search on row 2 to find the column I need. All of this is mind-numbingly, excruciatingly slow...
Is there a way to make Excel jump to a specific column based on a value in a specific cell (in this case, B2-....MO2). Like maybe creating a form with buttons or checkboxes, so that whenever the right box is checked the focus would move to that column and to the row where the Find dialog just found a match in column A. Only I've never had anything to do with programming, so wouldn't even know where to start with this...
I WANT it to search all of column A for the highest number in the range (which will change daily to a new high number above the highest currently), and return the value in column 4 (column D) that corresponds to that value from column A. This is what I have, but it is not working, it is giving me the number (1) in Cell A800 (which is the lowest valued number in my range. The numbers range in value from 1 to 762 with none repeating (they are sequential). The formula I have is : =+VLOOKUP(MAX(A76:A2000),A76:I2000,4)
I'm currently trying to insert the copy range of one worksheet to another. Let's say I have 3 worksheets, one main and two additional info sheet. I want to search column C in main worksheet for blank cell and use column F in the same row as the worksheet name which I want to copy from, then use column E from main worksheet as the keyword for the range I want to copy(range from column E to column Z). But when inserting, I want the keyword in main worksheet such as 'G' in the same row as blank cell to match the 'G' in copied worksheet. Because the 'G' in main worksheet is not in the same column for each row, I try to search the forum but can't find the inserting part. I also attach the example workbook.
I need a macro, or a formula that can identify if the words in the Words Column (Column A) is contained in Title Column (Column B). If it is, It displays as "Yes". If not, display as "No".
I have this VBA (from a recorded macro) how can I amend it so that these two formulas change depending on the number of values....right now the range is to 129 only because we only have 129 cells of info but some sheets will have more some will have less. I want excel to base it based on the column I which used to be H.
VB: Sub SLCREPORTDATA() Cells.Select Range("F1").Activate Cells.EntireColumn.AutoFit Columns("H:H").Select
When I do a SEARCH operation to find the number 10 among hundreds of cells containing data, I'm often finding the way Excel highlights the cell that meets the search result is not easy enough to see.
Is there anyway I could setup Excel so that the resulting cell that meets the search result is highlighted in a RED border or RED Fill? I would like the cell that meets the search result to scream at me with a color highlight that is not easy to miss.
i have a sheet for monitoring sickness. i have used a macro before which automatically selects a range and emails it to recipients when i press a button. what i would like to do is have excel automatically choose the range for me based on today's day. in the attached example i would like column "A" copied along with the 5 columns before today's date, today's date column and also the 5 columns after today's date. paste the lot into an email and send. (i have highlghted the parts i would like copied and pasted into an email based on today's date (21st july).
Can i use for this "live search" in column range any control toolbox or form? only what i want is during writting name it will be shows the results in any textbox or so.
Control box or form can not be programmed so, that after each character check the range and return the results.
I am trying to create a graph for a range of data that updates monthly (adding an extra month each time). I wanted the graph source data to update automatically each time the data is refreshed so used an OFFSET formula to identify a named range. I then point the graph to the named range as the source data.
When I enter the range as the source data the graph picks it up. However, when I re-enter the source data option on the graph it has converted the named range into a cell written range (ie. replaces "=QUALITY" with "='Front page'!$B$7:$J$10" - which therefore will not update when the range increases.
I am trying to import txt files into Excel but instead of inmporting every row, I use the following method to select only the rows I want:
Much ADO About Text Files
However, it mentions a schema needs to be created.
For example:
[File1.txt] Col1=Name Text Col2=Age Integer [File2.txt] Col1=Name Text Col2=Age Integer
This is not a problem if I know the names of all the csv files I am importing (I can create one manually) but since I don't know how many files need to be imported, how can I create a schema for it?
Would it be possible to use VBA and create the schema dynamically?
Is there a way to change a line of vba code using an input box?
The current macro downloads a file from our intranet. However the filename changes each week. It is only the last portion of the filename that changes since this part is the date it is created. e.g filename010507.xls filename090507.xls
What I want to do is for an input box to pop up, the user then enter the date and this will then replace the existing filename in the code with the new date.
I have been tasked with creating a scheduling system for my work. There are three groups of shifts that can be selected, Opens, Swings, and Closes. I have been asked to make the cells turn red if a user selects a sequence of shifts. We are trying to avoid having a person working three scenarios:
Close>Swing>Open Close>Off>Open Close>Open
If they select the shift that meet this scenario, then I need the cells to turn red. The complete list of shifts are on the "Data" tab and the the three scenarios have been inputted into the "Jan" tab in E13:G15.
I am using the code below to change the background of the cells in my worksheet based on what is in the cell. How can I modify it to accomplish the changes below and meet the criteria above?
I am trying to automate the status date on worksheet so that if ANY data is changed/ added/ deleted within a range (cells "B6:L34"), the status date will insert today's date in Cell "S6" but if the worksheet is open and no changes occur within that range, the date remains the same. I used this code below and it is not working.
Private Sub Worksheet_Change(ByVal Target As Range) Dim R1 As Range Dim R2 As Range Dim InRange As Boolean Set R1 = Range("S6") Set R2 = Range("B6:L34")
I am trying to create a model that has a mixture of conditional formatting and data validation formulas within it, but have run into a problem in one area. Essentially, cell E10 is a Validation which pulls a drop down list of names. One of the options in this list is "Other".
I want to create a VBA code to make sure that cell E12 will get rid of any input, turn grey (color index = 15), and potentially lock (if possible!) if the "Other" is chosen by the user in cell E10. If any other value is chosen for cell E10, Cell E12 needs to remain unlocked and white (color index=2).
I'm uploading a sample worksheet- on the scenario A tab, there is a pivot table that are pulling from the data range that is between B3:c13. I copied tab A and made tab B. However, on tab B, the pivot table is still pulling from the data range on scenario A even though I need it to be pulling from tab B.
I do not want to use dynamic ranges because the pivot table is being used to make a pivot chart. How do I get it so that on the scenario B tab the pivot table automatically pulls from the right tab?
I have also have a macro/VBA (I don't know what it is considered, I just copied code from somewhere online) that automatically refreshes all pivot tables (there are other pivot tables being used in the spreadsheet). If I did need to use vba/macro to accomplish what I need to do, where would I copy/paste it in the code I currently have (not sure if the button I have in sample will work properly)?
Sub Refresh() Dim PT As PivotTable Dim WS As Worksheet For Each WS In ThisWorkbook.Worksheets For Each PT In WS.PivotTables PT.RefreshTable Next PT Next WS End Sub
On tab B, I manually created another pivot table to show what I want the end result to look like. The pivot table between rows 22 and 24 and the corresponding pivot chart between columns E and M (lets call this pivot table/chart #1) is what I'm trying to manipulate. The pivot table between rows 26 and 28 and the corresponding pivot chart between columns o and V is what I want the end result to look like. I made pivot table/chart #2 by just manually setting the data source to be "ScenarioB!$B$3:$C$13.
However what I'm trying to do is get it so that pivot table #1 would automatically refer to ScenarioB!$B$3:$C$13 instead of ScenarioA!$B$3:$C$13. I created the tab "scenario B" but just duplicating the scenario a sheet. Thus, when I duplicate, the pivot table still incorrectly refers to data on the scenario A tab. I want it to pull from the scenario B tab.
The data range between $B$3:$C$13 automatically pulls from another sheet in my real spreadsheet.
I was using this code in a sheet that contained combo boxes and it worked great. When I redone the sheet a used data validation instead of CB it wold error out.
Code: Sub savee() Range("A4:J22").Select For Each Cell In Selection If Not Cell.HasFormula Then Cell.Value = UCase(Cell.Value) End If Next Cell Range("h4").Select ActiveWorkbook.Save End Sub
I have a workbook, that when opened the first time needs to prompt the user to save it. I got that working with no issues by using
Private Sub Workbook_Open() SaveOnOpen End Sub
where SaveOnOpen is a procedure in another module. What I would like to do now is re-assign the Workbook_Open sub to be set to null, so that it doesn't run any more. Is it possible to somehow assign Workbook_Open to call a null procedure, as opposed to setting up an onTime call to delete the code itself?
I have made several Shapes and assigned macros to them, all is good.
What i am curious about: is it possible to highlight or change colour of the shape on press and return to normal if another is shape is pressed? Just anything to make that particular shape stand out that it has been selected then return to normal when another shape has been selected...
[Code] ....
That is the code so far that is attached to 1x shape...
Is it possible to write VBA code that will prevent a user from changing a cell's contents, depending on the cell's font color?
More specifically, I have a column of text in range B15:B64. Some of the cells will have a black font, others will be blue. Is it possible to lock the black font cells only, leaving the blue cells unlocked for users to change?
