replace apostrophe with values above

[Koen]

Hi, I'm looking for a solution for the following problem:
I have data in more or less the following form:

A B
1/1/08 Alex
" "
" "
13/1/08 Sarah
" "
" "

The apostrophes meaning that this cell has the same entry as the one above

So what I want to have is my data in the following form:

A B
1/1/08 Alex
1/1/08 Alex
1/1/08 Alex
13/1/08 Sarah
13/1/08 Sarah
13/1/08 Sarah


I used the code like in the following thread:

http://www.microsoft.com/communitie...899d6e6086cd&lang=en&cr=US&sloc=en-us&m=1&p=1

It works fine for all symboles except for the apostrophe, because it has
some sort of function in VBA (don't know, because I cannot program in VBA)

Can anyone help me, or do I have to use a different symbol?

Thanks in advance,

Koen

 

[Dave Miller]

Hi, I'm looking for a solution for the following problem:
I have data in more or less the following form:

     A           B
1/1/08    Alex
     "             "
     "             "
13/1/08 Sarah
     "             "
     "             "

The apostrophes meaning that this cell has the same entry as the one above

So what I want to have is my data in the following form:

     A           B
1/1/08    Alex
1/1/08    Alex
1/1/08    Alex
13/1/08 Sarah
13/1/08 Sarah
13/1/08 Sarah

I used the code like in the following thread:

http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg...

It works fine for all symboles except for the apostrophe, because it has
some sort of function in VBA (don't know, because I cannot program in VBA)

Can anyone help me, or do I have to use a different symbol?

Thanks in advance,

Koen

If cell.Value = chr(34) Then

 

[XP]

Sorry, the second version for double quotes should be (also, Dim lX as Long
is not needed in either version):

Dim rCell As Range
For Each rCell In ActiveSheet.UsedRange.Columns(1).Rows
If rCell.FormulaR1C1 = """" Then MsgBox "got it " & rCell.Address
rCell.Value = rCell.Offset(-1, 0).Value
rCell.Offset(0, 1).Value = rCell.Offset(-1, 1).Value
End If
Next rCell

 

[XP]

No, that last one is wrong too; perhaps one day I'll get it right! See below:

Dim rCell As Range
For Each rCell In ActiveSheet.UsedRange.Columns(1).Rows
If rCell.FormulaR1C1 = """" Then
rCell.Value = rCell.Offset(-1, 0).Value
rCell.Offset(0, 1).Value = rCell.Offset(-1, 1).Value
End If
Next rCell

Sorry for confusion!

 

[JLGWhiz]

It would help if the OP would distinguish between an apostrophe and a
quotation mark. What he referred to as an apotrophe looks suspiciously like
a quotation mark.

 

[Koen]

Sorry,

It's like JLGwhiz says the " symbol is a quotation mark.

None of the answers XP gave me, work.

So still help needed.

Thanks,

Koen

 

[Dave Miller]

Sorry,

It's like JLGwhiz says the " symbol is a quotation mark.

None of the answers XP gave me, work.

So still help needed.

Thanks,

Koen






- Show quoted text -

Like I said in my first post this will work, just select the entire
range you would like to perfor this procedure on then run it:

Sub REPLACE_APOSTROPHES()
Dim c As Range
For Each c In Selection
If c.Value Like Chr(34) Then
c.Value = c.Offset(-1, 0).Value
End If
Next
End Sub