| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Determine if any Cell in Range Equals a String
I am using the following code to check for errors before appending data
to a sheet in the workbook. I get a data type mismatch for the first second line: If Sheets("Error Report").Range("E11:E67") = "Error" Then How do I check the range E11:E67 on the sheet "Error Report" to determine if any cell in that range is equal to "Error"? Private Sub CommandButton4_Click() 'Check for Errors If Sheets("Error Report").Range("E11:E67") = "Error" Then MsgBox "You have unresolved ERRORS. Please View Report and resolve all ERRORS before proceeding." Exit Sub Else 'Export Data ActiveWindow.ScrollWorkbookTabs Sheets:=1 Sheets("Summary Totals").Select Sheets("Summary Totals").Range("A9:O15").Select Selection.Copy Sheets("Compiled Totals").Select Sheets("Compiled Totals").Range("A9").Select Do Until ActiveCell.Offset(0, 1).Value = "" ActiveCell.Offset(1, 0).Range("A1").Select Loop Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Field Rep Time Sheet").Select Range("A19").Select End If End Sub Thanks. Connie |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Determine if any Cell in Range Equals a String
Connie,
Do you mean exact word "Error" or an error that is generated by Excel, for whatever reason ? NickHK "Connie" wrote in message oups.com... I am using the following code to check for errors before appending data to a sheet in the workbook. I get a data type mismatch for the first second line: If Sheets("Error Report").Range("E11:E67") = "Error" Then How do I check the range E11:E67 on the sheet "Error Report" to determine if any cell in that range is equal to "Error"? Private Sub CommandButton4_Click() 'Check for Errors If Sheets("Error Report").Range("E11:E67") = "Error" Then MsgBox "You have unresolved ERRORS. Please View Report and resolve all ERRORS before proceeding." Exit Sub Else 'Export Data ActiveWindow.ScrollWorkbookTabs Sheets:=1 Sheets("Summary Totals").Select Sheets("Summary Totals").Range("A9:O15").Select Selection.Copy Sheets("Compiled Totals").Select Sheets("Compiled Totals").Range("A9").Select Do Until ActiveCell.Offset(0, 1).Value = "" ActiveCell.Offset(1, 0).Range("A1").Select Loop Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Field Rep Time Sheet").Select Range("A19").Select End If End Sub Thanks. Connie |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Determine if any Cell in Range Equals a String
Connie
You cannot compare a single value against a range of cells. You could loop through each cell testing its value, but it's quicker to get Excel to count them for you: If Application.CountIf(Sheets(1).Range("E11:E67"), "Error") 0 Then MsgBox "You have unresolved ERRORS. Please View Report and resolve all ERRORS before proceeding." Exit Sub End If NickHK "Connie" wrote in message oups.com... I am using the following code to check for errors before appending data to a sheet in the workbook. I get a data type mismatch for the first second line: If Sheets("Error Report").Range("E11:E67") = "Error" Then How do I check the range E11:E67 on the sheet "Error Report" to determine if any cell in that range is equal to "Error"? Private Sub CommandButton4_Click() 'Check for Errors If Sheets("Error Report").Range("E11:E67") = "Error" Then MsgBox "You have unresolved ERRORS. Please View Report and resolve all ERRORS before proceeding." Exit Sub Else 'Export Data ActiveWindow.ScrollWorkbookTabs Sheets:=1 Sheets("Summary Totals").Select Sheets("Summary Totals").Range("A9:O15").Select Selection.Copy Sheets("Compiled Totals").Select Sheets("Compiled Totals").Range("A9").Select Do Until ActiveCell.Offset(0, 1).Value = "" ActiveCell.Offset(1, 0).Range("A1").Select Loop Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Field Rep Time Sheet").Select Range("A19").Select End If End Sub Thanks. Connie |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Determine if any Cell in Range Equals a String
Thanks, Nick. Makes sense. I knew it was something obvious. The code
works. I was testing for the actual value of "Error" (in answer to your question). Thanks again. NickHK wrote: Connie You cannot compare a single value against a range of cells. You could loop through each cell testing its value, but it's quicker to get Excel to count them for you: If Application.CountIf(Sheets(1).Range("E11:E67"), "Error") 0 Then MsgBox "You have unresolved ERRORS. Please View Report and resolve all ERRORS before proceeding." Exit Sub End If NickHK "Connie" wrote in message oups.com... I am using the following code to check for errors before appending data to a sheet in the workbook. I get a data type mismatch for the first second line: If Sheets("Error Report").Range("E11:E67") = "Error" Then How do I check the range E11:E67 on the sheet "Error Report" to determine if any cell in that range is equal to "Error"? Private Sub CommandButton4_Click() 'Check for Errors If Sheets("Error Report").Range("E11:E67") = "Error" Then MsgBox "You have unresolved ERRORS. Please View Report and resolve all ERRORS before proceeding." Exit Sub Else 'Export Data ActiveWindow.ScrollWorkbookTabs Sheets:=1 Sheets("Summary Totals").Select Sheets("Summary Totals").Range("A9:O15").Select Selection.Copy Sheets("Compiled Totals").Select Sheets("Compiled Totals").Range("A9").Select Do Until ActiveCell.Offset(0, 1).Value = "" ActiveCell.Offset(1, 0).Range("A1").Select Loop Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Field Rep Time Sheet").Select Range("A19").Select End If End Sub Thanks. Connie |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| If a cell equals _, at the next row that equals _, return value fr | Excel Worksheet Functions | |||
| If cell is left blank, or equals zero, then cell equals a different cell | Excel Discussion (Misc queries) | |||
| Determine if range has NO Blank Cells without looping through each cell in range | Excel Programming | |||
| if cell contains string then make cell next to it equals "X" | Excel Programming | |||
| if a:a (range) equals january and c:c equals gas then add g:g ($) | Excel Worksheet Functions | |||
Linear Mode
