Copy cell data

yoavp13

New Member
Joined
Nov 29, 2016
Messages
7
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
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Trevor_S

Well-known Member
Joined
Oct 17, 2015
Messages
614
Office Version
  1. 2016
Platform
  1. Windows
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?
 
Last edited:

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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.
 
Last edited:

yoavp13

New Member
Joined
Nov 29, 2016
Messages
7
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

New Member
Joined
Nov 29, 2016
Messages
7

ADVERTISEMENT

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

str = ""1st iteration
abcstr = "abc"2nd iteration
str = ""3rd iteration
defstr = "def"4th iteration
str = ""5th iteration
ghistr = "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

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Try this
Code:
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

Well-known Member
Joined
Oct 17, 2015
Messages
614
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Try this then:
Code:
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

New Member
Joined
Nov 29, 2016
Messages
7
Thank all of you!
I have managed to write the code.
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
 
Last edited:

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Thank all of you!
I have managed to write the code.
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,448
Messages
5,642,218
Members
417,262
Latest member
andrewd1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top