Comparing Values in 2 Different Worksheets 1

[jennuhw]

I have a VBA script in Excel to search for a value matching the one in "Book1" then checking to see if the cell two columns over in "Book2" matches? I cannot get it to work. It finds all of the instances of the first match, but it will not compare value1 to value2 in the 2nd column. Here is my script:

' Checking to see if the part number and annual quantity exist
Workbooks.Open Filename:= "test.xls"
With Worksheets(1).Range("C4:C6000&quot
Set c = .Find(P1, LookIn:=xlValues)
If Not c Is Nothing Then
firstaddress = c.Address
Do
If Range(c.Address).Offset(columnoffset:=2).Value = Qty1 Then
MsgBox prompt:="Sorry, that has already been quoted!", Title:="Duplicate Quote!!"
End If
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstaddress
End If

I know that the first line after the 'Do' statement is bombing out. It never ends up equaling when it should! I am pretty new at this, so any suggestions are deeply appreciated!! TIA!

 

[SkipVought]

Hi,
Where are you defining Book2? Skip,
metzgsk@voughtaircraft.com

 

[jennuhw]

Oops! I meant to call it test.xls. Sorry!

 

[jennuhw]

The script should be:

' Checking to see if the part number and annual quantity exist
Workbooks.Open Filename:= &quot;book2.xls&quot;
With Worksheets(1).Range(&quot;C4:C6000&quot
Set c = .Find(P1, LookIn:=xlValues)
If Not c Is Nothing Then
firstaddress = c.Address
Do
If Range(c.Address).Offset(columnoffset:=2).Value = Qty1 Then
MsgBox prompt:=&quot;Sorry, that has already been quoted!&quot;, Title:=&quot;Duplicate Quote!!&quot;
End If
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstaddress
End If

 

[SkipVought]

I would do it a bit differently...

    Set rng1 = Worksheets(1).Range("C4:C6000")
    Set rng2 = Worksheets(2).Range("E4:E6000")
    For Each cell1 In rng1
        If cell1.Value = P1 Then
            For Each cell2 In rng2
                If cell2.Value = P1 Then
                    'FOUND IT!
                    ' Exit For or continue on to find more matches
                End If
            Next
            ' Exit For or continue on to find more matches
        End If
    Next

Skip,
metzgsk@voughtaircraft.com

 

[jennuhw]

I changed my code to have those lines, but it goes through either an endless loop or it is checking all 6000 lines. It should have found the match because it is in the first line of the spreadsheet. I want it to find a part number, then check the column (2 over) to see if the quantities match. If it doesn't, then go to the next instance of the part number and so on. That's why I had a find statement with a if then loop. What am I doing wrong with either one? Thanks!

 

[SkipVought]

Well it could be checking all 6000 lines unless you code an Exit For when you are done. Skip,
metzgsk@voughtaircraft.com

 

[jennuhw]

I am trying to make the code as non-interactive as possible. I am writing it for customer service, and sometimes they even have problems saving documents!! So, I don't want them to see anything that is going on or have them answer and extra questions. Do you know what I mean? :-D

 

[jennuhw]

Skip,
Do you know why my first script isn't catching that they are the same?
Thanks,
Jen

 

[SkipVought]

Since I don't have a P1 value, I tested matching amy value in book1 with book 2.

I made a few changes. It works. Modify it to suit your needs.

    Dim  cell1, cell2, rng1, rng2
    Workbooks(1).Activate
    Set rng1 = Range("rng1")
    Workbooks(2).Activate
    Set rng2 = Range("rng2")
    For Each cell1 In rng1
        For Each cell2 In rng2
            If cell2.Value = cell1.Value Then
                MsgBox cell1.Value
                ' Exit For or continue on to find more matches
            End If
        Next
            ' Exit For or continue on to find more matches
    Next

Skip,
metzgsk@voughtaircraft.com

 

[jennuhw]

Skip,
I tried this. I am not sure that I understand the script right becuase it doesn't seem to be doing what I need it to do. I have a worksheet with information that someone enters. After they are done, they hit a command button that does some other things and compares the information they entered to another spreadsheet.

Worksheet 1 (spreadsheet info is put into)
PART NUMBER QTY
12345 10

Worksheet 2 (spreadsheet it compares to)
PART NUMBER QTY
65483N 1
UIER30 5
12345 7
12345 10

The script needs to go through and check to see if the part number and qty have already been entered. The part number and qty are always in the same cells in worksheet 1. If it finds the same part number and qty on worksheet 2, it will give them an error message and stop. If it ends up not finding a match, it then adds that part number and qty to worksheet 2.

I am very sorry for all of the questions and confusion. This is my first try at VBA. If you want, I can send you what I have done, then it might be easier to see what I am doing.
Thanks!

 

[SkipVought]

OK,
This in not particularly difficult. However, the weekend is upon me and I must meet the love of my life.

So if no one has answered this by Monday, I will. Skip,
metzgsk@voughtaircraft.com

 

[jennuhw]

I have a problem with the cells not lining up in the search. I have the ranges set as c5:c10 and e5:e10. When I put them in a watch window, I see c5 for the first one, then it shows e5 incrementing to e10. After that, it shows c6 and e5 up to e10. So, I think I have something in the wrong spot. I haven't programmed since Basic 1.0 and Pascal, so please bear with me!! Thanks for your help, I do appreciate. I just have to get my hard head thinking the right way! Have a good weekend!

 

[jennuhw]

Skip,
I finally got it to work using some of your suggestion! Thanks! This is what I did:
Workbooks.Open Filename:= _
&quot;C:\test\sun hua master quote.xls&quot;
Windows(&quot;sun hua master quote.xls&quot.Activate
Set rng1 = ActiveSheet.Range(&quot;C5:C10&quot
For Each cell1 In rng1
Set cell2 = cell1.Offset(0, 2)
If P1 = cell1 Then
If Qty1 = cell2.FormulaR1C1 Then
MsgBox prompt:=&quot;Sorry, that has already been quoted!&quot;, Title:=&quot;Duplicate Quote!!&quot;
End If
End If
Next
Thanks, again! I

 

[SkipVought]

Good for you!

I would just offer one other suggestion. Test to see if the workbook is open. If not then open it. OR put the Workbook Open in another procedure. Then you can execute the search code as many times as you need.

    MyWorkbookName = "C:\test\sun hua master quote.xls"
    bOpen = False
    For Each Workbook In Workbooks
        With Workbook
            If .Path & "\" & .Name = MyWorkbookName Then bOpen = True
        End With
    Next
    If Not bOpen Then
    'open you workbook here
        Workbooks.Open Filename:=MyWorkbookName
    End If

Dale Watson and I have been working on another solution -- Dale is offering a database lookup technique using DGET that works quite nice. (most of the time) Hang on for an alternate approch in the hours/days/weeks to come ;-) Skip,
metzgsk@voughtaircraft.com

 

[jennuhw]

The workbook isn't suppose to be open, but you never know! I will check into that. Thanks!

 

[jennuhw]

Hey, Skip! I have another question for you! Part of this script that you were helping me on emails the workbook to a certain person. When this person receives it back, they open it up and hit the command button again. This works fine, but if they receive it more than once Outlook renames the attachment when it opens to the same file name with 1 on the end. Is there way to pull the file name it is opening so I can refer to it in the script? Does this make sense? LMK

 

[SkipVought]

LMK,
1. You eMail the workbook to Sam
2. Sam Opens the workbook, TWICE? Why? Not following the process and order of envents. Skip,
metzgsk@voughtaircraft.com

 

[jennuhw]

This is how it works. Sam fills in the spreadsheet and clicks on the command button. The command button checks for certain information and emails it to someone. This person fills out the information that they need to and email it back to Sam. The information is quote for parts so there is a chance that all of the information might not be filled out the first time around. So, they would end up receiving the spreadsheet twice which creates a problem with the filename. When they get the spreadsheet back, they hit the command button again. This time it does other different things.

 

[SkipVought]

But this RENAMING only happens IF a worksheet is OPEN with the same name when Excel tries to open the attachment.

So, if that is the case, could you Close the file after the file is eMailed? The VBA would have an eMail Command Button that would execute code that would...

1. Bring up the eMail DialogBox
2. Close the workbook

Here's some code I wrote for a similar application...

Sub SurveyComplete()
   Dim sWorkbookName As String, sPath As String
'
' SurveyComplete
' 8/30/00 Skip Metzger
'

'
   Dim sSendTo As String
   
   Application.ScreenUpdating = False
   
   sPath = ActiveWorkbook.Path
   sWorkbookName = sPath & "\" & "S" & Month(Now()) & Day(Now()) & Hour(Now()) & Minute(Now()) & Second(Now()) & ".xls"
   ActiveWorkbook.SaveAs FileName:=sWorkbookName
   
   sSendTo = Range("SendTo")
   Sheets("Parameters").Activate
   Range("SendTo").Copy
   
   With Application.Dialogs(xlDialogSendMail)
      .Show sSendTo, "Survey"
   End With
End Sub

Hope this helps Skip,
metzgsk@voughtaircraft.com