Code not working- help

[NeoSez]

What is wrong with this code? It's not working.
I want to extract date only, from column T in this format 08/26/2020. The date usually appears at the beginning of a bunch of text.

or kk = 1 To Len(fullstr)

        digt = Mid(A1, Find("/", A1, 1) - 2, 10)

        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 current 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

    Next kk

      If startstr > 0 Then

      dt = Mid(fullstr, startstr, endstr - startstr + 1)

      mastarr(i, 7) = dt

 

[jasonb75]

What is wrong with this code? It's not working.

Not working is an ambiguous term, it could mean a lot of different things.

Runs but does nothing.
Runs and does the wrong thing.
Doesn't run (runtime error).

The obvious thing that I can see is that the variable digt needs to be tested with IsDate not IsNumeric.

There could be other problems, but without the full code and some samples with expected output we could spend all day looking for them.

 

[NeoSez]

Not working is an ambiguous term, it could mean a lot of different things.

Runs but does nothing.
Runs and does the wrong thing.
Doesn't run (runtime error).

The obvious thing that I can see is that the variable digt needs to be tested with IsDate not IsNumeric.

There could be other problems, but without the full code and some samples with expected output we could spend all day looking for them.

Hi Jason. Thanks for your reply. I am a newbie so I don't quite understand everything yet. I get help from the forum and try to add/modify the script to help me understand and learn. So please be patient with me.
The code below works well and was provided by another member. Except that it extracts the date with a space afterwards. The date format on the extraction template is consistently dd/mm/yyyy. So how do I write the VBA script to only extract the date with no spaces in front or back? I tried Trim, but that did not work consistently at all. Thank you.

Sub test3()
Dim fullstr As String
With Worksheets("Sheet2")
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
' new mapping G is now F column 6
' H  is now X column 24
'I is now T column 20 Date is assumed to be all the characters up to the first space
End With
Worksheets("Sheet1").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 ' Column F
  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 = "DELIVERED" Or sts = "RECEIVED" 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
    Next kk
      If startstr > 0 Then
      dt = Mid(fullstr, startstr, endstr - startstr + 1)
      mastarr(i, 4) = dt
      End If
  End If
  End If
Next j
Next i
Range(Cells(1, 1), Cells(lastmast, 4)) = mastarr
End Sub

 

[jasonb75]

So please be patient with me.

My replies might seem a bit impatient at time but I'm not really, I just don't do the modern society norm of buffing every reply with a sugar coating with a sprinkle of fairy dust

Looking at your code to see what we can do with it, I would start by changing these 2 lines and see what happens,
from

digt = Mid(fullstr, kk, 1)
        If IsNumeric(digt) And startstr < 0 Then

to

digt = Trim(Mid(fullstr, kk, 1))
        If IsDate(digt) And startstr < 0 Then

Unlike a formula in excel vba doesn't recognise dates as numeric, also the use of Mid would make a numeric value a string, with this in mind IsNumeric probably wouldn't work here with any value.

It is possible that some more things need to be changed, I find it best to work through such things a bit slower rather than changing things that we don't need to. Having said that, it might be a good idea to post an example of your sheet (before and after) so that we can see what the code should be doing. The best way to do that is with XL2BB (see link below) which allows you to post a section of the sheet including any formulas, formatting, etc which we can copy and paste straight into excel.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

 

[NeoSez]

TRACKING	DATE	STATUS	DATE RECEIVED
123456	07/01/2020	Received
123457	07/02/2020
123458	07/03/2020	In Progress
123459	07/04/2020

 

[NeoSez]

My replies might seem a bit impatient at time but I'm not really, I just don't do the modern society norm of buffing every reply with a sugar coating with a sprinkle of fairy dust

Looking at your code to see what we can do with it, I would start by changing these 2 lines and see what happens,
from

digt = Mid(fullstr, kk, 1)
        If IsNumeric(digt) And startstr < 0 Then

to

digt = Trim(Mid(fullstr, kk, 1))
        If IsDate(digt) And startstr < 0 Then

Unlike a formula in excel vba doesn't recognise dates as numeric, also the use of Mid would make a numeric value a string, with this in mind IsNumeric probably wouldn't work here with any value.

It is possible that some more things need to be changed, I find it best to work through such things a bit slower rather than changing things that we don't need to. Having said that, it might be a good idea to post an example of your sheet (before and after) so that we can see what the code should be doing. The best way to do that is with XL2BB (see link below) which allows you to post a section of the sheet including any formulas, formatting, etc which we can copy and paste straight into excel.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

Thank you for the suggestion. It threw an error and said the
digasc wasn't defined.

I put a table in here. Not sure how to insert the sheet.

 

[NeoSez]

Tracking	GGG	RRR	SSS	Date Received	UUU	VVV	WWW	Status	YYY	ZZZ
123456​				07/12/2020 The package was received by John Smith				Received
123459​				07/14/2020 Delivered				Del
123460​				07/13/2020 The package was received				Received
123461​				07/14/2020 Delivered				Del
123462​				7/16/2020 Picked up				In Prog
123463​				7/17/2020 Picked up				In Prog
123464​				Picked up 7/15/2020				In Prog
123465​				7/15/2020 Picked up				In Prog
123466​				7/18/2020 Picked up				In Prog
123467​				7/15/2020 Picked up				In Prog

 

[jasonb75]

I tried running the code from your post but as I don't have the correct data to run it on it ends after hitting For i = 2 To lastmast without doing anything, I did try pasting your tables into excel but still the same.

It is possible that I've simply added the tables to the wrong sheets or wrong locations on the sheet.

I put a table in here. Not sure how to insert the sheet.

As I said in my earlier reply, that is best done with XL2BB (for which I provided a link in that post). This would tell me the correct sheet name and range for the data that you post so then I would know where it needs to go.

The date format on the extraction template is consistently dd/mm/yyyy.

I notice in your second table that the dates are in a different format, so that could be one of the problems. Any dates need to be in the same format as your system settings in order for things to work correctly, although DMY dates do appear to be problematic with vba anyway.

In order to make any progress, I'm going to need XL2BB samples of both sheets, (before and after for the sheet that the code should be making changes to) along with a description of what the code should be doing (not what you need fixing in the code).

Not sure that I'll get much chance to look at it over the next few days but will do what I can once you post the information that I need.

 

[NeoSez]

Hi jasonb75.
I hope I did this right.
-----------------------------------------------
Sheet1

Tracking	Date Sent	Status	Date Received
123456	1-Jul
123457	2-Jul	RECEIVED	7/4/2020
123458	3-Jul
123459	4-Jul	In Progress
123460	5-Jul
123461	6-Jul
123462
123463
123464

Sheet2

AAA	BBB	BBB	DDD	EEE	Tracking	GGG	RRR	SSS	Date Received	UUU	VVV	WWW	Status	YYY	ZZZ
					123456				07/12/2020 The package was received by John Smith				Received
					123459				07/14/2020 Delivered				Del
					123460				07/13/2020 The package was received				Received
					123461				07/14/2020 Delivered				Del
					123462				07/16/2020 Picked up				In Prog
					123463				07/17/2020 Picked up				In Prog
					123464				Picked up 07/15/2020				In Prog
					123465				07/15/2020 Picked up				In Prog
					123466				07/18/2020 Picked up				In Prog
					123467				07/15/2020 Picked up				In Prog
					123468				07/15/2020 Picked up
					123469				07/15/2020 Picked up
					123470				07/15/2020 Picked up
					123471				07/15/2020 Picked up
					123472				07/15/2020 Picked up

 

[jasonb75]

Yep, that came out just fine

I've just noticed that the code was originally provided to you by @offthelip hopefully they will understand what is needed without me trying to work it all out again from the beginning.