Posts by Jaffey

  • VBA Code To Convert Dates

    I received a solution to a question recently whereby I was instructed to use the text-to columns function to solve a problem I was having with a column of dates.


    http://www.ozgrid.com/forum/showthread.php?t=142147


    The solution provided was;
    Select Data > Text to Columns > Next > Next > Column Data Format > Date.


    The solution works fine when performed manually but if you perform this function while recording a macro, the crucial piece that changes the date output is not captured by the recorder so when you run the macro nothing happens.



    To expand on how I recorded it, after starting the text to column dialogue and pressing Next twice, the dialog provides me with a drop down selector where I am able to change the date format from the default DMY to the desired MDY which - when selected manually - fixes my problem. However, when I record a macro while performing this action the date format choice I made is not being recorded in the macro output so when I try running the macro later on the original data, nothing happens.


    The recorded macro ends up looking like this;


    Code
    Columns("C:C").TextToColumns Destination:= Range("C1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False,  _
FieldInfo:= Array(1, 3), TrailingMinusNumbers:=True


    Is there a way to add a piece of code to this routine so that it will change the output the same as when it is done manually? Please note that when the text to column function is run manually in this fashion it actually changes (rearranges) the data in the selected column. I.e. it is not a matter of changing the formatting.

  • Formula To Check Length Of Corresponding Cells & Return 1st 4 Digit Number

    Attached is a sample of raw call data from our phone switch.


    My problem is determining which agent to attribute the calls to.


    There are approx. 16 agents and they all have 2 or more extensions on their phone.


    Here are the rules for determining who gets credit for the call as best as I can determine;


    If Column E (Calling Party) is a 4 digit extension (with or without an asterisk prefix), that is the extension.


    If Column K (Called Party) is a 4 digit extension (with or without an asterisk prefix), then that is the extension.


    Finally, if Column M (Third Party) is a 4 digit extension (with or without an asterisk prefix), then that is the extension.


    In column Y I have entered a formula that works most of the time for the first 2 columns but I have no luck incorporating the 3rd.


    Additional Info


    Most phone switches make it easier to figure this out by dedicating one column for the ext. and another column for the direction of the call. (In /Out)


    The manufacturer of this switch does it differently and there is no dedicated column to specify the direction of the call, instead;
    1. If the call is an inbound call the name of the trunk the call came in on appears in Column E (Calling Party). Ex. T1, T12 etc. and the ext. that received the call will appear in Column K (Called Party)
    2. If the call is an outbound call, the name of the trunk the call came in on appears in Column K (Called Party). Ex. T1, T12 etc. and the ext. that made the call will appear in Column E (Calling Party)
    3. If an agent transfers or conferences a call, the ext. of the person receiving the call appears in Column K (Third Party)


    What I would like is to have is the extension that either made, received or was transferred the call appear in one column so that I can make pivot tables and reports more easily.


    Any help is greatly appreciated!

  • Convert Dates To Another Format

    I apologize in advance if my title is not up to par but I don't know how else to state my problem without breaking some other rules.


    I have attached a sample of the raw output of some call records from our telephone switch.


    My problem with the date data is twofold; first of all it outputs it in mm/dd/yyyy format, or so it appears. But when I try to change the format to mmm/ddd/yy Excel always interprets the 2nd pair of digits as the month instead of the first two. For example,the following 2 calls are from Jan 1st and Jan 2nd respectively;


    01/01/2010
    01/02/2010


    but excel always treats the 2nd record as Feb 1st. I have read numerous posts from people with similar problems but I can't get any of their solutions to work with my data.


    The second problem is that on the 13 of each month the phone switch suddenly stops putting a leading zero in front of the date making the data totally unreadable to Excel apparently. Again, I read a post of a similar problem but can't get their solution to work.


    My ultimate goal is to reformat the date data so that it is displayed as;
    ddd mm/dd and leave out the year entirely. Ex. Fri 02/12 as in today...Friday, Feb 12th.


    I really don't know if the problem is with the data or me at this point. Any help greatly appreciated!

  • Copy Same Sheet From Multiple Workbooks Into New File

    Re: Copy Same Sheet From Multiple Workbooks Into New File


    It's combining the sheets but not renaming them. It also crashes when encountering a workbook that has over 31 chars in cell A1 on the Template tab. The odd thing is that's it's not renaming the tabs but leaving them as "Template(1)", "Template(2)" even though I know its reading the cell contents for the names because of the crashing. I suspect the problem is in the 2nd half of this line but I can't get it to work.

    Code
    .Sheets(.Sheets.Count).Name = .Sheets(.Sheets.Count).Cells(1, 1)


    I observed that if I commented out that line it would still run but the values in cell A1 could be as long as they were before I shortened them....so I know it's reading the names in cell A1 but not using them to rename the tabs even though you're telling it to(?)

  • Closing Internet Explorer

    Re: Closing Internet Explorer


    Hi Captbobb, would closing the browser be sufficient or are you trying to logout? kill a tcp/ip connection? And things usually progress a lot faster if you insert the code you already have. cheers

  • Hide Rows Based On Range Values

    Greetings Oz! It's been a while. The site is looking good and I must say I feel a little less guilty asking for your help now that I'm paying for it :)


    Now for my problem;
    I have a table of data containing statistics on approx. 10,000 call centre agents that is refreshed each day - 1 agent per row. Each agent has two extensions. (Ext & Ext2)
    On a second tab I have a list of ~100 agents that I have to report on daily with their name in column A, Ext1 in column B and Ext2 in column C.
    Based on the extensions on the second tab I would like to hide the 9900 rows of data in the table I don't need to see. I have attached a sample of what the data looks like.
    If necessary, I could easily combine the two columns of extensions into one column if using two is problematic.

  • Alternate Button Macro & Caption Between 2

    I have a worksheet with 2 buttons labelled "Hide" and "Show". As the names imply, they allow the user to hide or show parts of the worksheet. I would like to combine them into one button and have the button label and the associated macro change with each press of the button. Here is what I have so far;



    These macros change the label fine after hiding or unhding the rows but I can't find the proper terms to use to change the macro associated with the button (if there is one?)
    As always, your help is greatly appreciated!

  • Decimal Places For Close Ties Of Numbers Based On Cell Value

    Re: Format Decimal Places Based On Cell Value


    Thank you for responding SHG! Unfortunately I must not have explained my problem well enough. Here is an example of the data I receive;


    Agency A 95.26%
    Agency B 84.52%
    Agency C 84.36%
    Agency D 75.02%
    Agency E 64.23%
    Agency F 64.24%


    And here is how it is supposed to be formatted before distribution;


    Agency A 95%
    Agency B 84.5%
    Agency C 84.4%
    Agency D 75%
    Agency E 64.23%
    Agency F 64.24%


    Agency A is the clear winner so no decimals are required.
    Agencies B and C are within a 10th of a percentage % point of each other so 1 decimal place is sufficent to differentiate them. Agencies E and F are within 100'ths of a percent of each other so two decimal places are required to see who is ahead. I have to adjust the formatting on these cells manually on a daily basis so I'm hoping there's a way to automate it. I typically just select the range, then hide the decimals using the buttons on the toolbar, then add decimals back wherever two agencies look tied. It's easy but after 2 years it's become become very tiresome!

  • Decimal Places For Close Ties Of Numbers Based On Cell Value

    I have a pivot table of values displaying agency's performance based on their sales percentage to target. When the race is 'loose', and all companies are 1 or more percentage point away from each other, I format the values without decimal places. If two agencies appear tied, I add a decimal place to seperate them. Occasionally the race is really tight and I have to increase the format to two decimal places to seperate them. Does anyone have a suggestion on how to do this automatically? Is it even possible? I've searched the formums but no luck so far.

  • Count Time Differences Over X In Cells With Times

    I receive approx. 50,000 call records per day generated by agents working in a busy call centre. I use pivot tables to make a variety of reports but a recent request has me stumped. The queston is how to count the number of times an agent went for 10 minutes or longer without making or taking a call. Internal (inter office) calls don't count. I was also asked if it would be possible to show "Agent X went without making a call for 10 minutes 3x and for 1/2 an hour once". I have enclosed a sample of the data I have to work with. Any tips would be appreciated. I'm usually pretty good at figuring things out with the help files but in this case I don't even know where to start or what I should be searching for![hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]here is the file...

  • Copy Formulas Down / Across To Last Used Row & Column

    Greetings Wizards of Oz! I have a workbook with financial data on two sheets in columns A - G on each sheet and formulas in columns H through P on both sheets which compares the data on the two sheets. What I would like is for the formulas to autofill down the same number of rows as there is data on each sheet. I currently have the formulas copying down 500 rows using the following macro but I'd like to do it properly.



    I experimented using FillDown from something I found in another post and modified it successfully - or so I thought - this script runs fine on one sheet but not the other. I get "Selection too big" when I run it on the second sheet. I think it has something to do with excel "remembering" that at one time I had data in all 65536 rows but I don't know what to do about it.

    Code
    Sub Copy_Formulas_Down()
    Range("H2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, ActiveCell.Offset(0, -1).End(xlDown).Offset(0, 1)).FillDown
    Range("H2").Select
                
    End Sub


    These are the formulas I'm copying down from the CLIENTdata sheet. The formulas on the TCRdata sheet are essentially the same

    Code
    =IF(B2<>"",+COUNTIF($B$2:$B$500,B2),0)
=IF(B2<>"",+COUNTIF(TCRdata!F:F,SUBSTITUTE(B2," ","")))
=SUBSTITUTE(B2," ","")&"#"&E2&"#"&F2
=ISNUMBER(MATCH(B2&"#"&E2&"#"&F2,TCRdata!J:J,0))
=MATCH(B2&"#"&E2&"#"&F2,TCRdata!J:J,0)
=INDEX(TCRdata!$C$1:$C$500,+MATCH(SUBSTITUTE(B2," ","")&"#"&E2,TCRdata!K:K,0))
=INDEX(TCRdata!$D$1:$D$500,+MATCH(SUBSTITUTE(B2," ","")&"#"&E2&"#"&F2,TCRdata!J:J,0))
=IF(ISNA(O2),IF(ISNA(N2),"No Match","COMM No match"),"matches")


    Any help is much appreciated! I've been going through posts for 2 hours now but I can't quite get it.

  • Move Pivot Table Total To Left Hand Side

    Re: Move Pivot Table Total To Left Hand Side


    I use GetPivotData as well...I was just hoping there was some way to reconfigure the default layout of the actual table...and I figured if anybody knew how to do it they'd have to live in Oz!