Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: Excel macros VBA
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
select2 = wkb2.Sheets(Shipment).Cells(j,4).Value
OBJECT OR APPLICATION DEFINED ERROR !!!....
 
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 23:00pm
aksh619297
Edited 24-Oct-12 23:54pm
v5
Comments
Richard MacCutchan at 25-Oct-12 4:55am
   
Use the macro debugger to step through the code and see what values are missing or incorrect.
aksh619 at 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 at 25-Oct-12 5:12am
   
OK, so you need to investigate why it is not returning the value you expect.
aksh619 at 25-Oct-12 6:05am
   
yes ...

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

This code works fine but only for single value . But i want to apply for every coulmn cells like A1 Coulmn cells
 
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
  Permalink  
Comments
Richard MacCutchan at 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 at 25-Oct-12 10:24am
   
thanks Richard ... appreciate your help.

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

  Print Answers RSS
0 OriginalGriff 277
1 Maciej Los 240
2 DamithSL 200
3 Raul Iloc 185
4 Richard MacCutchan 175
0 OriginalGriff 5,130
1 DamithSL 4,197
2 Maciej Los 3,670
3 Kornfeld Eliyahu Peter 3,470
4 Sergey Alexandrovich Kryukov 2,821


Advertise | Privacy | Mobile
Web01 | 2.8.141216.1 | Last Updated 25 Oct 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100