Question about dates.

[superbeast326]

I have two columns, A and B. Both have 50 rows. Column A is filled entirely with "No". What I want is that the moment I switch a row in Column A to "Yes". I want the row in Column B to store the date that the change was made. Is this possible?

 

[superbeast326]

This is in another worksheet. I have two ranges C3:E52 and C56:E105. In the first range, I have text in the format, [Name 1] vs [Name 2], in every cell. What I want is for the cells in the second range to be populated with DV lists with each list containing the [Name 1] and [Name 2] in their corresponding cell in the first range.

Which means DV List in C56 contains [Name 1] and [Name 2] in C3, and so on and so forth.

 

[Peter_SSs]

So you have 150 cells (C3:E52) each with 2 names separated by " vs " and you want to create Data Validation in C56:E105 with drop-down lists containing those 300 names? Or is it just 2 names for cell C56, 2 names for C57 etc

 

[superbeast326]

I want the two names that appear in C3 to be in C56, C4 to be in C57, so on and so forth.

 

[Peter_SSs]

This seems to work for a small 2-cell test area I used so you should be able to adapt it to your layout.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> DV()<br>    <SPAN style="color:#00007F">Dim</SPAN> Rng1 <SPAN style="color:#00007F">As</SPAN> Range, Rng2 <SPAN style="color:#00007F">As</SPAN> Range, c <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> rowdiff <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, coldiff <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> DVvals <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <br>    <SPAN style="color:#00007F">Set</SPAN> Rng1 = Range("C3:C4")   <SPAN style="color:#007F00">'<- Change to suit</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> Rng2 = Range("F5:F6")   <SPAN style="color:#007F00">'<- Change to suit</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> Rng1.Rows.Count = Rng2.Rows.Count And _<br>            Rng1.Columns.Count = Rng2.Columns.Count <SPAN style="color:#00007F">Then</SPAN><br>        rowdiff = Rng1.Row - Rng2.Row<br>        coldiff = Rng1.Column - Rng2.Column<br>        Rng2.Validation.Delete<br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Rng2.Cells<br>            DVvals = c.Offset(rowdiff, coldiff).Value<br>            DVvals = Replace(DVvals, " vs ", ",", 1, -1, vbTextCompare)<br>            <SPAN style="color:#00007F">If</SPAN> Len(DVvals) > 0 <SPAN style="color:#00007F">Then</SPAN><br>                <SPAN style="color:#00007F">With</SPAN> c.Validation<br>                    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _<br>                        Operator:=xlBetween, Formula1:=DVvals<br>                    .IgnoreBlank = <SPAN style="color:#00007F">True</SPAN><br>                    .InCellDropdown = <SPAN style="color:#00007F">True</SPAN><br>                    .InputTitle = ""<br>                    .ErrorTitle = ""<br>                    .InputMessage = ""<br>                    .ErrorMessage = ""<br>                    .ShowInput = <SPAN style="color:#00007F">True</SPAN><br>                    .ShowError = <SPAN style="color:#00007F">True</SPAN><br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> c<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[superbeast326]

Thee questions,
1. Where should I put this code?

2. Which of my ranges should I set as rng1 and rng2?

3. Could you please explain to me what each line of the code mean?

 

[Peter_SSs]

Thee questions,
1. Where should I put this code?

2. Which of my ranges should I set as rng1 and rng2?

3. Could you please explain to me what each line of the code mean?

1. In a standard module (Insert|Module)

2. Read the code, even if you don't fully understand it, there's some good hints in there. Otherwise, do a test in a dummy worksheet and see.

3. Sorry, I think I've just about put enough time into this thread and it's nearly midnight here. You can step through the code a line at a time with F8, and hover over variables to see their value or swith to the worksheet to see what the line of code did, look in the Help etc.

 

[superbeast326]

Thank you for your time.

 

[superbeast326]

However, using of F8 does not aid me in understanding it, could you please take the time to explain to me, what each line means? I really need an understanding.

 

[superbeast326]

Also, how can I code a macro that allows me to reset the DV lists so that all the cells in the worksheet, with the DV lists will become blank, thus allowing me a fresh start.

This is so that I can keep testing and resetting as I am developing the workbook.

 

[superbeast326]

Help in understanding VBA Code in another thread

Before I begin, I would like to extend my gratitude to Peter_SSs, for his time and patience in answering my questions in the thread titled, "Question about Dates".

In another thread, created by me, he gave me a VBA Code to help me with a problem. The only problem is that, I do not understand why the code works the way it does. Here is the code:

[FONT=Courier][COLOR=#00007F]Sub[/COLOR] DV()
    [COLOR=#00007F]Dim[/COLOR] Rng1 [COLOR=#00007F]As[/COLOR] Range, Rng2 [COLOR=#00007F]As[/COLOR] Range, c [COLOR=#00007F]As[/COLOR] Range
    [COLOR=#00007F]Dim[/COLOR] rowdiff [COLOR=#00007F]As[/COLOR] [COLOR=#00007F]Long[/COLOR], coldiff [COLOR=#00007F]As[/COLOR] [COLOR=#00007F]Long[/COLOR]
    [COLOR=#00007F]Dim[/COLOR] DVvals [COLOR=#00007F]As[/COLOR] [COLOR=#00007F]String[/COLOR]
    
    [COLOR=#00007F]Set[/COLOR] Rng1 = Range("C3:C4")   [COLOR=#007F00]'<- Change to suit[/COLOR]
    [COLOR=#00007F]Set[/COLOR] Rng2 = Range("F5:F6")   [COLOR=#007F00]'<- Change to suit[/COLOR]
    [COLOR=#00007F]If[/COLOR] Rng1.Rows.Count = Rng2.Rows.Count And _
            Rng1.Columns.Count = Rng2.Columns.Count [COLOR=#00007F]Then[/COLOR]
        rowdiff = Rng1.Row - Rng2.Row
        coldiff = Rng1.Column - Rng2.Column
        Rng2.Validation.Delete
        [COLOR=#00007F]For[/COLOR] [COLOR=#00007F]Each[/COLOR] c [COLOR=#00007F]In[/COLOR] Rng2.Cells
            DVvals = c.Offset(rowdiff, coldiff).Value
            DVvals = Replace(DVvals, " vs ", ",", 1, -1, vbTextCompare)
            [COLOR=#00007F]If[/COLOR] Len(DVvals) > 0 [COLOR=#00007F]Then[/COLOR]
                [COLOR=#00007F]With[/COLOR] c.Validation
                    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
                        Operator:=xlBetween, Formula1:=DVvals
                    .IgnoreBlank = [COLOR=#00007F]True[/COLOR]
                    .InCellDropdown = [COLOR=#00007F]True[/COLOR]
                    .InputTitle = ""
                    .ErrorTitle = ""
                    .InputMessage = ""
                    .ErrorMessage = ""
                    .ShowInput = [COLOR=#00007F]True[/COLOR]
                    .ShowError = [COLOR=#00007F]True[/COLOR]
                [COLOR=#00007F]End[/COLOR] [COLOR=#00007F]With[/COLOR]
            [COLOR=#00007F]End[/COLOR] [COLOR=#00007F]If[/COLOR]
        [COLOR=#00007F]Next[/COLOR] c
    [COLOR=#00007F]End[/COLOR] [COLOR=#00007F]If[/COLOR]
[COLOR=#00007F]End[/COLOR] [COLOR=#00007F]Sub
[COLOR=black]
[/COLOR][/COLOR][/FONT]

Could someone please take the time to explain to me the above code, line by line.