[RESOLVED] Code Dont work

[Siddharth Rout]

What is the error message that you get?

[westconn1]

what error?

[hensucker]

ok a few problems now, just realised the additem does work but it adds entries to my comboboxes causing duplicates, all i want it do is display what exists and not add anything to the comboboxes.
alo my search function is there anyway i can get it to descend search? from bottom to top not top to bottom?

Private Sub Search()
Set r = Sheets("Database").UsedRange.Find(TextBox3.Value)
        If Not r Is Nothing Then
        
    TextBox1 = r.Offset(0, -2)
    TextBox2 = r.Offset(0, -1)
    TextBox3 = r.Offset(0)
    TextBox4 = r.Offset(0, 1)
    TextBox5 = r.Offset(0, 2)
    TextBox6 = r.Offset(0, 3)
    ComboBox1.AddItem r.Offset(0, 4)
    ComboBox2.AddItem r.Offset(0, 5)
    ComboBox3.AddItem r.Offset(0, 6)
    Worksheets("ES19").Range("i7").Value = TextBox1.Value
    Worksheets("ES19").Range("i9").Value = TextBox2.Value
    Worksheets("ES19").Range("z7").Value = TextBox3.Value
    Worksheets("ES19").Range("z11").Value = TextBox4.Value
    Worksheets("ES19W").Range("i7").Value = TextBox1.Value
    Worksheets("ES19W").Range("i9").Value = TextBox2.Value
    Worksheets("ES19W").Range("z7").Value = TextBox3.Value
    Worksheets("ES19W").Range("z11").Value = TextBox4.Value

[Siddharth Rout]

Check this Example by Hack and implement it in your code

http://www.vbforums.com/showpost.php...35&postcount=2

[hensucker]

ok sorted the code out just changed additem to text and it displays it fine now thanks,,,,
now all i need is my search code to descend search :s

[hensucker]

Set r = Sheets("Database").UsedRange.Find(TextBox3.Value).Order1 = xlDescending
        If Not r Is Nothing Then

this dont work and similar alterations dont work either, please help

[westconn1]

from msdn (f1 for help)

SearchDirection Optional Variant. Can be one of the following XlSearchDirection constants: xlNext or xlPrevious. The default constant is xlNext.

if you do not have help installed, why not?

[hensucker]

Ok ill try it mate thanks

[hensucker]

ok added the previous code to the end of that line and then, the textbox1 argument below......now has trouble...

also when returning time data to my textboxes it returns numbers, even though the cells are set for time display.

[Siddharth Rout]

returning time data to my textboxes it returns numbers, even though the cells are set for time display.

I haven't gone thru the rest of the code... Let's say the time is in Cell A1 and you want to display it in Textbox1, they try this...

Textbox1.Text = Format(Range("A1").Value,"HH:MM:SS")

[hensucker]

ok right i got this error.....application-defined or object defined error!

when this code runs...

Private Sub Search()
Set r = Sheets("Database").UsedRange.Find(TextBox3.Value).Previous
        If Not r Is Nothing Then
        
    TextBox1.Text = r.Offset(0, -2)
    TextBox2.Text = r.Offset(0, -1)
    TextBox3.Text = r.Offset(0)
    TextBox4.Text = Format(Range("A1").Value, "HH:MM:SS") = r.Offset(0, 1)
    TextBox5.Text = Format(Range("A1").Value, "HH:MM:SS") = r.Offset(0, 2)
    TextBox6.Text = r.Offset(0, 3)
    ComboBox1.Text = r.Offset(0, 4)
    ComboBox2.Text = r.Offset(0, 5)
    ComboBox3.Text = r.Offset(0, 6)
    Worksheets("ES19").Range("i7").Value = TextBox1.Value
    Worksheets("ES19").Range("i9").Value = TextBox2.Value
    Worksheets("ES19").Range("z7").Value = TextBox3.Value
    Worksheets("ES19").Range("z11").Value = TextBox4.Value
    Worksheets("ES19").Range("b29").Value = ComboBox1.Value
    Worksheets("ES19W").Range("i7").Value = TextBox1.Value
    Worksheets("ES19W").Range("i9").Value = TextBox2.Value
    Worksheets("ES19W").Range("z7").Value = TextBox3.Value
    Worksheets("ES19W").Range("z11").Value = TextBox4.Value
    Worksheets("ES19W").Range("b29").Value = ComboBox1.Value

i think its the .previous on the beginning search line that causes the problem because it works without that, but i need the search to go in that direction.

[westconn1]

xlprevious is a constant, not a method or property, it has to be an argument passed to the find method

read the help file or search on line

also your textbox4 and 5 code is wrong

[hensucker]

THIS IS IT.zip

ok koolsid, here is the file i mentioned.

[Siddharth Rout]

I will get to your time problem later. First let's tackle the .Find issue...

Is this what you want?

Private Sub Search()
    Dim myAddr As String, MyArray() As String, r As Range
    
    '~~> Get the used range address
    myAddr = Sheets("Database").UsedRange.Address
    '~~> Split the address to get the last cell
    MyArray = Split(myAddr, ":")
    '~~> Select the last cell
    Range(MyArray(1)).Select
    
    strSearch = Trim(TextBox3.Value)
    
    '~~> Using xlPrevious to find in reverse
    Set r = Cells.Find(What:=strSearch, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False _
    , SearchFormat:=False)
    
    If Not r Is Nothing Then
        TextBox1.Text = r.Offset(0, -2)
        TextBox2.Text = r.Offset(0, -1)
        '
        '~~> Your Rest of the code
        '

[hensucker]

thats good does what i want it too, but that code caused textbox1 to return a figure now instead of text....


textbox1 - text - did display correctly - now displays a number instead of text
textbox2 - text - displays correctly
textbox3 - text - displays correctly
textbox4 - time - doesnt display correctly - displays a number instead of time
textbox5 - time - doesnt display correctly - displays a number instead of time
textbox6 - date - displays correctly
textbox7 - text - displays correctly

[Siddharth Rout]

I just amended/worked on the .find issue...

I didn't check the code between

If Not r Is Nothing Then

Endif

Check if the offset code is correct for each one of them...

[hensucker]

yes i had a duplicate textbox1 there so now just the problem of getting 4 and 5 to show time no matter what cell the data comes from....

[Siddharth Rout]

Try this

'
        '~~> Your Rest of the code
        '
        TextBox4.Text = Format(r.Offset(0, 1), "HH:MM:SS")
        TextBox5.Text = Format(r.Offset(0, 2), "HH:MM:SS")
        '
        '~~> Your Rest of the code
        '

[hensucker]

Darn It! why is it when u try it works so simply, yet i try everything everyway round and get nowhere thanks mate that is perfect and works a treat.

[Siddharth Rout]

Darn It! why is it when u try it works so simply, yet i try everything everyway round and get nowhere thanks mate that is perfect and works a treat.

Hope all your queries are resolved?

[hensucker]

thaks buddy sorted