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

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


"(E-Mail Removed)" wrote:

> 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
>
>

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


On 16 Feb, 11:47, Joel <J...@discussions.microsoft.com> wrote:
> 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
>
>
>
> "amitmanda...@gmail.com" wrote:
> > 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- Hide quoted text -
>
> - Show quoted text -

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.

"(E-Mail Removed)" wrote:

> 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
>
>
> On 16 Feb, 11:47, Joel <J...@discussions.microsoft.com> wrote:
> > 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
> >
> >
> >
> > "amitmanda...@gmail.com" wrote:
> > > 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- Hide quoted text -
> >
> > - Show quoted text -
>
>
>

it fails at the b line.

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

On 16 Feb, 13:00, Joel <J...@discussions.microsoft.com> wrote:
> 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.
>
>
>
> "amitmanda...@gmail.com" wrote:
> > 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
>
> > On 16 Feb, 11:47, Joel <J...@discussions.microsoft.com> wrote:
> > > 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
>
> > > "amitmanda...@gmail.com" wrote:
> > > > 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- Hide quoted text -
>
> > > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

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

On 16 Feb, 13:09, amitmanda...@gmail.com wrote:
> it fails at the b line.
>
> if i change the target cell formats to the same, would that help?
>
> On 16 Feb, 13:00, Joel <J...@discussions.microsoft.com> wrote:
>
>
>
> > 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.
>
> > "amitmanda...@gmail.com" wrote:
> > > 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
>
> > > On 16 Feb, 11:47, Joel <J...@discussions.microsoft.com> wrote:
> > > > 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
>
> > > > "amitmanda...@gmail.com" wrote:
> > > > > 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- Hide quoted text -
>
> > > > - Show quoted text -- Hide quoted text -
>
> > - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

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.


"(E-Mail Removed)" wrote:

> 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
>
> On 16 Feb, 13:09, amitmanda...@gmail.com wrote:
> > it fails at the b line.
> >
> > if i change the target cell formats to the same, would that help?
> >
> > On 16 Feb, 13:00, Joel <J...@discussions.microsoft.com> wrote:
> >
> >
> >
> > > 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.
> >
> > > "amitmanda...@gmail.com" wrote:
> > > > 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
> >
> > > > On 16 Feb, 11:47, Joel <J...@discussions.microsoft.com> wrote:
> > > > > 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
> >
> > > > > "amitmanda...@gmail.com" wrote:
> > > > > > 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- Hide quoted text -
> >
> > > > > - Show quoted text -- Hide quoted text -
> >
> > > - Show quoted text -- Hide quoted text -
> >
> > - Show quoted text -
>
>
>

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.

--
Regards,
Tom Ogilvy




<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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
>
> On 16 Feb, 13:09, amitmanda...@gmail.com wrote:
>> it fails at the b line.
>>
>> if i change the target cell formats to the same, would that help?
>>
>> On 16 Feb, 13:00, Joel <J...@discussions.microsoft.com> wrote:
>>
>>
>>
>> > 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.
>>
>> > "amitmanda...@gmail.com" wrote:
>> > > 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
>>
>> > > On 16 Feb, 11:47, Joel <J...@discussions.microsoft.com> wrote:
>> > > > 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
>>
>> > > > "amitmanda...@gmail.com" wrote:
>> > > > > 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- Hide quoted text -
>>
>> > > > - Show quoted text -- Hide quoted text -
>>
>> > - Show quoted text -- Hide quoted text -
>>
>> - Show quoted text -
>
>

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

On 16 Feb, 14:19, "Tom Ogilvy" <twogi...@msn.com> wrote:
> 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.
>
> --
> Regards,
> Tom Ogilvy
>
> <amitmanda...@gmail.com> wrote in message
>
> news:(E-Mail Removed)...
>
>
>
> >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
>
> > On 16 Feb, 13:09, amitmanda...@gmail.com wrote:
> >> it fails at the b line.
>
> >> if i change the target cell formats to the same, would that help?
>
> >> On 16 Feb, 13:00, Joel <J...@discussions.microsoft.com> wrote:
>
> >> > 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.
>
> >> > "amitmanda...@gmail.com" wrote:
> >> > > 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
>
> >> > > On 16 Feb, 11:47, Joel <J...@discussions.microsoft.com> wrote:
> >> > > > 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
>
> >> > > > "amitmanda...@gmail.com" wrote:
> >> > > > > 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- Hide quoted text -
>
> >> > > > - Show quoted text -- Hide quoted text -
>
> >> > - Show quoted text -- Hide quoted text -
>
> >> - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

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

--
Regards,
Tom Ogilvy

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
> On 16 Feb, 14:19, "Tom Ogilvy" <twogi...@msn.com> wrote:
>> 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.
>>
>> --
>> Regards,
>> Tom Ogilvy
>>
>> <amitmanda...@gmail.com> wrote in message
>>
>> news:(E-Mail Removed)...
>>
>>
>>
>> >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
>>
>> > On 16 Feb, 13:09, amitmanda...@gmail.com wrote:
>> >> it fails at the b line.
>>
>> >> if i change the target cell formats to the same, would that help?
>>
>> >> On 16 Feb, 13:00, Joel <J...@discussions.microsoft.com> wrote:
>>
>> >> > 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.
>>
>> >> > "amitmanda...@gmail.com" wrote:
>> >> > > 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
>>
>> >> > > On 16 Feb, 11:47, Joel <J...@discussions.microsoft.com> wrote:
>> >> > > > 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
>>
>> >> > > > "amitmanda...@gmail.com" wrote:
>> >> > > > > 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- Hide quoted text -
>>
>> >> > > > - Show quoted text -- Hide quoted text -
>>
>> >> > - Show quoted text -- Hide quoted text -
>>
>> >> - Show quoted text -- Hide quoted text -
>>
>> - Show quoted text -
>
>