Hi all,

I have a small script that formats and emails a simple spreadsheet.
However, I am having a small problem getting one part of the script to
work correctly.

Column D of the spreadsheet contains various IDs and I want the script
to change those IDs to the corresponding user's initials. However, it
is possible that this column could contain blank cells and, if so, I
want the script to change the text of the cell to "NONE". The code
looks like this so far :-

For Each Cell In Range(Range("D2"), Range("D2").End(xlDown))
If IsEmpty(Cell) = True Then Cell = "None"
If Cell = "idxxxx" Then Cell = "foo"
If Cell = "idxxxx" Then Cell = "foo"
If Cell = "idxxxxx" Then Cell = "foo"
Next

What happens is that that the script runs and appears to work
correctly. However, when I check the spreadsheet I see that all cells
that are BEFORE the first blank cell are changed and any cells
including and following the blank cell are not changed.

I've tried using IsEmpty and IsNull and neither work.

Any ideas?

Perhaps try

If Cell = "" then Cell ="None"

Jesse


"Matt" wrote in message
om...
Hi all,

I have a small script that formats and emails a simple spreadsheet.
However, I am having a small problem getting one part of the script to
work correctly.

Column D of the spreadsheet contains various IDs and I want the script
to change those IDs to the corresponding user's initials. However, it
is possible that this column could contain blank cells and, if so, I
want the script to change the text of the cell to "NONE". The code
looks like this so far :-

For Each Cell In Range(Range("D2"), Range("D2").End(xlDown))
If IsEmpty(Cell) = True Then Cell = "None"
If Cell = "idxxxx" Then Cell = "foo"
If Cell = "idxxxx" Then Cell = "foo"
If Cell = "idxxxxx" Then Cell = "foo"
Next

What happens is that that the script runs and appears to work
correctly. However, when I check the spreadsheet I see that all cells
that are BEFORE the first blank cell are changed and any cells
including and following the blank cell are not changed.

I've tried using IsEmpty and IsNull and neither work.

Any ideas?

The range that is being searched extends down only to the first cell
above the first blank. Try

Range(Range("D65536"), Range("D65536").End(xlUp)

Alan Beban

Matt wrote:
Hi all,

I have a small script that formats and emails a simple spreadsheet.
However, I am having a small problem getting one part of the script to
work correctly.

Column D of the spreadsheet contains various IDs and I want the script
to change those IDs to the corresponding user's initials. However, it
is possible that this column could contain blank cells and, if so, I
want the script to change the text of the cell to "NONE". The code
looks like this so far :-

For Each Cell In Range(Range("D2"), Range("D2").End(xlDown))
If IsEmpty(Cell) = True Then Cell = "None"
If Cell = "idxxxx" Then Cell = "foo"
If Cell = "idxxxx" Then Cell = "foo"
If Cell = "idxxxxx" Then Cell = "foo"
Next

What happens is that that the script runs and appears to work
correctly. However, when I check the spreadsheet I see that all cells
that are BEFORE the first blank cell are changed and any cells
including and following the blank cell are not changed.

I've tried using IsEmpty and IsNull and neither work.

Any ideas?

The range that is being searched extends down only to the first cell
above the first blank. Try

Range(Range("D65536"), Range("D65536").End(xlUp))

Alan Beban

Matt wrote:
Hi all,

I have a small script that formats and emails a simple spreadsheet.
However, I am having a small problem getting one part of the script to
work correctly.

Column D of the spreadsheet contains various IDs and I want the script
to change those IDs to the corresponding user's initials. However, it
is possible that this column could contain blank cells and, if so, I
want the script to change the text of the cell to "NONE". The code
looks like this so far :-

For Each Cell In Range(Range("D2"), Range("D2").End(xlDown))
If IsEmpty(Cell) = True Then Cell = "None"
If Cell = "idxxxx" Then Cell = "foo"
If Cell = "idxxxx" Then Cell = "foo"
If Cell = "idxxxxx" Then Cell = "foo"
Next

What happens is that that the script runs and appears to work
correctly. However, when I check the spreadsheet I see that all cells
that are BEFORE the first blank cell are changed and any cells
including and following the blank cell are not changed.

I've tried using IsEmpty and IsNull and neither work.

Any ideas?

Alan Beban wrote in message ...
The range that is being searched extends down only to the first cell
above the first blank. Try

Range(Range("D65536"), Range("D65536").End(xlUp))

Alan Beban


No. All that will do is change every cell in Column D of the
worksheet to say "None" apart from the ones that have login ids in
them. However, you did point me in the right direction as I didn't
realise the range extended only to the first blank cell. I fixed it
by determining the last row used and explicity stating the range to be
used. I also changed the IF loop to a Select/Case loop. Here's the
new code :-

RowCount = Range("a1").CurrentRegion.Rows.Count
LastRow = "D" & RowCount

'Change idxxxxx values to ticket owner initials.

For Each Cell In Range(Range(LastRow), Range("D2").End(xlUp))
Select Case Cell
Case (IsNull(Cell))
Cell = "None"
Case "idxxx"
Cell = "foo"
Case "idxxx"
Cell = "foo"
...
...
...
End Select
Next

This works perfectly now.

Cheers,

Matt

Of course! It should have been

Range(Range("D2"), Range("D65536").End(xlUp))

The point was to come up from the bottom rather than go down from the
top (which was stopping before the first blank).

Sorry,
Alan Beban

Matt wrote:
Alan Beban wrote in message ...

The range that is being searched extends down only to the first cell
above the first blank. Try

Range(Range("D65536"), Range("D65536").End(xlUp))

Alan Beban



No. All that will do is change every cell in Column D of the
worksheet to say "None" apart from the ones that have login ids in
them. However, you did point me in the right direction as I didn't
realise the range extended only to the first blank cell. I fixed it
by determining the last row used and explicity stating the range to be
used. I also changed the IF loop to a Select/Case loop. Here's the
new code :-

RowCount = Range("a1").CurrentRegion.Rows.Count
LastRow = "D" & RowCount

'Change idxxxxx values to ticket owner initials.

For Each Cell In Range(Range(LastRow), Range("D2").End(xlUp))
Select Case Cell
Case (IsNull(Cell))
Cell = "None"
Case "idxxx"
Cell = "foo"
Case "idxxx"
Cell = "foo"
...
...
...
End Select
Next

This works perfectly now.

Cheers,

Matt

Hi,

Did you try cell.value="None"

It works with your code. But it's better to use Excel's
SpecialCells(xlCellTypeBlanks) future. I use following macro to fill up
blank cells in selection with zeros. May be it helps to you.

Sub FillZero()
Dim cll
Dim slct
On Error GoTo ErrorHandler:
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
If TypeName(Selection) = "Range" Then
Set slct = Selection.SpecialCells(xlCellTypeBlanks)
slct.Value = 0
End If
ErrorHandler:
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub



Haldun



"Matt" , iletide sunu yazdi
om...
Hi all,

I have a small script that formats and emails a simple spreadsheet.
However, I am having a small problem getting one part of the script to
work correctly.

Column D of the spreadsheet contains various IDs and I want the script
to change those IDs to the corresponding user's initials. However, it
is possible that this column could contain blank cells and, if so, I
want the script to change the text of the cell to "NONE". The code
looks like this so far :-

For Each Cell In Range(Range("D2"), Range("D2").End(xlDown))
If IsEmpty(Cell) = True Then Cell = "None"
If Cell = "idxxxx" Then Cell = "foo"
If Cell = "idxxxx" Then Cell = "foo"
If Cell = "idxxxxx" Then Cell = "foo"
Next

What happens is that that the script runs and appears to work
correctly. However, when I check the spreadsheet I see that all cells
that are BEFORE the first blank cell are changed and any cells
including and following the blank cell are not changed.

I've tried using IsEmpty and IsNull and neither work.

Any ideas?