| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enter NA() into All Blank Cells in Range
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Enter various number of blank lines in cells | New Users to Excel | |||
| Maximum Number of Blank Cells between Non Blank Cells in a Range | Excel Worksheet Functions | |||
| Determine if range has NO Blank Cells without looping through each cell in range | Excel Programming | |||
| User must enter something in a range of cells | Excel Discussion (Misc queries) | |||
| enter numbers in blank cells | Excel Worksheet Functions | |||
Hybrid Mode
