I have dates in Column A, and data in Column B:E.

I am using this little snippet of code to insert NA into cells to force my
graph to plot correctly:

Sheets("GRAPH CURRENT").Select
MyVar = ""
For Each Item In Range("B3:E3")
If Item.Value = MyVar Then Item.Value = "=NA()"
Next Item

The graph is based on this tip:
http://j-walk.com/ss/excel/usertips/tip024.htm

So, Im plugging NA into cells that would otherwise be zero. I used this
code to fill down with NA in each column:

Range("B3").Select
Selection.AutoFill Destination:=Range _
(Selection, Selection.Offset(0, 1).End(xlDown).Offset(0, -1))

However, there are some cells towards the bottom of the list in B:E, before
the dates in Column A end. So, I get some zeros under the actual data! Im
trying to come up with a way of getting to the end of the used range in
Column A, offset 1 column, and select all cells from this current cell up to
B3, and fill all blanks with "=NA()". For instance, select B600:B3, and
enter =NA() into all blanks, knowing that the B600 changes constantly. Then,
move to Column C, D, and E. Does anyone have a good idea as to how to do
this?

Regards,
Ryan--


--
RyGuy

Consider:

Sub ryan()
n = Cells(Rows.Count, 1).End(xlUp).Row
Set r = Range("B3:B" & n)
For Each rr In r
If IsEmpty(rr.Value) Then
rr.Formula = "=NA()"
End If
Next
End Sub

this sub:

1. finds the end of column A
2. creates a range from B3 thru the row found in step #1
3. fills the empty cells in the range with the formula.
--
Gary''s Student - gsnu200772


"ryguy7272" wrote:

I have dates in Column A, and data in Column B:E.

I am using this little snippet of code to insert NA into cells to force my
graph to plot correctly:

Sheets("GRAPH CURRENT").Select
MyVar = ""
For Each Item In Range("B3:E3")
If Item.Value = MyVar Then Item.Value = "=NA()"
Next Item

The graph is based on this tip:
http://j-walk.com/ss/excel/usertips/tip024.htm

So, Im plugging NA into cells that would otherwise be zero. I used this
code to fill down with NA in each column:

Range("B3").Select
Selection.AutoFill Destination:=Range _
(Selection, Selection.Offset(0, 1).End(xlDown).Offset(0, -1))

However, there are some cells towards the bottom of the list in B:E, before
the dates in Column A end. So, I get some zeros under the actual data! Im
trying to come up with a way of getting to the end of the used range in
Column A, offset 1 column, and select all cells from this current cell up to
B3, and fill all blanks with "=NA()". For instance, select B600:B3, and
enter =NA() into all blanks, knowing that the B600 changes constantly. Then,
move to Column C, D, and E. Does anyone have a good idea as to how to do
this?

Regards,
Ryan--


--
RyGuy

Hummm, seems like it should work, but it is not working. This is what I am
using now:

CountRows = Sheets("INPUTS").Range("AA1")
n = Cells(CountRows, 1).End(xlUp).Row
Set r = Range("B3:B" & n)
For Each rr In r
If IsEmpty(rr.Value) Then
rr.Formula = "=NA()"
End If
Next

The inputs screen has this function in AA1:
=COUNTA('GRAPH CURRENT'!A2:A65536)

In my current example, I counted down 596 rows, and then try to offset (0,
1), but it doesnt work. All I get is a =NA() in cell B3. What did I do
wrong?


Thanks,
Ryan--


--
RyGuy


"Gary''s Student" wrote:

Consider:

Sub ryan()
n = Cells(Rows.Count, 1).End(xlUp).Row
Set r = Range("B3:B" & n)
For Each rr In r
If IsEmpty(rr.Value) Then
rr.Formula = "=NA()"
End If
Next
End Sub

this sub:

1. finds the end of column A
2. creates a range from B3 thru the row found in step #1
3. fills the empty cells in the range with the formula.
--
Gary''s Student - gsnu200772


"ryguy7272" wrote:

I have dates in Column A, and data in Column B:E.

I am using this little snippet of code to insert NA into cells to force my
graph to plot correctly:

Sheets("GRAPH CURRENT").Select
MyVar = ""
For Each Item In Range("B3:E3")
If Item.Value = MyVar Then Item.Value = "=NA()"
Next Item

The graph is based on this tip:
http://j-walk.com/ss/excel/usertips/tip024.htm

So, Im plugging NA into cells that would otherwise be zero. I used this
code to fill down with NA in each column:

Range("B3").Select
Selection.AutoFill Destination:=Range _
(Selection, Selection.Offset(0, 1).End(xlDown).Offset(0, -1))

However, there are some cells towards the bottom of the list in B:E, before
the dates in Column A end. So, I get some zeros under the actual data! Im
trying to come up with a way of getting to the end of the used range in
Column A, offset 1 column, and select all cells from this current cell up to
B3, and fill all blanks with "=NA()". For instance, select B600:B3, and
enter =NA() into all blanks, knowing that the B600 changes constantly. Then,
move to Column C, D, and E. Does anyone have a good idea as to how to do
this?

Regards,
Ryan--


--
RyGuy

Actually, the fact that B3 got filled is good news. It means theat we can
start to de-bug.

First put a:
MsgBox(n)
right after :
n = Cells(CountRows, 1).End(xlUp).Row

just to make sure n is good



Second, check that the cells in column B are really empty
--
Gary''s Student - gsnu200773


"ryguy7272" wrote:

Hummm, seems like it should work, but it is not working. This is what I am
using now:

CountRows = Sheets("INPUTS").Range("AA1")
n = Cells(CountRows, 1).End(xlUp).Row
Set r = Range("B3:B" & n)
For Each rr In r
If IsEmpty(rr.Value) Then
rr.Formula = "=NA()"
End If
Next

The inputs screen has this function in AA1:
=COUNTA('GRAPH CURRENT'!A2:A65536)

In my current example, I counted down 596 rows, and then try to offset (0,
1), but it doesnt work. All I get is a =NA() in cell B3. What did I do
wrong?


Thanks,
Ryan--


--
RyGuy


"Gary''s Student" wrote:

Consider:

Sub ryan()
n = Cells(Rows.Count, 1).End(xlUp).Row
Set r = Range("B3:B" & n)
For Each rr In r
If IsEmpty(rr.Value) Then
rr.Formula = "=NA()"
End If
Next
End Sub

this sub:

1. finds the end of column A
2. creates a range from B3 thru the row found in step #1
3. fills the empty cells in the range with the formula.
--
Gary''s Student - gsnu200772


"ryguy7272" wrote:

I have dates in Column A, and data in Column B:E.

I am using this little snippet of code to insert NA into cells to force my
graph to plot correctly:

Sheets("GRAPH CURRENT").Select
MyVar = ""
For Each Item In Range("B3:E3")
If Item.Value = MyVar Then Item.Value = "=NA()"
Next Item

The graph is based on this tip:
http://j-walk.com/ss/excel/usertips/tip024.htm

So, Im plugging NA into cells that would otherwise be zero. I used this
code to fill down with NA in each column:

Range("B3").Select
Selection.AutoFill Destination:=Range _
(Selection, Selection.Offset(0, 1).End(xlDown).Offset(0, -1))

However, there are some cells towards the bottom of the list in B:E, before
the dates in Column A end. So, I get some zeros under the actual data! Im
trying to come up with a way of getting to the end of the used range in
Column A, offset 1 column, and select all cells from this current cell up to
B3, and fill all blanks with "=NA()". For instance, select B600:B3, and
enter =NA() into all blanks, knowing that the B600 changes constantly. Then,
move to Column C, D, and E. Does anyone have a good idea as to how to do
this?

Regards,
Ryan--


--
RyGuy

try this

If Item.Value = MyVar Then Item.Value = CVErr(xlErrNA)

Regards,
Peter T


"ryguy7272" wrote in message
...
I have dates in Column A, and data in Column B:E.

I am using this little snippet of code to insert NA into cells to force my
graph to plot correctly:

Sheets("GRAPH CURRENT").Select
MyVar = ""
For Each Item In Range("B3:E3")
If Item.Value = MyVar Then Item.Value = "=NA()"
Next Item

The graph is based on this tip:
http://j-walk.com/ss/excel/usertips/tip024.htm

So, I'm plugging NA into cells that would otherwise be zero. I used this
code to fill down with NA in each column:

Range("B3").Select
Selection.AutoFill Destination:=Range _
(Selection, Selection.Offset(0, 1).End(xlDown).Offset(0, -1))

However, there are some cells towards the bottom of the list in B:E,
before
the dates in Column A end. So, I get some zeros under the actual data! I
'm
trying to come up with a way of getting to the end of the used range in
Column A, offset 1 column, and select all cells from this current cell up
to
B3, and fill all blanks with "=NA()". For instance, select B600:B3, and
enter =NA() into all blanks, knowing that the B600 changes constantly.
Then,
move to Column C, D, and E. Does anyone have a good idea as to how to do
this?

Regards,
Ryan--


--
RyGuy

The MsgBox says 2
Also, I think the cells are empty.
=code(B4) gives me: #VALUE

--
RyGuy


"Gary''s Student" wrote:

Actually, the fact that B3 got filled is good news. It means theat we can
start to de-bug.

First put a:
MsgBox(n)
right after :
n = Cells(CountRows, 1).End(xlUp).Row

just to make sure n is good



Second, check that the cells in column B are really empty
--
Gary''s Student - gsnu200773


"ryguy7272" wrote:

Hummm, seems like it should work, but it is not working. This is what I am
using now:

CountRows = Sheets("INPUTS").Range("AA1")
n = Cells(CountRows, 1).End(xlUp).Row
Set r = Range("B3:B" & n)
For Each rr In r
If IsEmpty(rr.Value) Then
rr.Formula = "=NA()"
End If
Next

The inputs screen has this function in AA1:
=COUNTA('GRAPH CURRENT'!A2:A65536)

In my current example, I counted down 596 rows, and then try to offset (0,
1), but it doesnt work. All I get is a =NA() in cell B3. What did I do
wrong?


Thanks,
Ryan--


--
RyGuy


"Gary''s Student" wrote:

Consider:

Sub ryan()
n = Cells(Rows.Count, 1).End(xlUp).Row
Set r = Range("B3:B" & n)
For Each rr In r
If IsEmpty(rr.Value) Then
rr.Formula = "=NA()"
End If
Next
End Sub

this sub:

1. finds the end of column A
2. creates a range from B3 thru the row found in step #1
3. fills the empty cells in the range with the formula.
--
Gary''s Student - gsnu200772


"ryguy7272" wrote:

I have dates in Column A, and data in Column B:E.

I am using this little snippet of code to insert NA into cells to force my
graph to plot correctly:

Sheets("GRAPH CURRENT").Select
MyVar = ""
For Each Item In Range("B3:E3")
If Item.Value = MyVar Then Item.Value = "=NA()"
Next Item

The graph is based on this tip:
http://j-walk.com/ss/excel/usertips/tip024.htm

So, Im plugging NA into cells that would otherwise be zero. I used this
code to fill down with NA in each column:

Range("B3").Select
Selection.AutoFill Destination:=Range _
(Selection, Selection.Offset(0, 1).End(xlDown).Offset(0, -1))

However, there are some cells towards the bottom of the list in B:E, before
the dates in Column A end. So, I get some zeros under the actual data! Im
trying to come up with a way of getting to the end of the used range in
Column A, offset 1 column, and select all cells from this current cell up to
B3, and fill all blanks with "=NA()". For instance, select B600:B3, and
enter =NA() into all blanks, knowing that the B600 changes constantly. Then,
move to Column C, D, and E. Does anyone have a good idea as to how to do
this?

Regards,
Ryan--


--
RyGuy

Is the IsEmpty function really the one you want to be using? The help files
says it returns True only if the **variable** being tested has never been
initialized. What about this code instead?

Sub Ryan()
n = Cells(Rows.Count, 1).End(xlUp).Row
Set R = Range("B3:B" & n).SpecialCells(xlCellTypeBlanks)
For Each rr In R
rr.Formula = "=NA()"
Next
End Sub

Rick


"Gary''s Student" wrote in message
...
Consider:

Sub ryan()
n = Cells(Rows.Count, 1).End(xlUp).Row
Set r = Range("B3:B" & n)
For Each rr In r
If IsEmpty(rr.Value) Then
rr.Formula = "=NA()"
End If
Next
End Sub

this sub:

1. finds the end of column A
2. creates a range from B3 thru the row found in step #1
3. fills the empty cells in the range with the formula.
--
Gary''s Student - gsnu200772


"ryguy7272" wrote:

I have dates in Column A, and data in Column B:E.

I am using this little snippet of code to insert NA into cells to force
my
graph to plot correctly:

Sheets("GRAPH CURRENT").Select
MyVar = ""
For Each Item In Range("B3:E3")
If Item.Value = MyVar Then Item.Value = "=NA()"
Next Item

The graph is based on this tip:
http://j-walk.com/ss/excel/usertips/tip024.htm

So, Im plugging NA into cells that would otherwise be zero. I used this
code to fill down with NA in each column:

Range("B3").Select
Selection.AutoFill Destination:=Range _
(Selection, Selection.Offset(0, 1).End(xlDown).Offset(0, -1))

However, there are some cells towards the bottom of the list in B:E,
before
the dates in Column A end. So, I get some zeros under the actual data!
Im
trying to come up with a way of getting to the end of the used range in
Column A, offset 1 column, and select all cells from this current cell up
to
B3, and fill all blanks with "=NA()". For instance, select B600:B3, and
enter =NA() into all blanks, knowing that the B600 changes constantly.
Then,
move to Column C, D, and E. Does anyone have a good idea as to how to do
this?

Regards,
Ryan--


--
RyGuy

I'm up to this point now:
CountRows = Sheets("INPUTS").Range("AA1")
Range("A65536").Select
Selection.End(xlUp).Select
Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, 4)).Select
Range(Selection, Selection.End(xlUp)).Select
Range(Selection, Selection.End(xlUp)).Select
'n = Cells(CountRows, 1).End(xlUp).Row
Set R = Range("B3:E" & n).SpecialCells(xlCellTypeBlanks)
For Each rr In R
rr.Formula = "=NA()"
Next

It selects all of the cells that I want to evaluate for empty and nonempty.
I get an error on this line:
Set R = Range("B3:E" & n).SpecialCells(xlCellTypeBlanks)

I just need to fill the active range with "=NA()" and I should be done. Can
anyone find the problem now? I don't see it.


Thanks for all the help,
Ryan--

--
RyGuy


"Peter T" wrote:

try this

If Item.Value = MyVar Then Item.Value = CVErr(xlErrNA)

Regards,
Peter T


"ryguy7272" wrote in message
...
I have dates in Column A, and data in Column B:E.

I am using this little snippet of code to insert NA into cells to force my
graph to plot correctly:

Sheets("GRAPH CURRENT").Select
MyVar = ""
For Each Item In Range("B3:E3")
If Item.Value = MyVar Then Item.Value = "=NA()"
Next Item

The graph is based on this tip:
http://j-walk.com/ss/excel/usertips/tip024.htm

So, I'm plugging NA into cells that would otherwise be zero. I used this
code to fill down with NA in each column:

Range("B3").Select
Selection.AutoFill Destination:=Range _
(Selection, Selection.Offset(0, 1).End(xlDown).Offset(0, -1))

However, there are some cells towards the bottom of the list in B:E,
before
the dates in Column A end. So, I get some zeros under the actual data! I
'm
trying to come up with a way of getting to the end of the used range in
Column A, offset 1 column, and select all cells from this current cell up
to
B3, and fill all blanks with "=NA()". For instance, select B600:B3, and
enter =NA() into all blanks, knowing that the B600 changes constantly.
Then,
move to Column C, D, and E. Does anyone have a good idea as to how to do
this?

Regards,
Ryan--


--
RyGuy

Do you really have the line assigning a value to 'n' commented out as your
posted code shows? If so, then 'n' will be 0 when it gets used in the
attempt to set R (a row value of 0 is not allowed).

Rick


"ryguy7272" wrote in message
...
I'm up to this point now:
CountRows = Sheets("INPUTS").Range("AA1")
Range("A65536").Select
Selection.End(xlUp).Select
Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, 4)).Select
Range(Selection, Selection.End(xlUp)).Select
Range(Selection, Selection.End(xlUp)).Select
'n = Cells(CountRows, 1).End(xlUp).Row
Set R = Range("B3:E" & n).SpecialCells(xlCellTypeBlanks)
For Each rr In R
rr.Formula = "=NA()"
Next

It selects all of the cells that I want to evaluate for empty and
nonempty.
I get an error on this line:
Set R = Range("B3:E" & n).SpecialCells(xlCellTypeBlanks)

I just need to fill the active range with "=NA()" and I should be done.
Can
anyone find the problem now? I don't see it.


Thanks for all the help,
Ryan--

--
RyGuy


"Peter T" wrote:

try this

If Item.Value = MyVar Then Item.Value = CVErr(xlErrNA)

Regards,
Peter T


"ryguy7272" wrote in message
...
I have dates in Column A, and data in Column B:E.

I am using this little snippet of code to insert NA into cells to force
my
graph to plot correctly:

Sheets("GRAPH CURRENT").Select
MyVar = ""
For Each Item In Range("B3:E3")
If Item.Value = MyVar Then Item.Value = "=NA()"
Next Item

The graph is based on this tip:
http://j-walk.com/ss/excel/usertips/tip024.htm

So, I'm plugging NA into cells that would otherwise be zero. I used
this
code to fill down with NA in each column:

Range("B3").Select
Selection.AutoFill Destination:=Range _
(Selection, Selection.Offset(0, 1).End(xlDown).Offset(0, -1))

However, there are some cells towards the bottom of the list in B:E,
before
the dates in Column A end. So, I get some zeros under the actual data!
I
'm
trying to come up with a way of getting to the end of the used range in
Column A, offset 1 column, and select all cells from this current cell
up
to
B3, and fill all blanks with "=NA()". For instance, select B600:B3,
and
enter =NA() into all blanks, knowing that the B600 changes constantly.
Then,
move to Column C, D, and E. Does anyone have a good idea as to how to
do
this?

Regards,
Ryan--


--
RyGuy

You have put an apostrophe before the line that calculates n
--
Gary''s Student - gsnu200773


"ryguy7272" wrote:

I'm up to this point now:
CountRows = Sheets("INPUTS").Range("AA1")
Range("A65536").Select
Selection.End(xlUp).Select
Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, 4)).Select
Range(Selection, Selection.End(xlUp)).Select
Range(Selection, Selection.End(xlUp)).Select
'n = Cells(CountRows, 1).End(xlUp).Row
Set R = Range("B3:E" & n).SpecialCells(xlCellTypeBlanks)
For Each rr In R
rr.Formula = "=NA()"
Next

It selects all of the cells that I want to evaluate for empty and nonempty.
I get an error on this line:
Set R = Range("B3:E" & n).SpecialCells(xlCellTypeBlanks)

I just need to fill the active range with "=NA()" and I should be done. Can
anyone find the problem now? I don't see it.


Thanks for all the help,
Ryan--

--
RyGuy


"Peter T" wrote:

try this

If Item.Value = MyVar Then Item.Value = CVErr(xlErrNA)

Regards,
Peter T


"ryguy7272" wrote in message
...
I have dates in Column A, and data in Column B:E.

I am using this little snippet of code to insert NA into cells to force my
graph to plot correctly:

Sheets("GRAPH CURRENT").Select
MyVar = ""
For Each Item In Range("B3:E3")
If Item.Value = MyVar Then Item.Value = "=NA()"
Next Item

The graph is based on this tip:
http://j-walk.com/ss/excel/usertips/tip024.htm

So, I'm plugging NA into cells that would otherwise be zero. I used this
code to fill down with NA in each column:

Range("B3").Select
Selection.AutoFill Destination:=Range _
(Selection, Selection.Offset(0, 1).End(xlDown).Offset(0, -1))

However, there are some cells towards the bottom of the list in B:E,
before
the dates in Column A end. So, I get some zeros under the actual data! I
'm
trying to come up with a way of getting to the end of the used range in
Column A, offset 1 column, and select all cells from this current cell up
to
B3, and fill all blanks with "=NA()". For instance, select B600:B3, and
enter =NA() into all blanks, knowing that the B600 changes constantly.
Then,
move to Column C, D, and E. Does anyone have a good idea as to how to do
this?

Regards,
Ryan--


--
RyGuy

Yeah, ticking out that n was a mistake. This is what I have now:
CountRows = Sheets("INPUTS").Range("AA1")
Range("A65536").Select
Selection.End(xlUp).Select
Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, 4)).Select
Range(Selection, "B3:E3").Select

n = Cells(Rows.Count, 1).End(xlUp).Row
Set r = Range("B3:E" & n)
For Each rr In r
If IsEmpty(rr.Value) Then
rr.Formula = "=NA()"
End If
Next

Almost there, but not quite. B3:E3 fills in fine, and many of the cells
below fill in fine, but it fails when it gets to the cell above the cell in
Column A, where the dates are, if the dates don't go to the top. For
instance, sometimes the dates will begin in A46, I don't have any data for
B46:E46, so these cells are blank, but I do have data for B47:E47 down to
b390:E390. Then D391:E391 are blank but B391:C391 down to B597:C597 has data
-- D597:E597 are blank so I would like to fill these with =NA(). The entire
range is selected, and it seems like the sub should fill in all blanks with
=NA(), but it gets stuck at the top and never makes its way down...

Any more ideas?


Thanks so much,
Ryan--

--
RyGuy


"Rick Rothstein (MVP - VB)" wrote:

Do you really have the line assigning a value to 'n' commented out as your
posted code shows? If so, then 'n' will be 0 when it gets used in the
attempt to set R (a row value of 0 is not allowed).

Rick


"ryguy7272" wrote in message
...
I'm up to this point now:
CountRows = Sheets("INPUTS").Range("AA1")
Range("A65536").Select
Selection.End(xlUp).Select
Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, 4)).Select
Range(Selection, Selection.End(xlUp)).Select
Range(Selection, Selection.End(xlUp)).Select
'n = Cells(CountRows, 1).End(xlUp).Row
Set R = Range("B3:E" & n).SpecialCells(xlCellTypeBlanks)
For Each rr In R
rr.Formula = "=NA()"
Next

It selects all of the cells that I want to evaluate for empty and
nonempty.
I get an error on this line:
Set R = Range("B3:E" & n).SpecialCells(xlCellTypeBlanks)

I just need to fill the active range with "=NA()" and I should be done.
Can
anyone find the problem now? I don't see it.


Thanks for all the help,
Ryan--

--
RyGuy


"Peter T" wrote:

try this

If Item.Value = MyVar Then Item.Value = CVErr(xlErrNA)

Regards,
Peter T


"ryguy7272" wrote in message
...
I have dates in Column A, and data in Column B:E.

I am using this little snippet of code to insert NA into cells to force
my
graph to plot correctly:

Sheets("GRAPH CURRENT").Select
MyVar = ""
For Each Item In Range("B3:E3")
If Item.Value = MyVar Then Item.Value = "=NA()"
Next Item

The graph is based on this tip:
http://j-walk.com/ss/excel/usertips/tip024.htm

So, I'm plugging NA into cells that would otherwise be zero. I used
this
code to fill down with NA in each column:

Range("B3").Select
Selection.AutoFill Destination:=Range _
(Selection, Selection.Offset(0, 1).End(xlDown).Offset(0, -1))

However, there are some cells towards the bottom of the list in B:E,
before
the dates in Column A end. So, I get some zeros under the actual data!
I
'm
trying to come up with a way of getting to the end of the used range in
Column A, offset 1 column, and select all cells from this current cell
up
to
B3, and fill all blanks with "=NA()". For instance, select B600:B3,
and
enter =NA() into all blanks, knowing that the B600 changes constantly.
Then,
move to Column C, D, and E. Does anyone have a good idea as to how to
do
this?

Regards,
Ryan--


--
RyGuy

See inline...

"ryguy7272" wrote in message
...
Yeah, ticking out that n was a mistake. This is what I have now:
CountRows = Sheets("INPUTS").Range("AA1")
Range("A65536").Select
Selection.End(xlUp).Select
Range(ActiveCell.Offset(0, 1), ActiveCell.Offset(0, 4)).Select
Range(Selection, "B3:E3").Select

If I am reading your code correctly, when you execute the next lines below,
nothing you did in the code above matters (nor is it necessary for what you
want to ultimately do).

n = Cells(Rows.Count, 1).End(xlUp).Row
Set r = Range("B3:E" & n)
For Each rr In r
If IsEmpty(rr.Value) Then
rr.Formula = "=NA()"
End If
Next

Almost there, but not quite. B3:E3 fills in fine, and many of the cells
below fill in fine, but it fails when it gets to the cell above the cell
in
Column A, where the dates are, if the dates don't go to the top. For
instance, sometimes the dates will begin in A46, I don't have any data for
B46:E46, so these cells are blank, but I do have data for B47:E47 down to
b390:E390. Then D391:E391 are blank but B391:C391 down to B597:C597 has
data
-- D597:E597 are blank so I would like to fill these with =NA(). The
entire
range is selected, and it seems like the sub should fill in all blanks
with
=NA(), but it gets stuck at the top and never makes its way down...

Do me a favor... comment out all the current code (shown above) and replace
it with this code...

n = Cells(Rows.Count, 1).End(xlUp).Row
Set R = Range("B3:B" & n).SpecialCells(xlCellTypeBlanks)
For Each rr In R
rr.Formula = "=NA()"
Next

and tell me if it does what you want.

Rick

I'm confused over the range you want changed to n/a's, but if you want the empty
cells in B3:E(lastrowincolumnA), then this may work:

Option Explicit
Sub testme03()
Dim LastRow As Long
Dim myRng As Range

With Worksheets("INPUTS")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set myRng = Nothing
On Error Resume Next
Set myRng = .Range("B3:E" & LastRow) _
.Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "No empty cells!"
Else
myRng.Formula = "=na()"
End If
End With
End Sub

ryguy7272 wrote:

I have dates in Column A, and data in Column B:E.

I am using this little snippet of code to insert NA into cells to force my
graph to plot correctly:

Sheets("GRAPH CURRENT").Select
MyVar = ""
For Each Item In Range("B3:E3")
If Item.Value = MyVar Then Item.Value = "=NA()"
Next Item

The graph is based on this tip:
http://j-walk.com/ss/excel/usertips/tip024.htm

So, Im plugging NA into cells that would otherwise be zero. I used this
code to fill down with NA in each column:

Range("B3").Select
Selection.AutoFill Destination:=Range _
(Selection, Selection.Offset(0, 1).End(xlDown).Offset(0, -1))

However, there are some cells towards the bottom of the list in B:E, before
the dates in Column A end. So, I get some zeros under the actual data! Im
trying to come up with a way of getting to the end of the used range in
Column A, offset 1 column, and select all cells from this current cell up to
B3, and fill all blanks with "=NA()". For instance, select B600:B3, and
enter =NA() into all blanks, knowing that the B600 changes constantly. Then,
move to Column C, D, and E. Does anyone have a good idea as to how to do
this?

Regards,
Ryan--

--
RyGuy

--

Dave Peterson

OK, almost there. When I looked closely at the source data for this, it
occurred to me that the guy was using an IF, with a complex vlookup, and if
the logic evaluated to true, he entered a space as " ". Arghhhh! I thought
I could simply change it to "" (with no space), but something still gets
carried over to the 'Current Graph' sheet when the copy/paste macro runs. I
even tried a "-" instead of the "", and it didn't work. If I manually select
all the blanks, and then hit delete, and run Dave's macro, everything works
fine. If I just run the macro, I can't get the =NA() in those cells because
those cells are not blank. I can't tell what the heck is in those cells now.
If I evaluate the cells as =ISTEXT() I get TRUE and if I evaluate those
cells as ISBLANK() I get FALSE. If I try =CODE() I get 32 and if I try
=CODE() for the "-" I get 45. How can I clear out those 45s and 32s?

I tried a snippet of code like this:
Cells.Select
With Selection
.Replace What:=Chr(45), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
End With

However, it does not permit me to get blanks in those cells. I must be
missing something really silly here.....


Please help!!
Ryan--

--
RyGuy


"Dave Peterson" wrote:

I'm confused over the range you want changed to n/a's, but if you want the empty
cells in B3:E(lastrowincolumnA), then this may work:

Option Explicit
Sub testme03()
Dim LastRow As Long
Dim myRng As Range

With Worksheets("INPUTS")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set myRng = Nothing
On Error Resume Next
Set myRng = .Range("B3:E" & LastRow) _
.Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "No empty cells!"
Else
myRng.Formula = "=na()"
End If
End With
End Sub

ryguy7272 wrote:

I have dates in Column A, and data in Column B:E.

I am using this little snippet of code to insert NA into cells to force my
graph to plot correctly:

Sheets("GRAPH CURRENT").Select
MyVar = ""
For Each Item In Range("B3:E3")
If Item.Value = MyVar Then Item.Value = "=NA()"
Next Item

The graph is based on this tip:
http://j-walk.com/ss/excel/usertips/tip024.htm

So, I€„¢m plugging NA into cells that would otherwise be zero. I used this
code to fill down with NA in each column:

Range("B3").Select
Selection.AutoFill Destination:=Range _
(Selection, Selection.Offset(0, 1).End(xlDown).Offset(0, -1))

However, there are some cells towards the bottom of the list in B:E, before
the dates in Column A end. So, I get some zeros under the actual data! I€„¢m
trying to come up with a way of getting to the end of the used range in
Column A, offset 1 column, and select all cells from this current cell up to
B3, and fill all blanks with "=NA()". For instance, select B600:B3, and
enter =NA() into all blanks, knowing that the B600 changes constantly. Then,
move to Column C, D, and E. Does anyone have a good idea as to how to do
this?

Regards,
Ryan--

--
RyGuy

--

Dave Peterson

Chip Pearson has a very nice addin that will help determine what that
character(s) is:
http://www.cpearson.com/excel/CellView.htm

After you determine what those values are, maybe just use edit|Replace to put in
the =na()'s.

Option Explicit
Sub testme03()
Dim LastRow As Long
Dim myRng As Range
Dim iCtr As Long

With Worksheets("INPUTS")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set myRng = .Range("B3:E" & LastRow)

'clean up to 10 spaces in the cell
'change it if you think there could be more space characters
For iCtr = 1 To 10
myRng.Replace what:=Space(iCtr), replacement:="", _
lookat:=xlWhole, searchorder:=xlByRows, MatchCase:=False
Next iCtr

'clean up the -
myRng.Replace what:="-", replacement:="", _
lookat:=xlWhole, searchorder:=xlByRows, MatchCase:=False

'one edit|replace to replace all the empty cells with =na()'s.
myRng.Replace what:="", replacement:="=na()", _
lookat:=xlWhole, searchorder:=xlByRows, MatchCase:=False

End With
End Sub



ryguy7272 wrote:

OK, almost there. When I looked closely at the source data for this, it
occurred to me that the guy was using an IF, with a complex vlookup, and if
the logic evaluated to true, he entered a space as " ". Arghhhh! I thought
I could simply change it to "" (with no space), but something still gets
carried over to the 'Current Graph' sheet when the copy/paste macro runs. I
even tried a "-" instead of the "", and it didn't work. If I manually select
all the blanks, and then hit delete, and run Dave's macro, everything works
fine. If I just run the macro, I can't get the =NA() in those cells because
those cells are not blank. I can't tell what the heck is in those cells now.
If I evaluate the cells as =ISTEXT() I get TRUE and if I evaluate those
cells as ISBLANK() I get FALSE. If I try =CODE() I get 32 and if I try
=CODE() for the "-" I get 45. How can I clear out those 45s and 32s?

I tried a snippet of code like this:
Cells.Select
With Selection
.Replace What:=Chr(45), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
End With

However, it does not permit me to get blanks in those cells. I must be
missing something really silly here.....

Please help!!
Ryan--

--
RyGuy

"Dave Peterson" wrote:

I'm confused over the range you want changed to n/a's, but if you want the empty
cells in B3:E(lastrowincolumnA), then this may work:

Option Explicit
Sub testme03()
Dim LastRow As Long
Dim myRng As Range

With Worksheets("INPUTS")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set myRng = Nothing
On Error Resume Next
Set myRng = .Range("B3:E" & LastRow) _
.Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "No empty cells!"
Else
myRng.Formula = "=na()"
End If
End With
End Sub

ryguy7272 wrote:

I have dates in Column A, and data in Column B:E.

I am using this little snippet of code to insert NA into cells to force my
graph to plot correctly:

Sheets("GRAPH CURRENT").Select
MyVar = ""
For Each Item In Range("B3:E3")
If Item.Value = MyVar Then Item.Value = "=NA()"
Next Item

The graph is based on this tip:
http://j-walk.com/ss/excel/usertips/tip024.htm

So, I€„¢m plugging NA into cells that would otherwise be zero. I used this
code to fill down with NA in each column:

Range("B3").Select
Selection.AutoFill Destination:=Range _
(Selection, Selection.Offset(0, 1).End(xlDown).Offset(0, -1))

However, there are some cells towards the bottom of the list in B:E, before
the dates in Column A end. So, I get some zeros under the actual data! I€„¢m
trying to come up with a way of getting to the end of the used range in
Column A, offset 1 column, and select all cells from this current cell up to
B3, and fill all blanks with "=NA()". For instance, select B600:B3, and
enter =NA() into all blanks, knowing that the B600 changes constantly. Then,
move to Column C, D, and E. Does anyone have a good idea as to how to do
this?

Regards,
Ryan--

--
RyGuy

--

Dave Peterson


--

Dave Peterson

Whoooooooaa!!! That did it Dave. I just ran your code, as is, and
everything is lovely now. I have a roommate named Dave; he is a good guy
and I think you are too.

Thanks to everyone for the help!!


Regards,
Ryan--


--
RyGuy


"Dave Peterson" wrote:

Chip Pearson has a very nice addin that will help determine what that
character(s) is:
http://www.cpearson.com/excel/CellView.htm

After you determine what those values are, maybe just use edit|Replace to put in
the =na()'s.

Option Explicit
Sub testme03()
Dim LastRow As Long
Dim myRng As Range
Dim iCtr As Long

With Worksheets("INPUTS")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set myRng = .Range("B3:E" & LastRow)

'clean up to 10 spaces in the cell
'change it if you think there could be more space characters
For iCtr = 1 To 10
myRng.Replace what:=Space(iCtr), replacement:="", _
lookat:=xlWhole, searchorder:=xlByRows, MatchCase:=False
Next iCtr

'clean up the -
myRng.Replace what:="-", replacement:="", _
lookat:=xlWhole, searchorder:=xlByRows, MatchCase:=False

'one edit|replace to replace all the empty cells with =na()'s.
myRng.Replace what:="", replacement:="=na()", _
lookat:=xlWhole, searchorder:=xlByRows, MatchCase:=False

End With
End Sub



ryguy7272 wrote:

OK, almost there. When I looked closely at the source data for this, it
occurred to me that the guy was using an IF, with a complex vlookup, and if
the logic evaluated to true, he entered a space as " ". Arghhhh! I thought
I could simply change it to "" (with no space), but something still gets
carried over to the 'Current Graph' sheet when the copy/paste macro runs. I
even tried a "-" instead of the "", and it didn't work. If I manually select
all the blanks, and then hit delete, and run Dave's macro, everything works
fine. If I just run the macro, I can't get the =NA() in those cells because
those cells are not blank. I can't tell what the heck is in those cells now.
If I evaluate the cells as =ISTEXT() I get TRUE and if I evaluate those
cells as ISBLANK() I get FALSE. If I try =CODE() I get 32 and if I try
=CODE() for the "-" I get 45. How can I clear out those 45s and 32s?

I tried a snippet of code like this:
Cells.Select
With Selection
.Replace What:=Chr(45), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
End With

However, it does not permit me to get blanks in those cells. I must be
missing something really silly here.....

Please help!!
Ryan--

--
RyGuy

"Dave Peterson" wrote:

I'm confused over the range you want changed to n/a's, but if you want the empty
cells in B3:E(lastrowincolumnA), then this may work:

Option Explicit
Sub testme03()
Dim LastRow As Long
Dim myRng As Range

With Worksheets("INPUTS")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set myRng = Nothing
On Error Resume Next
Set myRng = .Range("B3:E" & LastRow) _
.Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "No empty cells!"
Else
myRng.Formula = "=na()"
End If
End With
End Sub

ryguy7272 wrote:

I have dates in Column A, and data in Column B:E.

I am using this little snippet of code to insert NA into cells to force my
graph to plot correctly:

Sheets("GRAPH CURRENT").Select
MyVar = ""
For Each Item In Range("B3:E3")
If Item.Value = MyVar Then Item.Value = "=NA()"
Next Item

The graph is based on this tip:
http://j-walk.com/ss/excel/usertips/tip024.htm

So, I€„¢m plugging NA into cells that would otherwise be zero. I used this
code to fill down with NA in each column:

Range("B3").Select
Selection.AutoFill Destination:=Range _
(Selection, Selection.Offset(0, 1).End(xlDown).Offset(0, -1))

However, there are some cells towards the bottom of the list in B:E, before
the dates in Column A end. So, I get some zeros under the actual data! I€„¢m
trying to come up with a way of getting to the end of the used range in
Column A, offset 1 column, and select all cells from this current cell up to
B3, and fill all blanks with "=NA()". For instance, select B600:B3, and
enter =NA() into all blanks, knowing that the B600 changes constantly. Then,
move to Column C, D, and E. Does anyone have a good idea as to how to do
this?

Regards,
Ryan--

--
RyGuy

--

Dave Peterson


--

Dave Peterson

Glad you got things working.

ryguy7272 wrote:

Whoooooooaa!!! That did it Dave. I just ran your code, as is, and
everything is lovely now. I have a roommate named Dave; he is a good guy
and I think you are too.

Thanks to everyone for the help!!

Regards,
Ryan--

--
RyGuy

"Dave Peterson" wrote:

Chip Pearson has a very nice addin that will help determine what that
character(s) is:
http://www.cpearson.com/excel/CellView.htm

After you determine what those values are, maybe just use edit|Replace to put in
the =na()'s.

Option Explicit
Sub testme03()
Dim LastRow As Long
Dim myRng As Range
Dim iCtr As Long

With Worksheets("INPUTS")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set myRng = .Range("B3:E" & LastRow)

'clean up to 10 spaces in the cell
'change it if you think there could be more space characters
For iCtr = 1 To 10
myRng.Replace what:=Space(iCtr), replacement:="", _
lookat:=xlWhole, searchorder:=xlByRows, MatchCase:=False
Next iCtr

'clean up the -
myRng.Replace what:="-", replacement:="", _
lookat:=xlWhole, searchorder:=xlByRows, MatchCase:=False

'one edit|replace to replace all the empty cells with =na()'s.
myRng.Replace what:="", replacement:="=na()", _
lookat:=xlWhole, searchorder:=xlByRows, MatchCase:=False

End With
End Sub



ryguy7272 wrote:

OK, almost there. When I looked closely at the source data for this, it
occurred to me that the guy was using an IF, with a complex vlookup, and if
the logic evaluated to true, he entered a space as " ". Arghhhh! I thought
I could simply change it to "" (with no space), but something still gets
carried over to the 'Current Graph' sheet when the copy/paste macro runs. I
even tried a "-" instead of the "", and it didn't work. If I manually select
all the blanks, and then hit delete, and run Dave's macro, everything works
fine. If I just run the macro, I can't get the =NA() in those cells because
those cells are not blank. I can't tell what the heck is in those cells now.
If I evaluate the cells as =ISTEXT() I get TRUE and if I evaluate those
cells as ISBLANK() I get FALSE. If I try =CODE() I get 32 and if I try
=CODE() for the "-" I get 45. How can I clear out those 45s and 32s?

I tried a snippet of code like this:
Cells.Select
With Selection
.Replace What:=Chr(45), Replacement:=Chr(32), _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
End With

However, it does not permit me to get blanks in those cells. I must be
missing something really silly here.....

Please help!!
Ryan--

--
RyGuy

"Dave Peterson" wrote:

I'm confused over the range you want changed to n/a's, but if you want the empty
cells in B3:E(lastrowincolumnA), then this may work:

Option Explicit
Sub testme03()
Dim LastRow As Long
Dim myRng As Range

With Worksheets("INPUTS")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set myRng = Nothing
On Error Resume Next
Set myRng = .Range("B3:E" & LastRow) _
.Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If myRng Is Nothing Then
MsgBox "No empty cells!"
Else
myRng.Formula = "=na()"
End If
End With
End Sub

ryguy7272 wrote:

I have dates in Column A, and data in Column B:E.

I am using this little snippet of code to insert NA into cells to force my
graph to plot correctly:

Sheets("GRAPH CURRENT").Select
MyVar = ""
For Each Item In Range("B3:E3")
If Item.Value = MyVar Then Item.Value = "=NA()"
Next Item

The graph is based on this tip:
http://j-walk.com/ss/excel/usertips/tip024.htm

So, I€„¢m plugging NA into cells that would otherwise be zero. I used this
code to fill down with NA in each column:

Range("B3").Select
Selection.AutoFill Destination:=Range _
(Selection, Selection.Offset(0, 1).End(xlDown).Offset(0, -1))

However, there are some cells towards the bottom of the list in B:E, before
the dates in Column A end. So, I get some zeros under the actual data! I€„¢m
trying to come up with a way of getting to the end of the used range in
Column A, offset 1 column, and select all cells from this current cell up to
B3, and fill all blanks with "=NA()". For instance, select B600:B3, and
enter =NA() into all blanks, knowing that the B600 changes constantly. Then,
move to Column C, D, and E. Does anyone have a good idea as to how to do
this?

Regards,
Ryan--

--
RyGuy

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson