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?

 

[Peter_SSs]

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?

1. Not an area I am strong at. Googling "excel vba deletes itself" returned lots of links.

2. Could you provide an example to demonstrate just what you mean?

 

[superbeast326]

For number 2, I mean D51:E52 taken out out C3:E52 = new Range. Is this possible. The reason I want to this is that in C3:E52, all the [Name 1 vs Name 2]s are generated using a formula. However, the two names in each of the cells in D51:E52, are going to change based on a ranking system. I would like to run a macro that deletes itself, that converts the cells that are not in the subrange, D51:E52, into its value. I have actually found a code for a macro that deletes the macro that it is housed in. I am going to add that macro to the end of the DV macro code. After that, I am going to modify the code so that it will only on the last four cells.

For that, I am going to check if that particular cell in rng2, has a text in it. If it does, then it would go to next cell and do the DV for that cell. I am going to add this under worksheet_calculate. And then under worksheet_range. I am going to just use the locking and c.validation.delete for the cells in rng2.

I apologize for the long wall of text. I am taking this opportunity to run my logic by you and would appreciate it, if you could give your comments.

 

[superbeast326]

The only thing that is stopping me from doing so is that I have no idea on how to check if a particular cell has a String or not.

 

[superbeast326]

Here is the above-mentioned macro code

[B]Sub DeleteThisModule()[/B]  Dim vbCom As Object      MsgBox "Hi, I will delete myself "     Set vbCom = Application.VBE.ActiveVBProject.VBComponents      vbCom.Remove VBComponent:= _     vbCom.Item("Module1")  [B]End Sub

[/B]</pre>

 

[superbeast326]

By worksheet_range, I mistakenly meant worksheet_change.

 

[Peter_SSs]

For number 2, I mean D51:E52 taken out out C3:E52 = new Range. Is this possible.

You could just do this

Dim New_Range As Range

Set New_Range = Range("C3:E50,C51:C52")

The only thing that is stopping me from doing so is that I have no idea on how to check if a particular cell has a String or not.

Perhaps you haven't been reading my answers to your previous questions.

I believe I covered that in answering one of your questions in post #64

 

[superbeast326]

will this statement work:

If c.value=1 then

 

[superbeast326]

Come to think of it, it does not make sense.

 

[Peter_SSs]

will this statement work:

If c.value=1 then

It will test if the number 1 is in the cell and if so it will execute the following code.

 

[superbeast326]

So if I only want the code to work in empty cells, I can use the following statement right?

if len(DVvals) > 1 and len(c.value)=0 then