Code Comparison

[zmatus]

Example Excel Data:
<TABLE style="WIDTH: 944pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=1257><COLGROUP><COL style="WIDTH: 70pt; mso-width-source: userset; mso-width-alt: 3401" width=93><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3510" width=96><COL style="WIDTH: 92pt; mso-width-source: userset; mso-width-alt: 4461" width=122><COL style="WIDTH: 710pt; mso-width-source: userset; mso-width-alt: 34596" width=946><TBODY><TR style="HEIGHT: 36pt; mso-height-source: userset" height=48><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 70pt; HEIGHT: 36pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl66 height=48 width=93>384/936-7314 </TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 72pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl64 width=96>JK015876</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 92pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl64 width=122>King,Julia</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 710pt; BORDER-TOP: silver 0.5pt solid; BORDER-RIGHT: silver 0.5pt solid" class=xl64 width=946>example notes here</TD></TR><TR style="HEIGHT: 36pt; mso-height-source: userset" height=48><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 70pt; HEIGHT: 36pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl66 height=48 width=93>384/936-7315 </TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 72pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl64 width=96>JS015683</TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 92pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl64 width=122>smith,joe </TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 710pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl64 width=946></TD></TR><TR style="HEIGHT: 36pt; mso-height-source: userset" height=48><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 70pt; HEIGHT: 36pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl66 height=48 width=93>384/936-7316 </TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 72pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl64 width=96></TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 92pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl64 width=122></TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 710pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl64 width=946>example notes here</TD></TR><TR style="HEIGHT: 36pt; mso-height-source: userset" height=48><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 70pt; HEIGHT: 36pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl66 height=48 width=93>384/936-7317 </TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 72pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl64 width=96></TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 92pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl64 width=122></TD><TD style="BORDER-BOTTOM: silver 0.5pt solid; BORDER-LEFT: silver; BACKGROUND-COLOR: transparent; WIDTH: 710pt; BORDER-TOP: silver; BORDER-RIGHT: silver 0.5pt solid" class=xl64 width=946></TD></TR></TBODY></TABLE>


I have a macro button that finds if cell b# and c# are blank and if they are, that fax number row is marked as the next available fax number

I've never coded in VB and I haven't coded since college so if I'm doing something stupid, don't be surprized...

Working Code(It appears to work anyways):

Private Sub NextAvailable_Click()
Dim rngArea As Range
Set rngArea = Range("A1:A3040")
ActiveSheet.Range(Cells(ActiveCell.Row, 2), Cells(ActiveCell.Row, 3)).Select
Do
ActiveSheet.Range(Cells(ActiveCell.Row, 2), Cells(ActiveCell.Row, 3)).Offset(1, 0).Select
Loop Until ((Len(ActiveCell.Value) = 0) And (Len(ActiveCell.Offset(0, 1).Value) = 0))

ActiveSheet.Cells(ActiveCell.Row, 1).Select
If Application.Intersect(rngArea, ActiveCell) Is Nothing Then
MsgBox ("End of file reached.")
Else
'do nothing MsgBox ("In fax range")
End If
End Sub

(Non working code - It seems to crash in excel on the rngSel.Offset(1, 0).Select
line)

Private Sub NextAvailable_Click()
Dim rngArea As Range
Dim rngSel As Range
Set rngArea = Range("A1:A3040")
Set rngSel = Range(Cells(ActiveCell.Row, 2), Cells(ActiveCell.Row, 3))
Do
rngSel.Offset(1, 0).Select
'The above line appears to be what is crashing the code
ActiveSheet.Range(Cells(ActiveCell.Row, 2), Cells(ActiveCell.Row, 3)).Offset(1, 0).Select
'Loop Until ((Len(rngSel.Cells(1)) = 0) And (Len(rngSel.Cells(2)) = 0))
Loop Until ((Len(ActiveCell.Value) = 0) And (Len(ActiveCell.Offset(0, 1).Value) = 0))
ActiveSheet.Cells(ActiveCell.Row, 1).Select
If Application.Intersect(rngArea, ActiveCell) Is Nothing Then
MsgBox ("End of file reached.")
Else
'do nothing MsgBox ("In fax range")
End If
End Sub

I was just trying to clean the code up, but now I'm confused as to why the newer, in my mind, cleaner version no longer works...


Also, if this excel document is say 3,500 fax numbers long, what is the easiest way to tell how many fax numbers are unassigned? (I'm assuming I just loop through all of the data again and if the same two cells are empty I add to a counter)
Any ideas? Thanks for the help.

 

[Saagar]

Not sure, but "offset" may need to be used with a worksheet object.

Try this

ActiveSheet.rngSel.Offset(1, 0).Select

or

With ActiveSheet
.rngSel.Offset(1, 0).Select
End With

For the second quesion - I assume the Fax Number is considered unassigned if cells B and C in the row are blank. Instead of a loop, there is a better way. I will respond after you confirm.