Heyas all, I'm entering data fro 1

[kha0s]

Heyas all,

I'm entering data from textboxes on my form into excel using:
Sheet1.Cells(2, 1).End(xlDown).Offset(1, 0) = UserForm1.textbox1.value

What I need is for the data to entered in a specific order, first by the date field descending then alphabetical by the name field. So it might require the insertion of rows to squeeze data in between already existing data.

Is this possible? if so could you give me some insight as to how I might go about doing this?

Thanks guys/gals
Kha0s

 

[rdodge]

Yes, it is very much so possible. I created a set of Text functions as the one below is just one of them that adds an item to a string variable. I created this function in Access. However, there are things I could do to make this more improved, but it's something you may want to take a look at.

Function fncAddItem(ByVal strItemList As String, ByVal item As String, ByVal Sep As String, Optional ByVal lngPos As Long, _
Optional ByVal lngOrder As Long, Optional ByVal SepCpr As Long = 0, Optional ByVal ItemCase As Long = 0, _
Optional ByVal TieBreaker As Long = 0) As String
'Either use lngPos or lngOrder for the sorting. If both are stated, order will take precedence.
'lngOrder has the possible valid values
'1 = Numeric Ascending
'2 = Numeric Descending
'3 = Alpha Ascending
'4 = Alpha Descending
'5 = Alpha, then Numeric Ascending
'6 = Alpha, then Numeric Descending
'7 = Numeric, then Alpha Ascending
'8 = Numeric, then Alpha Descending
'9 = First
'10 = Last
'11 = Random
Dim X As Long, L As Long, U As Long, M As Long, UI As String, LI As String, MI As String, LL As Long, ML As Long, UL As Long, IL As Long, XL As Long
Dim I As Long, MA As Long, IA As Long, LA As Long, UA As Long, YL As Long, UP As Long, LP As Long, IP As Long, TS As String
Dim IU As Long, MU As Long, LU As Long, UU As Long
On Error GoTo ErrHandle
Randomize
fncAddItem = strItemList
If SepCpr <> 1 Then
SepCpr = 0
End If
If ItemCase <> 1 Then
ItemCase = 0
End If
If TieBreaker <> 1 Then
TieBreaker = 0
End If
X = modTxtFncs.fncItemCount(strItemList, Sep, SepCpr)
Select Case lngOrder
Case 1 To 8
L = 1
U = X
LI = modTxtFncs.fncItem(strItemList, Sep, L, SepCpr)
UI = modTxtFncs.fncItem(strItemList, Sep, U, SepCpr)
LL = VBA.Strings.Len(LI)
UL = VBA.Strings.Len(UI)
IL = VBA.Strings.Len(item)
Do While L <= U
M = Int((L + U) / 2)
MI = modTxtFncs.fncItem(strItemList, Sep, M, SepCpr)
ML = VBA.Strings.Len(MI)
Select Case lngOrder
Case 1
If CLng(LI) > CLng(MI) Or CLng(UI) < CLng(MI) Then
GoTo ExitFnc 'List not properly sorted
ElseIf CLng(MI) > CLng(item) Then
U = M - 1
Else
L = M + 1
End If
Case 2
If CLng(LI) < CLng(MI) Or CLng(UI) > CLng(MI) Then
GoTo ExitFnc 'List not properly sorted
ElseIf CLng(MI) > CLng(item) Then
L = M + 1
Else
U = M - 1
End If
Case 3 To 4
'To be sorted based on how the ascii codes are done
'The only exception to this rule, all letters are to be sorted with upper case before lower case as a secondary sort.
'Example: AaBbCc...
'If items are not to be sorted based on case sensitivity, then all lower cases will be considered as upper cases for this purpose.
If item = &quot;&quot; Then
TS = item & Sep & strItemList
Else
If IL > ML Then
XL = IL
YL = ML
Else
XL = ML
YL = IL
End If
IP = 0
LP = 0
UP = 0
For I = 1 To XL Step 1
IA = fncCC(IL, I, ItemCase, item)
MA = fncCC(ML, I, ItemCase, MI)
UA = fncCC(UL, I, ItemCase, UI)
LA = fncCC(LL, I, ItemCase, LI)
IU = fncUC(IA)
MU = fncUC(MA)
LU = fncUC(LA)
UU = fncUC(UA)
If lngOrder = 3 Then
If LP > -1 Then
If LU > MU Then
GoTo ExitFnc 'List not properly sorted
ElseIf LU < MU Then
LP = LP - 3
ElseIf LP = 0 And ItemCase = 1 Then
If LA < MA Then
LP = 1
ElseIf LA > MA Then
LP = 2
End If
End If
End If
If UP > -1 Then
If UU < MU Then
GoTo ExitFnc 'List not properly sorted
ElseIf UU > MU Then
UP = UP - 3
ElseIf UP = 0 And ItemCase = 1 Then
If UA > MA Then
UP = 1
ElseIf UA < MA Then
UP = 2
End If
End If
End If
If IU < MU Then
IP = IP - 3
U = M - 1
Exit For
ElseIf IU > MU Then
IP = IP - 3
L = M - 1
Exit For
ElseIf IP = 0 And ItemCase = 1 Then
If IA < MA Then
IP = 1
ElseIf IA > MA Then
IP = 2
End If
End If
Else
If LP > -1 Then
If LU < MU Then
GoTo ExitFnc 'List not properly sorted
ElseIf LU > MU Then
LP = LP - 3
ElseIf LP = 0 And ItemCase = 1 Then
If LA > MA Then
LP = 1
ElseIf LA < MA Then
LP = 2
End If
End If
End If
If UP > -1 Then
If UU > MU Then
GoTo ExitFnc 'List not properly sorted
ElseIf UU < MU Then
UP = UP - 3
ElseIf UP = 0 And ItemCase = 1 Then
If UA < MA Then
UP = 1
ElseIf UA > MA Then
UP = 2
End If
End If
End If
If IU > MU Then
IP = IP - 3
U = M - 1
Exit For
ElseIf IU < MU Then
IP = IP - 3
L = M - 1
Exit For
ElseIf IP = 0 And ItemCase = 1 Then
If IA > MA Then
IP = 1
ElseIf IA < MA Then
IP = 2
End If
End If
End If
Next I
If UP = 2 Or LP = 2 Then
GoTo ExitFnc 'List not properly sorted
ElseIf L <= M And U >= M Then
If IP = 2 Then
L = M + 1
ElseIf IP = 1 Then
U = M - 1
ElseIf TieBreaker = 1 Then
U = M - 1
Else
L = M + 1
End If
End If
End If
Case 5

Case 6

Case 7

Case 8

End Select
Loop
TS = fncItemAdd(strItemList, item, Sep, L, SepCpr)
Case 9
TS = item & Sep & strItemList
Case 10
TS = strItemList & Sep & item
Case 11
lngPos = Int(Rnd * X + 1)
TS = fncItemAdd(strItemList, item, Sep, lngPos, SepCpr)
Case Else
If lngPos > 0 And lngPos < X + 2 Then
TS = fncItemAdd(strItemList, item, Sep, lngPos, SepCpr)
Else
GoTo ExitFnc
End If
End Select
fncAddItem = TS
ExitFnc:
Exit Function
ErrHandle:
Resume ExitFnc
End Function


Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[kha0s]

There are no functions included to do simple searching and/or sorting?

 

[rdodge]

Unfortunately not other than in Excel, you have the Sort Method of a Range Object, like:
With Thisworkbook.Worksheets(&quot;Sheet1&quot
.Range(&quot;B4:J20000&quot.Sort Key1:=.Range(&quot;G4&quot, Order1:=xlAscending, Header:=xlNo
End With

But if you wanted to do a sort apart from a range object, there is no simple function for that. Note, the Sort Method on the Range Object is just the same as using the Sort Dialog box in Excel, but only it's done via code.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.