Test

[Demosassiner]

To be honest, I don't even know how to explain this fully in words, hoping the table will illustrate the dilemma.

I have a data validation list in column F, with the appropriate selection made in cell E2 e.g. "white" in table 1.


Table 1

	A	B	C	D	E	F
Micro	MICR	Data Validation List
Apple123	AAPL	White	Black
Fbook1	FBBC	White
Fbuckeyd	FACE	Both

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

I need to populate column A with the appropriate selection from the data validation list, which is "white" as shown in table 2.

Table 2

	A	B	C	D	E	F
White	Micro	MICR	White	Black
White	Apple123	AAPL	White
White	Fbook1	FBBC	Both
White	Fbuckeyd	FACE

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]8[/TD]

[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

I need to use a script to say that, if "both" is selected, populate "black" AND "white" in table A by copy-pasting the B and C selection downwards, and then proceeding to add the terms to the A column such as is shown below.

Table 3

	A	B	C	D	E	F
Black	Micro	MICR	Both	Black
Black	Apple123	AAPL	White
Black	Fbook1	FBBC	Both
Black	Fbuckeyd	FACE
White	Black	MICR
White	Apple123	AAPL
White	Fbook1	FBBC
White	Fbuckeyd	FACE

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]14[/TD]

[TD="align: right"][/TD]

[TD="align: center"]15[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Hope this is somewhat clear.

 

[Demosassiner]

OMG I accidentally posted this before I was finished and I can't even find how to edit or delete the thread..

 

[Peter_SSs]

.. and I can't even find how to edit or delete the thread..

You cannot delete a thread. Once you have been around for a while and established yourself as a legitimate forum member (that is, not a spammer) you will have 10 minutes after posting to edit it. Once you are eligible, an "Edit Post" should appear at the bottom right of your post for 10 minutes.

You could try this on a copy of your workbook.
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test by selecting a value in E2.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("E2")) Is Nothing Then
    Application.EnableEvents = False
    Select Case Range("E2").Value
      Case ""
      Case "Both"
        With Range("B1", Range("C" & Rows.Count).End(xlUp))
          .Offset(, -1).Resize(, 1).Value = Range("F2").Value
          .Offset(.Rows.Count, -1).Resize(, 1).Value = Range("F3").Value
          .Offset(.Rows.Count).Value = .Value
        End With
      Case Else
        Range("A1:A" & Range("B" & Rows.Count).End(xlUp).Row).Value = Range("E2").Value
    End Select
    Application.EnableEvents = True
  End If
End Sub