Copy cell data

[yoavp13]

Hey,
I'm trying to write a code and I just don't know how to do it.
If someone could translate it to VBA it could be awsome:

-Go over column A1
-String str = ""
--For cell c : all cells in coulmn A1
---if(c.isEmpty)
----c.value = str
---else
----str = cell.value
---end else
---next cell
--end for

I think I wrote it right.
Thank you in advance!
Yoav

 

[Trevor_S]

I think that what you're trying to do is fill blank cells with the contents of the cell above?
If so, is there anything that marks the last row of data? Or do you want it to continue to the end of the spreadsheet?
Also, do the cells contain formulae or values? If its formulae, do you want them copied as they are, or just their results?

 

[JLGWhiz]

Hey,
I'm trying to write a code and I just don't know how to do it.
If someone could translate it to VBA it could be awsome:

-Go over column A1
-String str = ""
--For cell c : all cells in coulmn A1
---if(c.isEmpty)
----c.value = str
---else
----str = cell.value
---end else
---next cell
--end for

I think I wrote it right.
Thank you in advance!
Yoav

Based on the description of the logic, here is how the loop would yield results.

	str = ""	1st iteration
abc	str = "abc"	2nd iteration
	str = ""	3rd iteration
def	str = "def"	4th iteration
	str = ""	5th iteration
ghi	str = "ghi"	6th iteration

<tbody>
</tbody>

After 6 iterations of the loop, the only remaining value of str would be the last one, with all others abandoned.

 

[yoavp13]

I think that what you're trying to do is fill blank cells with the contents of the cell above?
If so, is there anything that marks the last row of data? Or do you want it to continue to the end of the spreadsheet?
Also, do the cells contain formulae or values? If its formulae, do you want them copied as they are, or just their results?

You are right.
The cells contain values only.
I want it from line 1 up to line 3400

 

[yoavp13]

Based on the description of the logic, here is how the loop would yield results.

	str = ""	1st iteration
abc	str = "abc"	2nd iteration
	str = ""	3rd iteration
def	str = "def"	4th iteration
	str = ""	5th iteration
ghi	str = "ghi"	6th iteration

<tbody>
</tbody>

After 6 iterations of the loop, the only remaining value of str would be the last one, with all others abandoned.

Yes.
I want to copy data to the cell from the cell above and not overwrite any exist data.

 

[JLGWhiz]

Try this

Sub copyStuff()
Dim i As Long, lr As Long
With ActiveSheet
    lr = .Cells(Rows.Count, 1).End(xlUp).Row + 1
    For i = lr To 2 Step -1
        If .Cells(i - 1, 1) <> "" Then
            .Cells(i, 1) = .Cells(i - 1, 1).Value
        End If
    Next
End With
End Sub

I am not sure that I understand what you are trying to do, so if this does not do what you want, try to expand on what your data looks like and what you want to do with it. A screen shot would be very helpful. Click on the word 'Attachments' at the end of my signature blurb below for a link to a post that links to different types of tools for attaching images.

 

[Trevor_S]

You are right.
The cells contain values only.
I want it from line 1 up to line 3400

Try this then:

For n = 1 to 3400
  If Range("A" & n).Value = "" And n <> 1 Then Range("A" & n).Value = Range("A" & n - 1).Value
Next

The check on n<>1 is because there is no cell A0, so if A1 is blank, it must stay blank.

 

[yoavp13]

Thank all of you!
I have managed to write the code.

Sub copyCellAbove()
    Dim Rng As Range, Dn As Range, str As String
    Set Rng = Range(Range("B2"), Range("B3350"))
    str = ""
    For Each Dn In Rng
        If Dn.Value = "" Then
            Dn.Value = str
        Else
            str = Dn.Value
        End If
    Next Dn
End Sub

 

[JLGWhiz]

Thank all of you!
I have managed to write the code.

Sub copyCellAbove()
    Dim Rng As Range, Dn As Range, str As String
    Set Rng = Range(Range("B2"), Range("B3350"))
    str = ""
    For Each Dn In Rng
        If Dn.Value = "" Then
            Dn.Value = str
        Else
            str = Dn.Value
        End If
    Next Dn
End Sub

So what you wanted was to copy each value and fill all blank cells between one value until the next value in the range. Glad you worked it out.
Regards, JLG