Comparing Cell values in two sheets within the same workbook

[amitmandalia]

This is the code i have.

i want to compare a range of values in column H in sheet 1 with a
column that is in the sheet called 'mis tool'

I have the code looping down the range in sheet 1 and comparing to the
1st value in sheet 'mis tool', once the range in sheet 1 has run
throught, the cell selected in 'mis tool' will move down 1 and then it
will re-check for similar values.

the problem i have is that i can't get the code to pick up on entries
that are equal to each other.

I need an if statement that can compare the selected cell in sheet 1
with the selected cell in sheet 'mis tool', all cells are numerical
entries

the code as it is stops at the first IF statement, but otherwise does
what i need it to do.

any ideas?

Sub Macro1()

Sheets("Sheet1").Select
Range("H2").Select
Sheets("mis tool").Select
Range("F2").Select
Sheets("Sheet1").Select
Do
Do While ActiveCell <> 0
If ActiveCell = Sheets("mis tool").ActiveCell Then
Sheets("mis tool").Select
ActiveCell.Offset(0, 3).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
ActiveCell.Offset(0, 13).Select
ActiveSheet.Paste
ElseIf ActiveCell <> Sheets("mis tool").ActiveCell Then
Sheets("Sheet1").Select
ActiveCell.Offset(1, 0).Select
End If
Loop
If ActiveCell = 0 Then
Sheets("Sheet1").Select
Range("H2").Select
Sheets("mis tool").Select
ActiveCell.Offset(1, 0).Select
End If
Loop Until ActiveCell = 0
End Sub

 

[Joel]

I sthe data in the same format? If it is strings then use strcomp.

when I havve this problem I add extra code to debug the problem
I added a & b before the if staement. Put a break point (F9) at the If
statement and check the values of a & b. this should find the problem. You
also may need to add the .value to the If statement.

a = ActikveCell.value
b = Sheets("mis tool").ActiveCell.value

If ActiveCell = Sheets("mis tool").ActiveCell Then

 

[amitmandalia]

the data is all in the same format, i have to compare the numbers in a
column in sheet 1 with the numbers in the column in the sheet called
'mis tool'

im only a beginner at writing macro code.

i've changed the code to include the a and b as shown below, but now i
get an error '438'

"Object does't support this property or method"

any ideas?

Sub Macro1()

Sheets("Sheet1").Select
Range("H2").Select
Sheets("mis tool").Select
Range("F2").Select
Sheets("Sheet1").Select
Do
Do While ActiveCell <> 0
a = ActiveCell.Value
b = Sheets("mis tool").ActiveCell.Value
If a = b Then
Sheets("mis tool").Select
ActiveCell.Offset(0, 3).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet1").Select
ActiveCell.Offset(0, 13).Select
ActiveSheet.Paste
ElseIf a <> b Then
Sheets("Sheet1").Select
ActiveCell.Offset(1, 0).Select
End If
Loop
If ActiveCell = 0 Then
Sheets("Sheet1").Select
Range("H2").Select
Sheets("mis tool").Select
ActiveCell.Offset(1, 0).Select
End If
Loop Until ActiveCell = 0
End Sub

 

[Joel]

is it failing at the a = or b = lines? the line it fails at should be
highlight in yellow. try again and take out .value. It seems like the
format of the two cell being comparred are not the same.

 

[amitmandalia]

it fails at the b line.

if i change the target cell formats to the same, would that help?

 

[amitmandalia]

i removed the .value from the b = line, and even changed the cell
formats on the target cells to make them the same, but that hasn't
helped.

it still fails on the b = line with the same error number (438) and
same error message and is highlighted in yellow as well.

my feeling is that there is something wrong with the statement 'b=
Sheets("mis tool").ActiveCell'

I've set that cell as the data i want to check for, and i am going
down the range of cells in sheet 1 to look for it. if they match, i
need it to copy a cell further along the sheet and paste the
information into sheet 1, then carry on down the range and move onto
the next cell in the 'mis tool' sheet to compare against.

The code i have created so far can move down the range and stop when
all entries have been scanned through, but it doesn't copy the entries
i need for the items that match

 

[Joel]

The cell is not active! I'm rewriting the code. give me a few minutes


You select sheet1 then sheet "mis tool". Only one sheet can be selected at
a time.

 

[Tom Ogilvy]

Yes, sheets does not have an ActiveCell property:

Try:
b=Sheets("mis tool").Parent.Windows(1).ActiveCell

Or you can try code like this:

Sub CopyData()
Dim rng1 As Range, rng2 As Range
Dim cell1 As Range, cell2 As Range
Dim res As Variant
With Worksheets("sheet1")
Set rng1 = .Range(.Cells(2, "H"), .Cells(Rows.Count, "H").End(xlUp))
End With
With Worksheets("mis tool")
Set rng2 = .Range(.Cells(2, "F"), .Cells(Rows.Count, "F").End(xlUp))
End With
For Each cell1 In rng1
If Application.CountIf(rng2, cell1) > 0 Then
res = Application.Match(cell1.Value, rng2, 0)
If Not IsError(res) Then
Set cell2 = rng2(res)
cell2.Offset(0, 3).Copy cell1.Offset(0, 13)
Else
MsgBox "Should be a match, but fails: " & cell1.Value
End If
End If
Next cell1
End Sub

Adjust to fit you actual needs.

 

[amitmandalia]

Sub Macro1()

Sheets("Sheet1").Select
Range("H2").Select
Sheets("mis tool").Select
Range("F2").Select
Sheets("Sheet1").Select
Do
Do While ActiveCell <> 0
a = ActiveCell.Value
b = Sheets("mis tool").Select
If a = b Then
Sheets("mis tool").Select
ActiveCell.Offset(0, 3).Select
Selection.Copy
Sheets("Sheet1").Select
ActiveCell.Offset(0, 13).Select
ActiveSheet.Paste
ElseIf a <> b Then
Sheets("Sheet1").Select
ActiveCell.Offset(1, 0).Select
End If
Loop
If ActiveCell = 0 Then
Sheets("Sheet1").Select
Range("H2").Select
Sheets("mis tool").Select
ActiveCell.Offset(1, 0).Select
End If
Loop Until ActiveCell = 0

End Sub


this is the code i am using now, this can go through the range, but
now does not copy the cells that i want and paste them into the sheet.
i want it to check if the cells are the same, and them copy cells
related to that information

 

[Tom Ogilvy]

Apparently the code and advice I provided is unacceptable to you, so work
with Joel.

 

[Joel]

I think the problem is the sheet may not be activated ocorrectly. this code
may better

Sub Macro1()
Worksheets("Sheet1").Activate
Set sheet1_sel = Worksheets("Sheet1").Range("H2")
Worksheets("mis tool").Activate
Set mis_tool_sel = Sheets("mis tool").Range("F2")
Do
Do While sheet1_sel.Value <> 0
a = sheet1_sel.Value
b = mis_tool_sel.Value
If a = b Then
Worksheets("mis tool").Activate
mis_tool_sel.Select
ActiveCell.Offset(0, 3).Select
Application.CutCopyMode = False
Selection.Copy
Worksheets("Sheet1").Activate
sheet1_sel.Select
ActiveCell.Offset(0, 13).Select
ActiveSheet.Paste
ElseIf a <> b Then
Worksheets("Sheet1").Activate
sheet1_sel.Select
ActiveCell.Offset(1, 0).Select
End If
Loop
If ActiveCell = 0 Then
Worksheets("Sheet1").Activate
sheet1_sel.Select
Worksheets("mis tool").Activate
mis_tool_sel.Select
ActiveCell.Offset(1, 0).Select
End If
Loop Until ActiveCell = 0
End Sub