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

Rojena

New Member
Joined
Nov 19, 2020
Messages
14
Office Version
  1. 365
Platform
  1. Windows
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
 
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.
Hi Mumps,

Apologies if this was unclear, I have updated the excel sheet with the logic for the code: Box

Please see the first four entries in Column AB in worksheet X, the categories in worksheet Y and the logic for the code as explained in the last worksheet, labelled "Logic for Code." Please let me know if you need any other instructions.


Essentially, the first code you provided would work but I just need the code to reference worksheet Y, instead of putting having static code. I want to be able to update worksheet Y, so that even if I added in new categories into worksheet Y, the code should be dynamic and recognize new categories (depending on if a specific word in Column AB is present).


thanks!!
Rojena :) :)
 
Upvote 0

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Your worksheet y would have to look something like this:
CommentsMetrics Comment
autoAuto-Upload Tool Fail
eucEUC Run Failure
GMORSGMORS System Issue
uploadGMORS System Issue
holidayHoliday Schedule
forgotHuman/Operational Issues
manuallyIssues with Report Generation - Manual Intervention Needed
yearMonth-End/Year-End Processes
pythonPython Code Run Failure
sprxSPRX/SPR System issues
volumeStaffing Capacity
trainingStaffing Capacity
coverageStaffing Capacity
vacationStaffing Capacity
capacityStaffing Capacity
TBSMTBSM Technology Issue
Technology IssueTechnology Issue - Unspecified

Column A contains all the possible comments in column AB of sheet x and column B would have the corresponding metrics comment. I based this data on the "Comment" macro in your file. Perhaps you can upload an updated file using your possible actual comments in both sheets.
 
Upvote 0
Your worksheet y would have to look something like this:
CommentsMetrics Comment
autoAuto-Upload Tool Fail
eucEUC Run Failure
GMORSGMORS System Issue
uploadGMORS System Issue
holidayHoliday Schedule
forgotHuman/Operational Issues
manuallyIssues with Report Generation - Manual Intervention Needed
yearMonth-End/Year-End Processes
pythonPython Code Run Failure
sprxSPRX/SPR System issues
volumeStaffing Capacity
trainingStaffing Capacity
coverageStaffing Capacity
vacationStaffing Capacity
capacityStaffing Capacity
TBSMTBSM Technology Issue
Technology IssueTechnology Issue - Unspecified

Column A contains all the possible comments in column AB of sheet x and column B would have the corresponding metrics comment. I based this data on the "Comment" macro in m your file. Perhaps you can upload an updated file using your possible actual comments in both sheets.


Hi Mumps,

Thanks for your reply. Here is an updated version of the file - Box

As suggested, I have updated worksheet Y with the comments and the associated metrics comments for your reference. Please let me know if this helps, and if you could help me modify the code to incorporate this new feature.


Thanks! Really appreciate it!
Rojena :)
 
Upvote 0
I have a macro that works. However, there is one problem with the comments in column A of sheet y. For example, one of the comments you included is the text "FO". This creates a problem when the macro searches for "FO" in column AB of sheet x because the macro will recognize any word that contains "FO". This means that the comment "high volume tasks for year end" will meet the criteria because it contains the text "FO" in the word "for". So column AJ of sheet x will return "FO Delayed Submission" for the comment "high volume tasks for year end" which is not what you want. In order for the macro to work properly on all the comments, the comments in column A of sheet y must be unique enough so that the macro doesn't mistakenly recognize unwanted comments. In other words, the comments in column A of sheet y must be more specific so that they cannot be found in any of the comments in column AB. I hope that makes sense.
 
Upvote 0
I have a macro that works. However, there is one problem with the comments in column A of sheet y. For example, one of the comments you included is the text "FO". This creates a problem when the macro searches for "FO" in column AB of sheet x because the macro will recognize any word that contains "FO". This means that the comment "high volume tasks for year end" will meet the criteria because it contains the text "FO" in the word "for". So column AJ of sheet x will return "FO Delayed Submission" for the comment "high volume tasks for year end" which is not what you want. In order for the macro to work properly on all the comments, the comments in column A of sheet y must be unique enough so that the macro doesn't mistakenly recognize unwanted comments. In other words, the comments in column A of sheet y must be more specific so that they cannot be found in any of the comments in column AB. I hope that makes sense.
Hi Mumps,

I see, I can work around that and change the criteria depending on what the code does. Could you kindly show me what your code looks like?


Thanks!!!
Rojena :)
 
Upvote 0
Here you go:
VBA Code:
Sub Comment()
    Application.ScreenUpdating = False
    Dim lastrow As Long, lRow As Long, srcWS As Worksheet, desWS As Worksheet, rng As Range, com As Range
    Set srcWS = Sheets("worksheet y")
    Set desWS = Sheets("worksheet x")
    lastrow = desWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    lRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For Each com In srcWS.Range("A2:A" & lRow)
        With desWS
            If WorksheetFunction.CountIf(.Range("AB:AB"), "*" & com & "*") > 0 Then
                With .Cells(1, 1)
                    .CurrentRegion.AutoFilter 28, Criteria1:="=*" & com & "*"
                    For Each rng In desWS.Range("AB2:AB" & lastrow).SpecialCells(xlCellTypeVisible)
                        rng.Offset(, 8) = com.Offset(, 1)
                    Next rng
                End With
            End If
        End With
    Next com
    Range("A1").AutoFilter
    Application.ScreenUpdating = True
End Sub
 
Upvote 0
Solution
Here you go:
VBA Code:
Sub Comment()
    Application.ScreenUpdating = False
    Dim lastrow As Long, lRow As Long, srcWS As Worksheet, desWS As Worksheet, rng As Range, com As Range
    Set srcWS = Sheets("worksheet y")
    Set desWS = Sheets("worksheet x")
    lastrow = desWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    lRow = srcWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For Each com In srcWS.Range("A2:A" & lRow)
        With desWS
            If WorksheetFunction.CountIf(.Range("AB:AB"), "*" & com & "*") > 0 Then
                With .Cells(1, 1)
                    .CurrentRegion.AutoFilter 28, Criteria1:="=*" & com & "*"
                    For Each rng In desWS.Range("AB2:AB" & lastrow).SpecialCells(xlCellTypeVisible)
                        rng.Offset(, 8) = com.Offset(, 1)
                    Next rng
                End With
            End If
        End With
    Next com
    Range("A1").AutoFilter
    Application.ScreenUpdating = True
End Sub
Thanks so much Mumps, you're the best!!!
 
Upvote 0

Forum statistics

Threads
1,213,553
Messages
6,114,279
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top