Results 1 to 3 of 3

Thread: Macro To Update Data in Cell Based On Value In Adjacent Cell

  1. #1
    Senior Member
    Join Date
    Apr 2012
    Posts
    193
    Rep Power
    12

    Macro To Update Data in Cell Based On Value In Adjacent Cell

    Where corporate signage and Generator appears in Col A, I would like the narrarive in Col E that appears in the same row as these two items to be changed to ADMINISTRATION. When activating the Macro only the narrative In Col E pertaining to Corporate Signage in Col A is changed to ADMINISTRATION

    Your assistance in resolving this is most appreciated


    Code:
    Sub ChangeTextInColumnE()
            Const sA As String = "Corporate Signage"
             Const sB As String = "Generator"
                    
            Const sE As String = "Administration"
            Const sF As String = "Administration"
            Dim lr As Long, r As Range, vA As Variant, i As Long
            lr = Range("A" & Rows.Count).End(xlUp).Row
            Set r = Range("A2", "E" & lr)
            vA = r.Value
            Application.ScreenUpdating = False
            For i = LBound(vA, 1) To UBound(vA, 1)
                If UCase(vA(i, 1)) = UCase(sA) Then r.Cells(i, 5) = sE
                 If UCase(vA(i, 1)) = UCase(sB) Then r.Cells(i, 5) = sF
            Next i
            End Sub
    Last edited by Excel Fox; 05-30-2013 at 03:53 PM. Reason: Code Tags Added

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    Please used code tags to wrap codes. I have added it for you this time.

    Don't see any apparent issues, except maybe for trimming the values. try this

    Code:
    Sub ChangeTextInColumnE()
    
        Const sA As String = "Corporate Signage"
        Const sB As String = "Generator"
                
        Const sE As String = "Administration"
        Const sF As String = "Administration"
        Dim lr As Long, r As Range, vA As Variant, i As Long
        lr = Range("A" & Rows.Count).End(xlUp).Row
        Set r = Range("A2", "E" & lr)
        vA = r.Value
        Application.ScreenUpdating = False
        For i = LBound(vA, 1) To UBound(vA, 1)
            If Trim(UCase(vA(i, 1))) = Trim(UCase(sA)) Then r.Cells(i, 5) = sE
            If Trim(UCase(vA(i, 1))) = Trim(UCase(sB)) Then r.Cells(i, 5) = sF
        Next i
        
    End Sub
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Senior Member
    Join Date
    Apr 2012
    Posts
    193
    Rep Power
    12
    Thanks for the help. The text was generators and not generator, which caused the problem. should have used a wildcard

Similar Threads

  1. Replies: 5
    Last Post: 03-09-2013, 09:01 AM
  2. Replies: 2
    Last Post: 01-24-2013, 09:03 PM
  3. Autofill the data based on non blank cell in next row?
    By Rajesh Kr Joshi in forum Excel Help
    Replies: 3
    Last Post: 11-29-2012, 04:16 PM
  4. Replies: 1
    Last Post: 08-21-2012, 07:36 PM
  5. Excel Macro Functions (GET.CELL)
    By Admin in forum Excel and VBA Tips and Tricks
    Replies: 1
    Last Post: 05-17-2011, 08:56 AM

Posting Permissions

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