Comparing values from one sheet and have result to another 1

[panoscnr]

i have one sheet with my data and i want a macro to compare by pairs the values which is just numbers and write the result in an another sheet.When i did it to the same sheet it was OK but i can't connect the two sheets.

I want to do something like that :

Sub aaa()

if sheet1 cells(1, 1) = sheet1 cells(1, 3) then
if sheet1 cells(1, 2) = shhet1 cells(1, 4) then
word=1
else:word=" "
end if
end if

end sub

it tells me that Sub or Fuction not defined

 

[DaleWatson123321]

panoscnr,

I'd like to be able to introduce you to one of Excel's most powerful components - its "database extraction" capability. This is found under Excel's menu under: Data - Filter - Advanced Filter.

Many Excel users unfortunately do not use this powerful tool because of two bugs that Microsoft has failed to eliminate. However, these bugs are fairly easy to avoid and this component can indeed be a VERY valuable tool.

With the situation you've described, I believe this "database extraction" option would be ideal. Moreover, once you understand how this powerful component works, it will likely enable you to use it for other applications.

The best way for me to demonstate this option, would be for you to email me your file. I'll then modify and return it. If you happen to have sensitive data, perhaps you could trim down the size of your data to a small number of records with fictitious data.

I hope this can be an option.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[BrainB]

'---------------------------------------------
Sub aaa()
If Sheets(1).Cells(1, 1).Value = Sheets(1).Cells(1, 3).Value And _
Sheets(1).Cells(1, 2).Value = Sheets(1).Cells(1, 4).Value Then
'- or use something like :-Worksheets("Sheet1&quot.Cells(1, 1).Value
word = 1
Else
word = " "
End If
End Sub
'------------------------------------------------

Regards
BrianB
** Let us know if you get something that works !
================================

 

[panoscnr]

Brain thank you that was what i wanted but there is one more problem i have too many data to compare and i done a stupid thing because i don't know anything else i copy paste as much times as i want the code and i reach the 64kb limit.Anyway it works great i'll try to find how i can reduce the size because there is two things a)too much slow and b)too many code lines.

 

[Nikita6003]

Why not go with Dale's suggestion:
1. In a column next to the table you want to copy put an xl IF formula to determine whether or not to copy the data:
e.g. in cell E1 you put this formula

=(If(AND(A1=C1,B1=D1),"copy","")

2. Use the Advanced Filter option to copy "all rows where
column E = "copy" to your second sheet
MUCH faster ;-)

If you're not going to use Dale's Andvanced Filter suggestion, try looping through your worksheet:
(NB - code assumes you have whatever you need to copy on the first sheet of the xl file)

Dim l_lMaxRow As Long
Dim l_lRow As Long


Thisworkbook.Sheets(1).Activate

l_lMaxRow = Selection.SpecialCells(xlCellTypeLastCell).Rows.Count
For l_lRow = 1 to l_lMaxRow
    If Sheets(1).Cells(l_lRow, 1).Value = Sheets(1).Cells(l_lRow, 3).Value And _
            Sheets(1).Cells(l_lRow, 2).Value = Sheets(1).Cells(l_lRow, 4).Value Then
        word = 1
    Else
        word = " "
    End If
Next l_lRow

HTH

Cheers
Nikki