Macro To Select Dates Based On Cell Values
I might be using something completely wrong here, but can anyone tell me if this code is possible or am I being very naive.
H4 and I4 are cells in which I want to enter dates, and then I want these dates to be used in a custom filter on another page.
Below is what my limited understanding of VB came up with.
Sub Date_Range()
Dim First As Date
Dim Last As Date
First = Range("H4").Value
Last = Range("I4").Value
Sheets("Graph Data").Select
Selection.AutoFilter Field:=1, Criteria1:=">=First", Operator:=xlAnd _
, Criteria2:="
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Run Macro Based On Corresponding Cell Values In Range
I have a range "RangeOne" and i want to check the range row by row. In each row I want to check for value in second cell. If the value is 10 or more then the entire row in the range should be selected and then execute a macro. If the value is 0 to 10(but not 10 exactly) then the row in the range should be selected and then execute macro2.
View Replies!
View Related
Sum Values Based Between Two Dates And Another Varible
I want to sum values in column "C" based on if they fall between and equal to two dates, dates in "A" and a varible in "B" Column "A" a2-a32 dates of each day in Jan 09 31 days. Column "B" b2-b32 letters either X Y Z randomly through range some cells may have a letter. Column "C" Dollar value in cell D1 Startdate in cell D2 Enddate Formula located in D3 is the sum of all values falling between the date ranges and by one of the letters X. D4 Y. D5 Z.
View Replies!
View Related
Macro Logic: Extract Higher/lower Values Based On Unique Cell
This is a logic based macro that needs to compare values in 1 column above a specific cell and take action until the column has no more data. Here's what I'm trying to do (and I've also attached a sample of desired results): 1) I want user to input starting cell. So I need to create a button for that. 2) This is where the logic begins and I'm having difficulty. From cell defined in button, I move up one cell in that column and compare that value to the original cell. If the value is greater than original cell then logic needs 2 outcomes: a) move up 1 cell in column and if that value is less than original cell, then extract that value and copy that value one cell to the right or 1c. b) move up 1 cell in column and if that value is greater than original cell, then move up again, until find lesser value than original cell. Once we find value less than original cell, extract value and copy value one cell to the right or 1c.
View Replies!
View Related
Programming A Macro To Select Row Ranges Based On A Formula
I'm building a macro thats copying 4 rows of data at a time from a spreadsheet (has 1500 rows of data total) and transposing it to a master spread sheet. There are blank rows in between each entry (the data source I'm using isn't clean), so how can I either have the macro jump to the first row it finds with a value, copy rows until it stops finding a value, paste+transpose into a new document and then repeat the process throughout the document. If thats too complicated, a macro that finds rows based on a formula would work as well. E.g. Start at row 5, copy+paste+transpose 5-8, start at row 11 (5+6), copy + paste+transpose 11-14, start at row 17 (x+6) ...etc... Here's the code I have developed thus far:
View Replies!
View Related
Select Range Based On A Cell Value
I have a worksheet with a block of data. The size of this block changes. I am calculating and storing the upperleft and lower right cell position of the block in say cells AA3 and AB3 on the same worksheet. So far I have everything working fine. Now I want to create a button to trigger a macro to sort that block. I can figure out the button and make it trigger a macro. One of the lines in the macro is
View Replies!
View Related
Select Multiple Worksheets Based On Cell
I have a column where each cell contains the names of a variable number of worksheets in the format ["sheet1", "sheet2", etc. ] without the brackets but with the quotation marks. I would like to have some code that will select all of the sheets mentioned in a given cell. I found lots of threads about selecting an individual sheet based on a cell, but none about selecting multiple sheets. If it is easier, I can change the format of of the cells, or break each cell out into multiple cells in a given row. The best code I could come up with was Sub Selects() Dim strSht As String Sheets("Criteria").Select strSht = ActiveSheet.Range("L31") Sheets( Array(strSht)).Select End Sub
View Replies!
View Related
Select Sheet Based On Cell Content
I am trying to do some thing simple, but i causes me a lot of problem in several areas. I am using the sheets( range("c5")).select to simply select the sheet that is named in cell c5 on the current sheet. can anyone give me a correct way to accomplish this that I can use in other applications that i come accross?
View Replies!
View Related
Select Row Based On Cell Criteria
I am trying to create a macro that selects an entire row based on a date criteria. The explanation is as follows: I have fields in an excel sheet such as: Name, Date, Place, Time, Country etc….. There are many rows with data in them…. I require a prompt box that asks for a date range and then selects all the contents matching that criteria and copies into another excel sheet called Export.xls.
View Replies!
View Related
Select A Range Based On Active Cell Location?
I'm trying to select a range of cells whereby the range is dependent on the currently active cell. I know you can use the "Activesheet.Range("A1:D2").select" method to select a range where the cells are always the same, but I'm after a dynamic selection where the values can be programmatically altered depending on some other result. For example, let's say that I make a certain cell active (based on the result of some other formula), and I want to select the range of cells in the adjacent column that is X rows deep. Putting this into context, imagine the resultant active cell is B2, I then want to select the range C2:C10, but if the active cell is E10, the range selected would be F10:F18 (if active cell is X, then range would be Y:Z). The Offset function would allow me to position the cell based on the current active one, but it doesn't let me select a range. The Range function only lets you choose either hard coded or index cells, e.g. "Range(cells(y,z), cells(y,z)).select", but this is still no good because I'd need to know the index value of the active cell (can this be done?).
View Replies!
View Related
Select Range Based On Variable Cell Content
Based on if the value in col A contains the characters "TT" I want to select the range starting with this cell and ending at the end at the end of the row I'm using (.End(xlRight) and then merge these cells, change colors etc. And then looping this through a 'range' so that it only occurs where the values occur. I can amend various cells based on this idea, but am unable to identify the range and then merge the cells.
View Replies!
View Related
Select To Last Cell Macro
I used to do some VB stuff several years ago, but it's unfortunately not exactly like a bicycle and I'm having a hard time remembering what I'm needing to do. I have several functions in my macro which are supposed to select an entire column of data and do various things with it, from cutting & pasting to combining data from multiple text cells. Also it's got to input in one column, a text string "CIS". Anyway, if I tell it the whole column, then it winds up overflowing beyond what I need, and I really only want it to affect as many rows as have data in the spreadsheet. Here's the data: 'Unlimited Range K here', that's where I want it to select to the last row of data for the range. Sub ComputeCSV() ' ' ComputeCSV Macro ' Macro recorded 12/18/2006 by Matthew Roberts ' ' Keyboard Shortcut: Ctrl+l ' Columns("G:G").Select Selection.Cut Columns("B:B").Select Selection.Insert Shift:=xlToRight Columns("G:G").Select Selection.Cut
View Replies!
View Related
Add To Dates Based On Criteria & Flag Overdue Dates
I have a very large Excel spreadsheet that is generated through an Oracle application at least once a month at work. I would like some code (either a worksheet function or a VBA) to check and report the following. If the frequency in column C is "Monthly" or "Weekly" just go to the next row. If the frequency in column C is "Annual", add 183 days to the date in column B; if it is "Semi-Annual", add 92 days to date in column B; if it is "Quarterly" , add 46 days to the date in column B; if the frequency in column C is "2-Year", add 365 days to the date in column B. Once the check has been made; I need the new total or date that was calculated checked against the date the report is being run (system clock date). If the date or total days is less than today, a new worksheet, titled "Late" needs to contain that row of data. If the date or total days is greater than today's date, just go on to the next row. I've attached an example worksheet.
View Replies!
View Related
Macro To Select Cell After Last Row In Column
the macro code to select the cell after the bottom of a column of data e.g. I have data in A1:A300 and I require the macro to automatically calculate the last row in the range (in this case A300) and then to select A301 (in this case) (I know I can record a macro but the number of rows can potentially differ every time I run the macro) N.B. Excel version 2003
View Replies!
View Related
Macro: Select Cell A Of Selected Row
Currently working on a project for university and still learning how to use macro. Got a problem which im sure is an easy solution for all you experts out there. As shown in the picture; http://img300.imageshack.us/img300/1205/macrohelp.jpg. When the Sap number is entered (from A19 and onwards), it copies the value to cell B10. The general code is used below.
View Replies!
View Related
Is It Possible For A User To Select A File And Cell To Paste In During A Macro
I was planning to do was have a macro where the document opened and the cell that information was pasted in was decided depending on which cell was selected when the macro was run. This worked fine, but it does mean that it needs someone who can edit the VBscript every time there is a new starter or someone leaves so isn't really viable. What I was wondering, is if it is possible for me to start a macro, which then drags in the information I need from a text file, does all the calculations and rearranging, and then brings up a prompt for the user to open the document they want to paste it in, click on the cell where the pasting will start, and then the macro continues from there? To try to explain it in as clear a way as I can. Say we have three managers, Alan, Brenda and Charlie. In Alan's team there are 10 staff members. Aaron, Bill etc.etc. If we have just run the report for Aaron, the macro will run, automatically drag all the information in and rearrange it, then it will pause while the user opens up the "Alan" spreadsheet, they select the "Aaron" first cell, click OK or something, and then the macro continues on its happy way?
View Replies!
View Related
Run Macro Based On Values In Column
I am trying to write code that will run a macro based on values in a certain column. I do not know how many rows are present at the time the macro will be run. For example, if Column A has the number 2 in every row then I want macro Z to run. However, if the values are not the same then I want macro Y to run.
View Replies!
View Related
Lookup Via Macro Code: Find The Match And Then Select That Cell In VBA
I have a large database of equipment on one sheet and an input form on another. For inventory control, when a user scans a number into the input form, the main inventory sheet is updated with the current location. I have it working using functions, but I need to do this in VBA. I am looking to do the following; User enters 2222 on the input form. The code matches 2222 on the inventory sheet, moves the activecell 10 columns and updates a value in that cell. Cell A2343 is "2222" then Cell K2343 is changed to "WAREHOUSE 4". What is the best way to find the match and then select that cell in VBA?
View Replies!
View Related
Formula: Select A Range Of Dates Within A Range Of Dates?
In column B is a list of dates, which are broken out by fiscal year (FY=7/1/ to 6/30). Column C is a list of dollar amounts. Cell F3 is the query ‘start date’ Cell F4 is today's date, which will be the formula: =today() Using a date range of F3:F4, I'm trying to get each cell bordered in red to query the dates in column B for each FY; then display the matching sum from column C. For illustrative purposes I went and manually determined what the values should be displaying as of 7/22. Here are a couple of examples of how I am trying to get the formula to work. Example 1 FY’08: Start date- 7/1 End date- 7/22 Searching cells B1:B94, cells B1:B13 fall within the start and end date parameters listed above. Sum of cells C1:C13 is $45,112.00, which should display in cell F6. Example 2 FY’09: Start date- 7/1 End date- 12/13 Searching cells B95:B222, cells B95:B149 fall within the start and end date parameters listed above. Sum of cells C95:C149 is $150,873.03, which F7 should display in cell F7. How to I type up this formula; its way more challenging that I first thought!
View Replies!
View Related
Clear Contents Of Cell Based On Comparsion Of Cell Values
I am a financial administrator & every month I have down load 4 bank accts as CSV, import into xl & code (CACode) the amounts for our accountant. I have VBA that formats, adds headings & formula etc but I have a problem/s. In H col I place CAcode & I use a sumif formula in I col to sum all the amounts with that have the same CAcode. For simplicity sake I copy the sumif down & then sort H col ascendindingly (this is done by VBA). Now I am trying write a macro to clear the contents of the cell in I col if eg h60 = h59 then I60 clearContents, Select h59 Else select H59 Do until H3 is selected Psuedocode Select table (A2:I Xldown) Sort Table by CAcode (H Col) Set Range as H3:Xldown Select Last cell with CAcode (Xldown) in H col For every cell in Range (H3:Xldown) Use If/thenIf Last cell = 2nd Last cell (H Col) then Clear contents of I col (last row) select 2nd Last cell (H Col) Else 2nd Last cell (H Col)Next Cell I have to use Xldown to select range as the range will be variable each month & for each bank acct. I need to clear contents of cell to verify that all the sub-totals of unique CAcodes of the CAcoded amounts = the totals Because I may have up to 120 rows X 3 bank accts I am slowly using vba to do my work. Sub sort_And_delete_Sumif_amounts() Dim r As Range 'Select range to sort Range("A2:I98").Select 'Sort CAcode in H col ascendingly Selection.Sort Key1:=Range("H2"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal 'select range for comparisomn of CAcode Set r = Range("H3", Selection.End(xlDown))................
View Replies!
View Related
Cell Values Based On Input Of Another Cell And Sheet
I want to tie the values of some cells on Sheet 2 to what's put in into corresponding cells on Sheet 1. Example If Cell B3 on Sheet 1 has the string "B off" put in, Sheet 2 should automatically get "R1" in cell B12, "B off" in cell C12 and "ID123456" in cell D12. If cell B3 on Sheet 1 is empty, B12:D12 should be blank.
View Replies!
View Related
Comments Based On Cell Values
I would like help with a formula or VBA that would show comments based on cell values. For example, if cell A1 has cell value '2143', I want comments to be 'Twinkle Twinkle Little Stars' and if cell D1 has cell value '4567', I want 'Humpty Dumpty Fall on a Wall', and etc. How can I do it? I have about 35 different cell value categories. I appreciate any help you can give me. Thanks in advance for your time and effort.
View Replies!
View Related
If Statement In Macro: Macro To Change A Range Of Cells Colours Based On A Single Cell?
1st - Need a macro to change a range of cells colours based on a single cell having a value greater than 0.001. ie. cells A1 - G1 need to change to grey based on cell F1 having a value greater than 0.001 entered in it? 2nd - Also a macro for deleting the text contents of cell C1 based on cell F1 having a value greater than 0.001. Therefor if cell F1 has a number greater than 0.001 it changes the colour of celss A1 - G1 and also deletes the text in cell C1?
View Replies!
View Related
Macro (Select, Sort And Select)
I need to get a macro to select all the data in column "A", sort it in ascending order, omit the blanks if any, then select (highlight) all the data so that another macro can be run. When I record it, it will only record up to the last row I highlight but the data always changes so there could be more or less.
View Replies!
View Related
Change Event Based On Two Other Cell Values
myColumnOne = Range("NPN").Column 'this is column B, NPN is a Named Range of B1 myColumnTwo = Range("NPCH").Column 'this is column E, NPCH is a Named Range of E1 using these variables I want to say When data is entered into any cell in myColumnOne first check to see if this same data already exists in myColumnOne if it does then check to see if in the row where the data already exists, if the corresponding cell in myColumnTwo ISBLANK then MsgBox if the corresponding cell in myColumnTwo is not blank, allow the data to be entered. Example: Col B….Col E ABC…..xxx XZY….........
View Replies!
View Related
Assign Range Name Based On Cell Values
In my workbook sheet 11 has some ranges that need to have names based on cell values in sheet2 (for purposes of data validation lists). Range S28:S46 will assume the name of sheet2A11 & sheet2A3. (example name period_1unit_1) Range U28:U46 will assume the name of sheet2A11 & sheet2A4. Range W28:W46 will assume the name of sheet2A11 & sheet2A5 Right now I am calling the code when something is entered into A11. I have tried if statement and select case, but I ran into complications with both. I have posted both codes with the questions I have concerning those codes.
View Replies!
View Related
Fill In Cells Based On Other Cell Values
I am looking for a way for Excel to fill in cells in a column that has data sporadically in it. I am importing a text file into excel and saving it as an Excel file. In the example attached, I need the numbers filled in all the way down to the next number. I don't care about the text, just the numbers. I realize I can copy and paste, but these files can be several hundred rows long and it seems there should be a better way.
View Replies!
View Related
Changing Values In Column Based On Value In One Cell
Based on the attached (much simplified) spreadsheet, I want the values in the green cells on the "Month" page (B6:B10) to vary depending on the value in the yellow cell (B4), and for these cells to to look up the relevant column in the "Full Year" page - i.e. if B4 (on "Month") = Jan, look up C6:C10 in "Full Year"; if B4 = Feb, look up D6:D10, and so on. I know I can nest IF functions in order to achieve this, but could be a bit messy. I'm sure there must be a simpler formula within Excel ... just don't know what it's called!
View Replies!
View Related
Move Data Based On Cell Values
I would like to know if the following problem can be solved using only Excel without any operator intervention. Can a formula be created? If yes I would appreciate it very much Problem: If B5 = X Move B6 thru K6 to B5 thru K5 And Move B7 thru K7 to B6 thru K6 And than Blank out B7 thru K7
View Replies!
View Related
Return Value Based On Multiple Possible Values In A Cell
I have in A1 a string of 100 characters.. I would like to create a SELECT CASE where, if in A1 there is the word "BLACK" , put 1 in A10 if in A1 there is the word "WHITE", put 2 in A10 else put 50 in A10 I'm trying to use the instruction INSTR but with negative results.. (I have many cases other than BLACK and WHITE, that's why I need a SELECT CASE)
View Replies!
View Related
|
TRACKER
