i understand now where i went wrong, i needed to keep the first to cols of the databases as Date and Updated and i hadn't
Posts by LearningXL
-
-
sorry RoyUK, i understand your code now, many many many thanks for your help on this, i know this is now how you wanted me to do it but it works perfectly. thank you
*****SOLVED******
-
or do you mean like this
Code
Display MoreSet myTableRange = Union(Range("FA2:FF10000"), Range("BG2:BL10000"), Range("DH2:DK100000")) Select Case True 'Check if the changed cell is in the data tabe or not. Case Not Intersect(Target, myTableRange.Areas(3)) Is Nothing 'Column for the date/time Set myDateTimeRange = Cells(Target.Row, myTableRange.Areas(2).Columns(1).Column) Set myUpdatedRange = Cells(Target.Row, myTableRange.Areas(2).Columns(2).Column) '******Add Line here???? Case Not Intersect(Target, myTableRange.Areas(1)) Is Nothing Set myDateTimeRange = Cells(Target.Row, myTableRange.Areas(1).Columns(1).Column) Set myUpdatedRange = myDateTimeRange.Offset(, 4) '******Add Line here???? Case Else: Exit Sub -
Code
Display MoreSet myTableRange = Union(Range("FA2:FF10000"), Range("BG2:BL10000"), Range("DH2:DK100000")) Select Case True 'Check if the changed cell is in the data tabe or not. Case Not Intersect(Target, myTableRange.Areas(3)) Is Nothing 'Column for the date/time Set myDateTimeRange = Cells(Target.Row, myTableRange.Areas(2).Columns(1).Column) Set myUpdatedRange = Cells(Target.Row, myTableRange.Areas(2).Columns(2).Column) Case Not Intersect(Target, myTableRange.Areas(1)) Is Nothing Set myDateTimeRange = Cells(Target.Row, myTableRange.Areas(1).Columns(1).Column) Set myUpdatedRange = myDateTimeRange.Offset(, 4) Case Else: Exit Sub -
in what way did in not define ranges correctly?? please dont take that question the wrong way im just asking
-
Thank you RoyUk, it worked like a dream, however i have another question regarding this code. if i want to add another area for adding time added and Updated, is it this area only that i amend
Code
Display More'example adding ,Range("DH2:DK100000") on line below Set myTableRange = Union(Range("FA2:FF10000"), Range("BG2:BL10000")) Select Case True 'Check if the changed cell is in the data tabe or not. Case Not Intersect(Target, myTableRange.Areas(2)) Is Nothing '*********changing (2)) to (3)) 'Column for the date/time Set myDateTimeRange = Cells(Target.Row, myTableRange.Areas(2).Columns(1).Column) Set myUpdatedRange = Cells(Target.Row, myTableRange.Areas(2).Columns(2).Column) 'adding to above Set myDateTimeRange = Cells(Target.Row, myTableRange.Areas(3).Columns(1).Column) Set myUpdatedRange = Cells(Target.Row, myTableRange.Areas(3).Columns(3).Column) -
Thank you RoyUk for your help only that code updates both "FF" & "BH" at the same time, i would only like the ranges that are updated to be updated not both at the same time, they would need to be independant.
-
would it be possible to have some further help please
-
wow royuk, i was not being aggressive. you asked me question and i answered you. i can here for help on some code, i did not expect to have my hand slapped and sent to the corner
-
but thats not how i want it, i want them all on one sheet
-
that sheet is being used as a database sheet, so it has quite a few databases on it
-
here you go
-
Good morning everyone,
i have the following code but i would like to run this code for two different areas of the worksheet, ive tried different variations of this code but nothing worked so far.
Code
Display MorePrivate Sub Worksheet_Change(ByVal Target As Range) 'Timestamp Data Dim myTableRange As Range Dim myDateTimeRange As Range Dim myUpdatedRange As Range 'Your data table range Set myTableRange = Range("FB2:FD10000") '***Also what this range as well ("BI2:BL10000") 'Check if the changed cell is in the data tabe or not. If Intersect(Target, myTableRange) Is Nothing Then Exit Sub 'Stop events from running Application.EnableEvents = False 'Column for the date/time Set myDateTimeRange = Range("FA" & Target.Row) '***Also what this ("BG" & Target.Row) 'Column for last updated date/time Set myUpdatedRange = Range("FE" & Target.Row) '***Also what this ("BH" & Target.Row) 'Determine if the input date/time should change If myDateTimeRange.Value = "" Then myDateTimeRange.Value = Now End If 'Update the updated date/time value myUpdatedRange.Value = Now 'Turn events back on Application.EnableEvents = True End sub