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]

Hi, put some data example using XL2BB.
Note: If would necessary, pay attention to this post XL2BB 2 Square

 

[VBAProIWish]

Ok, I will try to do that. In the meantime, is there any way that you can get my existing code to work, even when there are large numbers instead of text?

Thanks

 

[hernantorres23]

You can try with those number, in text format

 

[VBAProIWish]

Yeah, I already tried that and it didn't work. XL2BB works great! See below...

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	BEFORE MACRO						AFTER MACRO
2	Customer Number	Date	Model Number	Size	Model Color		Customer Number	Date	Model Number	Size	Model Color
3	100-01	12/1/2019	Sky	Small			100-01	12/1/2019	Sky	Small	Blue
4	100-02			Medium			100-02			Medium	No Color
5	100-03	12/1/2019	C480000B9147-0009-08	Large			100-03	12/1/2019	C480000B9147-0009-08	Large	Yellow
6	100-04	12/11/2019		Large			100-04	12/11/2019		Large	No Color
7	100-05		Ground				100-05		Ground
8	100-06	12/1/2019	Hill	Small			100-06	12/1/2019	Hill	Small
9	100-07	12/1/2019					100-07	12/1/2019			No Color
10	100-08		Sky				100-08		Sky		Blue
(7)

Sorry if you saw my first picture, I forgot to delete the values in the "before" side!

 

[hernantorres23]

Now, I'm in my cellphone, after dinner I will check your example.
But, Which is the difference between two ranges (Before and After macro)?

 

[VBAProIWish]

Ahh, I guess I didn't make the changes fast enough! Sorry about that! I edited my original post to take the values away in the "before" section.

Thanks!

 

[hernantorres23]

Ok, I will check it now

 

[VBAProIWish]

I should have mentioned that I want the cells to have the words "Blue", "Yellow" and "No Color" as shown in my picture above and not color the cell. I just realized that it might be confusing.

Thanks to anyone that can help me figure this toughie out!

 

[DanteAmor]

Try this. I hope this is what you need.

Sub Populate_Cells()
  Dim c1 As Range, c2 As Range, c3 As Range, lr As Long, lc As Long, sh As Worksheet
 
  Application.ScreenUpdating = False
 
  Set sh = ActiveSheet
  Set c1 = sh.Rows(1).Find("Customer Number", , xlValues, xlWhole)
  Set c2 = sh.Rows(1).Find("Model Number", , xlValues, xlWhole)
  Set c3 = sh.Rows(1).Find("Model Color", , xlValues, xlWhole)
 
  If sh.AutoFilterMode Then sh.AutoFilterMode = False
  If Not c1 Is Nothing And Not c2 Is Nothing And Not c3 Is Nothing Then
    lr = sh.Cells(Rows.Count, c1.Column).End(xlUp).Row
    lc = sh.Cells(1, Columns.Count).End(xlToLeft).Column
    sh.Range("A1", sh.Cells(lr, lc)).AutoFilter Field:=c2.Column, Criteria1:="<>"
    sh.AutoFilter.Range.Range(sh.Cells(2, c3.Column), sh.Cells(lr, c3.Column)).SpecialCells(xlCellTypeVisible).Value = "Yellow"
    sh.Range("A1", sh.Cells(lr, lc)).AutoFilter Field:=c2.Column, Criteria1:="Sky"
    sh.AutoFilter.Range.Range(sh.Cells(2, c3.Column), sh.Cells(lr, c3.Column)).SpecialCells(xlCellTypeVisible).Value = "Blue"
    sh.Range("A1", sh.Cells(lr, lc)).AutoFilter Field:=c2.Column, Criteria1:="="
    sh.AutoFilter.Range.Range(sh.Cells(2, c3.Column), sh.Cells(lr, c3.Column)).SpecialCells(xlCellTypeVisible).Value = "No color"
    sh.ShowAllData
  Else
    If c1 Is Nothing Then MsgBox "Model color column does not exists"
    If c2 Is Nothing Then MsgBox "Model Number column does not exists"
    If c3 Is Nothing Then MsgBox "Customer Number column does not exists"
  End If
End Sub