Posts by Jaffey

  • List OLAP cube page fields, multiple items selected

    Re: List OLAP cube page fields, multiple items selected


    This is something I have been looking for as well but I get a 'Next without For' error when I try to run the code above. I also had to change :


    Code
    For i = 1 To PivotTables(1).PivotFields("Filter1").PivotItems.Count


    to

    Code
    For i = 1 To ActiveSheet.PivotTables("PivotTable1").PivotFields("Filter1").PivotItems.Count

  • split address into address/city/province/postal

    Re: split address into address/city/province/postal


    Thank you very much. I will try this tomorrow. I noticed the same thing about the missing cities. I grabbed some lists that I found on Wikipedia but obviously there are a lot more out there. I will keep searching. Thanks again

  • split address into address/city/province/postal

    I have a column containing addresses that I need to split into multiple columns.
    I have borrowed some formulas from other posts that almost do what I need but I don't know how to modify it further to split out the city into column F. Column E is currently showing both the address and the city.


    Current formulas:


    B2 =LEN(A2)
    C2 =SEARCH(" ON ",A2,1)
    D2 =C2+3
    E2 =LEFT(A2,C2-1) (is giving address plus city)
    F2 = (I don;t know what to put here)
    G2 =MID(A2,C2+1,2)
    H2 =MID(A2,D2+1,B2-D2)


    Note: I would prefer not to have to hard code the province abbreviation "ON" (for 'Ontario') as I have done in the C2 formula as some addresses will contain other abbreviations. The odd address will have the province spelled out 'Ontario' instead of ON, for example. however I clean those up with a search and replace first.



    Data sample
    82 Horsely Hill Dr Scarborough ON M1B 1W5
    4-46 ADVANCED RD ETOBICOKE ON M8Z 2T4
    25 Planchet Road Unit 4 Concord ON L4K 2C5
    41 Castle Dr Barrie ON L4N 1P9
    596 Oster Lane Concord ON L4K 2C1
    125 Anne St S Barrie ON L4N 7B6
    1 Laidlaw Blvd Unit 1 Markham ON L3P 1W5
    1360 Birchmount Rd Scarborough ON M1P 2E3
    155 Lynden Rd Brantford ON N3R 8A7
    5245 Harvester Rd Burlington ON L7L 5L4
    PO Box 696 Barrie ON L4M 4Y5
    110 Hopkins St , Whitby,ON L1N 2B7
    110 Anne St S Unit 15 Barrie ON L4N 2E3
    9 WOODS AVENUE RR OMMEME ON K0L 2W0
    3437 CRESCENT HARBOUR RD INNISFIL ON L9S 2Y7
    240 Brock St Peterborough ON K9H 2P7
    2565 Kingsway,Sudbury ,ON P3B 2G1
    30 Lawson Cr. Rosseau ON P0C 1J0


    see attached


    'forum.ozgrid.com/index.php?attachment/38553/

  • import multiple csv files with header record and addresses with carriage returns

    I have approx. 5000 csv files to import onto a single page.
    They all have the same format.
    When I open the files with excel the data parses properly but the titles don't with the result that the last column of data has no header. If you open one of the files you will see what I mean.
    When I tried to record a macro of the import it did not work because there are carriage returns in the last column of data which causes the data to wrap around so the columns don't line up and I don't know how to get around that using the import tool.
    The following code I found in another thread does much of what I want but needs adjusting as the records end up split over two rows so it needs to be adjusted.

  • Import and parse csv files with extraneous commas?

    I have a lot of csv files to import. When I double click on any one of them manually using explorer excel opens them perfectly. However, when I loop through the same files using a script that incorporates a parsing array I recorded with excel's import tool, any file where one of the 'columns' has extra commas in it excel recognizes them and splits the data for that row into additional columns. For example, one column is for 'company name' and many companies have commas in their name. Can anybody tell me if it possible in VB to have excel open the files the same way it does natively as opposed to the hard coded array method created by the import tool?

  • Reset agent's status on a master list while looping through daily refresh list

    Re: Reset agent's status on a master list while looping through daily refresh list


    Hi Bill, I've been able to determine that the initial size of the 'from' range determines the maximum number of 'new licence' records that can be generated. Subsequent loads/refreshes of the 'from' page with new data will not generate 'new licence' records. Instead it will generate 2 new rows for each new record and flag them as "new name" and "new agency" respectively. That's all I've been able to figure out so far.

  • Reset agent's status on a master list while looping through daily refresh list

    Re: Reset agent's status on a master list while looping through daily refresh list


    I'm getting some strange results when I run it with real data (about 4500 rows). Ex. new records are being added but on subsequent loads without 'new licence' and instead are showing up twice with 'new name' and 'new agency'. I tried to determine if it was the number of records that was causing the problem but if it is, I don't see where. I did notice the A2:F500 range you are using on the hidden sheet in conjunction with the user form/controls but it had no effect when I increased it. Should the number of records make a difference?
    forum.ozgrid.com/index.php?attachment/36531/

  • Reset agent's status on a master list while looping through daily refresh list

    [I tried to think of a more succinct thread title but I couldn't so I phrased it as a question; (How do I) reset an agent's status on a master list while looping through the refresh list]


    I am creating a spreadsheet to track agents and where they are licensed. Each day I receive a file containing the names and licence numbers of all licensed agents including the name of the agency they are licensed at. Agents tend to move around and/or drop out of action for a while and then resurface at a different (or same) agency. My goal is to create a report detailing which agencies are gaining/shedding agents while detailing who moved where etc. The daily file I receive contains the following 4 columns on the FROM tab;


    Col A = Lic#
    Col B = AgentName
    Col C = Agency
    Col D = ReportDt


    Which I use to update the master list on the TO tab containing;


    Col A = Lic#
    Col B = Agent
    Col C = OrigAgency
    Col D = DateAdded
    Col E = Mode
    Col F = Updated
    Col G = CurrAgency
    Col H = Status
    Col I = EventDt


    The logic for the updating is;


    If Lic# does not exist on master list (TO tab);
    1) Add Lic# to master list (create new row), along with the Agent’s name, Agency and ReportDt in Columns A,B,C,D, where;
    A=Lic#, B=Agent, C=OrigAgency, D= DateAdded
    2) Set Mode to “Licenced”. Column E
    3) Copy ReportDt to “Updated”. Column F
    4) Copy Agency to “CurrAgency”. Column G
    5) Set Status to “NewLic”. Column H
    6) Copy ReportDt to “EventDt”. Column I


    If Lic# already exists on master list (TO tab);
    1) Check if Status = NewLic, if yes, set Status = Confirmed, copy ReportDt to “EventDt”.
    2) Check if Status = Dropped, if yes, set Status = ReLicenced, copy ReportDt to “EventDt”.
    3) Check if Status = Moved, if yes, set Status = Confirmed, copy ReportDt to “EventDt”.
    4) Check if Status = ReLicenced, if yes, set Status = Confirmed, copy ReportDt to “EventDt”.
    5) Check if Agency name has changed, if yes, set Status = MOVED, copy ReportDt to “EventDt”.


    If Lic# exists on master list but no longer appears on FROM tab;
    1) Set Status to Dropped. Column F
    2) Set Mode to Unlicensed. Column E.


    The code below handles most of the above except agents who transfer agencies (see PART E). The first time an agent moves their status should be changed to Moved. The next time they are reported at that same agency their status should change to Confirmed. It gets a little more complicated when they stop working (become UnLicensed) and then start working again at a new agency. My current code flags them as Moved when it should be ReLicensed. With my current code once an agent is flagged Moved, they stay that way. I have tried using various AND and ELSE combinations unsuccessfully. I am a VBA hack and was only able to develop what I have so far with help from other Oz members but I have hit a wall again and would very much appreciate further assistance. (most recent post was ‘Combine two macros and consolidate changes between new data and master list’; http://www.ozgrid.com/forum/showthread.php?t=149738)


    I have attached a sample spreadsheet. Please click the 'Edit macro" to view the code. forum.ozgrid.com/index.php?attachment/36506/

  • Run macro when double-clicking a sheet tab

    Re: Run macro when double-clicking a sheet tab


    Another too simple solution - just tie your macro to the 'before right-click' event. That way your users just have to right click ever so slightly above the tab to make it work. You could also tie it to the double-click event of course but that can be dangerous depending on what's in the cell they are clicking on. I suspect you may have thought of that but I just thought I'd throw it out there for anyone else following this thread.

  • Run macro when double-clicking a sheet tab

    Re: Run macro when double-clicking a sheet tab


    I can relate to your frustration. My original goal was to make buttons that 'float' down the spreadsheet the way they do on some websites as you scroll down (which I actually find rather annoying now) but that was a verry complex solution so I settled for locking them to the top row and making them semi-translucent. At 5-6 lines of code and something that can be easily replicated&modified it's a winner. And it's still very user friendly imo as people naturally tend to hover their mouse near the top of the screen. Good luck though!


    I do have one other suggestion for you though, since double clicking a tab currently selects/highlights the name of the tab; simply create a macro that fires on a change in the tab name (after capturing the name of the tab of course so you can cange it back at the end)

  • Run macro when double-clicking a sheet tab

    Re: Run macro when double-clicking a sheet tab


    You could make a macro to create a small button or two at the top of the page in row one, then turn freeze panes on row one so the buttons are always visible no matter how many pages they scroll down. By tying this macro to the create new sheet event the buttons will appear on every new sheet in your workbook so they are always useable. I use this trick all the time and it works great. On many projects I give users a virtual dashboard of buttons to choose from in row 1 and they love it.


    Here is an example of a simple macro to create two buttons at the top of the page.

    Code
    Range("A2").Select
      ActiveWindow.FreezePanes = True 
   
      ActiveSheet.Buttons.Add(290, 0, 10, 13).Select ‘290 =dist from left margin, 0 is how far down it appears and 10,13 are height/width
      Selection.OnAction = "PivotPageDown" ‘ this links the button to the macro you want to run
      Selection.Characters.Text = "<" ‘set display text for the button here
   
      ActiveSheet.Buttons.Add(299, 0, 10, 13).Select 
      Selection.OnAction = "PivotPageUp" 
      Selection.Characters.Text = ">"



    The result is two small buttons locked to the top of the screen that look like “<|>” and the user (in this case) can click one or the other to go forward or back through a pivot table one ‘page’ at a time. (using some wonderful code I found for linking a button to pivot filter control at http://www.contextures.com/excelfiles.html ) Of course you can tie the buttons to any macro you want just by inserting the name of it between the quotes after Selection.OnAction =

  • Range changing error with pivot table

    Re: Range changing error with pivot table


    You don't mention if you're using a dynamic named range for the data in your pivot table but it may solve your problem. I don't recall the exact menu item to find it under but look for 'insert range' or 'inset named range' under the tools menu and create a new named range, give it whatever name you want and in the "refers to:" field enter the following;

    Code
    =OFFSET(DATA!$A$1,0,0,COUNTA(DATA!$A:$A),COUNTA(DATA!$1:$1))


    replace DATA with the name of the tab your data is on. Then go to your pivot table options and change the data range to the name you selected for your dynamic range. If you can find it, right click on your pivot, select pivot table wizard and keep hitting the back button until you see it. The dynamic range should automatically adjust to any new columns you add/delete. Good luck.

  • Combine two macros and collate changes between new data and master list

    Re: Combine two macros and collate changes between new data and master list


    I'm getting an "application defined or object defined error" on the following line;

    Code
    MyRngeLstCln = Sheets("from").Range("A1").End(xlRight).Column


    even though MyRngeLstCln is declared above it

    Code
    Dim MyCounter, i, x, MyRngeLstRw, MyNewRngeLstRw, MyNewRngeLstCln, MyRngeLstCln As Integer


    Here is how the whole thinkg looks after I inserted your last post: