Populate one cell based on another

[VBAProIWish]

Hello All,

I am looking for code to do the following...

1. Find the column (in row 1) named "Customer Number"
2. Find the column (in row 1) named "Model Number"
3. Find the column (in row 1) named "Model Color"
4. For every cell in the "Model Number" column named "Sky", populate the cell in the "Model Color" column (same row), "Blue".
5. For every cell in the "Model Number" column that is blank, populate the cell in the "Model Color" column (same row), "No Color".
6. For every cell in the "Model Number" column with any combination of ONLY numbers (whole numbers, integers), populate the cell in the "Model Color" column (same row), "Yellow".
7. There will be blanks in the "Model Number" column, but the search needs to stop at the last populated cell in the "Customer Number" column, where there are no blanks.


Putting it more simply...
If the cell in the "Model Number" column says "Sky", make the cell in the "Model Color" column, "Blue", etc...

Please note the following...
1. Everything is based on row 2, downward. (Row 1 is for Column Headers).

2. Due to my columns never being in the same place, I would like this to find columns by name and not by column position.

3. This needs to work on whatever number format the "Model Number" column is currently in. Text, numbers, accounting, etc.
My code below works fine with text, but not on long numbers like this C480000B9147-0009-08.
It also isn't programmed by column header name.

4. The code needs to be cAsE INsENTivE. Sometimes in my lines of code, "Sky" for example, works, but "SKY" or "sky" doesn't work.


This code only works by column position and doesn't work if there are long numbers in column D

Sub Populate_Dynamically
Dim lastRow As Long, icell As Long
lastRow = Range("C" & Rows.Count).End(xlUp).Row
For icell = 1 To lastRow
If Range("D" & icell).Value = "Sky" Then
Range("E" & icell).Value = "Blue"
End If
Next icell
End Sub

Thanks much to anyone that can help me with this

 

[hernantorres23]

Try this, you can interchange the columns

Book1
	A	B	C	D	E
1	Customer Number	Date	Model Number	Size	Model Color
2	100-01	12/01/2019	Sky	Small	Blue
3	100-02			Medium	No color
4	100-03	12/01/2019	C480000B9147-0009-08	Large	--Check--
5	100-04	12/11/2019		Large	No color
6	100-05		Ground		You don't tell me nothing
7	100-06	12/01/2019	Hill	Small	You don't tell me nothing
8	100-07	12/01/2019			No color
9	100-08		Sky		Blue
Sheet1

Option Explicit
'by Hernan Torres (Mikel ERP)
'December 22, 2019
'Refer to Sheet1
'Question by VBAProIWish
'topic: https://www.mrexcel.com/board/threads/populate-one-cell-based-on-another.1118613/#post-5392628

Sub Populate_Dynamically()
Dim lastCol As Long
Dim colF, colT As Integer 'Column Find, Target
Dim colL As String 'Column Letter (know the last column letter)
Dim rangeF As Range 'range to find Column match
Dim b() As Variant
Dim r As Long

Application.ScreenUpdating = False
'Run ("check_dictionary") 'good idea, but only if you want it
Sheets("Sheet1").Activate
lastCol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column
Set rangeF = Range("A1:" & Cells(1, lastCol).Address) 'I prefer set a range to will use in second argument of Match in next line
colF = Application.Match("Model Number", rangeF, 0)
colT = Application.Match("Model Color", rangeF, 0)
colL = Split(Cells(1, lastCol).Address, "$")(1)
b = Sheets("Sheet1").Range("A2:" & colL & Range(Split(Cells(1, colF).Address, "$")(1) & Rows.Count).End(xlUp).Row).Value2

For r = 1 To UBound(b)
Select Case b(r, colF) 'if you have several values to check, I would prefere use a dictionary
Case "Sky"
Cells(r + 1, colT) = "Blue"
Case IsNull(b(r, colF))
Cells(r + 1, colT) = "No color"
Case IsNumeric(b(r, colF))
Cells(r + 1, colT) = "Yellow"
Case "Ground"
Cells(r + 1, colT) = "You don't tell me nothing"
Case "Hill"
Cells(r + 1, colT) = "You don't tell me nothing"
Case Else   'you could avoid this else statement, building a dictionary
            'or put here cases like "C480000B9147-0009-08"
Cells(r + 1, colT) = "--Check--"
End Select
Next r
Application.ScreenUpdating = True
End Sub

 

[DanteAmor]

6. For every cell in the "Model Number" column with any combination of ONLY numbers (whole numbers, integers), populate the cell in the "Model Color" column (same row), "Yellow".

3. This needs to work on whatever number format the "Model Number" column is currently in. Text, numbers, accounting, etc.
My code below works fine with text, but not on long numbers like this C480000B9147-0009-08.
It also isn't programmed by column header name.

I have doubt in the data that you put as an example: C480000B9147-0009-08.
Do you have it stored as a number in the cell? or is it stored as text?
The requirement would be any data that contains a number within the text?

 

[VBAProIWish]

DanteAmor,

All I can say is WWwwoowwWW!

This is exactly what I was looking for!

Thank you so very much!

Wow, coding is so much fun.

Thanks again!

 

[VBAProIWish]

Hernan, I just saw your code after I saw Dante's code and while Dante's code look a bit more user friendly for newbies like myself, I absolutely love the detailed explanations that you give after many of the lines. I will study this and know that I will learn from your "Laymen's Term" explanations.

Thank you so much!

 

[hernantorres23]

No problem, both codes are get same result, in my case you can apply one worksheet with key words (Dictionary) for add color, column names, that was my focus.
Here an full example with both suggestion

 

[VBAProIWish]

Thanks very much hernan

 

[DanteAmor]

DanteAmor,

All I can say is WWwwoowwWW!

This is exactly what I was looking for!

Thank you so very much!

Wow, coding is so much fun.

Thanks again!

Im glad to help you. Thanks for the feedback and merry christmas!