VBA User Form To Select Column To Copy Cells Into If Destination Is Blank

[252194252194]

I attached the worksheet with a ton of macros that I am trying to cannibalize from and get it to work.

Non-VBA CTRL G is easy, but it still takes a coupe repetitive steps that should be able to be automated.

This code works if I specify the "=RC2" formula at the end. I would like a user form to pop up and list which column to select.

I have a ton of garbage in Module 1 that I have tried to get to work, but I hit a wall.


Sub CopyEmptyCellsOver()
'' without VBA, CTRL+G and click on special.

'' Select blank and press OK.

'' In formula bar, type "=" and CRTL+click thefirst

'' Cell where the data starts and CRTL+enter

Dim rRange AsRange

On Error ResumeNext

Set rRange =Application.InputBox(Prompt:="Select Column", _

Default:=ActiveCell.EntireColumn.Address, Type:=8)

On Error GoTo 0

Selection.SpecialCells(xlCellTypeBlanks).Select

Application.CutCopyMode = False

Selection.FormulaR1C1 = "=RC[2]"





End Sub
Thanks In Advance!

[p45cal]

try:
Sub CopyEmptyCellsOver3()
Dim rRange As Range
Dim z As Range
On Error Resume Next
Set rRange = Application.InputBox(Prompt:="Select Destination Column (top left cell will be used)", Default:=ActiveCell.Address, Type:=8)
On Error GoTo 0
Set rRange = Intersect(ActiveSheet.UsedRange, rRange.Cells(1).EntireColumn)
Set z = Application.InputBox("Select Source column (top left cell will be used)", "Select", , , , , , 8)
rRange.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=RC" & z.Column
rRange.Value = rRange.Value
End Sub

[252194252194]

It works great! Thanks for your help! Greatly appreciated!!