Use VBA to create array, search through array and make changes to data values

[elevate_yourself]

Hello,

I am very new to VBA and am looking to find a way to search through an array using a range of cells and then make updates to the cells if there is a match. As an example:

	A	B	C	D	E
1	Change Names	Array of Names	Number 1	Number 2	Number 3
2	Timmy	John	5	6	7
3	John	Billy	5	6	7
4	Mark	Mike	5	6	7
5		Mark	5	6	7
6		Timmy	5	6	7

<tbody>
</tbody>

What I would like to be able to do is search the "Array of Names" column (which I would like to make as an array) and for any names that match the "Change Names" column, and update the values in the Number columns to 0 for all. So in the end it would look like:

	A	B	C	D	E
1	Change Names	Array of Names	Number 1	Number 2	Number 3
2	Timmy	John	0	0	0
3	John	Billy	5	6	7
4	Mark	Mike	5	6	7
5		Mark	0	0	0
6		Timmy	0	0	0

<tbody>
</tbody>

I hope this is clear enough - if more clarification is needed please let me know

 

[elevate_yourself]

Morning Hiker95,

I have tried to see if there is a way for me to remove all the sensitive information in the spreadsheet and unfortunately I would basically have to create the spreadsheet from scratch to do so. So unfortunately I wont be able to provide screenshots. All I can tell you is that the modified code does work on a new spreadsheet and that the one I am using has about 20 tabs and a lot of pivot tables and links to different databases. If you happen to know a way to fix the "Subscript out of range" error on a general level that would be helpful. I have read other posts suggesting that its caused by moving from one sheet to another and the best way to fix it is to just append everything with the specific spreadsheet your using. However, I have dont that and it does not fix the issue in this case. Ill keep looking around to see what I can find but at this point, regrettably, I cant provide any more specific details.

Cheers,

 

[elevate_yourself]

Just wanted to post an update saying I found a way to make the code work. Instead of using the array I used a range find. Final version of the code below.

Option ExplicitSub testing()


Dim b As Variant
Dim i As Long
Dim lr As Long
Dim lc As Long
Dim a As Range


Application.ScreenUpdating = False


lr = Sheets("Sheet2").Cells(Rows.Count, "B").End(xlUp).Row


lc = Sheets("Sheet2").Cells(1, Columns.Count).End(xlToLeft).Column


'b = Sheets("Sheet2").Range("B8:B" & lr)


For i = 8 To lr
  Set a = Sheets("Sheet1").Range("C32:C34").Find(Sheets("Sheet2").Range("B" & i), LookAt:=xlWhole)
  If Not a Is Nothing Then
    Sheets("Sheet2").Cells(i, 9).Value = 0
    Sheets("Sheet2").Cells(i, 10).Value = 0
    Sheets("Sheet2").Cells(i, 11).Value = 0
    Sheets("Sheet2").Cells(i, 12).Value = 0
    Set a = Nothing
  End If
Next i


Application.ScreenUpdating = True


End Sub

 

[hiker95]

elevate_yourself,

Thanks for the feedback.

Glad you were able to find a solution that worked correctly.

And, come back anytime.