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

elevate_yourself

New Member
Joined
Oct 9, 2014
Messages
15
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:



ABCDE
1Change NamesArray of NamesNumber 1Number 2Number 3
2TimmyJohn567
3JohnBilly567
4MarkMike567
5Mark567
6Timmy567

<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:


ABCDE
1Change NamesArray of NamesNumber 1Number 2Number 3
2TimmyJohn000
3JohnBilly567
4MarkMike567
5Mark000
6Timmy000

<tbody>
</tbody>

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

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
elevate_yourself,

Welcome to the MrExcel forum.

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?


Sample raw data:


Excel 2007
ABCDE
1Change NamesArray of NamesNumber 1Number 2Number 3
2TimmyJohn567
3JohnBilly567
4MarkMike567
5Mark567
6Timmy567
7
Sheet1


After the macro using one array in memory:


Excel 2007
ABCDE
1Change NamesArray of NamesNumber 1Number 2Number 3
2TimmyJohn000
3JohnBilly567
4MarkMike567
5Mark000
6Timmy000
7
Sheet1


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code:
Sub UpdateNumbers()
' hiker95, 10/09/2014, ME810809
Dim b As Variant, i As Long
Dim lr As Long, lc As Long, a As Range
Application.ScreenUpdating = False
lr = Cells(Rows.Count, "B").End(xlUp).Row
lc = Cells(1, Columns.Count).End(xlToLeft).Column
b = Range("B1:B" & lr)
For i = 2 To lr
  Set a = Columns(1).Find(b(i, 1), LookAt:=xlWhole)
  If Not a Is Nothing Then
    Range(Cells(i, 3), Cells(i, lc)).Value = 0
    Set a = Nothing
  End If
Next i
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the UpdateNumbers macro.
 
Upvote 0
Thanks hiker95! I have literally spent 6 hours trying to figure out how to do this. To answer your first questions: im using excel 2010 and working on a PC.

If you wouldn't mind explaining (so I actually understand why you did what you did).

1) I understand everything up until the "Set a = Columns(1).Find(b(i,1), LookAt:=xlWhole)" line. What does this do? I presume its grabbing the range of names from Column A but am unsure
2) I'm confused about the "If Not a is Nothing Then" line as well - it seems like a double negative so I dont see what thats doing.
3) How could this script be changed to used a named range as the reference for both the array and the "Change Names" column?
I would think 'b' would just be redefined as "b = Range("NamedRangeHere"), but how would you work that in with 'a'?

Again thanks so much! I have been pounding my head against my desk trying to figure this out.
 
Upvote 0
And even further still - how to get it working where the information in column A is actually in a different spreadsheet and that it has other data above it so a generic "Columns(1)" approach wouldnt' work. I have tried changing the code my self (below), but when I run this I get a "subscript out of range" error. Any suggestions?



Code:
Sub UpdateNumbers()
' hiker95, 10/09/2014, ME810809
Dim b As Variant, i As Long
Dim lr As Long, lc As Long, a As Range
Application.ScreenUpdating = False
lr = Sheet2.Cells(Rows.Count, "B").End(xlUp).Row
lc = Sheet2.Cells(1, Columns.Count).End(xlToLeft).Column
b = Sheet2.Range("B1:B" & lr)
For i = 2 To lr
  Set a = Sheet1.Range("A1:A6").Find(b(i, 1), LookAt:=xlWhole)
  If Not a Is Nothing Then
    Sheet2.Cells(i, 3).Value = 0
    Sheet2.Cells(i, 4).Value = 0
    Sheet2.Cells(i, 5).Value = 0
    Set a = Nothing
  End If
Next i
Application.ScreenUpdating = True
End Sub
 
Upvote 0
vate_yourself,

Thanks for the feedback.

You are very welcome. Glad I could help.

If you wouldn't mind explaining (so I actually understand why you did what you did).

Be back in a little while with comments added to the macro code to explain what is going on in the macro.
 
Upvote 0
elevate_yourself,

Please see the comments beginning with the ' characters.

I hope this helps you to better understand what the macro is doing.


Code:
'   the name of the macro
Sub UpdateNumbers()



' the way I keep track of the macros I write
' the handle of the person that wrote the macro
' .        . the date the macro was written
' .        .           the Mister Excel Forum
' .        .             thread number
' .        .             .
' hiker95, 10/09/2014, ME810809



' it is a good programming practice to define all your variables
'   this way if there was a problem with the macro code, another
'   programmer could probably determine the problem, and, fix it



'   b is a variant array that can hold all types of information
'   .             i is a counter to loop thru the rows in the b array
Dim b As Variant, i As Long



'   lr stands for last row used
'   .           lc stands for the last column used
'   .           .           a is a range object that is used to find
'   .           .           .  each item in the Array of Names
'   .           .           .  in column A = 1
Dim lr As Long, lc As Long, a As Range



' this turns of screen updating so that the macro will run faster
Application.ScreenUpdating = False



' find the last used row in the
'                       B column
lr = Cells(Rows.Count, "B").End(xlUp).Row



' find the last used column in
'          1 = row 1
lc = Cells(1, Columns.Count).End(xlToLeft).Column



' the b array is set to range
'          B1:B  & lr = 6
b = Range("B1:B" & lr)



' we are going to loop in column B using the
'   i counter variable
'       2 = the starting row in column B with data
'       .    lr = 6
For i = 2 To lr



' set the
'     a range variable to find in
'     .   columns(1) = 1 = A
'     .   .               b(i, 1) for the first/next
'     .   .               .       item in the 'Array of Names'
  Set a = Columns(1).Find(b(i, 1), LookAt:=xlWhole)
  
  
  
' the following line of code stands for
'                          means that if the name IS found in column A
  If Not a Is Nothing Then



'   then the range of cells in the
'               i row
'               .  3 = column C
'               .  .         i row
'               .  .         .  lc = column E = 5
'               .  .         .  .    Value will all be set to 0 = zeros
    Range(Cells(i, 3), Cells(i, lc)).Value = 0
    
    
    
'   set the
'       a range object to
'       .   Nothing so that we can use it again
    Set a = Nothing
  End If



' loop to the next
'    i row in column B
Next i



' turn on screen updating
Application.ScreenUpdating = True



' exit the Sub, exit the macro
End Sub
 
Upvote 0
elevate_yourself,

And even further still - how to get it working where the information in column A is actually in a different spreadsheet and that it has other data above it so a generic "Columns(1)" approach wouldnt' work. I have tried changing the code my self (below), but when I run this I get a "subscript out of range" error. Any suggestions?

It is always best to display your actual raw data worksheet(s), and, the results that you are looking for. This way we can usually find a solution on the first go.


In order to continue I will have to see a screenshot of the actual raw data worksheet, with it's actual worksheet name.


Can you post a screenshot of the actual raw data worksheet?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post a small screen shot try one of the following:

Excel Jeanie
Download

MrExcel HTML Maker
https://onedrive.live.com/?cid=8cffdec0ce27e813&sc=documents&id=8CFFDEC0CE27E813!189

Borders-Copy-Paste
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045

To test the above:
Test Here



Or, you can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.
 
Upvote 0
And ill see what I can do - it willl probably be sometime tomorrow before I can get those screenshots up for you. Thanks again!
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

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