VBA Loop Through Columns

[MikeeRDX]

Hi, I have this spreadsheet that I need to write a Loop VBA to loop through a row of account numbers, find if there is match of the account number to a list of accounts in a table. Then, if there is a match to Column B1, place a fixed variable of 11/10/16 under that particular column for the matching account number. So in the example I have below, it would loop through and see that for account number 25141, the table has a D so under column D, place 11/10/16. Can anyone help me with a VBA code to perform this task. The list is much longer but there will NEVER be duplicate account numbers in the table. Thank you!


A B C D E
1 25141 10/1/16 11/1/16
2 11152 5/3/15 7/2/16 8/1/16
3 25141
4 21452 5/2/16
5 35241

Table:
25141......D
11152......C
25141......E
35241......B
21452......B

[SamT]

Set a Range = to the Acct# table range (For Ex: AcctNums.) Then, just "Find" each Acct Number in Column A in "AcctNums."

Dim Found As Range
Set Found = AcctNums.Find(CellInA)
If not Found is nothing Then
MsgBox "Acct Found"
Else
MsgBox "Acct NOT Found"
End If

fixed variable of 11/10/16

How did you arrive at that perticular date? If possible you sould use a standard Variable and assign it a value automatically. Also, should the Variable be a Date Type, A Double (number) Type, or a String Type? It is usually best practice to use a Date Type for Dates.

[MikeeRDX]

Thank you for responding. The dates in the data(top portion of my question) are random and pre-existing. The assigned dates in the table will be just one date that may change from time to time. I would to loop through the data and if there is a match of the account to a letter on the title of the data, then plug in the assigned date.

[SamT]

Can you upload an abbreviated version of the workbook? Use the "Go Advanced" button and look below the Advanced editor for "Manage Attachments."

[MikeeRDX]

Please see attached for a copy of the file. Thank you!

[MikeeRDX]

Good Morning. I've uploaded a copy of the file. Thank you for whatever help you can provide

In the sample file, the result I'm seeking is for Inventory # 686895, for example, since the Table indicates that Letter 2 was sent on 11/9/16, place this date under "Letter 2" in column C. Do not remove any other dates that are already there for the respective "Letter" (Columns B, C, D, E) unless there is a match. The "Sent Date" will always be the same for all and any Inventory in the Table. It may change to a different date but the change applies to all inventory in the table.

The Table will be stored in a another Worksheet tab.

[p45cal]

Run the following in your sample file AFTER selecting the data (G5:I11 in your file):

Sub blah()
For Each rw In Selection.Rows
  y = Application.Match(rw.Cells(1).Value, Columns(1), 0)
  x = Application.Match(rw.Cells(2).Value, Rows(4), 0)
  If IsError(y) Or IsError(x) Then
    rw.Interior.ColorIndex = 6 ' colours row yellow if something couldn't be found.
  Else
    Cells(y, x).Value = rw.Cells(3).Value
  End If
Next rw
End Sub

[MikeeRDX]

Thank you for you help! It looks good so let me give it a shot.

[MikeeRDX]

Hi P45Cal....It works awesomely. Is there a way I can set the table as a named range so that I don't have to click on it before I need to run the code? OR, can we add a code to "Activate" that table before the code to run the loop?

UPDATE: I placed the code to activate the table in front of the loop code and it worked.

THANK YOU SO MUCH p45cal.