Posts by IanDunnett

    Re: Find Blank Cell Then Input Then Input To Cell Beside


    This should get you most of the way there....



    Hope that helps


    Cheers,
    Ian

    Re: Automatically Copying Upcoming Dates Into New Spreadsheet


    I've done something similar before.


    Normally I just use conditional formatting to turn the cell or font red when the launch date is within a certain timeframe.


    If the date is in cell F10 and you want to highlight a date within the next 30 days you could usesomething like;


    =(F10-NOW())<30


    Set this up in one cell then just copy the formatting to the rest of the column.


    Then you can use something like the below, to scan through all the rows in your source sheet and map them across to a new sheet if they meet your criteria



    Hope that's clear and helps a little.


    Ian

    Re: Clear Cells Of Values Based On Cell Selection


    Hi,


    You need a worksheet change event that only executes if certain cells are selected.


    Something like the belwo (although you will need to unprotect and then reprotect the sheet with your own password)



    It's crude as it stands, you may have to generalise the code to work in your complete workbook.


    Cheers,
    Ian

    Re: Listbox To Filter Pivot Items


    Hi Trundle,


    I've made a start that should get you most of the way there.


    Assumptions;
    You need a command button called FilterPivots to fire the userform.
    You need a userform called BusFilter with a listbox on called BusLst (with multiselect turned on) and a command button called OKBtn


    Here's the code;


    and here's the code for the user form (just to make sure at least one business is selected)


    You might need to make the following changes to get this to work in your workbook;
    Change the additems "0", "1) etc. to be the names of the business a they appear in the workbook (case sensitive)
    Change where it says "Data2" when it is turning the filters on/off to say whatever the title of the data item containing your businesses names is according to your pivot tables.



    Hope this helps,
    PM me you e-mail address if you want to seeit working as it's too large to post on here.


    Ian

    Re: Change Color In Specific Columns


    Hi Kitvel,


    This should do what you need;


    I've also compresseed the second half of the code as I think all you are trying to achieve is to put today's date in the same row in column 'M'.



    Hope that helps,
    Ian

    Re: Listbox To Filter Pivot Items


    Hi there,


    This sounds pretty simple, but please can I just clarify the request...


    You want a listbox to then filter pivot tables to just the items selected?


    A couple of questions...
    Is the listbox in a userform or on a worksheet?
    Are the pivot tables to be filtered all the pivot tables in the workbook or just a certain set of them?
    Are all the pivot tables on the same sheet or different ones?


    Should be quite straightforward to fix this...


    Cheers,
    Ian

    Re: Code For Separating Data Types


    This is a simple solution that should solve your problem...


    It does rely on you having two further sheets in your workbook...one called Rows of 14 and one called Rows of 20...


    The code simply scans down counting how may rows it is until it finds a gap, then copies the data to the relevant sheet...




    Hope thsat helps, any probelms, let me know.


    Cheers,
    Ian

    Re: Find Function To Search On Hidden Rows


    Hi Andy,


    it's pretty simple really...


    AcRng in dim'd as a range and AcNum is dim'd as as integer with some checks done to make sure it's a number.


    Column B on the sheet "Action Log" has numbers in it but some of the rows are hidden.
    I am finding that if the row with the number I'm looking for is hidden, AcRng is retunred as Nothing.


    Code
    Set AcRng = Sheets("Action Log").Columns(2).Find(AcNum, lookat:=xlWhole, LookIn:=xlValues, _
    after:=Sheets("Action Log").Cells(8, 2))


    Thanks,
    Ian

    Hi Guys, hope this should be a quick one,


    I'm using the Find function in VBA on a column of data, but the range is being set to Nothing if the data item I'm looking for happens to be in a row that is hidden at the time.


    How do I set the find to look in all rows, hidden or not?
    Can this be done without unhiding all the rows first?


    Thanks,
    Ian

    Re: Retrieve E-mail Addresses From Sent Items


    Sorry Dave.


    I've got a small problem.


    My code relies on the .SenderEmailAddress property but I have tried to run this on another person's computer (and it may need to be run on many more) who is on an older version of Office and the object library doesn't have this property for eacgh mailitem.


    Is there a way with the v10 object library to retrieve a sender's e-mail address (not just their name) from inbox items?


    Many thanks,
    Ian[hr]*[/hr] Auto Merged Post;[dl]*[/dl]Nevermind, I fixed it, although I possibly went the long way around it.


    I created a reply e-mail and just grabbed the address of the recipient, then deleted it again.


    Here's the final code for reference;



    The row deletion is in there because it needs to eliminate any addresses that are internal to the company.


    Meery Xmas everybody,
    Ian

    Hi Guys 'n' Gals,


    I'm trying to generate a list of addresses that e-mails have been sent to out of a particular outlook account and then create a unique list of these in Excel.


    I can recall the 'To' string for all the e-mails but sometimes this is a short name version of an address assigned by outlook rather than the propoer [email protected] version.


    Any idea how this list can be retrieved? This needs to work when the e-mail was sent to multiple recipients as well.


    festive tidings,
    Ian

    Re: Filter Or Highlight Changed Data Between Two Workbooks


    Can you gurantee that the rows are in the same order or that there are the same number of total rows in both spreadsheets?


    Also can you guarantee that for multiple rows under the same identifier that the rows for comparison will be in the same order?


    Hope I can help,
    Ian

    Re: Visibility Based On Value Of Multiple Column ListBox


    Dave - doing it your way, if I am loo'ping for a, would make the box visible if column 4 had the right string in it, but then not visible for the next a if that column 4 didn't have the wantedstring in it, which is why I used an 'IF'.


    Doing it your way (unless I've misunderstood) would result in the CommandButton showing only if the last row in the list box had the wanted string in column 4 whereas jamierodgers has asked for the button to be vivsible if the string appears in any row of column 4.


    Did I misunderstand your post?
    Ian

    Re: Command Button Visibility Based On Value Of Multiple Column ListBox


    I'd just have something like the below on the listbox change event


    Assuming your userform is called UserForm1, your Command Button to be hidden is called CommandButton1 and the List Box is called ListBox1;



    Hope that's some use,
    Ian

    Re: Vba Decisions To Skip Columns


    try something like;


    Re: File Open Box While Importing External Data


    When I need to grab a filename I use the GetSaveAsFile method and use the dialog to steer to a filepath. something like;


    Code
    Dim WantedFile As String
    WantedFile = Application.GetSaveAsFilename("", , , "Choose File")


    This way you then have the full filepath in a stored string and can use it to do whatever you want.


    If you just need the filename and not the full path, add;

    Code
    WantedFile = Right(WantedFile, Len(WantedFile) - InStrRev(WantedFile, "\"))


    Hope that helps,
    Ian