Compairing cell Value from one workbook with another workbook ....

Question

0.00/5 (No votes)

See more:

what i want to do is here..
i want to take B2 cell value from Workbook Wkb2 And match it with the other workbook wkb3 and if it is not present there than append that that coulmn value ata the end of the work book .
Please some one help me out

Also giving error on

SQL

select2 = wkb2.Sheets(Shipment).Cells(j,4).Value

OBJECT OR APPLICATION DEFINED ERROR !!!....

VB

sub compare()
wknm1 = "d:\b.xlsx"
Set wkb2 = Workbooks.Open(wknm1)

wknm2 = "C:\vbproject\tracker\ysdflow2.xlsx"
Set wkb3 = Workbooks.Open(wknm2)
For i = 2 To 100
For j = 2 To 100

  select2 = wkb2.Sheets("Shipment").Cells("j,4").Value
  wkb3.Sheets("Sheet2").Activate
  select1 = wkb3.Sheets("Sheet2").Cells("i,2").Value

If select1 = select2 Then
'***********leave that value and loop to another cell in workbook wkb2
else 
'*******Append Code.... 
End If
Next j
Next i
End sub

Please help !!!....
Thanks ,
aksh619

Posted 24-Oct-12 22:00pm

aksh619

Updated 24-Oct-12 22:54pm

v5

Add a Solution

Comments

Richard MacCutchan 25-Oct-12 4:55am

Use the macro debugger to step through the code and see what values are missing or incorrect.

aksh619 25-Oct-12 5:00am

select2 = ""
giving error on the above code where shipment sheet is selected.its value is shown as above...

Richard MacCutchan 25-Oct-12 5:12am

OK, so you need to investigate why it is not returning the value you expect.

aksh619 25-Oct-12 6:05am

yes ...

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

aksh619 · Answer 1 · 2012-10-25T02:28:00

Solution 1

This code works fine but only for single value . But i want to apply for every coulmn cells like A1 Coulmn cells

VB

Set wkb3 = Workbooks.Open(wknm2)
 For i = 2 To 100
  For j = 2 To 100
  select2 = wkb3.Sheets("Sheet2").Range("A2").Value
  Set wkb2 = Workbooks.Open(wknm1)
   select1 = wkb2.Sheets("Shipment").Range("D2")



   If select1 = select2 Then
    MsgBox "Value is equal"

    Else
    MsgBox "Value not equal"



   End If
   Next j
   Next i

please help me out .. 
Thanks 
aksh619

Posted 25-Oct-12 2:28am

aksh619

Comments

Richard MacCutchan 25-Oct-12 9:42am

You need to use variables to refer to your cell range; the following is an extract from Microsoft's VBA Help, which you could easily find for youreself:

When using Visual Basic, you often need to run the same block of statements on each cell in a range of cells. To do this, you combine a looping statement and one or more methods to identify each cell, one at a time, and run the operation.

One way to loop through a range is to use the For...Next loop with the Cells property. Using the Cells property, you can substitute the loop counter (or other variables or expressions) for the cell index numbers. In the following example, the variable counter is substituted for the row index. The procedure loops through the range C1:C20, setting to 0 (zero) any number whose absolute value is less than 0.01.


Sub RoundToZero1()
    For Counter = 1 To 20
        Set curCell = Worksheets("Sheet1").Cells(Counter, 3)
        If Abs(curCell.Value) < 0.01 Then curCell.Value = 0
    Next Counter
End Sub

Another easy way to loop through a range is to use a For Each...Next loop with the collection of cells specified in the Range property. Visual Basic automatically sets an object variable for the next cell each time the loop runs. The following procedure loops through the range A1:D10, setting to 0 (zero) any number whose absolute value is less than 0.01.


Sub RoundToZero2()
    For Each c In Worksheets("Sheet1").Range("A1:D10").Cells
        If Abs(c.Value) < 0.01 Then c.Value = 0
    Next
End Sub

If you don't know the boundaries of the range you want to loop through, you can use the CurrentRegion property to return the range that surrounds the active cell. For example, the following procedure, when run from a worksheet, loops through the range that surrounds the active cell, setting to 0 (zero) any number whose absolute value is less than 0.01.


Sub RoundToZero3()
    For Each c In ActiveCell.CurrentRegion.Cells
        If Abs(c.Value) < 0.01 Then c.Value = 0
    Next
End Sub

aksh619 25-Oct-12 10:24am

thanks Richard ... appreciate your help.