Mid() Range #Value
I have a column A1:A5 which holds text format:
15:00  18:30
07:30  21:00
13:00  14:00
19:30  20:30
20:30  23:30
=SUMPRODUCT(MID(A1:A5;9;2)+0+MID(A1:A5;12;2)/60MID(A1:A5;1;2)+0MID(A1:A5;4;2)/60)
I use the above formula to calculate the total hours in the 5 timespans (in this case 22 hours). It can probably be shortended but remember im just learning this.
It works, but my problem is that once in a while one of the cells A1:A5 will be empty, or hold a text string with no numbers. When this happens my formula returns #Value!.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Function To Sort A Range By The Values In A Specific Column And Return The Range
I was just recently forced to create my first UDF and after how well it worked I now am very interested in learning more. I am trying to create a function to sort a range by the values in a specific column and return the range. I know this should be really simple but for some reason my code dies whenever it gets to my innermost loop. I need to use this in a larger function but for now this is my only question. I did find that Excel 2007 has built in Functions for this but my company still uses 2003. My Public Function SortRange(rngToSort As Range, valCol As Integer) Dim Swapper As Variant Dim i As Integer, _ j As Integer, _ k As Integer For i = 1 To rngToSort.Rows.Count For j = 1 To rngToSort.Rows.Count  i If rngToSort(j + 1, valCol) < rngToSort(j, valCol) Then For k = 1 To rngToSort.Columns.Count Swapper = rngToSort(j, k) rngToSort(j, k) = rngToSort(j + 1, k) rngToSort(j + 1, k) = Swapper Next k End If Next j Next i SortRange = rngToSort End Function
View Replies!
View Related
SUMIFS Function In 2007 (range To Sum, Range To Evaluate, Evaluation Criterion)
I’m trying to use the SUMIFS function in Excel 2007 to evaluate the following formula: {=SUMIFS(range to sum, range to evaluate, evaluation criterion)} The range to sum is A1:A10, the range (dates) to evaluate are in B1:B10, and the evaluation criterion is that the date is before 31/10/1999. So my formula looks like this: {=SUMIFS(A1:A10,B1:B10,”<31/10/1999”)} This works fine, but how do I refer the 3rd argument in the function to a date in a particular cell rather than typing in the date specifically?
View Replies!
View Related
Autofill In Macro Range Is Constant How Can I Code To Be A Variable Range?
I am trying to write a macro which will autofill specific columns. The macro will set the range from the start of my autofill to the end of my autofill as a constant range. The problem I need to get around is the end of my range can always change each time I run the macro. For instance, the first time I run the macro I may only need to autofill from row 4 to row 15. The next time, I may only need to autofill from row 4 to 23 (because of user updates). How can I make the end of my range not be a constant address but variable?
View Replies!
View Related
Get The Range Address Of A Dynamically Named Range That Refers To A Formula In VBA
I have a named range that expands and contracts based upon the amount of data that is in some column. Call it AllData_UsedRange. I have another named range that actually refers to a range. Call it AllData. Column A Row2 56 Row3 44 Row4 65 AllData is a named range that refers to the range A2:A65536 AllData_UsedRange refers to A2:A4 by way of this formula. =OFFSET(AllData,0,0,COUNTA(AllData)) How to I obtain an address of AllData_UsedRange in VBA code? These do not work... ThisWorkbook.Names("AllData_UsedRange").RefersToRange.Address Evaluate(ThisWorkbook.Names("AllData_UsedRange"))
View Replies!
View Related
Find Dates Between Monthly Range And Sum Another Cells Results That Are In A Range
I'm trying to make a by month spreadsheet that has all twelve month ranges starting in for a3. in a3 it would have the start date and in a4 it would have the end date. I'm trying to locate all of the dates between those two dates and pull in the profit ammounts from another sheet, the results would be in row 5. I would also like to pull in the loss amounts and have them in row 6. All corresponding with the date range in rows 3 and 4.
View Replies!
View Related
Dynamic Range For Pivot Table :: Error : Range Is Invalid
I am trying to get to grips with the dynamic ranges for pivot tables. I have named a range data and in the refers to section put: =OFFSET(Sheet2!A1,0,0,COUNTA(Sheet2!A:A),COUNTA(Sheet2!1:1)) I was hoping that then when i go to data > Pivot table and it asks for the range i could put =Data but it tells me that the range is invalid. I have attached a copy at the bottom.
View Replies!
View Related
Flexible Range Formula: Return The Minimum Value From A Column Range
I need a formula that will return the minimum value from a column range that flexs without having to manually go in an change row references. There is a blank row between each section of data in order to separate info. As an example:.... Need to formula to flex to include rows 5 thru 7 in the first section but expand to include rows 9 thru 13 in the second section. =MIN(A5.A7) works but how do I get next section to flex to =MIN(A9.A13) without manually changing the cell references? There is always a blank row in between the sections to separate.
View Replies!
View Related
Stop Graph From Changing Named Range To Formula Range
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 reenter 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.
View Replies!
View Related
Convert Dynamic Range To Static Named Range
My searches have not produced anything that I could apply to this situation. I'm trying to write VBA that would: 1. Search a Workbook for Dynamic Ranges. 2. When a Dynamic Range is found the code would: A. Determine the current coordinates for the range. B. Change the "Refers To" value From "=OFFSET...." To "=Worksheet_Name $Column$Row:$Column$Row" 3. Save Changes. 4. Close File. My apologies but I have very little experience in writing VBA. I understand about variables, arguments, and IF/THEN but just enough to use functions within Excel.
View Replies!
View Related
SUM Of Values In 1 Range Based On Specific Criteria In Another Range
I am tracking total man hours of contractors in columns of different rates applicable. At present I have structured my worksheet with the different rates across and days going down. As I am invoiced for a period of work I will log the invoice ref number in a separate field. I would like to sum totals to report against $ Invoiced and $ Committed (i,e worked but not actually invoiced against yet). Here is an example of what I am trying to achieve. The Actual showing sum mhrs * rate only for the mhrs entries with an invoice in corresponding Invoice Ref Column. The Committed producing sum of all mhrs * rate [which I am using =(SUM(D9:D37)*D7)] regardless of what is in invoice ref column. This way I can hopefully track what we have been billed and also what we can expect to have to budget for to cover what has been worked but not billed against.
View Replies!
View Related
Changing The Background Of A Range Based On Text In The Range
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?
View Replies!
View Related
Sumif(range,30,sum Range) Not Summing Values That Equal 0
I have a simple sumif formula that says =SUMIF(W61:W112,"<30",J61:J112). In column W, there are values ranging from 0 to 5000. If the formula is written like it is above, it excludes summing values from column J when the cell in column W equals 0. Why is this? I can just add another function that says sumif "=0", but I don't think I should have to.
View Replies!
View Related
Copy/Paste Range(s) Without Activating/selecting Range(s)
To initialize some cells/ranges, I am copying a given range and pasting it to another given range using the. Copy and .PaseSpecial methods. However, it would seem that both methods actually select the range(s) for the operations, i.e. the given ranges(s) are activated/selected thus changing the focus on the spreadsheet. I would like to perform both operations without actually selecting the given ranges.
View Replies!
View Related
Copy/Paste Range To Different Size/Shape Range
I have the following code that let's the user choose and " import" data to an existing sheet. It works well up until now. The problem is that the three ranges that I am trying to copy the data from on workbook to another has changed size. In previous version of my workbooks the range was two columns by 10 rows. Now, it is 1 column by 10 rows. So, when I run this macro it doesn't work because the two ranges are different. Is there any way to: 1) Only copy over one of the rows of a range thus making the macro run? 2) Do not run that part of the macro if there is an error? Thanks so much for reading this longwinded description but the error is a big problem Private Sub CommandButton1_Click() Import_Data_Form.Hide Run "UnProtectAll" Set b = Selection ad = b.Address ' Local Variables Dim wkbDataFile As Workbook
View Replies!
View Related
Loan ProcessingCopy Range From 1 Sheet To Same Range On Another
I'm developing a loan processing system for members of a club. When an applicant asks for a loan, the club will calculate 10 % of that interest and the applicant will have to pay it back in 5 successive fortnightly instalments. If he asks for a loan in the first fortnight (1), for example, he will have to start paying instalments in fortnights 2,3,4,5,6 to pay it all back. The system currently has 4 worksheets. The first sheet is a the loan application form. The cells outlined in thicker border, are the cells in which details must be input. Once it is input, the data will be automatically placed in the Processing worksheet using IF and VLookup functions (See spreadsheet attached), which is used as a basis for the loan schedule Worksheet. What I need is a macro that will copy the range filled in the Processing worksheet, and copy it to the exact same location in the Loan schedule worksheet (The cells with the same fortnight columns and the same member name. This is how the loans are to be filed.
View Replies!
View Related
Subscript Out Of Range When Selecting Range In Closed Worksheet
I am receiving a 'subscript out of range' error on the lines of code below. I would note that all variables are declared and all seem meaningful as regards what you would expect at that point. Below is a snippet from the immediate window which indicates what the values are: completecashname C:CashDevelopmentMyFolderoutputCASH042706.xls cashsheetname Formatted Sheet cashcurrcolumn A cashfirstrow 2 cashlastrow 876 Also the workbooks are both closed at this point (but it makes no difference) Set CashCopy = Workbooks(CompleteCashName).Sheets(CashSheetName). _ Range(CashCurrColumn & Cashfirstrow & ":K" & Cashlastrow).Value Set PelPaste = Workbooks(completepelname).Sheets(PELSheetName). _ Range((PELCurrColumn & PELlastrow)).Value
View Replies!
View Related
Copy Immediate Range, Paste To Another Sheet & Name Pasted Range
I have been struggling for a while to copy data from one worksheet to another and reset the target range. The copy bit is cool, the range resetting bit is not. I have tried various methods, but none seem to work. For example, the below code generates an error: "Compile error: Argument not optional" I have stuck the particular command button script below to let you see what I am trying to do: Private Sub cmbFilter_Click() Dim sCriteria As String On Error Resume Next
View Replies!
View Related
Reference Cell Looks At One Range Then See If Any Exist In Another Range
I have a workbook with two sheets. The first sheet is the data sheet. Data Worksheet A B C D colA colA,B colA,B,C colA,B,C,D 104 20 21 CR10600S 104 10420 1042021 1042021DUS 104 20 21 CR10600SX 104 10420 1042021 1042021DUS 104 20 21 CR10603S 104 10420 1042021 1042021DUS 104 20 21 CR10603SX 104 10420 1042021 1042021DUS 104 20 22 CR49605S 104 10420 1042022 1042022HKZ 104 20 22 CR49605SX 104 10420 1042022 1042022HKZ 104 20 23 CR39601P 104 10420 1042023 1042023SPR The next sheet is where the magic happens. Separate this worksheet into two sections. The reference section (column A and B) and the entry section (column EG). Column D is a formula that will concatenate column EG together. In the entry section any number of combinations can be entered. In the reference section I need to take the style number and reference it back to the data sheet. Then take the possible combinations from that style (column E and greater) and see if any of those combinations exist on the other sheet in column D. If so then say added....otherwise say needed. "....In Pricing Sheet" is where I am looking to put needed or added or something like it. A B C D E F G STYLE …IN PRICING SHEET Concat DEPT MCL SCL STYLE CR10600S Added 1042021 104 20 21 CR10600SX Added 1042022 104 20 22 CR10603S Added CR10603SX Added CR49605S Added CR49605SX Added CR39601P Needed Any ideas on how to do this in formulas and without code? I have tried and looked into index, match, dget and lookup. I just don't think I am finding the right combination of formulas.
View Replies!
View Related
Validate For Numeric Range & Alphanumeric Range
Can anyone help me to get a validation to check that what is entered in a cell, lets say A1, has to be number between 100000 and 899999 (this I already have) OR a value/text from I100000 to I899999. So it only acceots a vlaue betwwen 100000 and 899999 and it is also ok to have the letter I at the begining.
View Replies!
View Related
Dimming Range Causes Lower Case Range
Whenever I type " Range", such as in: Dim myRange As Range Excel immediately autocorrects the line as such: Dim myRange As range This creates a problem because apparently excel believes "range" is the name of a variable. So when I run the macro, it runs the line and declares the following error: Runtime error '91': Object variable or With block variable not set The following is my exact code. The very last line line is where the error occurs. Dim count As Integer Dim column As Integer Dim startRange As String Dim endRange As String Dim theRange As String
View Replies!
View Related
Range.Find Method On A Hidden Range
I have a module that contains the lines below: Dim myRng as Range set myRng = Range("B1:B100").Find(what:= "Symbol") I have run this module frequently and successfully over the past several months, during which time Column B has been hidden. When I tried to run it today I got a Runtime error 91: Object variable or with block variable not set. I checked to see that "Symbol" was present in the stated range (it was), and noted that when I went to debug the Runtime error, myRng was 'Nothing'. Now I find that if I unhide the column before the set myRng statement, the code runs without a hitch. My question is: Is there a known restriction on the Range.Find Method that prevents its use on a hidden range?
View Replies!
View Related
Calculate The Range And Fill With A Formula Using The New Range
I have a range of values that are not a constant length. I want a macro that will calculate the range and fill with a formula using the new range. In this example, I need a formula to look at the values in range f464:f471 and calculate the number of values greater than $1000. Column B is a salesman identifier. I can do this manually, but it takes me about 1/2 hour to complete. At every change in "B", I need to place this formula in the open, blank cell in "E" such as highlighted below. week39 ABCDEF45939SCHTERRWIEDEMANNDCCDISTMUSCHANY046039SCHTERRWIEDEMANNDCCDISTMUSCHANY046139SCHTERRWIEDEMANNDCCDISTMUSCHANY046239SCHTERRWIEDEMANNDCCDISTMUSCHANY0463 SCH Total 14042.2146439SCITERRSULLIVANDCCDISTMUSCHANY15587.1546539SCITERRSULLIVANDCCDISTMUSCHANY7217.2246639SCITERRSULLIVANDCCDISTMUSCHANY5316.6246739SCITERRSULLIVANDCCDISTMUSCHANY2720.6946839SCITERRSULLIVANDCCDISTMUSCHANY520.446939SCITERRSULLIVANDCCDISTMUSCHANY195.7147039SCITERRSULLIVANDCCDISTMUSCHANY047139SCITERRSULLIVANDCCDISTMUSCHANY0472 SCI Total 31557.7947339SCJTERRVANBUSKIRKDCCDISTMUSCHANY4190.7147439SCJTERRVANBUSKIRKDCCDISTMUSCHANY3219.7647539SCJTERRVANBUSKIRKDCCDISTMUSCHANY3187.147639SCJTERRVANBUSKIRKDCCDISTMUSCHANY2209.3747739SCJTERRVANBUSKIRKDCCDISTMUSCHANY2132.7547839SCJTERRVANBUSKIRKDCCDISTMUSCHANY2034.56 Spreadsheet FormulasCellFormulaF463=SUBTOTAL(9,F435:F462)F472=SUBTOTAL(9,F464:F471) Excel tables to the web >> http://www.exceljeaniehtml.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
View Replies!
View Related
Check A Numbers In A Range To See If Any Of Them Falls Into A Particular Range
make this formula more concise and shorter, it was design to check a numbers in a range to see if any of them falls into a particular range. =IF(SUM((COUNTIF(Fund,">11999")(COUNTIF(Fund,">12999"))),(COUNTIF(Fund,">21099")(COUNTIF(Fund,">28729"))),(COUNTIF(Fund,">28730")(COUNTIF(Fund,">33999"))),(COUNTIF(Fund,">58999")(COUNTIF(Fund,">59999"))),(COUNTIF(Fund,">82000")(COUNTIF(Fund,">84999"))),(COUNTIF(FUND1,">11999")(COUNTIF(FUND1,">12999"))),(COUNTIF(FUND1,">21099")(COUNTIF(FUND1,">28729"))),(COUNTIF(FUND1,">28730")(COUNTIF(FUND1,">33999"))),(COUNTIF(FUND1,">58999")(COUNTIF(FUND1,">59999"))),(COUNTIF(FUND1,">82000")(COUNTIF(FUND1,">84999"))))>0,"ATTACHMENT E IS REQUIRED","") and if the any of the number fall into the range it will print the message, "Attachment is Required"
View Replies!
View Related
Search A Range Of Cells For A Range Of Values
I'm trying to do is search a range of cells for a range of values AND then see if it matches one other value in another range of cells. In this case, =SUMPRODUCT((T3:T49="P6")+(T3:T49="P5")+(T3:T49="P4")+(T3:T49="P3"))*(U3:U49="w") T3:T49 can equal P6 or P5 or P4 or P3 but the cells can only be counted if U3:U49 is 'w' as well
View Replies!
View Related
Selecting A Range Based On Date Within That Range
What i'm trying to do is select a range based on the date which happens to be in the first column of the range. For example, I have a column (A) that contains dates and column (B) that contains names. Its easy enough to select the entire range using Range("A1:B4").Select I need someting that will analyse column A and conditionally selct the range based on the date in column A (ie. it will only select the rows where column A has todays date).
View Replies!
View Related
Dynamic Range 2003: Run Calculations On This Range
I have a range of data that will grow as the days of the month pass but I need to run calculations on this range. The data is 13 columns wide but the number of rows will increase daily. I remember using a formula in the Define Range that would automatically take into account new additions using the OFFSET function but cannot for the life of me think how exactly to do it.
View Replies!
View Related
Sum Range Using 1 Column Of Named Range As Criteria
how to use SUM Formula a column from within a Named Ranges or Dynamic Named Range? For example, if the range name "MyData" refers to the address: A1:G10, how could I sum all the numbers in column G of that range where column A meets certain criteria. Eg., Column A holds fruit names: Apple Orange Banana Apple and column G holds quantities of the particular fruit. I'd like to sum column G (quantity) for only those quantities that match "Apple" in column A.
View Replies!
View Related
Set Range Variable To Growing Dynamic Range
I have been working on part of the code for my spreadsheet and it works fine in the spreadsheet “Databaseform” however when I copied the code to my master spreadsheet “Paul_PartLocDBCombo” it does not work, I get the error: Method ‘ range’ of object ‘_worksheet’ failed The code is then highlighted in yellow, the code is: Set rng = wksPartsData.Range("a1", Range("a65536").End(xlUp)) Meaning this part is incorrect but I don’t know why? To work it: go to Databaseform and press start. Enter 7mm in the product field and press find all. It will then return all the matching results in the userform. Its this I want to try and achieve on the other spreadsheet when the button find label is pressed.
View Replies!
View Related
Conditional Formatting: Compare Range To Another Range
I was looking to use the VBA conditional formatting script posted on OzGrid and was curious if it would be easy to make some slight modifications. Private Sub Worksheet_Change(ByVal Target As Range) Dim icolor As Integer If Not Intersect(Target, Range("A1:A10")) Is Nothing Then Select Case Target Case 1 To 5 icolor = 6 Case 6 To 10 icolor = 12 Case 11 To 15 icolor = 7 Case 16 To 20 .......................... I can see that Case is the number range referenced to change the background...However, would it be possible to have it read a cells value as the criteria for the color change? Currently, I would like it to reference values in range I2:M2 and anything matching those cells in range A4:E28 change background to icolor = 30. I'm just not sure what to replace Case with to make it refence cells I2:M2
View Replies!
View Related
Copy Range If Cells Have Same Values As Another Range
1. I have two workbooks (eg. workbook1 and workbook2) 2. I compare the cell values in workbook1.sheet1.cell range (d6:d20) and workbook1.sheet2.cell range (d6:d20). 3. If the values in the range of cells are same, I want to take the value in workbook1.sheet2.cell range (d6:d20) and copy to workbook2.sheet1.cell range (d6:d20).
View Replies!
View Related
Copy Range Of Cells Over A Filtered Range
I wonder  is there a way to copy the green range over the filtered cells in col. C !? (I want to Copy range D20:D23 on to cells: C5, C9, C13, C17). I tried to select "Visible Cells Only" as the target for Pasting onto (using [F5] etc...) but no success. I prefer a solution that does not involve VBA. *** see attached picture.
View Replies!
View Related
Code That Copies A Range Opens Notepad Pastes The Range Opens Save Dialog And Types The File Name
I currently have the following code that copies a range opens notepad pastes the range opens save dialog and types the file name. The problem I have is with overwriting the existing file. Range("A1:A202").Select Selection.Copy Shell "notepad.exe", vbNormalFocus SendKeys "^V" SendKeys "^s" SendKeys "Total_IEDs_Hour_Of_Day_2009.xml" SendKeys "{TAB}" SendKeys "a" SendKeys "{ENTER}" Everything works fine to this point. Then it opens the do you want to overwrite dialog and I cant get it to hit yes.
View Replies!
View Related
Set A Range And Find All Numbers In That Range
I have the following columns and rows that I need to MOD by 15. Once they have been MOD, I need to set a range and find all numbers in that range of say, eg; all number between 9.6 and 9.8. Really hope someone can help, have been working on it day and night. I can do them one at a time, but is very time consuming. Is there a formula that will do the whole worksheet in record time? 2/18/1975, 156.7519, 175.8083, 235.4675, 253.765 4/11/1973, 237,7642, 346.5342, 113.3145, 321.435 5/12/1985, 342.7625, 123.4233, 253.4321, 357.234 etc. etc. etc.
View Replies!
View Related
Copy Column Range To Row Range
I have the following sheet1 (" Case Preview") column a column B column C ID project Custodian 1 ABC James Johns 2 DEF John Doe 3 GHI Laura DiNapoli 4 JKL Lydia Koernell I need to be able to display the custodians in column C on sheet1 ("Case Preview") in sheet2("Search Term Breakdwn") starting in B10,C10,D10,etc until all the names in column C are there. The tricky part is that column C gets populated each month with different custodians coming from different dbs, so I have to be able to know how many rows in column C are populated with info. I saw a few postings here regarding transpose and tried to used it, but to no avail. I am using the following Sub copyrows() Dim MySheet As Excel.Worksheet Dim MySheet2 As Excel.Worksheet Dim rcell As Range
View Replies!
View Related
Match Numbers In Range To Another Range
I'm trying this formula but it does not work properly in cell S5: =IF(ISNUMBER(SEARCH(MID(R5,1,1),K5:O5)*SEARCH(MID(R5,2,1),K5:O5)*SEARCH(MID(R5,3,1),K5:O5)),"win","lose") I tried excel help and the great OZ but nothing exactly to my problem. This is what I would like the formula to do. Cell S5 would search the solutions cells( K5:O7 ) and compare them to either cell ( R5 for all 3 matching digits) or all combinations cells ( D5:I5 ) if a match is there then it would produce a "Win" if no match it would produce a " Lose " I will need to be able to expand my range because my data will extend in the solution cells I have attached a sample file for your review.
View Replies!
View Related
