replace blanks with values above

[Susan]

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

 

[carlo]

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

 

[Susan]

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

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

 

[carlo]

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

 

[tkt_tang]

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.

 

[Susan]

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

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

 

[Susan]

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

 

[carlo]

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

 

[Susan]

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

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
Tools>Options>View>Windows 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