insert date based on cell date

[Trevor3007]

good afternoon,

i am struggling to solve my issue & hope some 'code wizard' can solve?

My issue is , if cell a2 > 0, then insert todays date. So i need a formula for that and if I use Now() the date will change all applicable cells the following day . I need the original date to stay etc.

thank you for your help & for your time today.

KR
Trevor3007

 

[Trevor3007]

I am thinking that you are probably leaning towards the second option listed above, but it needs to be defined more clearly, exactly:
​

- what is changing - the date will be inserted into B2:B200
- how is it changing - when the criteria is met (ie a Y in testdata cells within g2:g200 range) a2:a200 range (results) triggers range b2:b200 (results) to insert date
- when it it changing as above​

- What is in columns A and G on your testdata sheet (more formulas or hard-coded values)?

There is some VB within testdate, when a Y is placed into 2 of the applicable cells it generates a message to state 'only 1 Y allowed in range.

- How are those values in columns A and G on your testdata sheet being changed?

by inserting a Y
- When are those values in columns A and G on your testdata sheet being changed?

when Y is placed with in the range g2:g200

- Is this date/time that you want updated ONLY in row 2, or are there multiple rows of data?
the range is a2:a200 + b2:b200 in results

- If there are multiple rows of data, is the data from row 3 pulling from row 3 of your testdata sheet, and row 4 pulling from row 4 of your testdata sheet, etc?​

as above

sorry for any incon etc & thank you.

 

[Joe4]

It sounds like you are manually entering "Y"s in cells G2:G200 on the "testdata" sheet. Is that correct?

If so, it sounds like when you enter a "Y" in column G, that you want column B on your other sheet to be automatically filled with the current date/time.
So, for example, you enter a "Y" in cell G14 on your testdata sheet, then cell B14 on your other sheet should be updated with the current date/time. Is that correct?

If all that is correct, the only other thing I would need to know is the name of this "other" sheet, so I can write the VBA code to update it.

 

[Trevor3007]

hi

It sounds like you are manually entering "Y"s in cells G2:G200 on the "testdata" sheet. Is that correct?​

Yes

If so, it sounds like when you enter a "Y" in column G, that you want column B on your other sheet to be automatically filled with the current date/time. ​

Yes

So, for example, you enter a "Y" in cell G14 on your testdata sheet, then cell B14 on your other sheet should be updated with the current date/time. Is that correct?​

yes

If all that is correct, the only other thing I would need to know is the name of this "other" sheet, so I can write the VBA code to update it.

results is the worksheet that requires the VB


fingers crossed
_{^{<strike>
</strike>}}​

 

[Joe4]

OK, go to your "testdata" sheet, right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this VBA code in the resulting VB Editor window:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    
'   Check to see if any cells updated in range G2:G200 on "testdata" sheet
    Set rng = Intersect(Target, Range("G2:G200"))
    
'   Exit if no cells updated in that range
    If rng Is Nothing Then Exit Sub
    
'   Loop through updated cells
    For Each cell In rng
'       If "Y" entered in column G, ...
        If UCase(cell) = "Y" Then
'           then update column B of same row on "result" sheet
            Sheets("results").Cells(Target.Row, "B") = Now()
        End If
    Next cell
    
End Sub

Now, test it out. As you enter "Y"s in column G of your "testdata" sheet, it will automatically update column B of your "results" sheet with the date/time stamp.

 

[Trevor3007]

thought we were on a winner there :{

I already have some VB in there & is also called Private Sub Worksheet_Change(ByVal Target As Range). when I run yours , error message :-

compile error - ambiguous name detected : worksheet_change
what now ??

 

[Joe4]

You cannot have two different procedures in the same module with the same name. Otherwise, when it calls it to run by name, it wouldn't know which one to run.
What we need to do is combine the code from both into one.

If you need help in doing that, please post the code you currently have with this name.

 

[Trevor3007]

Private Sub Worksheet_Change(ByVal Target As Range)


ActiveSheet.Unprotect


Dim rng As Range
Dim rng2 As Range
Dim cell As Range
Dim r As Long
Dim c As Long

Set rng = Intersect(Target, Range("E2:H200"))

' Exit sub if no cells updated in range
If rng Is Nothing Then Exit Sub

Application.EnableEvents = False

' Loop through updated cells in range
For Each cell In rng
' Get row and column number of updated cell
r = Target.Row
c = Target.Column
' Count how many cells have "Y" in current row
Set rng2 = Range("E" & r & ":H" & r)
If Application.WorksheetFunction.CountIf(rng2, "Y") > 1 Then
' Clear entry
cell.ClearContents
' Return message
MsgBox "You can put one Y in cell range E-H " & cell.Address(0, 0), vbOKOnly, "ERROR!"
ElseIf Application.WorksheetFunction.CountIf(rng2, "Y") = 1 Then
' See which column was updated and make appropriate adjustments
If LCase(cell) = "y" Then
Select Case c
' What to do if column E updated to "y"
Case 5
'enter any desired code here
' What to do if column F updated to "y"
Case 6
'enter any desired code here
' What to do if column G updated to "y"
Case 7
'enter any desired code here
' What to do if column H updated to "y"

Case 8
With Cells(r, "B")
.NumberFormat = "dd/mm/yyyy"
.Value = Date
End With
End Select
End If
Else
Cells(r, "B").Value = ""
End If
Next cell

Application.EnableEvents = True

ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False, AllowFormattingCells:=True

End Sub


so sorry for this additional incon to you.
all fingers / eyes / legs etc crossed .

 

[Trevor3007]

FYI

i removed my code and replaced with yours and although no errors, it does not insert the date into b2 (results)

 

[Joe4]

Can you re-post your code using Code Tags, as described here: https://www.mrexcel.com/forum/board-announcements/515787-how-post-your-vba-code.html

It maintains all the spacing and indenting your code, and makes it much easier to read and work with, since I will be combining the two codes.

 

[Joe4]

i removed my code wand replaced with yours and although no errors, it does not insert the date into b2 (results)

Did you manually type a "Y" in cell G2 on your "testdata" page to test it?