Copy cells the previous cell in the same column ?

[KarenYT]

Dear All,

I frequently export the spreadsheet from SAP to Excel. If multiple rows continuously carry a SOLDTO and
with different SHIPTO, only the 1st row of the SOLDTO shows the account number in the cell, the other cells for the rest of the rows are blank (SOLDTO column).


Example:
Columns: SOLDTO, SHIPTO
row 1 till row 10 : SOLDTO 122260 (only row 1: 122260; row 2 till 10: blank )
(The following SHIPTO pertained to this SOLDTO row 1 till 4: 222280; row 5 till 6: 222282, row 7 till 8: 222294, and 8 till 10: 222297).

Row 11 is with a different SOLDTO.
etc. Etc.
The spreadsheet consists of thousands of rows with hundreds of different SOLDTO .

The problem is if for the same SOLDTO in multiple rows continuously, then only the1st row carries the SOLDTO the rest will be blank. Is there a way to fill up the related SOLDTO number with a VBA for the whole spreadsheet?
The exported data is by date sequence in the month with more than 10 columns. Reason I need to have the SOLDTO filled, I want to group the data by SOLDTO.

Or there's any other way to do that?
Or will it be easier to do it in Access, I need to import the data in one of my Databases anyway.

I hope I have made the above clear.

Thanks
Karen

[HansV]

Try this macro. Change the constants at the beginning as needed.

Code: Select all

Sub FillMissing()
    Const SOLDTO = "C"
    Const SHIPTO = "E"
    Const FirstRow = 2
    Dim LastRow As Long
    Dim Blanks As Range
    Dim BlankArea As Range
    Application.ScreenUpdating = False
    LastRow = Cells(Rows.Count, SHIPTO).End(xlUp).Row
    Set Blanks = Range(Cells(FirstRow, SOLDTO), Cells(LastRow, SOLDTO)).SpecialCells(xlCellTypeBlanks)
    Blanks.FormulaR1C1 = "=R[-1]C"
    For Each BlankArea In Blanks.Areas
        BlankArea.Value = BlankArea.Value
    Next BlankArea
    Application.ScreenUpdating = True
End Sub

Please test on a copy of your worksheet first.

[Rudi]

In addition to Hans's macro, I do this task quite frequently too, just with a few simple steps:

1. Select the entire SOLDTO column, but clicking on the column header
2. Press F5, click Special, select Blanks
3. With all the blanks selected, type an equals sign (=) and then press the up arrow on the keyboard (to produce a formula like =B1) assuming the SOLDTO is in the B column
4. Press CTRL+ENTER to fill all the blanks with the relative formula
5. Select the column again, press CTRL+C to copy, then paste values (you can also use: CTRL+ALT+V and press V), to convert the formula into a value

That's it.

[KarenYT]

As a result of testing both ways from Hans and Rudi, something interesting I just found out : those data exported from SAP with regards to those Blanks which were not actually Blanks !
After clearing out those Blanks from filtered out manually, it works out fine from Rudi's way.
From Hans, I got the following bug : (I have substituted the C by my column J)
Blanks.FormulaR1C1 = "=R[-1]C"

Although I can work on Rudi's way, I would like to straighten out the macro from Hans. Thanks, Hans.

By the way, I just wonder if there is another way to clear out those blanks in that column besides using Filter those Blanks and clear them manually ?

Thanks All !
Karen

[Rudi]

Hi,

Can you determine what character is in those supposed 'blank' cell?
If you can, you can replace the ^ character in the following code with this identified character and see if that helps.

Code: Select all

Sub FillMissing()
    Const SOLDTO = "J"
    Const SHIPTO = "E"
    Const FirstRow = 2
    Const Symbol = "^" '< Replace ^ with the relevant symbol
    Dim LastRow As Long
    Dim Blanks As Range
    Dim BlankArea As Range
    Application.ScreenUpdating = False
    LastRow = Cells(Rows.Count, SHIPTO).End(xlUp).Row
    Set Blanks = Range(Cells(FirstRow, SOLDTO), Cells(LastRow, SOLDTO))
    Blanks.Replace What:=Symbol, Replacement:=""
    Blanks.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
    For Each BlankArea In Blanks.Areas
        BlankArea.Value = BlankArea.Value
    Next BlankArea
    Application.ScreenUpdating = True
End Sub

[HansV]

Perhaps the offending character is a non-breaking space. You could try using Chr(160) instead of "^".
If not, you could try changing

Code: Select all

    Blanks.Replace What:=Symbol, Replacement:=""

to

Code: Select all

    Blanks.Value = Blanks.Value

[KarenYT]

I tried to have this replaced : Blanks.Value = Blanks.Value
What I got is returned in J2 : =R[-1]C

Reference SOLDTO SHIPTO
0081014153 122513 0000228236
0081011960 =R[-1]C 0000222619
0081011955 0000222619
0081012884 0000122650
0081013297 0000122650


Sub FillMissing()
Const SOLDTO = "J"
Const SHIPTO = "E"
Const FirstRow = 2
Const Symbol = "^" '< Replace ^ with the relevant symbol
Dim LastRow As Long
Dim Blanks As Range
Dim BlankArea As Range
Application.ScreenUpdating = False
LastRow = Cells(Rows.Count, SHIPTO).End(xlUp).Row
Set Blanks = Range(Cells(FirstRow, SOLDTO), Cells(LastRow, SOLDTO))
Blanks.Value = Blanks.Value
Blanks.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
For Each BlankArea In Blanks.Areas
BlankArea.Value = BlankArea.Value
Next BlankArea
Application.ScreenUpdating = True
End Sub

[HansV]

Do you mean that you saw the formula =R[-1]C in the cell itself? That suggests that the cell(s) are formatted as Text. Does it make a difference if you format the SOLDTO column as General?

[KarenYT]

Right, that's Text.
After changing into General, it works !

Thanks for all !

Karen