Modify to write results into B2 down instead of msgbox

[Max]

For the sub below, instead of msgbox, I would like to write
all results into say, B2 down on the sheet. How? Thanks.

Sub findmissingnumbersinlist()
mc = 1 'for col A
For i = 2 To Cells(Rows.Count, mc).End(xlUp).Row - 1
If Cells(i + 1, mc) <> Cells(i, mc) + 1 Then
MsgBox "Missing " & Cells(i, mc) + 1 & " at row " & i + 1
End If
Next i
End Sub

 

[Mike H]

Hi,

I assume this does what you want because ity doesn't do what the sub name
implies i.e. find the mssing numbers in a series. It will work OK for 1
mising number but not 2 or more consecutive missing ones.

Howere this replicates the original code but now puts the missing numbers in
B2 down

Sub findmissingnumbersinlist()
mc = 1 'for col A
bc = 2 'column B
For i = 2 To Cells(Rows.Count, mc).End(xlUp).Row - 1
If Cells(i + 1, mc) <> Cells(i, mc) + 1 Then
Cells(bc, 2).Value = Cells(i, mc) + 1
bc = bc + 1
End If
Next i
End Sub

Mike

 

[Mike H]

Hi,

If you want to list missing numbers where there are 2 or more consecutive
missing then try this modified version.

Sub findmissingnumbersinlist()
mc = 1 'for col A
bc = 2 'column B
y = 1
For i = 2 To Cells(Rows.Count, mc).End(xlUp).Row - 1
If Cells(i + 1, mc) <> Cells(i, mc) + 1 Then
For x = Cells(i, mc) + 1 To Cells(i + 1, mc) - 1
Cells(bc, 2).Value = Cells(i, mc) + y
bc = bc + 1
y = y + 1
Next
y = 1
End If
Next i
End Sub

Mike

 

[Max]

Many thanks, Mike, on both counts, the way to write to B2 down and the
intrinsic refinements. Runs fabulous.

Max

 

[Don Guillett]

Since I authored the sub ( Thanks for the MISSING acknowledgment and
thanks ) I have sent you a file with one macro showing how to indicate the
missing and another macro inserting the missing.
Sub findmissingnumbersinlist()
mc = 1 'for col A
Columns(mc + 1).ClearContents
For i = 2 To Cells(Rows.Count, mc).End(xlUp).Row - 1
If Cells(i + 1, mc) <> Cells(i, mc) + 1 Then
'MsgBox "Missing " & Cells(i, mc) + 1 & " at row " & i + 1
Cells(i + 1, mc + 1) = Cells(i, mc) + 1
End If
Next i
End Sub
Sub insertmissing()
mc = 1 'for col A
For i = 2 To Cells(Rows.Count, mc).End(xlUp).Row - 1
If Cells(i + 1, mc) <> Cells(i, mc) + 1 Then
Rows(i + 1).Insert
Cells(i + 1, mc) = Cells(i, mc) + 1
End If
Next i
End Sub

 

[Max]

Don,

My apologies for missing out the sub's authorship acknowledgement earlier. I
haven't received anything in my yahoo email, if you have sent me something.

Since we are on the line here,
I have one request for your GetWorkbook sub,
re this thread: http://tinyurl.com/9beyhs
Appreciate your response

Max

 

[Don Guillett]

I apologize. I thought you were the same poster that I originally sent it
to. If you want the workbook ,ask me off line. Here is the getworkbook macro
that works from the other based on the name of the workbook typed into the
cell.
Sub GetWorkbook()
If ActiveCell.Value = "" Then Exit Sub
workbookname = ActiveCell.Value
On Error GoTo OpenWorkbook
Windows(workbookname & ".xls").Activate
Exit Sub
OpenWorkbook:
Workbooks.Open(workbookname & ".xls").RunAutoMacros xlAutoOpen
End Sub

 

[Max]

Don,

No prob. Many thanks for your Sub GetWorkbook().

Max