Excel VBA - Change Cell Contents Based On Dynamic Cell Range

[jski21]

Good day Mr. Excel Team,

I have a dynamic database that grows as new information is added. The data entry folks don't understand the need for data consistency and I continually struggle with different cell contents for the same category of accounts. Seeking a way through VBA to change the contents of the name in Column D based on the content of what is in a cell in Column A. Example:

All_Balances_by_Major_Program (4).xlsx
	A	B	C	D
1	AcctNo	Major Program	Major Program Name	Name
2	19619001	19619	Sample 1	Sample 1 - 2024
Grant Balances

If the cell in Column A = 19619001 (A2), then the content in Column D of corresponding cell in that row for that entry (D2) would be changed to "Sample 1 - 2024".

The example above is just one instance. Unfortunately, I have several different instances of this for values in Column A so if it could cycle through the various instances that would be ideal. For example, in addition to the example above, the next instance might be:

If the cell in Column A = 123456078 (A2), then the content in Column D of corresponding cell in that row for that entry (D2) would be changed to "Sample 2 - 2025".


Hope this explanation is satisfactory. I hate data inconsistency.


Thanks all,

jski

 

[Jeffrey Mahoney]

You have not provided an explanation of how you get the descriptions from those numbers.

 

[jski21]

You have not provided an explanation of how you get the descriptions from those numbers.

Thanks for the reply. The description in D2 is what I'd like to to be and not tied to any other source/database (yet). Loosely, I think the code might follow something like this?

For i = 2 To LastRow
If Range("A" & i).Value = "19619001" Then
Range("D" & i).Value = "Sample 1 - 2024"
End If

 

[Jeffrey Mahoney]

Why don't you create a table and use XLOOKUP in Column D? It seems that you could new items to an Excel table and it would update automatically. Otherwise you're adding new code every time you have a new value.

 

[jski21]

Why don't you create a table and use XLOOKUP in Column D? It seems that you could new items to an Excel table and it would update automatically. Otherwise you're adding new code every time you have a new value.

I've considered that but the changes are only a good-sized handful of historical data. I've somewhat got a handle on the new stuff coming in...turnover makes it challenging. Appreciate the consideration and advice. Thanks.