Enter NA() into All Blank Cells in Range

[ryguy7272]

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--

 

[Gary''s Student]

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.

 

[ryguy7272]

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 doesn’t work. All I get is a =NA() in cell B3. What did I do
wrong?


Thanks,
Ryan--

 

[Gary''s Student]

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

 

[Peter T]

try this

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

Regards,
Peter T

 

[ryguy7272]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[ryguy7272]

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--

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Gary''s Student]

You have put an apostrophe before the line that calculates n

 

[ryguy7272]

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--

 

[Rick Rothstein \(MVP - VB\)]

See inline...

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

 

[Dave Peterson]

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]

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--

 

[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

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--

 

[ryguy7272]

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--

 

[Dave Peterson]

Glad you got things working.

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--