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

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


<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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
>

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.
--
Earl Kiosterud
www.smokeylake.com

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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
>

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??

> 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