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


    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;


    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.


    Re: Clear Cells Of Values Based On Cell Selection


    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.


    Re: Listbox To Filter Pivot Items

    Hi Trundle,

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

    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.


    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,

    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...


    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 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.


    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.

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


    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?


    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,

    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,

    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,

    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?

    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,

    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;

    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;

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

    Hope that helps,