is this the correct way of comparing rows??

[freezefiz]

Hey guyz..

If CStr(Sh.Cells(intRow, 3).Value) & (Sh.Cells(intRow, 6).Value) & (Sh.Cells(intRow, 7).Value) = _
(ShProd.Cells(intRow, 1).Value) & (ShProd.Cells(intRow, 2).Value) & (ShProd.Cells(intRow, 3).Value) Then

i'm trying to compare 3 cols from Sh & ShProd both worksheets in same workbook.

Is this the way to do it?
Or am i doing it all wrong?

Pls advise.

Thanks Thanks.

 

[Boller]

Is it doing what you want or not ?

 

[Richard Schollar]

Hi

What values are actually in the cells you are comparing? If string values then it should be fine, if numerical values with a fractional part (eg like 234.6789) then you may run into problems with Excel's limited precision (in which case you may want to round such numbers first).

 

[freezefiz]

Well there is not error to it..

But it seems like this if statement is being ignored and therefore not doing what's needed following after this statement..

so i reckon i must be doin something wrong to this statemet.

Well not sure if this helps but this is my full code :

Private Sub CommandButton2_Click()
    Dim Rng As Range
    Dim c As Range
    Dim List As New Collection
    Dim name As Variant
    Dim WbkNew As Workbook
    Dim wbkName As String
    Dim shName As String
    
Dim Sh As Worksheet
Set Sh = Worksheets("db")
    Application.ScreenUpdating = False
   
Dim template As Worksheet
Set template = ActiveWorkbook.Sheets("template")
     
Dim ShProd As Worksheet
Set ShProd = ActiveWorkbook.Sheets("products")
Application.ScreenUpdating = False
'----------------------create driver worksheet in protoV3.xls---------------
With Sh
       
     With .Range("J2:J" & .Range("B" & .Rows.Count).End(xlUp).Row)
       
       .FormulaR1C1 = "=VLOOKUP(RC[-5],DriverAllocation!C[-9]:C[-8],2,FALSE)"
        .Copy
     .PasteSpecial Paste:=xlValues

End With

 'add individual driver's name to list(store)
        Set Rng = .Range("J2:J" & .Range("J" & .Rows.Count).End(xlUp).Row)
        On Error Resume Next
        For Each c In Rng
            List.Add c.Value, CStr(c.Value)
        Next c
        On Error GoTo 0
   'range to be copied to individual driver's worksheet
        Set Rng = .Range("A1:J" & .Range("A" & .Rows.Count).End(xlUp).Row)
      
  For Each name In List 'for each driver
           Set ShNew = Workbooks.Add
        
template.Range("A1").Copy
    
      With ShNew
               wbkName = name 'item=driver's name
                
template.Cells.Copy Destination:=ShNew.Sheets("Sheet1").Range("A1")
Worksheets("Sheet1").Cells(3, 11).Value = wbkName
Dim intRow As Integer
Dim rProd As Range, rCust As Range
intRow = 2
Do While Sh.Cells(intRow, 5).Value <> ""
If CStr(Sh.Cells(intRow, 10).Value) = Worksheets("Sheet1").Cells(3, 11).Value Then
If CStr(Sh.Cells(intRow, 3).Value) & (Sh.Cells(intRow, 6).Value) & (Sh.Cells(intRow, 7).Value) = _
(ShProd.Cells(intRow, 1).Value) & (ShProd.Cells(intRow, 2).Value) & (ShProd.Cells(intRow, 3).Value) Then
Worksheets("Sheet1").Range("a3") = "Date"
Worksheets("Sheet1").Range("a4") = "Area"
Worksheets("Sheet1").Range("j3") = "Driver"
Worksheets("Sheet1").Range("j4") = "Vehicle"
Worksheets("Sheet1").Range("a5") = " "
Worksheets("Sheet1").Range("a6") = "S/NO"
Worksheets("Sheet1").Range("b5") = " "
Worksheets("Sheet1").Range("b6") = "CustID"
Worksheets("Sheet1").Range("c5") = " "
Worksheets("Sheet1").Range("c6") = "Customer"
Worksheets("Sheet1").Range("d5") = " "
Worksheets("Sheet1").Range("d6") = "Invoice"
Worksheets("Sheet1").Range("e5") = " "
Worksheets("Sheet1").Range("e6") = "Remarks"
    proid = Sh.Cells(intRow, 6).Value
    pro = Sh.Cells(intRow, 7).Value
    Set rProd = Worksheets("Sheet1").Cells(5, Columns.Count).End(xlToLeft)
    rProd.Offset(0, 1).Value = proid
    rProd.Offset(1, 1).Value = pro
    custID = Sh.Cells(intRow, 4).Value
    cust = Sh.Cells(intRow, 5).Value
    Set rCust = Worksheets("Sheet1").Range("b" & Columns.Count).End(xlUp)
    rCust.Offset(1, 0).Resize(, 2) = Array(cust, custID)
    qty = Sh.Cells(intRow, 8).Value
    Worksheets("Sheet1").Cells(rCust.Row + 1, rProd.Column + 1).Value = qty 'Cells(,start at which column?)

Dim Category As String
ShProd.Cells(intRow, 4).Value = Category
If Category = "froz" Then
ShNew.SaveAs ThisWorkbook.Path & "\" & "LOGISTIC" & "\" & wbkName & "_frozen"
ShNew.Close True
End If

If Category = "non-froz" Then
ShNew.SaveAs ThisWorkbook.Path & "\" & "LOGISTIC" & "\" & wbkName
ShNew.Close True
End If
 
End If
           End If
            
            intRow = intRow + 1
        Loop
    
            End With
        Next name
   
    
    End With
   
'----------end---------driver--------protoV3.xls-------------------
    
    Application.ScreenUpdating = True
 
End Sub

 

[freezefiz]

Thanks richard for replying..

well.. actually there isn't really a value i want to retrieve..

just that after the rows are compared, if it matches, the value of eg.col will define how the file name should be saved as.
(frozen or nonFrozen)

so basically this col just define whether its frozen or nonFrozen & that the whole workbook will be saved as eg.Dick_frozen

hmm... pls advise.

Thanks

 

[Richard Schollar]

But what actual values are contained in the cells? As I said, if they are fractional numbers you may well run into the problems caused by Excel's limited precision - it won't necessarily matter that they look the same in the cells, a comparison may still fail due to the lack of precision.

 

[freezefiz]

hey richard..

the values in the cells are basically just strings & numbers..

eg. productID, customerID, product, categories

hmm... bt... if there's no syntax error does that mean the codings is correct, just that perhaps the comparing of rows are wrong...?

 

[Richard Schollar]

Sounds like it to me