VBA Code not working Properly

NeoSez

Board Regular
Joined
Aug 14, 2020
Messages
243
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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:
Pet #Date ArivedStatusFlightDETAILSBreedDate AdoptedTimeUpdate Notes
1234561-JulAdoptedalphaAll Dogs are good boys 1pug
1234572-JulAdoptedbetaAll Dogs are good boys 2pit
1234583-JulAdopteddeltaAll Dogs are good boys 3mixed
1234594-JulAdoptedalphaAll Dogs are good boys 4chihuahua
1234605-JulAdoptedbetaAll Dogs are good boys 5mastif
1234616-JulAdopteddeltaAll Dogs are good boys 6poodle


UPDATES Sheet:
AAABBBBBBDDDEEEPet #GGGRRRSSSmamboUUUVVVWWWmouseYYYZZZUpdate NotesStatus
1000011DO123456alpha07/12/2020 AdoptedAdopted
1000111RE123457beta07/29/2020 AdoptedAdopted
1000211ME123458delta07/12/2020 AdoptedAdopted
1000311FA123459theta07/22/2020 AdoptedAdopted
1000411SO123460gamma07/22/2020 AdoptedAdopted
1000511LA123461alpha07/22/2020 AdoptedAdopted
1000611TI123464beta07/12/2020 Housecheck completedpending
1000711DO123465delta07/29/2020 Housecheck completedpending
1000811SO123466theta07/12/2020 Housecheck completedpending
1000911FA123467gamma07/22/2020 Housecheck completedpending
1001011ME123468pi07/22/2020 Housecheck completedpending
07/22/2020 Housecheck completedpending



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:

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
I am not sure what you want, and you are not making it easy!!
Going back to the original thread where I produced this code for you it is very clear in the requirements that the date would be written into column D , so why are you unsure about that?? it is what the code does!!! the original code only updates 4 columns A to D as per the original requirements!
your comments 3 and 4 above seem to say You DO want the date in column D.
Can I suggest you write a CLEAR and CONSISTENT set of requirements on the lines of my post #7 in this thread of yours:
Macro to find match and Update neighbouring cell(s)
There is no point in me modifying any code until you are clear in your mind as to what you want and have expressed clearly so that I can understand it.
Finally you have not posted all of the code if you look at the code you can see that the i and the j loops have not got "next i or j" so quite a few lines a missing.
 
Upvote 0
I am not sure what you want, and you are not making it easy!!
Going back to the original thread where I produced this code for you it is very clear in the requirements that the date would be written into column D , so why are you unsure about that?? it is what the code does!!! the original code only updates 4 columns A to D as per the original requirements!
your comments 3 and 4 above seem to say You DO want the date in column D.
Can I suggest you write a CLEAR and CONSISTENT set of requirements on the lines of my post #7 in this thread of yours:
Macro to find match and Update neighbouring cell(s)
There is no point in me modifying any code until you are clear in your mind as to what you want and have expressed clearly so that I can understand it.
Finally you have not posted all of the code if you look at the code you can see that the i and the j loops have not got "next i or j" so quite a few lines a missing.
As mentioned, I took your original code and tried to modify it for this Workbook.
My requirements are already posted. If you can not help, that's fine. I appreciate your reply. Thank you.
 
Upvote 0
As mentioned, I took your original code and tried to modify it to work for this Workbook.
My requirements are already posted. If you can not help, that's fine. I appreciate your reply. Thank you.
 
Upvote 0
Your code is incomplete, but the best way to learn is through trial and error. But to help you out look for anything in the code that says cells and change a number then do a trail run. Hint: the first number is for rows and the second number after the comma is for columns.
 
Upvote 0
But to help you out look for anything in the code that says cells and change a number then do a trail run. Hint: the first number is for rows and the second number after the comma is for columns.
This doesn't work with the code that I have written because I am using variant arrays to interact with the worksheet. The two arrays in the code above are updt and mastarr, in thse two arrays the indices relate to rows and columns exactly in the way you state above
 
Upvote 0

Forum statistics

Threads
1,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top