Populate Cells in Column AJ, based on input in Column AB

[Rojena]

Hi,

I am working on creating code in which a comment provided in Column AB will return a specific comment in Column AJ, based on if Column AB contains a specific word/criteria. The issue I am having is that I need the code to be dynamic, in which only a word in Column AB would trigger an associated comment to be populated in Column AJ - For example, if Column AB has comments in rows 1, 10 and 15 for instance as "Auto-Uploader Tool Failed", "Autoloader Fail", "Auto-tool Failed Run", then the code should populate Column AJ rows 1, 10 and 15 with "Auto-Upload Tool Fail."


Here is my flawed code - I cannot seem to understand how to change the requirement for Column AB in which simply having the word (as from our example above) "auto" should return "Auto-Upload Tool Fail" in Column AJ. The code below shows that I need the exact wording from Column AB, in order for Column AJ to populate - I would like it so that the code only needs to recognize one word in Column AB to trigger Column AJ. Hope this makes sense!


Sub Comment()

For Each r In Intersect(ActiveSheet.UsedRange, Range("AB:AB"))
If r.Text = "Auto upload fail" Then
r.Offset(0, 8) = "Auto-Uploader Tool Fail"
End If
Next r

For Each r In Intersect(ActiveSheet.UsedRange, Range("AB:AB"))
If r.Text = "autoloader not working" Then
r.Offset(0, 8) = "Auto-Uploader Fail"
End If
Next r

End Sub

For Each r In Intersect(ActiveSheet.UsedRange, Range("AB:AB"))
If r.Text = "Technology Issue" Then
r.Offset(0, 8) = "Technology Issue - Unspecified"
End If
Next r

 

[mumps]

Do you have headers in row 1 with the data starting in row 2 or does your data start in row 1?

 

[Rojena]

Do you have headers in row 1 with the data starting in row 2 or does your data start in row 1?

Hi mumps, thanks for your reply. I have headers set up for the columns, please see below:

 

[mumps]

This macro assumes you have headers in all columns starting in column A and there are no blank rows.

Sub Comment()
    Application.ScreenUpdating = False
    Dim rng As Range, lRow As Long
    lRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    With Cells(1, 1)
        .CurrentRegion.AutoFilter 28, "=*auto*"
        For Each rng In Range("AJ2:AJ" & lRow).SpecialCells(xlCellTypeVisible)
            rng = "Auto-Uploader Tool Fail"
        Next rng
        .CurrentRegion.AutoFilter 28, "Technology Issue"
        For Each rng In Range("AJ2:AJ" & lRow).SpecialCells(xlCellTypeVisible)
            rng = "Technology Issue - Unspecified"
        Next rng
    End With
    Range("A1").AutoFilter
    Application.ScreenUpdating = True
End Sub

 

[Rojena]

This macro assumes you have headers in all columns starting in column A and there are no blank rows.

Sub Comment()
    Application.ScreenUpdating = False
    Dim rng As Range, lRow As Long
    lRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    With Cells(1, 1)
        .CurrentRegion.AutoFilter 28, "=*auto*"
        For Each rng In Range("AJ2:AJ" & lRow).SpecialCells(xlCellTypeVisible)
            rng = "Auto-Uploader Tool Fail"
        Next rng
        .CurrentRegion.AutoFilter 28, "Technology Issue"
        For Each rng In Range("AJ2:AJ" & lRow).SpecialCells(xlCellTypeVisible)
            rng = "Technology Issue - Unspecified"
        Next rng
    End With
    Range("A1").AutoFilter
    Application.ScreenUpdating = True
End Sub

Thank you so much mumps, but I am still experiencing some issues with this- can it be applied to other comments/words in AB as well?

 

[mumps]

The cause of the error could be that filter criteria may not exist in column AB. In your case, the word "forgot" doesn't exist. The code you posted for the other comments should work. Use the pattern in this macro. It checks to see if the criteria exists in column AB.

Sub Comment()
    Application.ScreenUpdating = False
    Dim rng As Range, lRow As Long
    lRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    With Cells(1, 1)
        If WorksheetFunction.CountIf(Range("AB:AB"), "*auto*") > 0 Then
            .CurrentRegion.AutoFilter 28, "=*auto*"
            For Each rng In Range("AJ2:AJ" & lRow).SpecialCells(xlCellTypeVisible)
                rng = "Auto-Uploader Tool Fail"
            Next rng
        End If
        If WorksheetFunction.CountIf(Range("AB:AB"), "Technology Issue") > 0 Then
            .CurrentRegion.AutoFilter 28, "Technology Issue"
            For Each rng In Range("AJ2:AJ" & lRow).SpecialCells(xlCellTypeVisible)
                rng = "Technology Issue - Unspecified"
            Next rng
        End If
    End With
    Range("A1").AutoFilter
    Application.ScreenUpdating = True
End Sub

 

[Rojena]

The cause of the error could be that filter criteria may not exist in column AB. In your case, the word "forgot" doesn't exist. The code you posted for the other comments should work. Use the pattern in this macro. It checks to see if the criteria exists in column AB.

Sub Comment()
    Application.ScreenUpdating = False
    Dim rng As Range, lRow As Long
    lRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    With Cells(1, 1)
        If WorksheetFunction.CountIf(Range("AB:AB"), "*auto*") > 0 Then
            .CurrentRegion.AutoFilter 28, "=*auto*"
           [S] [/S]For Each rng In Range("AJ2:AJ" & lRow).SpecialCells(xlCellTypeVisible)
                [S]rng = "Auto-Uploader Tool Fail" [/S] [B][COLOR=rgb(209, 72, 65)]references a different sheet in the workbook with a list of comments and populates column AJ [/COLOR][/B]
            Next rng
        End If
        If WorksheetFunction.CountIf(Range("AB:AB"), "Technology Issue") > 0 Then
            .CurrentRegion.AutoFilter 28, "Technology Issue"
            For Each rng In Range("AJ2:AJ" & lRow).SpecialCells(xlCellTypeVisible)
               [S] rng = "Technology Issue - Unspecified" [/S][B][COLOR=rgb(209, 72, 65)]references a different sheet in the workbook with a list of comments and populates column AJ [/COLOR][/B]
            Next rng
        End If
    End With
    Range("A1").AutoFilter
    Application.ScreenUpdating = True
End Sub

Hi Mumps,


Thanks so much for your prompt replies. I am fairly new to VBA (very new...) so could you tell me how I would tweak this code so that instead of having certain comments populate to column AJ if criteria in AB is met, the code would check to see if column AB in worksheet X has a specific word, then a comment from worksheet Y (of the same workbook) would get picked up and populated in column AJ in worksheet X. Basically, I want to get ride of the static comments (I have put a strike through them in your code above) - the comments will all be updated periodically in worksheet Y and so the code should reference that worksheet and populate column AJ in worksheet X, depending on if criteria in column AB in worksheet X is met.


Hope you can help, thanks for all that you have done!

Rojena

 

[mumps]

I would need to know the corresponding word in sheet Y for every specified search word in column AB of sheet X. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of both sheets. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[Rojena]

GMORS Analysiss_commentary (1).xlsm | Powered by Box

app.box.com

I would need to know the corresponding word in sheet Y for every specified search word in column AB of sheet X. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of both sheets. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

HI mumps,

Here is a link to the box drive --> Box

Open the file as "microsoft excel online" and you should see three worksheets in the workbook. In the first worksheet, it is labelled worksheet x and the second is worksheet y.

Basically, I want to get ride of the static comments (I have put a strike through them in your code above- please reference the last message I sent) - the comments will all be updated periodically in worksheet Y and so the code should reference that worksheet and populate column AJ in worksheet X, depending on if criteria in column AB in worksheet X is met.

Thanks so much!!
Rojena

 

[mumps]

Thank you for the file. However, I don't see any criteria in column AB and I wouldn't know which comment in Sheet Y would correspond to any criteria in column AB of Sheet X. Perhaps you could manually insert a dozen or so criteria in column AB and the expected results in column AJ of Sheet X and upload the revised file. This would give me an idea of the correlation between the criteria and corresponding comment.