1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Clearing Cell Contents Based off other Cell

Discussion in 'Business Applications' started by FLOVETT, Aug 19, 2014.

Thread Status:
Not open for further replies.
  1. FLOVETT

    FLOVETT Thread Starter

    Joined:
    Sep 17, 2007
    Messages:
    79
    First Name:
    Felton
    Hello,

    I am attempting to clear 2 cells, based off the value of another. I am pretty sure the code is correct, because it works within another Macro. Thanks.

    Code:
     Sub REMOVE()
    
    
    Dim p As Long
    p = Cells(Rows.Count, "a").End(xlUp).Row
    
    
    For i = 1 To p
    Range("k2").Select
    If InStr(1, Range("k" & p), "None") > 0 Then Range("L" & p) = "" And Range("M" & p) = ""
                               'If no Issue, Location/Obsevations  should be blank
    Next i
    
    
    End Sub
        
     

    Attached Files:

  2. XCubed

    XCubed

    Joined:
    Feb 21, 2013
    Messages:
    520
    hi


    try this variation;


    Sub REMOVE()

    Dim p As Long
    p = Cells(Rows.Count, "a").End(xlUp).Row

    For i = 1 To p
    If InStr(1, Range("k" & i), "None") > 0 Then
    Range("L" & i) = ""
    Range("M" & i) = ""
    End If
    'If no Issue, Location/Obsevations should be blank
    Next i

    End Sub
     
  3. FLOVETT

    FLOVETT Thread Starter

    Joined:
    Sep 17, 2007
    Messages:
    79
    First Name:
    Felton
    Thanks for the suggestion; howeve, the word 'None' appears to not be recognized. I had to use the offset function to make it work properly. I don' tknow why, but this works.. Thanks again for your assistance.

    Code:
    Range("k2").Select
            For i = 2 To p
                 If Selection.Value = "None" Then
            Selection.Offset(0, 1).Select       'Move to next column
            Selection.Value = ""                'Remove info
            Selection.Offset(0, 1).Select      'Move to next column
            Selection.Value = ""                'Remove info
            Selection.Offset(0, 1).Select       'Move to next column
            Selection.Value = " "               'Remove info'
            Selection.Offset(1, -3).Select      'Go to Next Record's Issue Type Column (down 1 row, back 3 columns) and select it
            Else
            Selection.Offset(1, 0).Select       'If "None" is not found, go down to the next record and select it
            End If
           Next i
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1131888

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice