Consulting

Results 1 to 3 of 3

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

  1. #1

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

    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!
    Attached Files Attached Files

  2. #2
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    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
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    It works great! Thanks for your help! Greatly appreciated!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •