Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing Cell values in two sheets within the same workbook
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing Cell values in two sheets within the same workbook
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 " 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing Cell values in two sheets within the same workbook
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 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 " 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 - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing Cell values in two sheets within the same workbook
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. " 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 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 " 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 - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing Cell values in two sheets within the same workbook
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 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. " 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 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 " 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 - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing Cell values in two sheets within the same workbook
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, 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 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. " 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 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 " 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 - |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing Cell values in two sheets within the same workbook
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. " 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, 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 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. " 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 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 " 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 - |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing Cell values in two sheets within the same workbook
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 wrote in message oups.com... 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, 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 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. " 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 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 " 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 - |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing Cell values in two sheets within the same workbook
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" 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 wrote in message oups.com... 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, 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 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. " 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 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 " 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 - |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing Cell values in two sheets within the same workbook
Apparently the code and advice I provided is unacceptable to you, so work
with Joel. -- Regards, Tom Ogilvy wrote in message oups.com... 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" 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 wrote in message oups.com... 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, 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 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. " 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 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 " 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 - |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing Cell values in two sheets within the same workbook
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 " wrote: 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" 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 wrote in message oups.com... 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, 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 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. " 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 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 " 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 - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
comparing values from one workbook with another workbook | Excel Discussion (Misc queries) | |||
comparing values on different sheets and deleting | Excel Discussion (Misc queries) | |||
Comparing data in two sheets with similar values | Excel Worksheet Functions | |||
comparing 2 similar columns on seperate work sheets in 1 workbook | Excel Discussion (Misc queries) | |||
Comparing cell values | Excel Programming |