CODEWITHSUNDEEP
excel
Jiaji Huang
Jiaji Huang

Reputation: 331

insert text into excel cells after all matching cells

I have the following problem in excel. I want to insert text "1234" into the cells to the right of all matching cells. So, let's say the pattern to be matched is "abcd", and the excel looks like

enter image description here

What I want is

enter image description here

The main difficulty is that there are hundreds of matching cells. Is there a function to automatically fill that in?

Upvotes: 1

Views: 70

Answers (1)

pgSystemTester
pgSystemTester

Reputation: 9932

As stated in the comments, you would need VBA to do this. Assuming you want to perform on all sheets, this code should work. If you want to specify the sheet name, take out the ws loop and just set ws = Sheets("theName") or whatever.

If performance is an issue, lookup how to speed up code or consider researching some arrays.

Sub updateCellsToTheRight()
Const zTango As String = "abcd" 'case sensative
Const zHighlight As String = "1234"

Dim ws As Worksheet, aCell As Range
For Each ws In ThisWorkbook.Worksheets
    For Each aCell In ws.UsedRange.Cells
        If aCell.Value = zTango Then aCell.Offset(0, 1).Value = zHighlight
        
    Next aCell
Next ws

End Sub

Upvotes: 1

Related Questions