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]

Re: Help in understanding VBA Code in another thread

I really need help in understanding the code.

 

[tusharm]

Re: Help in understanding VBA Code in another thread

It's good that you want to understand the code. Too many just take the fish without showing any interest in learning how to fish. {grin}

Take the time to figure this out for yourself and you will also learn how to write code and if not write better code. {smile}

Understand code
http://www.tushar-mehta.com/excel/vba/vba-Understand code.htm

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.

 

[superbeast326]

Re: Help in understanding VBA Code in another thread

Could you please explain to me the purpose of the following line of codes:
1.

[FONT=Courier]   [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

and

[/FONT]
[FONT=Courier]  [COLOR=#00007F]If[/COLOR] Len(DVvals) > 0 [COLOR=#00007F]Then[/COLOR][/FONT]

 

[Joe4]

I merged your two threads together, as we do not want to start a new thread on the same topic. Threads of a duplicate nature are typically locked or deleted, per forum rules. You want to continue on in the same thread, since it is in reference to the question you had answered in the other thread.

 

[superbeast326]

Thank you, Joe4, for doing that for me.

 

[superbeast326]

By the way, this module can work in Worksheet_Calculate right?

 

[superbeast326]

Also, is it possible for me to declare ranges as public variables and use it in the modules as well?

 

[superbeast326]

Can someone please help me understand the code, every time I get close to understanding, I end up at the starting point again. I need to understand the logic

 

[tusharm]

What do you want help with?

In one of your earlier posts you indicated you wanted an explanation of the only 2 If statements in the code.

Is that what you want help with? An If statement? If so, you should start by searching Excel VBA help for 'If Then Else' (w/o the quotes).

Or do you want help with something specifically within the If statement? if so, what?

Can someone please help me understand the code, every time I get close to understanding, I end up at the starting point again. I need to understand the logic

 

[superbeast326]

I need help in understanding the code, from start to end.