auto sort

[ttowncorp]

this will be a major help but i'm not sure if anybody can help me. I have a massive data sheet that i would like to sort out. I dump the raw data on sheet two and would like to type in the item i want to see the history on in sheet one. example below sheet two then sheet one.

ticket id	priority	date of service	vehicle id	user name	department	status	problem	corrective action	service date	comments	compliance
1	no	5/1/19	3201	robert	southern	completed	tires	replaced tires	5/1/19
2	yes	5/2/19	2200	bob	western	active	piston 4	n/a	n/a
3	no	5/2/19	2200	bob	western	completed	tires	replaced tires	5/2/19
4	no	5/2/19	2200	bob	western	completed	oil change	replaced oil	5/2/19

<tbody>
</tbody>

					2200
ticket id	priority	date of service	user name	department	status	problem	corrective action	service date	comments	compliance
2	yes	5/2/19	bob	western	active	piston 4	n/a	n/a
3	no	5/2/19	bob	western	completed	tires	replaced tires	5/2/19
4	no	5/2/19	bob	western	completed	oil change	replaced oil	5/2/19

<tbody>
</tbody>

 

[ttowncorp]

to have A2 through K2 on sheet1 not resize when it's pulling data from sheet2 and dumping it on sheet1. I figured out how to move the Target from F1 and made it H1 so it looks more centered.

 

[MickG]

Try modifying the line in red, as below:-

With Sheets("Sheet1").Range("A3").Resize(c, 11)
     Set Rng = .Parent.Range("A3", .Parent.Range("A" & Rows.Count).End(xlUp))
    [COLOR="#FF0000"][B] If Not Rng.Row = 2 Then Rng.Resize(, 12).ClearContents
[/B][/COLOR]    .Value = Application.Index(nRay, Evaluate("Row(1:" & UBound(Ray, 1) & ")"), Array(1, 2, 3, 5, 6, 7, 8, 9, 10, 11, 12))
    .Columns("C:C").NumberFormat = "dd/mm/yyy"
    .Columns("I:I").NumberFormat = "dd/mm/yyy"
    .Borders.Weight = 2
    .Columns.AutoFit
End With

 

[ttowncorp]

thanks for taking your time in helping me out. I've made the changes but did not see a effect on the sheet. what i do notice is that if i type in a incorrect vehicle number I get a "run-time err 1004" and keeps point to this line of code highlighted in yellow With Sheets("Sheet1").Range("A3").Resize(c, 11) not sure why it's pointing to that since that has not been modified. i would like to send you my sheet so you can see what i'm talking about. i put it in my drop box. and again thanks

https://www.dropbox.com/s/q6z17o8e1wi2iu0/new auto sort program testing 6 demo.xlsm?dl=0

 

[MickG]

Can you send that file using "Box.com", I'm not able to download that file.

When you spoke about "Resize" were you referring to row 2 being deleted when the code was run or something else???
Please explain !!!

NB: I think that "Runtime Error 1004" is because the variable c has a value of 0. This should not be the case, because even if you enter a invalid number, c will always be "1" because that represents the header row. Even will an invalid number you should always see the headers in row 3. This is because the Code looks specifically for the Header name "vehicle id", if that header is not there, then c could be "0" and you would get the error. !!!!

 

[ttowncorp]

https://app.box.com/s/27pblocz3mr3sfvdfd2df0s8vqq6c8jj

yes it will resize the headers making some of them unreadable. when you try to resize them manually and look up another vehicle ID it gets small again. other then that the code is working perfectly. i opened up a account on box.com. this is pretty much just like dropbox i c.

 

[ttowncorp]

oh yes I have added the error message code in it as well but since the code before it is not working i cant get it to start. if a vehicle ID is inputed in that's not in sheet two that error message should pop up with the text i created.

 

[MickG]

Try this:-
I've remove the code part that copies the headers so the results start "A3" and also altered the error trapping so if you enter an incorrect number (i.e. value of "c" is 0). I've also removed the "Column "Autofit". That might help.
As a result you will need to have your row 2 headers pre set, which I think they are already.
I found that the "H1" number changed font size to cover "H2" if the Font size was set at 72, but did not if set to 48 ????.
NB:- Apart from cell "H1" and the columns Autofit, I did not find any other Resizing problems.

Private [COLOR="Navy"]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] ray [COLOR="Navy"]As[/COLOR] Variant, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Temp [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Application.EnableEvents = False
[COLOR="Navy"]If[/COLOR] Target.Address(0, 0) = "H1" [COLOR="Navy"]Then[/COLOR]
ray = Sheets("Sheet2").Range("A1").CurrentRegion
ReDim nRay(1 To UBound(ray, 1), 1 To UBound(ray, 2))
    [COLOR="Navy"]For[/COLOR] n = 1 To UBound(ray, 1)
        [COLOR="Navy"]If[/COLOR] ray(n, 4) = Target.Value [COLOR="Navy"]Then[/COLOR]
            c = c + 1
            [COLOR="Navy"]For[/COLOR] ac = 1 To UBound(ray, 2)
                [COLOR="Navy"]If[/COLOR] IsDate(ray(n, ac)) [COLOR="Navy"]Then[/COLOR]
                    nRay(c, ac) = CDbl(DateValue(ray(n, ac)))
                [COLOR="Navy"]Else[/COLOR]
                    nRay(c, ac) = ray(n, ac)
                [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]Next[/COLOR] ac
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] n
Temp = Target
[COLOR="Navy"]If[/COLOR] c > 0 [COLOR="Navy"]Then[/COLOR]
[COLOR="Navy"]With[/COLOR] Sheets("Sheet1").Range("A3").Resize(c, 11)
     [COLOR="Navy"]Set[/COLOR] Rng = .Parent.Range("A3", .Parent.Range("A" & Rows.Count).End(xlUp))
     Rng.Resize(, 3).ClearContents
    .Value = Application.Index(nRay, Evaluate("Row(1:" & UBound(ray, 1) & ")"), Array(1, 2, 3, 5, 6, 7, 8, 9, 10, 11, 12))
    .Columns("C:C").NumberFormat = "dd/mm/yyy"
    .Columns("I:I").NumberFormat = "dd/mm/yyy"
    .Borders.Weight = 2
    '[COLOR="Green"][B].Columns.AutoFit[/B][/COLOR]
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]Else[/COLOR]
MsgBox UCase(Sheet1.Range("H1")) & " No data has been entered for this bus at this current time.", vbCritical, "ALERT"
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] If
Application.EnableEvents = True
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[ttowncorp]

This is beyond perfect!!! thank you so much for your hard work in this coding and your patience, this thing works awesome now. Maybe later down the road I have a old project from yrs ago I been trying forever to get it to work, well it works but at times it lags, but that's for another time. but that you again friend.

 

[MickG]

You're very welcome
Glad its working for you.

 

[ttowncorp]

i hope all is well. I have been using the sheet for some time now and it's working great. I would like to add to more rows for display in the sheet and for the most part i thought all i needed to change .Value = Application.Index(nRay, Evaluate("Row(1:" & UBound(ray, 1) & ")"), Array(1, 2, 3, 4, 13, 6, 7, 8, 9, 10, 11, 12)) into .Value = Application.Index(nRay, Evaluate("Row(1:" & UBound(ray, 1) & ")"), Array(1, 2, 3, 4, 13, 6, 7, 8, 9, 10, 11, 12, 13, 14)) is there anything else i need to change so the other columns will populate like the rest