Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clear if "#N/A" and Find End of Range, Fill Blanks
Simple problem seems to be turning into a complex problem. I want to clear
all cells that show #N/A. Sub ClearRange() Dim r As Range Set r = Range("A2:A500") For Each rr In r If rr.Value = "#N/A" Then rr.Offset(0, 1).Clear End If Next End Sub As an aside, I was trying to fill all blank cells with values from above cells. It is easy to find the end of the range, but Excel seems to lose the address of the last cell in the used range once I tell it to go to the first cell in the used range (in my case it is A5) Sub FindEnd() Dim Rng As Range Cells(Rows.Count, "A").End(xlUp).Select ActiveCell.Select Rng = ActiveCell Selection.SpecialCells(xlCellTypeBlanks).Select Range("A5:Rng").Select Selection.FormulaR1C1 = "=R[-1]C" End Sub Any help with either macro would be greatly appreciated!! Regards, Ryan--- -- RyGuy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clear if "#N/A" and Find End of Range, Fill Blanks
Try something like the following. I'm unsure why you did
"rr.Offset(1,0).Clear" when you're trying to clear rr if rr.Value = "#N/A". Maybe I'm missing something. Also, I added a line to insert the above cell value into the newly cleared cell. Sub ClearRange() Dim rr, r As Range Set r = Range("A2:A500") For Each rr In r If rr.Value = "#N/A" Then rr.Clear rr.value = rr.offset(-1,0).value End If Next rr End Sub If you're looking to fill more blank cells, something like the following should work. Sub FillBlanks() Dim rr, r As Range Set r = Range("A2:A500") For Each rr In r If rr.Value = "" Then rr.value = rr.offset(-1,0).value End If Next rr End Sub Hope this helps! -- -SA "ryguy7272" wrote: Simple problem seems to be turning into a complex problem. I want to clear all cells that show #N/A. Sub ClearRange() Dim r As Range Set r = Range("A2:A500") For Each rr In r If rr.Value = "#N/A" Then rr.Offset(0, 1).Clear End If Next End Sub As an aside, I was trying to fill all blank cells with values from above cells. It is easy to find the end of the range, but Excel seems to lose the address of the last cell in the used range once I tell it to go to the first cell in the used range (in my case it is A5) Sub FindEnd() Dim Rng As Range Cells(Rows.Count, "A").End(xlUp).Select ActiveCell.Select Rng = ActiveCell Selection.SpecialCells(xlCellTypeBlanks).Select Range("A5:Rng").Select Selection.FormulaR1C1 = "=R[-1]C" End Sub Any help with either macro would be greatly appreciated!! Regards, Ryan--- -- RyGuy |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clear if "#N/A" and Find End of Range, Fill Blanks
For both macros i get:
I get a run time error '13' Type Mismatch Also, I need to find the used cell in Column A, because this list will shrink grow each time data is pulled to create a report. Any other ideas? Thanks, Ryan--- -- RyGuy "StumpedAgain" wrote: Try something like the following. I'm unsure why you did "rr.Offset(1,0).Clear" when you're trying to clear rr if rr.Value = "#N/A". Maybe I'm missing something. Also, I added a line to insert the above cell value into the newly cleared cell. Sub ClearRange() Dim rr, r As Range Set r = Range("A2:A500") For Each rr In r If rr.Value = "#N/A" Then rr.Clear rr.value = rr.offset(-1,0).value End If Next rr End Sub If you're looking to fill more blank cells, something like the following should work. Sub FillBlanks() Dim rr, r As Range Set r = Range("A2:A500") For Each rr In r If rr.Value = "" Then rr.value = rr.offset(-1,0).value End If Next rr End Sub Hope this helps! -- -SA "ryguy7272" wrote: Simple problem seems to be turning into a complex problem. I want to clear all cells that show #N/A. Sub ClearRange() Dim r As Range Set r = Range("A2:A500") For Each rr In r If rr.Value = "#N/A" Then rr.Offset(0, 1).Clear End If Next End Sub As an aside, I was trying to fill all blank cells with values from above cells. It is easy to find the end of the range, but Excel seems to lose the address of the last cell in the used range once I tell it to go to the first cell in the used range (in my case it is A5) Sub FindEnd() Dim Rng As Range Cells(Rows.Count, "A").End(xlUp).Select ActiveCell.Select Rng = ActiveCell Selection.SpecialCells(xlCellTypeBlanks).Select Range("A5:Rng").Select Selection.FormulaR1C1 = "=R[-1]C" End Sub Any help with either macro would be greatly appreciated!! Regards, Ryan--- -- RyGuy |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clear if "#N/A" and Find End of Range, Fill Blanks
You could use:
If rr.Text = "#N/A" Then or to test for any error in that cell: If iserror(rr.Value) Then ryguy7272 wrote: For both macros i get: I get a run time error '13' Type Mismatch Also, I need to find the used cell in Column A, because this list will shrink grow each time data is pulled to create a report. Any other ideas? Thanks, Ryan--- -- RyGuy "StumpedAgain" wrote: Try something like the following. I'm unsure why you did "rr.Offset(1,0).Clear" when you're trying to clear rr if rr.Value = "#N/A". Maybe I'm missing something. Also, I added a line to insert the above cell value into the newly cleared cell. Sub ClearRange() Dim rr, r As Range Set r = Range("A2:A500") For Each rr In r If rr.Value = "#N/A" Then rr.Clear rr.value = rr.offset(-1,0).value End If Next rr End Sub If you're looking to fill more blank cells, something like the following should work. Sub FillBlanks() Dim rr, r As Range Set r = Range("A2:A500") For Each rr In r If rr.Value = "" Then rr.value = rr.offset(-1,0).value End If Next rr End Sub Hope this helps! -- -SA "ryguy7272" wrote: Simple problem seems to be turning into a complex problem. I want to clear all cells that show #N/A. Sub ClearRange() Dim r As Range Set r = Range("A2:A500") For Each rr In r If rr.Value = "#N/A" Then rr.Offset(0, 1).Clear End If Next End Sub As an aside, I was trying to fill all blank cells with values from above cells. It is easy to find the end of the range, but Excel seems to lose the address of the last cell in the used range once I tell it to go to the first cell in the used range (in my case it is A5) Sub FindEnd() Dim Rng As Range Cells(Rows.Count, "A").End(xlUp).Select ActiveCell.Select Rng = ActiveCell Selection.SpecialCells(xlCellTypeBlanks).Select Range("A5:Rng").Select Selection.FormulaR1C1 = "=R[-1]C" End Sub Any help with either macro would be greatly appreciated!! Regards, Ryan--- -- RyGuy -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clear if "#N/A" and Find End of Range, Fill Blanks
Thanks for the code SA, and thanks, DP, for helping me to notice what I
should have noticed before I submitted my second post. Both macros are working great!! Regards, Ryan--- -- RyGuy "Dave Peterson" wrote: You could use: If rr.Text = "#N/A" Then or to test for any error in that cell: If iserror(rr.Value) Then ryguy7272 wrote: For both macros i get: I get a run time error '13' Type Mismatch Also, I need to find the used cell in Column A, because this list will shrink grow each time data is pulled to create a report. Any other ideas? Thanks, Ryan--- -- RyGuy "StumpedAgain" wrote: Try something like the following. I'm unsure why you did "rr.Offset(1,0).Clear" when you're trying to clear rr if rr.Value = "#N/A". Maybe I'm missing something. Also, I added a line to insert the above cell value into the newly cleared cell. Sub ClearRange() Dim rr, r As Range Set r = Range("A2:A500") For Each rr In r If rr.Value = "#N/A" Then rr.Clear rr.value = rr.offset(-1,0).value End If Next rr End Sub If you're looking to fill more blank cells, something like the following should work. Sub FillBlanks() Dim rr, r As Range Set r = Range("A2:A500") For Each rr In r If rr.Value = "" Then rr.value = rr.offset(-1,0).value End If Next rr End Sub Hope this helps! -- -SA "ryguy7272" wrote: Simple problem seems to be turning into a complex problem. I want to clear all cells that show #N/A. Sub ClearRange() Dim r As Range Set r = Range("A2:A500") For Each rr In r If rr.Value = "#N/A" Then rr.Offset(0, 1).Clear End If Next End Sub As an aside, I was trying to fill all blank cells with values from above cells. It is easy to find the end of the range, but Excel seems to lose the address of the last cell in the used range once I tell it to go to the first cell in the used range (in my case it is A5) Sub FindEnd() Dim Rng As Range Cells(Rows.Count, "A").End(xlUp).Select ActiveCell.Select Rng = ActiveCell Selection.SpecialCells(xlCellTypeBlanks).Select Range("A5:Rng").Select Selection.FormulaR1C1 = "=R[-1]C" End Sub Any help with either macro would be greatly appreciated!! Regards, Ryan--- -- RyGuy -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Shortcut to switch from "fill down" to "copy" with mouse drag | Excel Discussion (Misc queries) | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
"Invalid property" after "Clear Form" | Excel Programming | |||
use variable in Workbooks("book1").Worksheets("sheet1").Range("a1" | Excel Programming |