Search excel/csv sheet1 for a string taken from sheet2, then change a value in same row

Question

Situation is I have a list (excel). Need to go through that list and take each name in a column. Then search for that name is another file (csv). Once it's found I must change a value in the same Row as the found name, but in a column a few cells to the right.

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+q
'
Dim myRange As Range

ActiveCell.Select
Selection.Copy
Set myRange = Selection
Sheets("identities").Select
ActiveWorkbook.ActiveSheet.Unprotect
Cells.Find(What:=myRange, After:=ActiveCell, LookIn:= _
    xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
    xlNext, MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 13).Range("A1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "TRUE"
ActiveCell.Offset(1, -13).Range("A1").Select
End Sub

The above code works to help automate the process a little bit. I'm trying to get it to the point were it can cycle through the column on Sheet1 without me switching back and forth and running it manually on each cell. And I should probubly build in error handeling for if it doesn't find the name it's searching for.

Thanks for your help!

score 0 · Answer 1 · edited May 23 '17 at 12:32

You will need to adapt the range your are getting the original values from to reflect yours. At present I just used Range("A1:A10"), but you need to stop using select. It will spoeed the code up and make it much more readable.

Try this and amend the range to suit yours.

Dim rng As Range
Dim r As Range

   For Each r In Sheets("identities").Range("A1:A10")
        ActiveWorkbook.ActiveSheet.Unprotect
        Set rng = Cells.Find(What:=r, After:=ActiveCell, LookIn:= _
            xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
            xlNext, MatchCase:=False, SearchFormat:=False)
          rng = Sheets("identities").Offset(, 13)
   Next r

Read this stackoverflow thread on not using select. How to avoid using Select in Excel VBA macros

Search excel/csv sheet1 for a string taken from sheet2, then change a value in same row

1 Answers1