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?

 

[tusharm]

Did you look at the link I posted? It showed how one can do just that.

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

 

[superbeast326]

I just need to know how the code was able to take the two names and put in the DV lists. I am a beginner in VBA. I would appreciate it, if you could explain to me the way the code works, in your own words. Please.

 

[superbeast326]

Please, I need an answer. I also want to know how to make the code workable in instances with more than two names.

 

[Peter_SSs]

I'll give some additional explanation on a specific question or two below but first make a couple of points.

Please, I need an answer.

1. This sounds like a demand. Everybody who helps in the forum is a volunteer who gives their time and expertise for nothing in return (apart from an appreciative poster - hopefully). If you come across as demanding or impatient then you will soon turn potential helpers away.

2. On your own admission, you are a beginner in vba. We have no problem helping beginners. We were all beginners once. However, you cannot expect the forum to give you a complete course on learning and understanding vba.


OK, let's look at a couple of issues.

A)

I just need to know how the code was able to take the two names and put in the DV lists.

You said you had things like "Name 1 vs Name 2" in the first range of cells. If you used F8 to step though the code and hover your cursor over variables as I suggested (or do so again now), when you get to this blue line of code ..

With c.Validation
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
        Operator:=xlBetween, Formula1:=DVvals

.. and hover over the variable DVvals you should see something like Name 1,Name 2
That is, the DV values you want for that cell.

So, how does DVvals get to be "Name 1,Name 2"?
Looking back through the code it shouldn't be too hard to imagine that it relates to these two lines

DVvals = c.Offset(rowdiff, coldiff).Value
DVvals = Replace(DVvals, " vs ", ",", 1, -1, vbTextCompare)

Stop the code and start F8 again and stop after the first of these has been executed (that is, the second one will be yellow) and hover over DVvals. You should find "Name 1 vs Name 2", the value from the corresponding (first) cell in the first range.

Now press F8 again and hover over DVvals again and you should find "Name 1,Name 2"

From that it is clear that the red value was changed into the green value by the second line of code above.
Replace takes the first argument (DVvals = "Name 1 vs Name 2", looks for the second argument (" vs ") and replaces it with the third argument (",")
If you want to know what the other arguments do, or more about Replace, then look in the vba Help, or search online.


B) You also asked about these two bits of code

If Rng1.Rows.Count = Rng2.Rows.Count And _
        Rng1.Columns.Count = Rng2.Columns.Count Then
 
 
 

If Len(DVvals) > 0 Then

These are both safety checks,

The first counts the rows and columns for each range. If they don't agree the rest of the code is not attempted. If the ranges were different shape/size, how could we use corresponding cells from one range to put DV in the other range?

The second (is not a very comprehensive safety check, but) counts how many characters in the value from the cell in the first range. Check out the Len function in the vba Help.

If there are characters we proceed but if there are no characters then we skip the DV section for that cell. After all, how could we split the two names if there are no names?
This check would have been better to check that the cell actually contained the text " vs ".


This code should work (try it) if there are more than two names in the cell, provided they are separated by " vs " which may be unlikely. Test with this in a cell
Name 1 vs Name 2 vs Name 3 vs Name 4

 

[superbeast326]

So that means, by simply separating the names with a comma, a range is created. I apologize sincerely for my demanding tone. I've have another few questions for you, if you don't mind.

1) Do I have to run the macro every time I open the workbook?

2) What is the purpose of the rng2.validation.delete code before the For loop.

3) How did the first DVvals retrieve the text from the first range?

 

[superbeast326]

I have already solved my third question. With that, I would like to express the greatest form of appreciation for your advocation of self-learning, which presented itself in the resourceful links that were provided to me, by the members of this forum whom, out of their kind-hearted nature, decided to help me with my lack of knowledge in Excel.

 

[superbeast326]

Does the "c" in For each c stand for case?

 

[Peter_SSs]

So that means, by simply separating the names with a comma, a range is created.

No, a range is not created. It is simply a list of text values separated by commas and such a list can be used in data validation.

1) Do I have to run the macro every time I open the workbook?

I have no idea. That would most likely depend on what else is happening in your workbook.

2) What is the purpose of the rng2.validation.delete code before the For loop.

Tidiness. Get rid of any old DV that might happen to be there before setting the new lot of DV

3) How did the first DVvals retrieve the text from the first range?

DV is set in each cell of Rng2 by grabbing the names fromn the corresponding cell in Rng1. This line does it
DVvals = c.Offset(rowdiff, coldiff).Value
If you want to know how, research "Offset" in the vba Help or "how to refer to cells relative to other cells". Also, look at the rowdiff and coldiff lines of code, look up the "row" and the "column" property of a range and step through with F8 and look at the rowdiff, coldiff and DVvals variables.

Does the "c" in For each c stand for case

No, c is a range

Dim Rng1 As Range, Rng2 As Range, c As Range

The following line tells us that c is being used to represent each cell in Rng2

For Each c In Rng2.Cells

 

[superbeast326]

Here is two more questions:

1) Can I write a macro that deletes itself after I run it?.

2) Can I subtract a range from a bigger range and declare that as range?

 

[superbeast326]

Thanks for your previous post.