Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
replace blanks with values above
Hi,
I know this topic has already been posted and since I don't know VB excel programming, I used the code from a previous post to achieve what i want. I need to search for blank values in a range and replace them with the value above. My code: Sub BlankRepeats() For Each cell In Range("A2:A35") If cell.Value = "" Then cell.Value = cell.Offset(-1, 0).Value End If Next cell End Sub It iterates through the entire range and doesnt seem to pick up any values as though all are blanks and hence no changes are made. Meanwhile the column A has text values in many rows. Will appreciate some help. Thanks Susan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
replace blanks with values above
the problem is, if A1 is empty, A2 will be filled by empty and so on!
I'm not sure if "cell" is a reserved vba word, but I would try mycell or cell_ as variable, to avoid problems. Otherwise it should work. Cheers Carlo On Dec 20, 12:55 pm, Susan wrote: Hi, I know this topic has already been posted and since I don't know VB excel programming, I used the code from a previous post to achieve what i want. I need to search for blank values in a range and replace them with the value above. My code: Sub BlankRepeats() For Each cell In Range("A2:A35") If cell.Value = "" Then cell.Value = cell.Offset(-1, 0).Value End If Next cell End Sub It iterates through the entire range and doesnt seem to pick up any values as though all are blanks and hence no changes are made. Meanwhile the column A has text values in many rows. Will appreciate some help. Thanks Susan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
replace blanks with values above
On Dec 20, 6:09 am, carlo wrote:
the problem is, if A1 is empty, A2 will be filled by empty and so on! I'm not sure if "cell" is a reserved vba word, but I would try mycell or cell_ as variable, to avoid problems. Otherwise it should work. Cheers Carlo On Dec 20, 12:55 pm, Susan wrote: Hi, I know this topic has already been posted and since I don't know VB excel programming, I used the code from a previous post to achieve what i want. I need to search for blank values in a range and replace them with the value above. My code: Sub BlankRepeats() For Each cell In Range("A2:A35") If cell.Value = "" Then cell.Value = cell.Offset(-1, 0).Value End If Next cell End Sub It iterates through the entire range and doesnt seem to pick up any values as though all are blanks and hence no changes are made. Meanwhile the column A has text values in many rows. Will appreciate some help. Thanks Susan Hi, Thanks for responding. Do you mean I should put in the statement 'dim cell as variable' or? Anyway I tried that; still doesnt work. I use 'cell' in the for loop of other functions of this same worksheet and it works fine. At the moment A1 is not blank. Could it be sumthing with the type of value in the cell? does it make a difference whether its text or numbers? Thanks Susan |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
replace blanks with values above
Ok, try following:
Sub BlankRepeats() dim ws as worksheet set ws = worksheets("YourWorksheet") For Each cell In ws.Range("A2:A35") If cell.Value = "" Then cell.Value = cell.Offset(-1, 0).Value End If Next cell End Sub As I said, I'm not sure if "cell" is reserved, I just like to avoid stuff like that :) hth Carlo On Dec 20, 1:22 pm, Susan wrote: On Dec 20, 6:09 am, carlo wrote: the problem is, if A1 is empty, A2 will be filled by empty and so on! I'm not sure if "cell" is a reserved vba word, but I would try mycell or cell_ as variable, to avoid problems. Otherwise it should work. Cheers Carlo On Dec 20, 12:55 pm, Susan wrote: Hi, I know this topic has already been posted and since I don't know VB excel programming, I used the code from a previous post to achieve what i want. I need to search for blank values in a range and replace them with the value above. My code: Sub BlankRepeats() For Each cell In Range("A2:A35") If cell.Value = "" Then cell.Value = cell.Offset(-1, 0).Value End If Next cell End Sub It iterates through the entire range and doesnt seem to pick up any values as though all are blanks and hence no changes are made. Meanwhile the column A has text values in many rows. Will appreciate some help. Thanks Susan Hi, Thanks for responding. Do you mean I should put in the statement 'dim cell as variable' or? Anyway I tried that; still doesnt work. I use 'cell' in the for loop of other functions of this same worksheet and it works fine. At the moment A1 is not blank. Could it be sumthing with the type of value in the cell? does it make a difference whether its text or numbers? Thanks Susan- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
replace blanks with values above
Susan,
After testing your code, glad to affirm that it has worked fine as follows :- 1. The code is copied on to the WorkSheet module. 2. The code is copied on to a Standard module. One suggestion : Say, For Each cell In Range("A2:A35") to be, For Each cell In ActiveSheet.Range("A2:A35") Or, For Each cell In Sheet1.Range("A2:A35") . Then, it would enable one to look for results (i.e., Where is the WorkSheet ?) at ease. Regards. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
replace blanks with values above
On Dec 20, 6:41 am, carlo wrote:
Ok, try following: Sub BlankRepeats() dim ws as worksheet set ws = worksheets("YourWorksheet") For Each cell In ws.Range("A2:A35") If cell.Value = "" Then cell.Value = cell.Offset(-1, 0).Value End If Next cell End Sub As I said, I'm not sure if "cell" is reserved, I just like to avoid stuff like that :) hth Carlo On Dec 20, 1:22 pm, Susan wrote: On Dec 20, 6:09 am, carlo wrote: the problem is, if A1 is empty, A2 will be filled by empty and so on! I'm not sure if "cell" is a reserved vba word, but I would try mycell or cell_ as variable, to avoid problems. Otherwise it should work. Cheers Carlo On Dec 20, 12:55 pm, Susan wrote: Hi, I know this topic has already been posted and since I don't know VB excel programming, I used the code from a previous post to achieve what i want. I need to search for blank values in a range and replace them with the value above. My code: Sub BlankRepeats() For Each cell In Range("A2:A35") If cell.Value = "" Then cell.Value = cell.Offset(-1, 0).Value End If Next cell End Sub It iterates through the entire range and doesnt seem to pick up any values as though all are blanks and hence no changes are made. Meanwhile the column A has text values in many rows. Will appreciate some help. Thanks Susan Hi, Thanks for responding. Do you mean I should put in the statement 'dim cell as variable' or? Anyway I tried that; still doesnt work. I use 'cell' in the for loop of other functions of this same worksheet and it works fine. At the moment A1 is not blank. Could it be sumthing with the type of value in the cell? does it make a difference whether its text or numbers? Thanks Susan- Hide quoted text - - Show quoted text - Hi, I hate to say its still not working. Using a debug point I stepped through the code and I can see its iterating correctly, but it never enters the if loop i.e. doesnt pick up the blanks. I use 'cell' because I don't know any other way of iterating and accessing cell values that works. Please suggest any alternatives to using cell. Earlier you suggested "try mycell or cell_ as variable, to avoid problems." Please explain. Thanks for your patience. Susan |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
replace blanks with values above
On Dec 20, 7:03 am, wrote:
Susan, After testing your code, glad to affirm that it has worked fine as follows :- 1. The code is copied on to the WorkSheet module. 2. The code is copied on to a Standard module. One suggestion : Say, For Each cell In Range("A2:A35") to be, For Each cell In ActiveSheet.Range("A2:A35") Or, For Each cell In Sheet1.Range("A2:A35") . Then, it would enable one to look for results (i.e., Where is the WorkSheet ?) at ease. Regards. Hi, Thanks for responding. As mentioned in my reply to Carlo, I can confirm that it is iterating through the correct range on the correct sheet. I'm wondering whether "" in the if condition is the problem? Susan |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
replace blanks with values above
What I meant with: try mycell or cell_ is following:
Sub BlankRepeats() Dim ws As Worksheet Dim MyCell As Range Set ws = Worksheets("sheet1") For Each MyCell In ws.Range("A2:A35") If MyCell.Value = "" Then MyCell.Value = MyCell.Offset(-1, 0).Value End If Next MyCell End Sub that works fine for me hth Carlo On Dec 20, 2:16 pm, Susan wrote: On Dec 20, 7:03 am, wrote: Susan, After testing your code, glad to affirm that it has worked fine as follows :- 1. The code is copied on to the WorkSheet module. 2. The code is copied on to a Standard module. One suggestion : Say, For Each cell In Range("A2:A35") to be, For Each cell In ActiveSheet.Range("A2:A35") Or, For Each cell In Sheet1.Range("A2:A35") . Then, it would enable one to look for results (i.e., Where is the WorkSheet ?) at ease. Regards. Hi, Thanks for responding. As mentioned in my reply to Carlo, I can confirm that it is iterating through the correct range on the correct sheet. I'm wondering whether "" in the if condition is the problem? Susan- Hide quoted text - - Show quoted text - |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
replace blanks with values above
On Dec 20, 7:21 am, carlo wrote:
What I meant with: try mycell or cell_ is following: Sub BlankRepeats() Dim ws As Worksheet Dim MyCell As Range Set ws = Worksheets("sheet1") For Each MyCell In ws.Range("A2:A35") If MyCell.Value = "" Then MyCell.Value = MyCell.Offset(-1, 0).Value End If Next MyCell End Sub that works fine for me hth Carlo On Dec 20, 2:16 pm, Susan wrote: On Dec 20, 7:03 am, wrote: Susan, After testing your code, glad to affirm that it has worked fine as follows :- 1. The code is copied on to the WorkSheet module. 2. The code is copied on to a Standard module. One suggestion : Say, For Each cell In Range("A2:A35") to be, For Each cell In ActiveSheet.Range("A2:A35") Or, For Each cell In Sheet1.Range("A2:A35") . Then, it would enable one to look for results (i.e., Where is the WorkSheet ?) at ease. Regards. Hi, Thanks for responding. As mentioned in my reply to Carlo, I can confirm that it is iterating through the correct range on the correct sheet. I'm wondering whether "" in the if condition is the problem? Susan- Hide quoted text - - Show quoted text - Hi, Thanks its working now. I used MyCell as you suggested and saw it still didnt pick up blanks. Rather it was seeing the blank values as '0'. I had unchecked the 'ZeroValues' option in ToolsOptionsViewWindows options earlier to get rid of the unwanted zeroes, so even though the cells appeared blank their values were zero. Thanks to both of you for your asistance. Susan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Replace values in shaded cells with blanks | Excel Discussion (Misc queries) | |||
Search and replace blanks | Excel Programming | |||
replace blanks using formula | Excel Worksheet Functions | |||
Need to get rid of 0's and replace with Blanks | Excel Worksheet Functions | |||
replace "#DIV/0!" error with blanks | Excel Worksheet Functions |