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