Home |
Search |
Today's Posts |
#1
|
|||
|
|||
A few VBA questions - long post!
Hi,
I'm currently trying to teach myself VBA. I may have set myself a rather ambitious problem, and I'm wondering if anyone can help. There are a few queries. All the queries center around the same report that is generated by another program - the locations of headers can change column each time the report is generated, and the number of rows change each time. A simplistic view of the report is as follows: A B C 1.Origin Destination %Margin 2. ABC DEF 5 3. GHI JKL 10 4. MNO PQR 15 1: Range function The first thing I want to do is put in a formula in the column to the right of the %Margin column, whereever that happens to be. I've written code which finds the cell C1 (in this case), and increments the row and column by one, activates that cell (here - D2). I then do varStart= Active.Cell ActiveCell.FormulaR1C1 = //the required formula 'Find the last cell in that column/sheet: Set varLast = Range("D2").SpecialCells(xlCellTypeLastCell) lLastRow = varLast.Row lLastCol = varLast.Column 'Fill the formula down to the end Selection.AutoFill Destination:=Range("D2:D4"), Type:=xlFillDefault I'm sure there must be some way to replace the references D2 and D2:D4 in the code above with references to varStart and varLast that I have already found, but I can't get it to work. Through research it appears that the Range function only accepts a string as an argument - I tried converting varStart and varLast to strings, but just don't seem to get it right. Can anyone give me a hand with this? 2. Deleting rows based on multiple criteria The second problem is slightly more complicated (I think). Imagine the 3 letter codes above are airport codes. I have an array of these codes varCodes = Array("ABC", "DEF", "GHI", "MNO", "PQR") What I want to do is delete any line that doesn't have BOTH origin AND destination as one of the codes in the array. In this case I would want line 3 to be deleted as JKL is not in the array. My plan was to find the Origin column, run through that, and set a FALSE flag if it came across any row that had a code not in my array, then run through the Destination column doing the same thing, then finally delete any row that had a false flag. This is the snippet I tried: For lOriginRow = 1 To 100 //(I only used 100 here because I haven't managed to prob 1 working) For i = 1 To 5 //the number of items in the array If varCodes(i) = ActiveCell.Value Then Cells(lOriginRow, 13) = "True" End If Next i Next lOriginRow The problem here seems to be with varCodes(i)=ActiveCell.Value - am I actually incrementing the row each time? Also Cells(lOriginRow, 13) doesn't seem to be the right way to reference it. Any help would be greatly appreciated. If anyone would like me to send them the sample spreadsheet with code to date, please mail me offlist and I'll send it on. Regards, and thanks in advance, Fiona |
#2
|
|||
|
|||
You can actually assign the formula to the range in one plop--you don't need to
put it in one cell and copy down (but that's ok, too). And there's a worksheet function that you could use to check to see if the value is in that array. You could use =iserror(match(value,range,0)) to determine if that value was in that range. in VBA, you'd use application.match(), but you can still check for an error (=match() returns a number if there is a match). Option Explicit Sub testme01() Dim MarginCell As Range Dim MarginString As String Dim varCodes As Variant Dim res As Variant Dim LastRow As Long Dim FirstRow As Long Dim iRow As Long Dim delRng As Range varCodes = Array("ABC", "DEF", "GHI", "MNO", "PQR") MarginString = "%margin" With ActiveSheet With .Range("a1").EntireRow Set MarginCell = .Cells.Find(what:=MarginString, _ after:=.Cells(.Cells.Count), LookIn:=xlValues, _ lookat:=xlWhole, searchorder:=xlNext, _ searchdirection:=xlByColumns, MatchCase:=False) End With If MarginCell Is Nothing Then MsgBox "Can't find: " & MarginString Exit Sub End If LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row .Range(MarginCell.Offset(1, 1), _ .Cells(LastRow, MarginCell.Offset(0, 1).Column)).FormulaR1C1 _ = "=rc[-1]" 'or whatever you want FirstRow = 2 For iRow = LastRow To 2 Step -1 If IsError(Application.Match(.Cells(iRow, "A").Value, varCodes, 0)) _ Or IsError(Application.Match(.Cells(iRow, "B").Value, varCodes, 0)) Then If delRng Is Nothing Then Set delRng = .Cells(iRow, "A") Else Set delRng = Union(.Cells(iRow, "A"), delRng) End If End If Next iRow If delRng Is Nothing Then 'do nothing Else 'delRng.EntireRow.Delete 'for real delRng.EntireRow.Select 'for testing End If End With End Sub Fiona O'Grady wrote: Hi, I'm currently trying to teach myself VBA. I may have set myself a rather ambitious problem, and I'm wondering if anyone can help. There are a few queries. All the queries center around the same report that is generated by another program - the locations of headers can change column each time the report is generated, and the number of rows change each time. A simplistic view of the report is as follows: A B C 1.Origin Destination %Margin 2. ABC DEF 5 3. GHI JKL 10 4. MNO PQR 15 1: Range function The first thing I want to do is put in a formula in the column to the right of the %Margin column, whereever that happens to be. I've written code which finds the cell C1 (in this case), and increments the row and column by one, activates that cell (here - D2). I then do varStart= Active.Cell ActiveCell.FormulaR1C1 = //the required formula 'Find the last cell in that column/sheet: Set varLast = Range("D2").SpecialCells(xlCellTypeLastCell) lLastRow = varLast.Row lLastCol = varLast.Column 'Fill the formula down to the end Selection.AutoFill Destination:=Range("D2:D4"), Type:=xlFillDefault I'm sure there must be some way to replace the references D2 and D2:D4 in the code above with references to varStart and varLast that I have already found, but I can't get it to work. Through research it appears that the Range function only accepts a string as an argument - I tried converting varStart and varLast to strings, but just don't seem to get it right. Can anyone give me a hand with this? 2. Deleting rows based on multiple criteria The second problem is slightly more complicated (I think). Imagine the 3 letter codes above are airport codes. I have an array of these codes varCodes = Array("ABC", "DEF", "GHI", "MNO", "PQR") What I want to do is delete any line that doesn't have BOTH origin AND destination as one of the codes in the array. In this case I would want line 3 to be deleted as JKL is not in the array. My plan was to find the Origin column, run through that, and set a FALSE flag if it came across any row that had a code not in my array, then run through the Destination column doing the same thing, then finally delete any row that had a false flag. This is the snippet I tried: For lOriginRow = 1 To 100 //(I only used 100 here because I haven't managed to prob 1 working) For i = 1 To 5 //the number of items in the array If varCodes(i) = ActiveCell.Value Then Cells(lOriginRow, 13) = "True" End If Next i Next lOriginRow The problem here seems to be with varCodes(i)=ActiveCell.Value - am I actually incrementing the row each time? Also Cells(lOriginRow, 13) doesn't seem to be the right way to reference it. Any help would be greatly appreciated. If anyone would like me to send them the sample spreadsheet with code to date, please mail me offlist and I'll send it on. Regards, and thanks in advance, Fiona -- Dave Peterson |
#3
|
|||
|
|||
With linewrap fixed:
Dave Peterson wrote: You can actually assign the formula to the range in one plop--you don't need to put it in one cell and copy down (but that's ok, too). And there's a worksheet function that you could use to check to see if the value is in that array. You could use =iserror(match(value,range,0)) to determine if that value was in that range. in VBA, you'd use application.match(), but you can still check for an error (=match() returns a number if there is a match). Option Explicit Sub testme01() Dim MarginCell As Range Dim MarginString As String Dim varCodes As Variant Dim res As Variant Dim LastRow As Long Dim FirstRow As Long Dim iRow As Long Dim delRng As Range varCodes = Array("ABC", "DEF", "GHI", "MNO", "PQR") MarginString = "%margin" With ActiveSheet With .Range("a1").EntireRow Set MarginCell = .Cells.Find(what:=MarginString, _ after:=.Cells(.Cells.Count), LookIn:=xlValues, _ lookat:=xlWhole, searchorder:=xlNext, _ searchdirection:=xlByColumns, MatchCase:=False) End With If MarginCell Is Nothing Then MsgBox "Can't find: " & MarginString Exit Sub End If LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row .Range(MarginCell.Offset(1, 1), .Cells(LastRow, _ MarginCell.Offset(0, 1).Column)).FormulaR1C1 _ = "=rc[-1]" 'or whatever you want FirstRow = 2 For iRow = LastRow To 2 Step -1 If IsError(Application.Match(.Cells(iRow, "A").Value, _ varCodes, 0)) _ Or IsError(Application.Match(.Cells(iRow, "B").Value, _ varCodes, 0)) Then If delRng Is Nothing Then Set delRng = .Cells(iRow, "A") Else Set delRng = Union(.Cells(iRow, "A"), delRng) End If End If Next iRow If delRng Is Nothing Then 'do nothing Else 'delRng.EntireRow.Delete 'for real delRng.EntireRow.Select 'for testing End If End With End Sub Fiona O'Grady wrote: Hi, I'm currently trying to teach myself VBA. I may have set myself a rather ambitious problem, and I'm wondering if anyone can help. There are a few queries. All the queries center around the same report that is generated by another program - the locations of headers can change column each time the report is generated, and the number of rows change each time. A simplistic view of the report is as follows: A B C 1.Origin Destination %Margin 2. ABC DEF 5 3. GHI JKL 10 4. MNO PQR 15 1: Range function The first thing I want to do is put in a formula in the column to the right of the %Margin column, whereever that happens to be. I've written code which finds the cell C1 (in this case), and increments the row and column by one, activates that cell (here - D2). I then do varStart= Active.Cell ActiveCell.FormulaR1C1 = //the required formula 'Find the last cell in that column/sheet: Set varLast = Range("D2").SpecialCells(xlCellTypeLastCell) lLastRow = varLast.Row lLastCol = varLast.Column 'Fill the formula down to the end Selection.AutoFill Destination:=Range("D2:D4"), Type:=xlFillDefault I'm sure there must be some way to replace the references D2 and D2:D4 in the code above with references to varStart and varLast that I have already found, but I can't get it to work. Through research it appears that the Range function only accepts a string as an argument - I tried converting varStart and varLast to strings, but just don't seem to get it right. Can anyone give me a hand with this? 2. Deleting rows based on multiple criteria The second problem is slightly more complicated (I think). Imagine the 3 letter codes above are airport codes. I have an array of these codes varCodes = Array("ABC", "DEF", "GHI", "MNO", "PQR") What I want to do is delete any line that doesn't have BOTH origin AND destination as one of the codes in the array. In this case I would want line 3 to be deleted as JKL is not in the array. My plan was to find the Origin column, run through that, and set a FALSE flag if it came across any row that had a code not in my array, then run through the Destination column doing the same thing, then finally delete any row that had a false flag. This is the snippet I tried: For lOriginRow = 1 To 100 //(I only used 100 here because I haven't managed to prob 1 working) For i = 1 To 5 //the number of items in the array If varCodes(i) = ActiveCell.Value Then Cells(lOriginRow, 13) = "True" End If Next i Next lOriginRow The problem here seems to be with varCodes(i)=ActiveCell.Value - am I actually incrementing the row each time? Also Cells(lOriginRow, 13) doesn't seem to be the right way to reference it. Any help would be greatly appreciated. If anyone would like me to send them the sample spreadsheet with code to date, please mail me offlist and I'll send it on. Regards, and thanks in advance, Fiona -- Dave Peterson -- Dave Peterson |
#4
|
|||
|
|||
Hi Dave,
Thanks for that. It solves problem 1 perfectly - from examining your code it seems that I was using Cells instead of .Cells - I'm still getting to grips with VB/A syntax :s For problem 2 (deleting the rows) your code works great provided I know that Origin and Destination are definitely in columns A and B To cater for the case where Origin and Desination may be in other columns I defined OriginCell to be Set OriginCell = .Cells.Find(what:="Origin", _ after:=.Cells(.Cells.Count), LookIn:=xlValues, _ lookat:=xlWhole, searchorder:=xlNext, _ searchdirection:=xlByColumns, MatchCase:=False) and then further down I used If IsError(Application.Match(.Cells(iRow, OriginCell.Column).Value, _ varCodes, 0)) _ (i.e. I switched "A" for OriginCell.Column) and that didn't work. But then I defined Dim OriginColIndex as Long and then put OriginColIndex = OriginCell.Column and then changed it to If IsError(Application.Match(.Cells(iRow, OriginColIndex).Value, _ varCodes, 0)) _ it all worked fine. I'm guessing that the range datatype wasn't accepted by the Cells() property(object?), and that I managed to force a conversion by declaring OriginColIndex as Long. Can anyone tell me if that's what's actually happening, or if I just got it right by fluke? It would be nice to understand what I'm doing so that I can use the knowledge in future, rather than just playing with things until they work :) Thanks again for your help Dave, Regards, Fiona "Dave Peterson" wrote in message ... With linewrap fixed: Dave Peterson wrote: You can actually assign the formula to the range in one plop--you don't need to put it in one cell and copy down (but that's ok, too). And there's a worksheet function that you could use to check to see if the value is in that array. You could use =iserror(match(value,range,0)) to determine if that value was in that range. in VBA, you'd use application.match(), but you can still check for an error (=match() returns a number if there is a match). Option Explicit Sub testme01() Dim MarginCell As Range Dim MarginString As String Dim varCodes As Variant Dim res As Variant Dim LastRow As Long Dim FirstRow As Long Dim iRow As Long Dim delRng As Range varCodes = Array("ABC", "DEF", "GHI", "MNO", "PQR") MarginString = "%margin" With ActiveSheet With .Range("a1").EntireRow Set MarginCell = .Cells.Find(what:=MarginString, _ after:=.Cells(.Cells.Count), LookIn:=xlValues, _ lookat:=xlWhole, searchorder:=xlNext, _ searchdirection:=xlByColumns, MatchCase:=False) End With If MarginCell Is Nothing Then MsgBox "Can't find: " & MarginString Exit Sub End If LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row .Range(MarginCell.Offset(1, 1), .Cells(LastRow, _ MarginCell.Offset(0, 1).Column)).FormulaR1C1 _ = "=rc[-1]" 'or whatever you want FirstRow = 2 For iRow = LastRow To 2 Step -1 If IsError(Application.Match(.Cells(iRow, "A").Value, _ varCodes, 0)) _ Or IsError(Application.Match(.Cells(iRow, "B").Value, _ varCodes, 0)) Then If delRng Is Nothing Then Set delRng = .Cells(iRow, "A") Else Set delRng = Union(.Cells(iRow, "A"), delRng) End If End If Next iRow If delRng Is Nothing Then 'do nothing Else 'delRng.EntireRow.Delete 'for real delRng.EntireRow.Select 'for testing End If End With End Sub Fiona O'Grady wrote: Hi, I'm currently trying to teach myself VBA. I may have set myself a rather ambitious problem, and I'm wondering if anyone can help. There are a few queries. All the queries center around the same report that is generated by another program - the locations of headers can change column each time the report is generated, and the number of rows change each time. A simplistic view of the report is as follows: A B C 1.Origin Destination %Margin 2. ABC DEF 5 3. GHI JKL 10 4. MNO PQR 15 1: Range function The first thing I want to do is put in a formula in the column to the right of the %Margin column, whereever that happens to be. I've written code which finds the cell C1 (in this case), and increments the row and column by one, activates that cell (here - D2). I then do varStart= Active.Cell ActiveCell.FormulaR1C1 = //the required formula 'Find the last cell in that column/sheet: Set varLast = Range("D2").SpecialCells(xlCellTypeLastCell) lLastRow = varLast.Row lLastCol = varLast.Column 'Fill the formula down to the end Selection.AutoFill Destination:=Range("D2:D4"), Type:=xlFillDefault I'm sure there must be some way to replace the references D2 and D2:D4 in the code above with references to varStart and varLast that I have already found, but I can't get it to work. Through research it appears that the Range function only accepts a string as an argument - I tried converting varStart and varLast to strings, but just don't seem to get it right. Can anyone give me a hand with this? 2. Deleting rows based on multiple criteria The second problem is slightly more complicated (I think). Imagine the 3 letter codes above are airport codes. I have an array of these codes varCodes = Array("ABC", "DEF", "GHI", "MNO", "PQR") What I want to do is delete any line that doesn't have BOTH origin AND destination as one of the codes in the array. In this case I would want line 3 to be deleted as JKL is not in the array. My plan was to find the Origin column, run through that, and set a FALSE flag if it came across any row that had a code not in my array, then run through the Destination column doing the same thing, then finally delete any row that had a false flag. This is the snippet I tried: For lOriginRow = 1 To 100 //(I only used 100 here because I haven't managed to prob 1 working) For i = 1 To 5 //the number of items in the array If varCodes(i) = ActiveCell.Value Then Cells(lOriginRow, 13) = "True" End If Next i Next lOriginRow The problem here seems to be with varCodes(i)=ActiveCell.Value - am I actually incrementing the row each time? Also Cells(lOriginRow, 13) doesn't seem to be the right way to reference it. Any help would be greatly appreciated. If anyone would like me to send them the sample spreadsheet with code to date, please mail me offlist and I'll send it on. Regards, and thanks in advance, Fiona -- Dave Peterson -- Dave Peterson |
#5
|
|||
|
|||
I don't see a difference between using this:
If IsError(Application.Match(.Cells(iRow, _ OriginCell.Column).Value, varCodes, 0)) _ And Dim OriginColIndex as Long OriginColIndex = OriginCell.Column then If IsError(Application.Match(.Cells(iRow, _ OriginColIndex).Value, varCodes, 0)) _ I'd guess it was something else that was the problem. == did you put that A in double quotes? If IsError(Application.Match(.Cells(iRow, "A").value, _ varCodes, 0)) _ If IsError(Application.Match(.Cells(iRow, 1).value, _ varCodes, 0)) _ These two are equivalent. And .cells() will accept either numbers 1-256 or letters ("a"-"IV"). But glad you got it working. Fiona O'Grady wrote: Hi Dave, Thanks for that. It solves problem 1 perfectly - from examining your code it seems that I was using Cells instead of .Cells - I'm still getting to grips with VB/A syntax :s For problem 2 (deleting the rows) your code works great provided I know that Origin and Destination are definitely in columns A and B To cater for the case where Origin and Desination may be in other columns I defined OriginCell to be Set OriginCell = .Cells.Find(what:="Origin", _ after:=.Cells(.Cells.Count), LookIn:=xlValues, _ lookat:=xlWhole, searchorder:=xlNext, _ searchdirection:=xlByColumns, MatchCase:=False) and then further down I used If IsError(Application.Match(.Cells(iRow, OriginCell.Column).Value, _ varCodes, 0)) _ (i.e. I switched "A" for OriginCell.Column) and that didn't work. But then I defined Dim OriginColIndex as Long and then put OriginColIndex = OriginCell.Column and then changed it to If IsError(Application.Match(.Cells(iRow, OriginColIndex).Value, _ varCodes, 0)) _ it all worked fine. I'm guessing that the range datatype wasn't accepted by the Cells() property(object?), and that I managed to force a conversion by declaring OriginColIndex as Long. Can anyone tell me if that's what's actually happening, or if I just got it right by fluke? It would be nice to understand what I'm doing so that I can use the knowledge in future, rather than just playing with things until they work :) Thanks again for your help Dave, Regards, Fiona "Dave Peterson" wrote in message ... With linewrap fixed: Dave Peterson wrote: You can actually assign the formula to the range in one plop--you don't need to put it in one cell and copy down (but that's ok, too). And there's a worksheet function that you could use to check to see if the value is in that array. You could use =iserror(match(value,range,0)) to determine if that value was in that range. in VBA, you'd use application.match(), but you can still check for an error (=match() returns a number if there is a match). Option Explicit Sub testme01() Dim MarginCell As Range Dim MarginString As String Dim varCodes As Variant Dim res As Variant Dim LastRow As Long Dim FirstRow As Long Dim iRow As Long Dim delRng As Range varCodes = Array("ABC", "DEF", "GHI", "MNO", "PQR") MarginString = "%margin" With ActiveSheet With .Range("a1").EntireRow Set MarginCell = .Cells.Find(what:=MarginString, _ after:=.Cells(.Cells.Count), LookIn:=xlValues, _ lookat:=xlWhole, searchorder:=xlNext, _ searchdirection:=xlByColumns, MatchCase:=False) End With If MarginCell Is Nothing Then MsgBox "Can't find: " & MarginString Exit Sub End If LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row .Range(MarginCell.Offset(1, 1), .Cells(LastRow, _ MarginCell.Offset(0, 1).Column)).FormulaR1C1 _ = "=rc[-1]" 'or whatever you want FirstRow = 2 For iRow = LastRow To 2 Step -1 If IsError(Application.Match(.Cells(iRow, "A").Value, _ varCodes, 0)) _ Or IsError(Application.Match(.Cells(iRow, "B").Value, _ varCodes, 0)) Then If delRng Is Nothing Then Set delRng = .Cells(iRow, "A") Else Set delRng = Union(.Cells(iRow, "A"), delRng) End If End If Next iRow If delRng Is Nothing Then 'do nothing Else 'delRng.EntireRow.Delete 'for real delRng.EntireRow.Select 'for testing End If End With End Sub Fiona O'Grady wrote: Hi, I'm currently trying to teach myself VBA. I may have set myself a rather ambitious problem, and I'm wondering if anyone can help. There are a few queries. All the queries center around the same report that is generated by another program - the locations of headers can change column each time the report is generated, and the number of rows change each time. A simplistic view of the report is as follows: A B C 1.Origin Destination %Margin 2. ABC DEF 5 3. GHI JKL 10 4. MNO PQR 15 1: Range function The first thing I want to do is put in a formula in the column to the right of the %Margin column, whereever that happens to be. I've written code which finds the cell C1 (in this case), and increments the row and column by one, activates that cell (here - D2). I then do varStart= Active.Cell ActiveCell.FormulaR1C1 = //the required formula 'Find the last cell in that column/sheet: Set varLast = Range("D2").SpecialCells(xlCellTypeLastCell) lLastRow = varLast.Row lLastCol = varLast.Column 'Fill the formula down to the end Selection.AutoFill Destination:=Range("D2:D4"), Type:=xlFillDefault I'm sure there must be some way to replace the references D2 and D2:D4 in the code above with references to varStart and varLast that I have already found, but I can't get it to work. Through research it appears that the Range function only accepts a string as an argument - I tried converting varStart and varLast to strings, but just don't seem to get it right. Can anyone give me a hand with this? 2. Deleting rows based on multiple criteria The second problem is slightly more complicated (I think). Imagine the 3 letter codes above are airport codes. I have an array of these codes varCodes = Array("ABC", "DEF", "GHI", "MNO", "PQR") What I want to do is delete any line that doesn't have BOTH origin AND destination as one of the codes in the array. In this case I would want line 3 to be deleted as JKL is not in the array. My plan was to find the Origin column, run through that, and set a FALSE flag if it came across any row that had a code not in my array, then run through the Destination column doing the same thing, then finally delete any row that had a false flag. This is the snippet I tried: For lOriginRow = 1 To 100 //(I only used 100 here because I haven't managed to prob 1 working) For i = 1 To 5 //the number of items in the array If varCodes(i) = ActiveCell.Value Then Cells(lOriginRow, 13) = "True" End If Next i Next lOriginRow The problem here seems to be with varCodes(i)=ActiveCell.Value - am I actually incrementing the row each time? Also Cells(lOriginRow, 13) doesn't seem to be the right way to reference it. Any help would be greatly appreciated. If anyone would like me to send them the sample spreadsheet with code to date, please mail me offlist and I'll send it on. Regards, and thanks in advance, Fiona -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#6
|
|||
|
|||
"Dave Peterson" wrote in message I don't see a difference between using this: If IsError(Application.Match(.Cells(iRow, _ OriginCell.Column).Value, varCodes, 0)) _ And Dim OriginColIndex as Long OriginColIndex = OriginCell.Column then If IsError(Application.Match(.Cells(iRow, _ OriginColIndex).Value, varCodes, 0)) _ I'd guess it was something else that was the problem. It probably was. I thought the first version should work as well, that's why I was asking. I didn't want to start my VBA self-teaching with a misconception that would make me use extra lines of code when it wasn't necessary :) Thanks, Fiona |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How long have macros been available in Excel? | Excel Discussion (Misc queries) | |||
Elapsed time Calc...over 30 days long... | Excel Discussion (Misc queries) | |||
How can I create a table of contents(worksheets) for a large work. | Excel Discussion (Misc queries) | |||
Long Text Entry | Excel Discussion (Misc queries) | |||
How long until 5:00? | Excel Discussion (Misc queries) |