Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Filter list then change cell value in results

  1. #1
    Board Regular StuLux's Avatar
    Join Date
    Sep 2005
    Location
    Cardiff, UK
    Posts
    579
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Filter list then change cell value in results

    I am trying to filter a list of records by the value in a cell and then (for the filtered result(s)) change the value in column Y just for the filtered results. This is the code I have at present which filters the records OK but I can't fathom what I need to do to ensure I only chnage the value of the cell(s) in column Y?

    [CODE]ActiveSheet.Range("$A$1:$DC$5000").AutoFilter Field:=1, Criteria1:="GI255"
    'After filtering I want to change the value in column Y for only the filtered entriesCODE]
    Stuart
    "It's 5 o'clock somewhere"

    XP Pro (SP 3)/Excel 2007 (work) and Windows 8/Excel 2013 (home)

  2. #2
    Board Regular
    Join Date
    Jun 2005
    Location
    London
    Posts
    7,698
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Filter list then change cell value in results

    Code:
    ActiveSheet.Range("$A$1:$DC$5000").AutoFilter Field:=1, Criteria1:="GI255"
    Range("Y2:Y5000").SpecialCells(xlCellTypeVisible).Value = "Your value here"
    Neil

  3. #3
    Board Regular StuLux's Avatar
    Join Date
    Sep 2005
    Location
    Cardiff, UK
    Posts
    579
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Filter list then change cell value in results

    That works great thanks - initially it was changing some blank cells as well so I changed the range to ensure it only went to the last record using a variable ctRAG. I'm now having a problem when there is no valid record to be filtered - the code in red is my attempt to check whether there are any visible cells but this doesn't work - any suggestions?

    Code:
    Sheets("CMT_RAGs").Select
    ActiveSheet.ShowAllData
    ActiveSheet.Range("$A$1:$V$186").AutoFilter Field:=2, Criteria1:=ProjPRN
    If Range("V2:V" & ctRAG) Is Not Null Then
    Range("V2:V" & ctRAG).SpecialCells(xlCellTypeVisible).Value = "CONFIDENTIAL PROJECT"
    End If
    Stuart
    "It's 5 o'clock somewhere"

    XP Pro (SP 3)/Excel 2007 (work) and Windows 8/Excel 2013 (home)

  4. #4
    Board Regular
    Join Date
    Jun 2005
    Location
    London
    Posts
    7,698
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Filter list then change cell value in results

    Try changing "Null" to "Nothing"
    Neil

  5. #5
    Board Regular StuLux's Avatar
    Join Date
    Sep 2005
    Location
    Cardiff, UK
    Posts
    579
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Filter list then change cell value in results

    Worked a treat, thanks.
    Stuart
    "It's 5 o'clock somewhere"

    XP Pro (SP 3)/Excel 2007 (work) and Windows 8/Excel 2013 (home)

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •