Results 1 to 2 of 2

Thread: modify data based on range
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Feb 2013
    Posts
    382
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default modify data based on range

    i want a macro that loops from column K in sheet I'll call "ABC" and checks the values against a data range called "filter" (which is in another sheet called "filter criteria")

    if it finds a row in column K that's also found in the data range "filter" (eg the number 005), then it'll zero out the value in column J of sheet "abc," highlight the cell zeroed out cell in yellow, and in column M to display "new issue"


    here's my code right now, but i prefer the data range to be on the spreadsheet itself as it can change from time to time (dont want to go into the code and modify it)

    Code:
    'zeros out new issue values
    
    
    For i = 3 To finalrow
    
    
    If Range("K" & i).Value = "005" Or Range("K" & i).Value = "006" Or Range("K" & i).Value = "044" Or Range("K" & i).Value = "046" Or Range("K" & i).Value = "122" Or Range("K" & i).Value = "170" Or Range("K" & i).Value = "171" Or Range("K" & i).Value = "172" Or Range("K" & i).Value = "173" Or Range("K" & i).Value = "174" Or Range("K" & i).Value = "175" Or Range("K" & i).Value = "176" Or Range("K" & i).Value = "177" Or Range("K" & i).Value = "178" Or Range("K" & i).Value = "179" Or Range("K" & i).Value = "180" Or Range("K" & i).Value = "181" Or Range("K" & i).Value = "182" Or Range("K" & i).Value = "183" Or Range("K" & i).Value = "190" Or Range("K" & i).Value = "191" Or Range("K" & i).Value = "192" Or Range("K" & i).Value = "193" Or Range("K" & i).Value = "194" Or Range("K" & i).Value = "226" Then
    
    
    'zero out  mv
    Range("j" & i) = 0
    
    
    'highlight the 0
    
    
    Range("j" & i).Interior.Color = 65535
    
    
    'put new issue in column M
    Range("m" & i) = "New Issue"
    
    
    End If
    
    
    Next

  2. #2
    Board Regular Rijnsent's Avatar
    Join Date
    Oct 2005
    Location
    Utrecht, Holland
    Posts
    1,081
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: modify data based on range

    Hi Daveyc18,
    try something like this, it's basically a loop in a loop. More about that, check out this VBA course chapter - https://www.excel-pratique.com/en/vb..._continued.php
    Cheers,
    Koen
    Code:
    Sub ZeroOut()
    'zeros out new issue values
    
    'Set variables
    Set ShtABC = Worksheets("ABC")
    FilterValues = Range("Filter").Value
    Set DataRngStart = ShtABC.Range("K3")
    Set DataRngEnd = ShtABC.Range("K3").End(xlDown)
    
    'Loop through values
    For i = DataRngStart.Row To DataRngEnd.Row
        
        'For each value, loop through filter
        FilterFound = False
        For Each f In FilterValues
            If DataRngStart.Offset(i - DataRngStart.Row, 0).Value = f Then
                'Filter found, exit loop
                FilterFound = True
                Exit For
            End If
        Next f
        
        If FilterFound = True Then
            'zero out  mv
            ShtABC.Range("j" & i) = 0
            'highlight the 0
            ShtABC.Range("j" & i).Interior.Color = 65535
            'put new issue in column M
            ShtABC.Range("m" & i) = "New Issue"
        End If
        
    Next
    
    'Clean set variables
    Set ShtABC = Nothing
    
    End Sub
    You can't post attachments here, but you can help me helping you by posting a screen shot directly in your post with any of those tools.
    Otherwise use dropbox/google drive/etc to get your file accross (not preferred). For code, put it inside these tags: [ CODE][/CODE]. Do check the forum rules.
    Finally, please show that you made an effort to solve your problem: Yes, I like to help, but am not going to do your job.

Some videos you may like

User Tag List

Tags for this Thread

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
  •