| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Next w/o For?
I'm working on a macro that searches for text in certain cells and
counts the number if times it finds those text values. So far, I've got: Sub CountReasonContactCode() Dim Wksht As Worksheet Dim rReason As Range Dim lRow As Long Dim lCurRow As Long Dim rCell As Range Dim l16Rct As Long Dim l16Act As Long Dim l16BGct As Long Dim lCt As Long lRow = 107 l16Rct = 0 l16Act = 0 l16BGct = 0 Set rReason = ActiveSheet.Range("D8", Cells(lRow, "D")) rReason.Select For Each rCell In rReason If rCell = "16" Then lCt = InStr(1, rCell.Offset(0, 2).Value, "R") If lCt 0 Then l16Rct = l16Rct + 1 lCt = 0 lCt = InStr(1, rCell.Offset(0, 2).Value, "A") If lCt 0 Then l16Act = l16Act + 1 lCt = 0 lCt = InStr(1, rCell.Offset(0, 2).Value, "B") If lCt 0 Then l16BGct = l16BGct + 1 lCt = 0 End If Next rCell End Sub Getting a compile error, "Next without for". I see a next and a for, so can't figure out why XL isn't seeing it. What am I missing? Thanks in advance. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Next w/o For?
You are missing your "End If"s That will generate the error you are getting.
-- HTH... Jim Thomlinson "davegb" wrote: I'm working on a macro that searches for text in certain cells and counts the number if times it finds those text values. So far, I've got: Sub CountReasonContactCode() Dim Wksht As Worksheet Dim rReason As Range Dim lRow As Long Dim lCurRow As Long Dim rCell As Range Dim l16Rct As Long Dim l16Act As Long Dim l16BGct As Long Dim lCt As Long lRow = 107 l16Rct = 0 l16Act = 0 l16BGct = 0 Set rReason = ActiveSheet.Range("D8", Cells(lRow, "D")) rReason.Select For Each rCell In rReason If rCell = "16" Then lCt = InStr(1, rCell.Offset(0, 2).Value, "R") If lCt 0 Then l16Rct = l16Rct + 1 lCt = 0 lCt = InStr(1, rCell.Offset(0, 2).Value, "A") If lCt 0 Then l16Act = l16Act + 1 lCt = 0 lCt = InStr(1, rCell.Offset(0, 2).Value, "B") If lCt 0 Then l16BGct = l16BGct + 1 lCt = 0 End If Next rCell End Sub Getting a compile error, "Next without for". I see a next and a for, so can't figure out why XL isn't seeing it. What am I missing? Thanks in advance. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Next w/o For?
Jim Thomlinson wrote: You are missing your "End If"s That will generate the error you are getting. -- HTH... Jim Thomlinson "davegb" wrote: I'm working on a macro that searches for text in certain cells and counts the number if times it finds those text values. So far, I've got: Sub CountReasonContactCode() Dim Wksht As Worksheet Dim rReason As Range Dim lRow As Long Dim lCurRow As Long Dim rCell As Range Dim l16Rct As Long Dim l16Act As Long Dim l16BGct As Long Dim lCt As Long lRow = 107 l16Rct = 0 l16Act = 0 l16BGct = 0 Set rReason = ActiveSheet.Range("D8", Cells(lRow, "D")) rReason.Select For Each rCell In rReason If rCell = "16" Then lCt = InStr(1, rCell.Offset(0, 2).Value, "R") If lCt 0 Then l16Rct = l16Rct + 1 lCt = 0 lCt = InStr(1, rCell.Offset(0, 2).Value, "A") If lCt 0 Then l16Act = l16Act + 1 lCt = 0 lCt = InStr(1, rCell.Offset(0, 2).Value, "B") If lCt 0 Then l16BGct = l16BGct + 1 lCt = 0 End If Next rCell End Sub Getting a compile error, "Next without for". I see a next and a for, so can't figure out why XL isn't seeing it. What am I missing? Thanks in advance. Thanks, Jim. I copied that code almost directly from Walkenbach's book! I was wondering why he didn't have any "end ifs", but figured he is the guru. Live and learn! |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Next w/o For?
By any chance did he only have one line of code following the end ifs?
You are allowed to do one instruction following then without an end if If This=True then DoThat 'Good If This=True then _ DoThat 'Good If This=True then DoThat DoThe Other 'Bad If This=True then DoThat Do The Other End If 'Good -- HTH... Jim Thomlinson "davegb" wrote: Jim Thomlinson wrote: You are missing your "End If"s That will generate the error you are getting. -- HTH... Jim Thomlinson "davegb" wrote: I'm working on a macro that searches for text in certain cells and counts the number if times it finds those text values. So far, I've got: Sub CountReasonContactCode() Dim Wksht As Worksheet Dim rReason As Range Dim lRow As Long Dim lCurRow As Long Dim rCell As Range Dim l16Rct As Long Dim l16Act As Long Dim l16BGct As Long Dim lCt As Long lRow = 107 l16Rct = 0 l16Act = 0 l16BGct = 0 Set rReason = ActiveSheet.Range("D8", Cells(lRow, "D")) rReason.Select For Each rCell In rReason If rCell = "16" Then lCt = InStr(1, rCell.Offset(0, 2).Value, "R") If lCt 0 Then l16Rct = l16Rct + 1 lCt = 0 lCt = InStr(1, rCell.Offset(0, 2).Value, "A") If lCt 0 Then l16Act = l16Act + 1 lCt = 0 lCt = InStr(1, rCell.Offset(0, 2).Value, "B") If lCt 0 Then l16BGct = l16BGct + 1 lCt = 0 End If Next rCell End Sub Getting a compile error, "Next without for". I see a next and a for, so can't figure out why XL isn't seeing it. What am I missing? Thanks in advance. Thanks, Jim. I copied that code almost directly from Walkenbach's book! I was wondering why he didn't have any "end ifs", but figured he is the guru. Live and learn! |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Next w/o For?
Jim Thomlinson wrote: By any chance did he only have one line of code following the end ifs? You are allowed to do one instruction following then without an end if If This=True then DoThat 'Good If This=True then _ DoThat 'Good If This=True then DoThat DoThe Other 'Bad If This=True then DoThat Do The Other End If 'Good -- HTH... Jim Thomlinson LOL! Another one of those "if you're in an odd numbered zip code and your uncle wears a toupe, you can..." VBA seems to have a lot of those! If you mean one line of code following the "ifs", then yes. Actually, he had only one line for each if statement. Thanks for the help! "davegb" wrote: Jim Thomlinson wrote: You are missing your "End If"s That will generate the error you are getting. -- HTH... Jim Thomlinson "davegb" wrote: I'm working on a macro that searches for text in certain cells and counts the number if times it finds those text values. So far, I've got: Sub CountReasonContactCode() Dim Wksht As Worksheet Dim rReason As Range Dim lRow As Long Dim lCurRow As Long Dim rCell As Range Dim l16Rct As Long Dim l16Act As Long Dim l16BGct As Long Dim lCt As Long lRow = 107 l16Rct = 0 l16Act = 0 l16BGct = 0 Set rReason = ActiveSheet.Range("D8", Cells(lRow, "D")) rReason.Select For Each rCell In rReason If rCell = "16" Then lCt = InStr(1, rCell.Offset(0, 2).Value, "R") If lCt 0 Then l16Rct = l16Rct + 1 lCt = 0 lCt = InStr(1, rCell.Offset(0, 2).Value, "A") If lCt 0 Then l16Act = l16Act + 1 lCt = 0 lCt = InStr(1, rCell.Offset(0, 2).Value, "B") If lCt 0 Then l16BGct = l16BGct + 1 lCt = 0 End If Next rCell End Sub Getting a compile error, "Next without for". I see a next and a for, so can't figure out why XL isn't seeing it. What am I missing? Thanks in advance. Thanks, Jim. I copied that code almost directly from Walkenbach's book! I was wondering why he didn't have any "end ifs", but figured he is the guru. Live and learn! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
