Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching two ranges
I'm trying to iterate through range on one worksheet, and then look for a
matching name on a second worksheet where a count would be added when there's a match. My code follows, but it's generating a Next without For error message. Any help straightening this out would be hugely appreciated. Ken Public Sub sUpdateCollegeCounts() On Error GoTo 0 Dim strCollege As String Dim lngCount As Long Dim Cell As Range Dim Cell2 As Range With ThisWorkbook.Worksheets("Respondent Profile") For Each Cell In Range("RespondentID") If Len(Cell.Offset(0, 1)) 0 Then strCollege = Cell.Offset(0, 1) Else strCollege = "No college or non-response" End If Worksheets("Response by College").Activate For Each Cell2 In Range("hColleges") If Cell.Value = strCollege Then Cell.Offset(1, 0) = Cell.Offset(1, 0).Value + 1 Next Cell2 Worksheets("Respondent Profile").Activate Next Cell End With PROC_EXIT: Exit Sub End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching two ranges
Try this:
Public Sub sUpdateCollegeCounts() On Error GoTo 0 Dim strCollege As String Dim lngCount As Long Dim Cell As Range Dim Cell2 As Range With ThisWorkbook.Worksheets("Respondent Profile") For Each Cell In Range("RespondentID") If Len(Cell.Offset(0, 1)) 0 Then strCollege = Cell.Offset(0, 1) Else strCollege = "No college or non-response" End If Worksheets("Response by College").Activate For Each Cell2 In Range("hColleges") If Cell.Value = strCollege Then Cell.Offset(1, 0) = Cell.Offset(1, 0).Value + 1 End If Next Cell2 Worksheets("Respondent Profile").Activate Next Cell End With PROC_EXIT: Exit Sub End Sub RBS "Ken Warthen" wrote in message ... I'm trying to iterate through range on one worksheet, and then look for a matching name on a second worksheet where a count would be added when there's a match. My code follows, but it's generating a Next without For error message. Any help straightening this out would be hugely appreciated. Ken Public Sub sUpdateCollegeCounts() On Error GoTo 0 Dim strCollege As String Dim lngCount As Long Dim Cell As Range Dim Cell2 As Range With ThisWorkbook.Worksheets("Respondent Profile") For Each Cell In Range("RespondentID") If Len(Cell.Offset(0, 1)) 0 Then strCollege = Cell.Offset(0, 1) Else strCollege = "No college or non-response" End If Worksheets("Response by College").Activate For Each Cell2 In Range("hColleges") If Cell.Value = strCollege Then Cell.Offset(1, 0) = Cell.Offset(1, 0).Value + 1 Next Cell2 Worksheets("Respondent Profile").Activate Next Cell End With PROC_EXIT: Exit Sub End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Searching two ranges
Thanks. That got me back on track.
Ken "RB Smissaert" wrote: Try this: Public Sub sUpdateCollegeCounts() On Error GoTo 0 Dim strCollege As String Dim lngCount As Long Dim Cell As Range Dim Cell2 As Range With ThisWorkbook.Worksheets("Respondent Profile") For Each Cell In Range("RespondentID") If Len(Cell.Offset(0, 1)) 0 Then strCollege = Cell.Offset(0, 1) Else strCollege = "No college or non-response" End If Worksheets("Response by College").Activate For Each Cell2 In Range("hColleges") If Cell.Value = strCollege Then Cell.Offset(1, 0) = Cell.Offset(1, 0).Value + 1 End If Next Cell2 Worksheets("Respondent Profile").Activate Next Cell End With PROC_EXIT: Exit Sub End Sub RBS "Ken Warthen" wrote in message ... I'm trying to iterate through range on one worksheet, and then look for a matching name on a second worksheet where a count would be added when there's a match. My code follows, but it's generating a Next without For error message. Any help straightening this out would be hugely appreciated. Ken Public Sub sUpdateCollegeCounts() On Error GoTo 0 Dim strCollege As String Dim lngCount As Long Dim Cell As Range Dim Cell2 As Range With ThisWorkbook.Worksheets("Respondent Profile") For Each Cell In Range("RespondentID") If Len(Cell.Offset(0, 1)) 0 Then strCollege = Cell.Offset(0, 1) Else strCollege = "No college or non-response" End If Worksheets("Response by College").Activate For Each Cell2 In Range("hColleges") If Cell.Value = strCollege Then Cell.Offset(1, 0) = Cell.Offset(1, 0).Value + 1 Next Cell2 Worksheets("Respondent Profile").Activate Next Cell End With PROC_EXIT: Exit Sub End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Searching into Non-Contiguous ranges in a columns | Excel Programming | |||
Searching into Non-Contiguous ranges in a columns | Excel Programming | |||
Searching certain ranges and stopping when finding no match | Excel Programming | |||
Searching, matching then searching another list based on the match | Excel Discussion (Misc queries) | |||
Searching and matching the textvalues in specific column ranges | Excel Programming |