none
Using VBA in Excel, select a single column in an AutoFiltered range to enter a single value in each cell RRS feed

  • Question

  • Hi all,

    I've been struggling to find the solution for what I'm trying to do.

    I'm looking to run a macro in Excel 2003 that will filter my range of data, extract this data, and then update each visible cell in a single column so I know in future that this data has already been extracted.

    Background info - Billing a customer for re-work done which cannot be put through our system currently. The data is constantly udpated daily with invoice numbers etc, and each month a bill is produced at the end of it. Hence why I want to update a single column with the month each visible line has been billed. Each line will remain of the sheet for auditing purposes and will not be deleted after extraction.

    So far I have managed to filter and extract the data using the macro, but updating the visible cells in a single column I have not been able to do.

    The code I have written is as follows but this selects all of column DZ and not the visible cells in the Autofilter range which is what I require;

        Sheets("Input List").Select
        ActiveSheet.Range("$A$22:$EA$3024").AutoFilter Field:=9, Criteria1:= _
            "=100% recharge", Operator:=xlOr, Criteria2:= _
            "=Margin only recharge"
        ActiveSheet.Range("$A$22:$EA$3024").AutoFilter Field:=129, Criteria1:="<>"
        ActiveSheet.Range("$A$22:$EA$3024").AutoFilter Field:=130, Criteria1:="="
        Columns(130).SpecialCells(xlCellTypeVisible).Select

    Any help would be greatly appreciated.

    Thanks, Dean.

    Thursday, June 14, 2012 10:36 AM

Answers

  • I have been able to work to a solution using new worksheets and lookups.

    Same macro, and not as tidy, but I got the result I wanted in the end.

    Thanks anyway!

    • Marked as answer by DA74 Friday, June 15, 2012 9:24 AM
    Friday, June 15, 2012 9:24 AM

All replies

  • You will probably have more luck scanning through each line and then deciding what you want to do on each. Then just hide each row instead of using filters.
    Thursday, June 14, 2012 11:22 AM
  • Hi Derek,

    Unfortunately, I have approx 3000 lines of data which would be very time consuming.

    I can do it manually, copy and pasting to the filtered cells but ideally I want a macro to do it.

    That way, I can pass this to delegates to run as they are not very literate on Excel and I fear a mistake being made and things not being billed.

    Thanks again.

    Thursday, June 14, 2012 11:28 AM
  • Dean,

    This is the forum for VB in Visual Studio Net  not the VBA forum, try this one.

    http://social.msdn.microsoft.com/Forums/en-US/isvvba/threads


     


    Success
    Cor

    Thursday, June 14, 2012 11:33 AM
  • Apologies Cor.

    Is it possible to re-assign the question to a different forum? Or will I have to repost?

    Thursday, June 14, 2012 11:37 AM
  •       Here's a VBA forum but I don't know how to re-assign the post to a new thread.

    http://social.msdn.microsoft.com/Forums/en-US/isvvba/threads


    You've taught me everything I know but not everything you know.

    Thursday, June 14, 2012 11:43 AM
  • Hi DA74,

    Welcome to the MSND forum.

    I have moved your thread to VBA forum.

    Sorry for any incontinences and have a nice day.


    Mark Liu-lxf [MSFT]
    MSDN Community Support | Feedback to us

    Friday, June 15, 2012 7:44 AM
    Moderator
  • I have been able to work to a solution using new worksheets and lookups.

    Same macro, and not as tidy, but I got the result I wanted in the end.

    Thanks anyway!

    • Marked as answer by DA74 Friday, June 15, 2012 9:24 AM
    Friday, June 15, 2012 9:24 AM