Filling empty cells with contents from another cell

[paul.sternhagen]

I am attempting to create a macro that will allow me to survey a column
of data and fill any empty cells with the input from the corresponding
row of data from another column. As an example:


Store Name Distributor Name

Jiffy JIF
Skippy SKIP
Kraft
Butterball BUTTER

I would want my macro to insert the text "Kraft" into the third row of
data in the distributor column. I have toyed with the following macro,
to little success:

Dim i As Integer
Sheets("Data Mapping").Select
For i = 1 To 104
If Range("C & i + 7").Text = 0 Then
Range("A & i + 7").Select
Selection.Copy
Range("C & i +&").Select
ActiveSheet.Paste
Next i

(I have 104 rows of data that I want to "fill", starting in row 8).
Any input that you might have would be most appreciated. Thanks

 

[Bernie Deitrick]

Paul,

No need to loop:

Sub Paul()
With Range("C8:C112")
.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=RC[-2]"
.Copy
.PasteSpecial Paste:=xlPasteValues
End With
End Sub

HTH,
Bernie
MS Excel MVP

 

[Earl Kiosterud]

Paul,

Coll = 2 ' column
Roww = 2 ' starting row

Do
If Cells(Roww, Coll) = "" then ' empty?
Cells(Roww, Coll) = Cells(Roww,Coll).Offset(0,-1)
' get data from cell to left
End if
Roww = Roww + 1 ' move down
Loop While Cells(Roww, Coll).Offset(0, -1) <> ""

This is untested, and I'm not sure if it's the logic you want. There are
slicker ways to do it, but this is basic.

 

[paul.sternhagen]

I understand the logic of this code, but when I try to run the macro,
I get an run-time 438 error. Is there something here that I am still
missing??

 

[Paul Smith]

I am attempting to create a macro that will allow me to survey a column

of data and fill any empty cells with the input from the corresponding
row of data from another column. As an example:

Store Name Distributor Name

Jiffy JIF
Skippy SKIP
Kraft
Butterball BUTTER

I would want my macro to insert the text "Kraft" into the third row of
data in the distributor column.

Suppose that your Store Name and Distributor Name columns correspond,
respectively, to columns A and B. Select the cell C8 and run the macro
below. This should solve your problem.

Paul

Sub my_macro()
'
' my_macro Macro
'
' Keyboard Shortcut: Ctrl+e
'
ActiveCell.FormulaR1C1 = "=IF(ISBLANK(RC[-1]),RC[-2],RC[-1])"
Range("C8").Select
Selection.AutoFill Destination:=Range("C8:C104"),
Type:=xlFillDefault
Range("C8:C104").Select
Selection.Copy
Range("B8").Select
Selection.PasteSpecial Paste:=xlValues
Range("C8").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("C8:C104").Select
Selection.ClearContents
Range("B8").Select
End Sub