Collecting values for later display?
 

I have two columns of report numbers. Often, they do not match. I have a
formula in a third column that returns "ERROR" whenever it finds a number in
one column that is not found in the other. I then run the following code to
delete the excess numbers from the bottom up.



' Select the column with the ERROR message and go to the bottom

Range("AM1").Select

Selection.End(xlDown).Select



Do While ActiveCell.Value < "Header"

' If there is no ERROR message

If ActiveCell.Value = "" Then

ActiveCell.Offset(rowOffset:=-1, columnOffset:=0).Activate

' If there is an ERROR message

' get the row number

Else: rngR = ActiveCell.Row

' go to the other column in the same row

Range("AB" & rngR).Select

' delete the cell

Selection.Delete Shift:=xlUp

' go back and continue

Range("AM" & rngR).Select

ActiveCell.Offset(rowOffset:=-1, columnOffset:=0).Activate

End If

Loop



I would like to capture the values of the cells I'm deleting and display a
message box telling me what I've deleted. How do I collect and store these
values for later display?



Ed

Collecting values for later display?
 

Range("AM1").Select

Selection.End(xlDown).Select



Do While ActiveCell.Value < "Header"

' If there is no ERROR message

If ActiveCell.Value = "" Then

ActiveCell.Offset(rowOffset:=-1, columnOffset:=0).Activate

' If there is an ERROR message

' get the row number

Else: rngR = ActiveCell.Row

' go to the other column in the same row

Range("AB" & rngR).Select

' delete the cell
sStr = sStr & ActiveCell.Value & vbNewLine
Selection.Delete Shift:=xlUp

' go back and continue

Range("AM" & rngR).Select

ActiveCell.Offset(rowOffset:=-1, columnOffset:=0).Activate

End If

Loop

Msgbox sStr

This is limited to 255 characters. If you have more than that, you would
need to perhaps put the string somewhere else, like in a cell, or use a
userform with a listbox and add them to the list.

Regards,
Tom Ogilvy

"Ed" wrote in message
...
I have two columns of report numbers. Often, they do not match. I have a
formula in a third column that returns "ERROR" whenever it finds a number
in
one column that is not found in the other. I then run the following code
to
delete the excess numbers from the bottom up.



' Select the column with the ERROR message and go to the bottom

Range("AM1").Select

Selection.End(xlDown).Select



Do While ActiveCell.Value < "Header"

' If there is no ERROR message

If ActiveCell.Value = "" Then

ActiveCell.Offset(rowOffset:=-1, columnOffset:=0).Activate

' If there is an ERROR message

' get the row number

Else: rngR = ActiveCell.Row

' go to the other column in the same row

Range("AB" & rngR).Select

' delete the cell

Selection.Delete Shift:=xlUp

' go back and continue

Range("AM" & rngR).Select

ActiveCell.Offset(rowOffset:=-1, columnOffset:=0).Activate

End If

Loop



I would like to capture the values of the cells I'm deleting and display a
message box telling me what I've deleted. How do I collect and store
these
values for later display?



Ed

Collecting values for later display?
 

Thanks, Tom. I appreciate the help.

So I need to add a Dim sStr as String to the beginning, right? And what
does the vbNewLine do?

Ed


"Tom Ogilvy" wrote in message
...

[SNIP]

' delete the cell
sStr = sStr & ActiveCell.Value & vbNewLine
Selection.Delete Shift:=xlUp

' go back and continue

Range("AM" & rngR).Select

ActiveCell.Offset(rowOffset:=-1, columnOffset:=0).Activate

End If

Loop

Msgbox sStr

This is limited to 255 characters. If you have more than that, you would
need to perhaps put the string somewhere else, like in a cell, or use a
userform with a listbox and add them to the list.

Regards,
Tom Ogilvy

Collecting values for later display?
 

Yes, sStr would be dimensioned as String

sStr = sStr & "abc" & "cdf"
would produce abccdf

sStr = sStr & "abc & vbnewline & "cdf"
would produce
abc
cdf

Regards,
Tom Ogilvy


"Ed" wrote in message
...
Thanks, Tom. I appreciate the help.

So I need to add a Dim sStr as String to the beginning, right? And what
does the vbNewLine do?

Ed


"Tom Ogilvy" wrote in message
...

[SNIP]

' delete the cell
sStr = sStr & ActiveCell.Value & vbNewLine
Selection.Delete Shift:=xlUp

' go back and continue

Range("AM" & rngR).Select

ActiveCell.Offset(rowOffset:=-1, columnOffset:=0).Activate

End If

Loop

Msgbox sStr

This is limited to 255 characters. If you have more than that, you
would
need to perhaps put the string somewhere else, like in a cell, or use a
userform with a listbox and add them to the list.

Regards,
Tom Ogilvy

Collecting values for later display?
 

Thanks very much, Tom. This helps a lot.

Ed


"Tom Ogilvy" wrote in message
...
Yes, sStr would be dimensioned as String

sStr = sStr & "abc" & "cdf"
would produce abccdf

sStr = sStr & "abc & vbnewline & "cdf"
would produce
abc
cdf

Regards,
Tom Ogilvy


"Ed" wrote in message
...
Thanks, Tom. I appreciate the help.

So I need to add a Dim sStr as String to the beginning, right? And what
does the vbNewLine do?

Ed


"Tom Ogilvy" wrote in message
...

[SNIP]

' delete the cell
sStr = sStr & ActiveCell.Value & vbNewLine
Selection.Delete Shift:=xlUp

' go back and continue

Range("AM" & rngR).Select

ActiveCell.Offset(rowOffset:=-1, columnOffset:=0).Activate

End If

Loop

Msgbox sStr

This is limited to 255 characters. If you have more than that, you
would
need to perhaps put the string somewhere else, like in a cell, or use
a
userform with a listbox and add them to the list.

Regards,
Tom Ogilvy