Hi @offthelip . Sorry to bother you again, but I took your previous code and tried to apply it to this new spreadsheet.
It extracted the date from UPDATES Sheet, column T UPDATE NOTES and inserted the date under column D (instead of G) in DATA sheet. Not sure why.
It did not copy the UPDATES Sheet, column "T" "UPDATE NOTES" to DATA sheet column "I" "Update Notes".
Since, I am relatively new to VBA, I am not very good at changing your code to work with this new workbook. Please help. Thank you.
DATA Sheet:
UPDATES Sheet:
CRITERIA:
Update "DATA" sheet (master sheet) based on updates from "UPDATES" sheet. Relevant columns have been highlighted yellow.
1: "DATA" sheet columns A to D contain the master data that needs to be updated from updates contained on "UPDATES" sheet
2: Updates are held on "UPDATES" sheet , column F is PET # which is used to match the PET # on DATA sheet, Column X is Status and Column T is a string that contains a date
3: Updates to "DATA" sheet are to be made from "UPDATES" sheet for every row. In the event that column X contains "ADOPTED", then the date string will be extracted from column T to be entered in "DATA" sheet column D and update Column C with the status from Column X. If column X contains anything else, such as fostering or pending, then no date is required to be updated on "DATA" sheet column D, just the status "DATA" sheet column C needs to be updated.)
4: If column T contains "Pending" then the status on "DATA" sheet is updated to "Pending" but the "Date Received" column ( col D) is not changed
5: If Column T contains "ADOPTED" then the text in column T is inspected to find the first date in the string and this date is used to update the Date Received on "DATA" sheet COLUMN "D" as well as the status for COLUMN "D"
It extracted the date from UPDATES Sheet, column T UPDATE NOTES and inserted the date under column D (instead of G) in DATA sheet. Not sure why.
It did not copy the UPDATES Sheet, column "T" "UPDATE NOTES" to DATA sheet column "I" "Update Notes".
Since, I am relatively new to VBA, I am not very good at changing your code to work with this new workbook. Please help. Thank you.
DATA Sheet:
Pet # | Date Arived | Status | Flight | DETAILS | Breed | Date Adopted | Time | Update Notes |
123456 | 1-Jul | Adopted | alpha | All Dogs are good boys 1 | pug | |||
123457 | 2-Jul | Adopted | beta | All Dogs are good boys 2 | pit | |||
123458 | 3-Jul | Adopted | delta | All Dogs are good boys 3 | mixed | |||
123459 | 4-Jul | Adopted | alpha | All Dogs are good boys 4 | chihuahua | |||
123460 | 5-Jul | Adopted | beta | All Dogs are good boys 5 | mastif | |||
123461 | 6-Jul | Adopted | delta | All Dogs are good boys 6 | poodle | |||
UPDATES Sheet:
AAA | BBB | BBB | DDD | EEE | Pet # | GGG | RRR | SSS | mambo | UUU | VVV | WWW | mouse | YYY | ZZZ | Update Notes | Status | ||||||
10000 | 11 | DO | 123456 | alpha | 07/12/2020 Adopted | Adopted | |||||||||||||||||
10001 | 11 | RE | 123457 | beta | 07/29/2020 Adopted | Adopted | |||||||||||||||||
10002 | 11 | ME | 123458 | delta | 07/12/2020 Adopted | Adopted | |||||||||||||||||
10003 | 11 | FA | 123459 | theta | 07/22/2020 Adopted | Adopted | |||||||||||||||||
10004 | 11 | SO | 123460 | gamma | 07/22/2020 Adopted | Adopted | |||||||||||||||||
10005 | 11 | LA | 123461 | alpha | 07/22/2020 Adopted | Adopted | |||||||||||||||||
10006 | 11 | TI | 123464 | beta | 07/12/2020 Housecheck completed | pending | |||||||||||||||||
10007 | 11 | DO | 123465 | delta | 07/29/2020 Housecheck completed | pending | |||||||||||||||||
10008 | 11 | SO | 123466 | theta | 07/12/2020 Housecheck completed | pending | |||||||||||||||||
10009 | 11 | FA | 123467 | gamma | 07/22/2020 Housecheck completed | pending | |||||||||||||||||
10010 | 11 | ME | 123468 | pi | 07/22/2020 Housecheck completed | pending | |||||||||||||||||
07/22/2020 Housecheck completed | pending | ||||||||||||||||||||||
CRITERIA:
Update "DATA" sheet (master sheet) based on updates from "UPDATES" sheet. Relevant columns have been highlighted yellow.
1: "DATA" sheet columns A to D contain the master data that needs to be updated from updates contained on "UPDATES" sheet
2: Updates are held on "UPDATES" sheet , column F is PET # which is used to match the PET # on DATA sheet, Column X is Status and Column T is a string that contains a date
3: Updates to "DATA" sheet are to be made from "UPDATES" sheet for every row. In the event that column X contains "ADOPTED", then the date string will be extracted from column T to be entered in "DATA" sheet column D and update Column C with the status from Column X. If column X contains anything else, such as fostering or pending, then no date is required to be updated on "DATA" sheet column D, just the status "DATA" sheet column C needs to be updated.)
4: If column T contains "Pending" then the status on "DATA" sheet is updated to "Pending" but the "Date Received" column ( col D) is not changed
5: If Column T contains "ADOPTED" then the text in column T is inspected to find the first date in the string and this date is used to update the Date Received on "DATA" sheet COLUMN "D" as well as the status for COLUMN "D"
VBA Code:
Sub UPDATE()
Dim fullstr As String
With Worksheets("UPDATES")
lastup = .Cells(Rows.Count, "F").End(xlUp).Row ' find last row in column F of sheet 2
updt = Range(.Cells(1, 1), .Cells(lastup, 24)) ' pick columns A to X and all rows in sheet 2
End With
Worksheets("DATA").Select ' lots of people say don't use select but doing it once is quick and easy!!
lastmast = Cells(Rows.Count, "A").End(xlUp).Row
mastarr = Range(Cells(1, 1), Cells(lastmast, 4))
For i = 2 To lastmast
For j = 2 To lastup
If mastarr(i, 1) = updt(j, 6) Then ' what column is this?
mastarr(i, 3) = updt(j, 24) ' update status for all rows
' convert update status to upper case for comparison
sts = StrConv(updt(j, 24), vbUpperCase)
If sts = "ADOPTED" Then
fullstr = updt(j, 20) ' Column T
startstr = -1
endstr = Len(fullstr)
For kk = 1 To Len(fullstr)
digt = Mid(fullstr, kk, 1)
If IsNumeric(digt) And startstr < 0 Then ' check for the first number in the string
startstr = kk ' set this to start of the string
End If
digasc = Asc(digt) ' convert the curent character to ascii
If startstr > 0 And (digasc > 57 Or digasc < 47) Then ' this checks whether the digit is a number or /
endstr = kk ' set then end of the string as the first character which isn't a number or a slash
Exit For
End If
Last edited: