| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare cell content from two worksheets
I am importing two separate excel files into a master file that has macros
performing multiple tasks. There is a chance that the operator can select the wrong second file to import. I want to compare two cells in each of the two worksheets to determine if they are equal. The first file will be copied into the worksheet "Item Charts" the second into worksheet "Wire Charts". I need to verify that cell A2 in worksheet "Wire Charts" is equal to cell A2 in worksheet "Item Charts". As you can see, I have tried multiple ways and continue to fail. Problem starts at line "ActiveCell.FormulaR1C1". This is what I have: On Error GoTo errorhandler Workbooks.Open Filename:=Dir(Application.GetOpenFilename), UpdateLinks:=0 Range("A1").Select cellcheck = ActiveCell(1, 1) If cellcheck < "POS NBR" Then Application.ScreenUpdating = True MsgBox ("This File " & filetoopenitems & " Does Not Seem to be an Item Chart, please check the file name and start again") ActiveWorkbook.Close ThisWorkbook.Activate End End If Cells.Select Selection.Copy Windows("QIC_AM_r06 for GSD.xls").Activate Sheets("Item Charts").Select Range("A1").Select ActiveSheet.Paste Application.CutCopyMode = False Cells.Select Cells.EntireColumn.autofit Range("A1").Select Workbooks.Open Filename:=Dir(Application.GetOpenFilename), UpdateLinks:=0 Range("A1").Select cellcheck = ActiveCell(1, 1) If cellcheck < "CIRCUIT NBR" Then Application.ScreenUpdating = True MsgBox ("This File " & filetoopenitems & " Does Not Seem to be a Wire Chart, please check the file name and start again") Dim WkbkName As Object For Each WkbkName In Application.Workbooks() If WkbkName.Name < ThisWorkbook.Name Then WkbkName.Close Next ThisWorkbook.Activate Selection.ClearContents Range("A1").Select End End If Cells.Select Selection.Copy Windows("QIC_AM_r06 for GSD.xls").Activate Sheets("Wire Charts").Select Range("A1").Select ActiveSheet.Paste Application.CutCopyMode = False Cells.Select Cells.EntireColumn.autofit Range("A1").Select For Each WkbkName In Application.Workbooks() If WkbkName.Name < ThisWorkbook.Name Then WkbkName.Close Next ' ActiveCell.FormulaR1C1 = "=IF(R[1]C<'Item Charts'!R[1]C)" ' cellcheck = ActiveCell(2, 1) ' If cellcheck < Worksheets("Item Charts")!Cells("A2") Then ' If Cells("A2") < Worksheets("Item Charts")!Cells("A2") Then Range("A2").Select ' If Cell.Value < Worksheets("Item Charts").Cells("A2").Value Then Application.ScreenUpdating = True MsgBox ("These Files Do Not Seem to be the same harness assembly, please check the file name and start again") End End If Would appreciate any help, thanks. -- John S. Walker |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Compare cell content from two worksheets
The general code could look like this:
If Workbooks("WKBK1").Sheets("Item Charts").Range("A2") = Workbook("WKBK2").Sheets("Wire Charts").Range("A2") then ' your code Else msgbox "Workbooks not equal" End if Cautions: 1. The 2 line If statement should be a single line 2. You must replace the names with the names of your workbooks & names of your sheets 2a you can use variables for all the names. 3 If A2 is numeric - be prepared to deal with minor decimal differences. 3a If A2 is text than compare LCase() = LCase() or UCase() = UCase to avoid capitalization differences. Note: it isn't necessary to use select or selection in your code. -- steveB Remove "AYN" from email to respond "John" wrote in message ... I am importing two separate excel files into a master file that has macros performing multiple tasks. There is a chance that the operator can select the wrong second file to import. I want to compare two cells in each of the two worksheets to determine if they are equal. The first file will be copied into the worksheet "Item Charts" the second into worksheet "Wire Charts". I need to verify that cell A2 in worksheet "Wire Charts" is equal to cell A2 in worksheet "Item Charts". As you can see, I have tried multiple ways and continue to fail. Problem starts at line "ActiveCell.FormulaR1C1". This is what I have: On Error GoTo errorhandler Workbooks.Open Filename:=Dir(Application.GetOpenFilename), UpdateLinks:=0 Range("A1").Select cellcheck = ActiveCell(1, 1) If cellcheck < "POS NBR" Then Application.ScreenUpdating = True MsgBox ("This File " & filetoopenitems & " Does Not Seem to be an Item Chart, please check the file name and start again") ActiveWorkbook.Close ThisWorkbook.Activate End End If Cells.Select Selection.Copy Windows("QIC_AM_r06 for GSD.xls").Activate Sheets("Item Charts").Select Range("A1").Select ActiveSheet.Paste Application.CutCopyMode = False Cells.Select Cells.EntireColumn.autofit Range("A1").Select Workbooks.Open Filename:=Dir(Application.GetOpenFilename), UpdateLinks:=0 Range("A1").Select cellcheck = ActiveCell(1, 1) If cellcheck < "CIRCUIT NBR" Then Application.ScreenUpdating = True MsgBox ("This File " & filetoopenitems & " Does Not Seem to be a Wire Chart, please check the file name and start again") Dim WkbkName As Object For Each WkbkName In Application.Workbooks() If WkbkName.Name < ThisWorkbook.Name Then WkbkName.Close Next ThisWorkbook.Activate Selection.ClearContents Range("A1").Select End End If Cells.Select Selection.Copy Windows("QIC_AM_r06 for GSD.xls").Activate Sheets("Wire Charts").Select Range("A1").Select ActiveSheet.Paste Application.CutCopyMode = False Cells.Select Cells.EntireColumn.autofit Range("A1").Select For Each WkbkName In Application.Workbooks() If WkbkName.Name < ThisWorkbook.Name Then WkbkName.Close Next ' ActiveCell.FormulaR1C1 = "=IF(R[1]C<'Item Charts'!R[1]C)" ' cellcheck = ActiveCell(2, 1) ' If cellcheck < Worksheets("Item Charts")!Cells("A2") Then ' If Cells("A2") < Worksheets("Item Charts")!Cells("A2") Then Range("A2").Select ' If Cell.Value < Worksheets("Item Charts").Cells("A2").Value Then Application.ScreenUpdating = True MsgBox ("These Files Do Not Seem to be the same harness assembly, please check the file name and start again") End End If Would appreciate any help, thanks. -- John S. Walker |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Compare cell content from two worksheets
Thank you Steve. I modified the names of the workbooks and changed the "=" to
"<" and it is perfect. I don't know why I had such a problem with this, DUH. -- John S. Walker "STEVE BELL" wrote: The general code could look like this: If Workbooks("WKBK1").Sheets("Item Charts").Range("A2") = Workbook("WKBK2").Sheets("Wire Charts").Range("A2") then ' your code Else msgbox "Workbooks not equal" End if Cautions: 1. The 2 line If statement should be a single line 2. You must replace the names with the names of your workbooks & names of your sheets 2a you can use variables for all the names. 3 If A2 is numeric - be prepared to deal with minor decimal differences. 3a If A2 is text than compare LCase() = LCase() or UCase() = UCase to avoid capitalization differences. Note: it isn't necessary to use select or selection in your code. -- steveB Remove "AYN" from email to respond "John" wrote in message ... I am importing two separate excel files into a master file that has macros performing multiple tasks. There is a chance that the operator can select the wrong second file to import. I want to compare two cells in each of the two worksheets to determine if they are equal. The first file will be copied into the worksheet "Item Charts" the second into worksheet "Wire Charts". I need to verify that cell A2 in worksheet "Wire Charts" is equal to cell A2 in worksheet "Item Charts". As you can see, I have tried multiple ways and continue to fail. Problem starts at line "ActiveCell.FormulaR1C1". This is what I have: On Error GoTo errorhandler Workbooks.Open Filename:=Dir(Application.GetOpenFilename), UpdateLinks:=0 Range("A1").Select cellcheck = ActiveCell(1, 1) If cellcheck < "POS NBR" Then Application.ScreenUpdating = True MsgBox ("This File " & filetoopenitems & " Does Not Seem to be an Item Chart, please check the file name and start again") ActiveWorkbook.Close ThisWorkbook.Activate End End If Cells.Select Selection.Copy Windows("QIC_AM_r06 for GSD.xls").Activate Sheets("Item Charts").Select Range("A1").Select ActiveSheet.Paste Application.CutCopyMode = False Cells.Select Cells.EntireColumn.autofit Range("A1").Select Workbooks.Open Filename:=Dir(Application.GetOpenFilename), UpdateLinks:=0 Range("A1").Select cellcheck = ActiveCell(1, 1) If cellcheck < "CIRCUIT NBR" Then Application.ScreenUpdating = True MsgBox ("This File " & filetoopenitems & " Does Not Seem to be a Wire Chart, please check the file name and start again") Dim WkbkName As Object For Each WkbkName In Application.Workbooks() If WkbkName.Name < ThisWorkbook.Name Then WkbkName.Close Next ThisWorkbook.Activate Selection.ClearContents Range("A1").Select End End If Cells.Select Selection.Copy Windows("QIC_AM_r06 for GSD.xls").Activate Sheets("Wire Charts").Select Range("A1").Select ActiveSheet.Paste Application.CutCopyMode = False Cells.Select Cells.EntireColumn.autofit Range("A1").Select For Each WkbkName In Application.Workbooks() If WkbkName.Name < ThisWorkbook.Name Then WkbkName.Close Next ' ActiveCell.FormulaR1C1 = "=IF(R[1]C<'Item Charts'!R[1]C)" ' cellcheck = ActiveCell(2, 1) ' If cellcheck < Worksheets("Item Charts")!Cells("A2") Then ' If Cells("A2") < Worksheets("Item Charts")!Cells("A2") Then Range("A2").Select ' If Cell.Value < Worksheets("Item Charts").Cells("A2").Value Then Application.ScreenUpdating = True MsgBox ("These Files Do Not Seem to be the same harness assembly, please check the file name and start again") End End If Would appreciate any help, thanks. -- John S. Walker |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Compare cell content from two worksheets
John,
Glad to hear you got it to work!!! It always gets tricky for me when working with workbooks and worksheets. Sometimes I have to try many different approaches... The main trick is getting rid of all the select's.... keep on Exceling... -- steveB Remove "AYN" from email to respond "John" wrote in message ... Thank you Steve. I modified the names of the workbooks and changed the "=" to "<" and it is perfect. I don't know why I had such a problem with this, DUH. -- John S. Walker "STEVE BELL" wrote: The general code could look like this: If Workbooks("WKBK1").Sheets("Item Charts").Range("A2") = Workbook("WKBK2").Sheets("Wire Charts").Range("A2") then ' your code Else msgbox "Workbooks not equal" End if Cautions: 1. The 2 line If statement should be a single line 2. You must replace the names with the names of your workbooks & names of your sheets 2a you can use variables for all the names. 3 If A2 is numeric - be prepared to deal with minor decimal differences. 3a If A2 is text than compare LCase() = LCase() or UCase() = UCase to avoid capitalization differences. Note: it isn't necessary to use select or selection in your code. -- steveB Remove "AYN" from email to respond "John" wrote in message ... I am importing two separate excel files into a master file that has macros performing multiple tasks. There is a chance that the operator can select the wrong second file to import. I want to compare two cells in each of the two worksheets to determine if they are equal. The first file will be copied into the worksheet "Item Charts" the second into worksheet "Wire Charts". I need to verify that cell A2 in worksheet "Wire Charts" is equal to cell A2 in worksheet "Item Charts". As you can see, I have tried multiple ways and continue to fail. Problem starts at line "ActiveCell.FormulaR1C1". This is what I have: On Error GoTo errorhandler Workbooks.Open Filename:=Dir(Application.GetOpenFilename), UpdateLinks:=0 Range("A1").Select cellcheck = ActiveCell(1, 1) If cellcheck < "POS NBR" Then Application.ScreenUpdating = True MsgBox ("This File " & filetoopenitems & " Does Not Seem to be an Item Chart, please check the file name and start again") ActiveWorkbook.Close ThisWorkbook.Activate End End If Cells.Select Selection.Copy Windows("QIC_AM_r06 for GSD.xls").Activate Sheets("Item Charts").Select Range("A1").Select ActiveSheet.Paste Application.CutCopyMode = False Cells.Select Cells.EntireColumn.autofit Range("A1").Select Workbooks.Open Filename:=Dir(Application.GetOpenFilename), UpdateLinks:=0 Range("A1").Select cellcheck = ActiveCell(1, 1) If cellcheck < "CIRCUIT NBR" Then Application.ScreenUpdating = True MsgBox ("This File " & filetoopenitems & " Does Not Seem to be a Wire Chart, please check the file name and start again") Dim WkbkName As Object For Each WkbkName In Application.Workbooks() If WkbkName.Name < ThisWorkbook.Name Then WkbkName.Close Next ThisWorkbook.Activate Selection.ClearContents Range("A1").Select End End If Cells.Select Selection.Copy Windows("QIC_AM_r06 for GSD.xls").Activate Sheets("Wire Charts").Select Range("A1").Select ActiveSheet.Paste Application.CutCopyMode = False Cells.Select Cells.EntireColumn.autofit Range("A1").Select For Each WkbkName In Application.Workbooks() If WkbkName.Name < ThisWorkbook.Name Then WkbkName.Close Next ' ActiveCell.FormulaR1C1 = "=IF(R[1]C<'Item Charts'!R[1]C)" ' cellcheck = ActiveCell(2, 1) ' If cellcheck < Worksheets("Item Charts")!Cells("A2") Then ' If Cells("A2") < Worksheets("Item Charts")!Cells("A2") Then Range("A2").Select ' If Cell.Value < Worksheets("Item Charts").Cells("A2").Value Then Application.ScreenUpdating = True MsgBox ("These Files Do Not Seem to be the same harness assembly, please check the file name and start again") End End If Would appreciate any help, thanks. -- John S. Walker |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sort worksheets by cell content | Excel Discussion (Misc queries) | |||
| how to compare cell content to a row pf data in excel and return . | Excel Discussion (Misc queries) | |||
| Copy content of cell to another depending on value of third cell(between worksheets) | Excel Worksheet Functions | |||
| Compare and match names and extract a cell content | Excel Worksheet Functions | |||
| how do i compare 2 spread sheets if the content of a cell is avai. | Excel Worksheet Functions | |||
Linear Mode
