Solved Find and edit multiple cells in Excel at once.

October 26, 2020 at 00:02:16
Specs: Macintosh
Hi,

What i currently do at my job is I have to Ctrl+F multiple values from one worksheet to another and those values that I find I will have to highlight and add text at the end of the cell.

Is it possible to create a macro that will automate and do this for me?

Thanks in advance


See More: Find and edit multiple cells in Excel at once.


✔ Best Answer
November 13, 2020 at 17:37:05
I pretty sure that this works, at least on the example above. If you find a situation where it doesn't work, post that data and point out the problem, just like you did above, so I know what issue to look for.

Sub Edit_Strings_V3()

Dim Last_SearchRow As Long
Dim rw As Long
Dim c As Range
Dim firstAddress As String

'Determine last row with data in Sheet2 Column M
    Last_SearchRow = Sheets("Sheet2").Cells(Rows.Count, 13).End(xlUp).Row

'Loop through values in Sheet2 Column M
'and search "exception report" sheet
   With Sheets("exception report").Columns(1)
      For rw = 2 To Last_SearchRow
       Set c = .Find(Sheets("Sheet2").Cells(rw, 13), lookat:=xlPart, after:=Cells(7, 1))
         
'If search string is found, store address of first occurrence
         If Not c Is Nothing Then
            firstAddress = c.Address
              Do
               
'Check for "REMOVED", don't edit cell if found
                If c Like "*REMOVED*" Then
                   Exit Do
                End If

'If "MINT PEND" is present, edit cell
                If c Like "*MINT PEND*" Then
                   c.Font.Color = vbRed
                   c.FormatConditions.Delete
                   c.Value = c & " (REMOVED FROM QUEUE)"
                End If
                 Set c = .FindNext(c)
                 
'If neither "REMOVED" or "MINT PEND" is found, serach for
'same string again until all cells have been checked
              Loop While Not c Is Nothing And c.Address <> firstAddress
         End If
     Next
   End With
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code



#1
October 26, 2020 at 17:15:58
Yes, I'm pretty sure that it could be done with a macro, but we would need more details about what needs to be found, what text needed to be added, etc.

The more details you can provide about your data layout and your work process, the easier it will be for us to help.

If you want to post example data (non-personal, non-confidential) then please read the instructions found at the following link.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#2
October 26, 2020 at 20:39:56
Hi,

First of all, thanks for responding and being willing to help. Below you will find a description of the excel file.

There are 2 worksheets. First worksheet is just one column that includes a bunch of words and text in each row (example listed below).

(Contents of Worksheet1)

689 K 209021A2829010 DO211221A2490100 121.00 232 EJFORUVTATO BKKRUVMTLOX 4321-35-65 68.95 4930 COTT VIEW

In the second worksheet I would have a list on column M in which I will have an id code that will match with some of the contents in worksheet1 (the bold value in the example).

Everyday I am tasked to search the list on the excel sheet on column M and edit the cell in worksheet 1. I will be adding “(REMOVED FROM QUEUE)” at the end of the cell, highlighting the text in red, as well as clearing the rules of the selected cell as there are rules implemented in that first worksheet.

Thanks you again!

message edited by Excelbeginner1


Reply ↓  Report •

#3
October 27, 2020 at 11:34:32
Questions:

1 - Which column contains the Sheet1 data?
2 - What Row does the Sheet1 data start in?
3 - What Row does the data in Sheet2 data start in?
4 - Will the search string only be found once on Sheet1 or could it be found multiple times?
5 - What do you mean by "as there are rules implemented in that first worksheet"?
6 - How does Sheet2 Column M get populated? Does it change completely every day or are new search strings just added on.

The reason I ask Question 6 is because I need to know if e.g. DO211221A2490100 will be in the list every time you run the code. I assume that you don't want "(REMOVED FROM QUEUE)" appended time after time after time so I need to know if I have to deal with the fact that the "found cell" has already been edited. If the list in Column M is "brand new" every day, then I don't have to worry about that.

If there is anything else that you think I should know, please include the details.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

Related Solutions

#4
October 27, 2020 at 12:53:42
1 & 2) Sheet one starts at A8. Just one column with around 300 rows.

3) Data starts on column M row 2.

4) Yes, it can only be found once.

5) Rules as in the cells would have been conditionally formatted to be highlighted and I wish to get rid of the highlight.

6) It gets manually populated by me. Yes it changes completely each day.

Sheet1 is labelled as exception report and sheet2 is labelled Sheet2

Everyday I would have a brand new excel file with the same 2 sheets and I will have manually pasted new content on it in the morning. What I then have to do is copy the contents from sheet2 column M, go to sheet1, CTRL+F for the location of the cell and edit it.


I’m hoping there is a macro where once I run it, it can read the contents of sheet2 column M and will automatically find where the locations of the correspondent cells on sheet1 and edit it.


Reply ↓  Report •

#5
October 27, 2020 at 13:37:09
"exception report" or "Exception Report" or something else?

If referred to by name, the macro will require the exact name.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#6
October 27, 2020 at 13:50:33
it is "exception report"

Reply ↓  Report •

#7
October 27, 2020 at 14:07:10
Try this...

Sub Edit_Strings()

Dim Last_SearchRow As Long
Dim c As Range

'Determine last row with data in Sheet2 Column M
    Last_SearchRow = Sheets("Sheet2").Cells(Rows.Count, 13).End(xlUp).Row

'Search Sheet "exception report"
   With Sheets("exception report").Columns(1)
   
'Loop through values in Sheet2 Column M
     For rw = 2 To Last_SearchRow
       Set c = .Find(Sheets("Sheet2").Cells(rw, 13), lookat:=xlPart)
         
'If found, make changes
         If Not c Is Nothing Then
           c.Font.Color = vbRed
           c.FormatConditions.Delete
           c.Value = c & " (REMOVED FROM QUEUE)"
         End If
         
     Next
     
   End With
   
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#8
October 27, 2020 at 14:53:25
Thank you so much, you're going to save me a lot of time in the future!

Just a quick follow up question. What does (Rows.Count, 13).End(xlUp).Row mean? Why 13?

message edited by Excelbeginner1


Reply ↓  Report •

#9
October 27, 2020 at 16:57:47
There's a lot going on with that question.

re:

Cells(Rows.Count, 13).End(xlUp).Row

When using the Cells method to reference a cell, the syntax is Cells(row_index, column_index)

Cells(1, 1) refers to Row 1, Column 1, equivalent to A1. That could also be written as Cells(1, "A")

Cells(22, 13) refers to Row 22, Column 13, equivalent to M22. That could also be written as Cells(22, "M")

Cells(rw, 13) refers to whatever value the variable "rw" is set to as the row_index, within Column 13 ("M").

M2, M3, M4, M5 each time through the For-Next loop.

OK, that explains the Cells method. Next...

The defacto standard for finding the last row with data in a column is to have VBA go all the way down to the last row (row_index = Rows.Count) in a column (column_index = 13 or "M") then look upwards for data using .End(xlup).Row

If data isn't found until VBA reaches e.g. M345 on the way up, then the lastRw variable will be populated with 345. Now we know how long the data range that contains the search criteria is. Since you said that the list of search strings will vary, we are letting VBA determine the length of the For-Next loop each time the code run instead of hard coding a row number.

Some history: The lastRw technique used to be written as Cells(65536, 1) because prior to Excel 2007, the max number of rows was 65,536. Once Excel 2007 came along and the number of rows jumped to over 1MM, the Rows.Count syntax came into favor since it is compatible with both old, new and future versions of Excel. That allows VBA to determine the bottom row in a spreadsheet based on the version of Excel the code is running in.

Just FYI...another way to construct that instruction is:

lastRw = Range("M" & Rows.Count).End(xlUp).Row 

i.e. Range("M22") is equivalent to Cells(22, "M") and also Cells(22, 13)

Some of us prefer to use the column number instead of the letter, I think because we often calculate the column_index as a number, such as when using a For-Next loop. Feel free to change the 13 to "M" if you like that better.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

#10
October 27, 2020 at 21:44:02
Great! Thank you once again!

Reply ↓  Report •

#11
October 28, 2020 at 10:09:50
My apologies, I made the mistake of mentioning the string can only appear once. It rarely happens but it can appear twice. Is it possible to read only the contents that have the words MINT PEND on the exception report?

Reply ↓  Report •

#12
October 28, 2020 at 11:37:48
I'm confused.

Are you changing the search criteria to be MINT PEND only or is it MINT PEND in addition to the "DO211..." strings?

i.e. Search for the "DO211..." strings from Sheet2 and if found also check that cell contains MINT PEND. Only when both are present, make the changes.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

#13
October 28, 2020 at 12:26:16
Yes, you're example was spot on. Only make the changes if the words MINT PEND are also in the cell.

Reply ↓  Report •

#14
October 28, 2020 at 13:24:55
Try this version:

Sub Edit_Strings_V2()

Dim Last_SearchRow As Long
Dim c As Range

'Determine last row with data in Sheet2 Column M
    Last_SearchRow = Sheets("Sheet2").Cells(Rows.Count, 13).End(xlUp).Row

'Search Sheet "exception report"
   With Sheets("exception report").Columns(1)
   
'Loop through values in Sheet2 Column M
     For rw = 2 To Last_SearchRow
       Set c = .Find(Sheets("Sheet2").Cells(rw, 13), lookat:=xlPart)
         
'If search string found, check for "MINT PEND"
         If Not c Is Nothing Then
            If c Like "*MINT PEND*" Then

'If "MINT PEND" is present, make changes
             c.Font.Color = vbRed
             c.FormatConditions.Delete
             c.Value = c & " (REMOVED FROM QUEUE)"
          End If
         End If
         
     Next
     
   End With
   
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#15
October 28, 2020 at 14:03:12
This is perfect! Thank you again

Reply ↓  Report •

#16
November 1, 2020 at 09:32:39
Hi,

Is it possible to make this code only be able to run once? For example, if the cell already has (REMOVED FROM QUEUE), it cannot be ran again.


Reply ↓  Report •

#17
November 1, 2020 at 11:19:40
Your use of the words "run once" and "cannot be ran again" don't make sense. The ability of the code to run has nothing to do with the contents of any cell. It only runs when you cause it to run.

If you are really asking if the code can be made to not change a cell if the REMOVED string is already in the cell, that is a very different question.

If that is your question, then explain this:

In Post # 3 I asked:

6 - How does Sheet2 Column M get populated? Does it change completely every day or are new search strings just added on?

You answered:

6) It gets manually populated by me. Yes it changes completely each day.

If that is the case, why would you run it more than once against the same set of search strings from Sheet2? (That was the reason I asked question #6 in the first place.)

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#18
November 1, 2020 at 11:36:51
I just meant if someone were to click on the run button twice on accident, would there be a way to mitigate that risk? As CTRL+Z wouldn't be able to undo it.

But if not I'll just have to make sure everyone only runs it once when doing the task.


Reply ↓  Report •

#19
November 1, 2020 at 17:53:05
Try this. This code can be run against the same search string list multiple times but will only edit the found cell once. It checks for the word "REMOVED" and if found, it doesn't edit the cell.

Sub Edit_Strings_V2()

Dim Last_SearchRow As Long
Dim c As Range

'Determine last row with data in Sheet2 Column M
    Last_SearchRow = Sheets("Sheet2").Cells(Rows.Count, 13).End(xlUp).Row

'Search Sheet "exception report"
   With Sheets("exception report").Columns(1)
   
'Loop through values in Sheet2 Column M
     For rw = 2 To Last_SearchRow
       Set c = .Find(Sheets("Sheet2").Cells(rw, 13), lookat:=xlPart)
         
'If search string found, check for REMOVED. 
'Don't edit cell if found
         If Not c Is Nothing Then
            If c Like "*REMOVED*" Then
             GoTo Duplicate
            End If

'Check for MINT FOUND
            If c Like "*MINT PEND*" Then

'If "MINT PEND" is present, make changes
             c.Font.Color = vbRed
             c.FormatConditions.Delete
             c.Value = c & " (REMOVED FROM QUEUE)"
          End If
         End If
Duplicate:
     Next
     
   End With
   
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

#20
November 1, 2020 at 22:13:14
Thank you once again!

Reply ↓  Report •

#21
November 11, 2020 at 20:38:12
Hey, I ran into an issue today when using the code, and I'm hoping you can fix it.

As mentioned in posts #11-#13, it is possible for duplicates but I only want it to edit the cell that contains the words MINT PEND in it. I recently had a duplicate but when I ran the code it did not edit the cell with MINT PEND in it (or the duplicated cell without MINT PEND).

Is there a solution for this?


Reply ↓  Report •

#22
November 12, 2020 at 08:08:22
Without seeing your actual data, I can't tell you why the edit wasn't done.

What I can tell you is that from a code perspective there is no reason that any given MINT PEND entry would be ignored. Either code finds MINT PEND and executes the required steps or it doesn't find it and moves on.

What I find in most cases like this is that the cell doesn't actually contain what the code is looking for. For example, there might be an extra space between MINT and PEND. e.g. As far as the code is concerned, MINT (space space) PEND is not the same as MINT (space) PEND.

In a case like this, if I had the actual data in front of me, I would typically use some of the VBA Debugging tools, such as Single Stepping to watch the code and see what it is doing. Some examples of the debugging tools can found in the How-To found in the following link.

https://www.computing.net/howtos/sh...

Something that you could try within the workbook itself is to use Ctrl-F and open the Excel Find & Replace wizard. Search for MINT PEND, making sure that "Match entire cell contents" is unchecked. See if the Find & Replace wizard finds the cell that the code is skipping. In reality, that is all that the code is doing. The VBA .Find instruction simply uses the Excel Find & Replace wizard in the background.

Set c = .Find(Sheets("Sheet2").Cells(rw, 13), lookat:=xlPart)

Again, without your actual workbook to run the code against, there is not much I can do to determine the cause of the current issue. If you discover something that might help, just let me know.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#23
November 12, 2020 at 11:45:10
So I tested a few scenarios and this was my finding.

The code will only run a duplicated scenario successfully if the duplicated cell (that does not need to be edited) is below the MINT PEND cell. If the duplicated cell that does not contain the words MINT PEND are above the correct cell, it does not make the edit.

As you mentioned that specific code is equivalent to a CTRL-F. Maybe it reads the exception report from top to bottom, and if it finds that the cell does not say MINT PEND it moves on with the next string.

Would there be a solution for this?


Reply ↓  Report •

#24
November 12, 2020 at 14:42:38
I will not have access to Excel for the next few days. I will look at your latest issue early next week.

That said, without having visibility into your actual data, I am operating at a considerable disadvantage. The only data that I can test against is data that I have made up. You could try to explain your data layout using words a hundred times and it would not be as good as an actual list of your cell values so that I can run the code against it. Each time you add another criteria you make it more difficult for anyone to find a solution that fits your needs.

First you said that the strings would not be duplicated, then you said that there are duplicates.

Then you wanted protection against the the users running the code more than once against the same data set.

Now the issue appears to be related to the location of the duplicates.

We may be at a point where continuing to add bolt-ons to the original code is no longer viable. I may need to start over from the beginning. I am reluctant to do that without an example of your data - an example that includes all possible scenarios so that one complete piece of code can written.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#25
November 12, 2020 at 22:15:32
My apologies, I posted a sample version of what it looks like.

I understand how frustrating it can be and I really do appreciate the work you have done for me. This most likely will be the last update the code needs as I have been using it for a few weeks now going through all different possible scenarios.

689 K 209021A2829010 DO211221A2490100 121.00 232 EJFORUVTATO BKKRUVMTLOX 4321-35-65 68.95 4930 COTT VIEW
690 K 209021A2829010 DO211221A2103100 121.00 232 EJFORUVTATO BKKRUVMTLOX 4321-35-65 68.95 4930 MINT PEND
691 K 209021A2829010 DO211221A2490100 121.00 232 EJFORUVTATO BKKRUVMTLOX 4321-35-65 68.95 4930 COTT VIEW
692 K 209021A2829010 DO211221A2490100 121.00 232 EJFORUVTATO BKKRUVMTLOX 4321-35-65 68.95 4930 COTT VIEW
693 K 209021A2829010 DO211221A8274700 121.00 232 EJFORUVTATO BKKRUVMTLOX 4321-35-65 68.95 4930 MINT PEND
694 K 209021A2829010 DO211221A2490100 121.00 232 EJFORUVTATO BKKRUVMTLOX 4321-35-65 68.95 4930 COTT VIEW
695 K 209021A2829010 DO211221A2037694 121.00 232 EJFORUVTATO BKKRUVMTLOX 4321-35-65 68.95 4930 COTT VIEW
696 K 209021A2829010 DO211221A2037694 121.00 232 EJFORUVTATO BKKRUVMTLOX 4321-35-65 68.95 4930 MINT PEND
697 K 209021A2829010 DO211221A2490100 121.00 232 EJFORUVTATO BKKRUVMTLOX 4321-35-65 68.95 4930 COTT VIEW

DO211221A2103100 
DO211221A8274700 
DO211221A2037694  

As tested, the final string that needs to be edited (DO211221A2037694) will not edit because the same string is written above the MINT PEND cell (column 7 and 8).

Thank you again for the time and work you have given me.


Reply ↓  Report •

#26
November 13, 2020 at 17:37:05
✔ Best Answer
I pretty sure that this works, at least on the example above. If you find a situation where it doesn't work, post that data and point out the problem, just like you did above, so I know what issue to look for.

Sub Edit_Strings_V3()

Dim Last_SearchRow As Long
Dim rw As Long
Dim c As Range
Dim firstAddress As String

'Determine last row with data in Sheet2 Column M
    Last_SearchRow = Sheets("Sheet2").Cells(Rows.Count, 13).End(xlUp).Row

'Loop through values in Sheet2 Column M
'and search "exception report" sheet
   With Sheets("exception report").Columns(1)
      For rw = 2 To Last_SearchRow
       Set c = .Find(Sheets("Sheet2").Cells(rw, 13), lookat:=xlPart, after:=Cells(7, 1))
         
'If search string is found, store address of first occurrence
         If Not c Is Nothing Then
            firstAddress = c.Address
              Do
               
'Check for "REMOVED", don't edit cell if found
                If c Like "*REMOVED*" Then
                   Exit Do
                End If

'If "MINT PEND" is present, edit cell
                If c Like "*MINT PEND*" Then
                   c.Font.Color = vbRed
                   c.FormatConditions.Delete
                   c.Value = c & " (REMOVED FROM QUEUE)"
                End If
                 Set c = .FindNext(c)
                 
'If neither "REMOVED" or "MINT PEND" is found, serach for
'same string again until all cells have been checked
              Loop While Not c Is Nothing And c.Address <> firstAddress
         End If
     Next
   End With
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

Ask Question