copying rows from one table to another 1

[SnayJ]

Can someone please tell me why this code works...

If Sheets("Sales Datasheet").Cells(i, "C").Value = "672144" Then
     Sheets("Sales Datasheet").Cells(i, "E").EntireRow.Copy Destination:=Sheets("Office View").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If

But this one doesn't.

If Sheets("Sales Datasheet").Cells(i, "C").Value = byagentcb.value Then
     Sheets("Sales Datasheet").Cells(i, "E").EntireRow.Copy Destination:=Sheets("Office View").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If

And if I debug.print byagentcb.value, it is "672144". Column C is a Number Value and I've even tried declaring byagentcb as Integer, Long, and Variant.

 

[combo]

What is baygentcb? Test its value from code (Msgbox or Debug.Print), test [pre]Sheets("Sales Datasheet").Cells(i, "C").Value = byagentcb.value[/pre] for [pre]True/False[/pre].

combo

 

[SnayJ]

When I do debug.print byagentcb.value, I get '672144'. But when I do debug.print Sheets("Sales Datasheet").cells(i,"C").value = byagentcb.value, I get False, and when I hard code "672144" instead of byagentcb.value... I get the results I'm looking for.

 

[SkipVought]

672144" <> 672144

A string is not equal to a number all containing the same digits in the same order, if that indeed is the case.

"byagentcb.value" implies that byagentcb is an Object of some kind and not a normal variable. How is byagentcb 1) declared, 2) Set and 3) assigned a value?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SnayJ]

The combobox has 3 columns:

B C D from the Worksheet "Agent Datasheet"
|licnumber|lastname|firstname|

and bound to licnumber. The B column in the table is formatted as text. The data is entered into the table on another form with the following code:

    With ws          'worksheet "Agent Datasheet"
        .Cells(lRow, 1).Value = Me.TRECLICNUMBERbox.Value + 123     'loginpasscode
        .Cells(lRow, 2).Value = Me.TRECLICNUMBERbox.Value           'TREC License Number
        .Cells(lRow, 3).Value = Me.LASTNAMEbox.Value                'Last Name
        .Cells(lRow, 4).Value = Me.FIRSTNAMEbox.Value               'First Name
        .....more to this.....
    End With

The textbox on the data entry from is formatted as Text

Then on the worksheet I have the dropdown "byagentcb" which gets assigned the |licnumber|lastname|firstname| on Worksheet.Activate() using this code:

Private Sub Worksheet_Activate()
    Dim lastrow
    lastrow = Sheets("Agent Datasheet").Range("A" & Rows.Count).End(xlUp).Row
    Dim myArray As Variant
    myArray = Worksheets("Agent Datasheet").Range("B3:D" & lastrow)
    byagentcb.List = myArray
End Sub

It's using this dropdown that I use to pull the data from one sheet to another temp sheet using this code. This was the initial code I used, which didn't work... then I also tried to use CStr(byagentcb.value), but that didn't work either.

    Dim i, lastrow
    lastrow = Sheets("Sales Datasheet").Range("A" & Rows.Count).End(xlUp).Row
    Sheets("Office View").Range("A3:AB1000").ClearContents
    For i = 2 To lastrow
        If Sheets("Sales Datasheet").Cells(i, "C").Value = byagentcb.Value Then
            Sheets("Sales Datasheet").Cells(i, "E").EntireRow.Copy Destination:=Sheets("Office View").Range("A" & Rows.Count).End(xlUp).Offset(1)
        End If
    Next i

 

[SnayJ]

Sorry.... I still don't know why it didn't work the way I wrote it originally because it all seemed to be text... but it's working now by using a variable instead of the dropdown value.

Added/changed the highlighted

    Dim i, lastrow
    [highlight #FCE94F]Dim byagent As String[/highlight]
    lastrow = Sheets("Sales Datasheet").Range("A" & Rows.Count).End(xlUp).Row
    Sheets("Office View").Range("A3:AB1000").ClearContents
    [highlight #FCE94F]byagent = CStr(BYAGENTcb.Value)[/highlight]
    For i = 2 To lastrow
        If Sheets("Sales Datasheet").Cells(i, "C").Value = [highlight #FCE94F]byagent[/highlight] Then
            Sheets("Sales Datasheet").Cells(i, "E").EntireRow.Copy Destination:=Sheets("Office View").Range("A" & Rows.Count).End(xlUp).Offset(1)
        End If
    Next i