VBA to Paste from Clipboard to Empty Cells

[jmchowbizarre]

I am in need of help for VBA code to paste a formula that I will have copied into the clipboard into empty cells only of a range of cells.

Example, my formula is:
=IF(ISNA(VLOOKUP(F100,gpcucndata,3,FALSE)),"",IF(VLOOKUP(F100,gpcucndata,3,FALSE)=0,"",VLOOKUP(F100,gpcucndata,3,FALSE)))

I need to paste that into the empty cells only in selection C6:C18744 (some cells have data I do not want to overwrite)

The range can vary and I'd gladly manually select the range to paste to if necessary.

 

[ParamRay]

Try something like this:

Sub CopyFormula()

  'Declare variables:
  Dim rngCopyFrm As Range
  Dim rngPasteTo As Range
  
  'Set the range objects:
  Set rngCopyFrm = Range("A1")  'cell containing formula to copy
  Set rngPasteTo = Range("C6:C18744") 'cells in which to paste formula
  
'  -----------------------------------------
'  Or if you want to use the selected range:
'  Set rngPasteTo = Selection
'  -----------------------------------------
  
  'Re-set paste destination to blank cells only:
  Set rngPasteTo = rngPasteTo.SpecialCells(xlCellTypeBlanks)
  
  'Do the copy and paste special:
  rngCopyFrm.Copy
  rngPasteTo.PasteSpecial Paste:=xlPasteFormulasAndNumberFormats

End Sub

 

[jmchowbizarre]

Thank you for the code. I get a 400 error when I try to run the Macro. I've tried to see what the problem is but cannot. Any direction you can provide would be great. Again, thank you for the code.

 

[jmchowbizarre]

And here's what I put in based off of reading your comments. I wanted to copy C6 and paste it to the selection I made (which I highlighted C7 to C33 as a test)

Sub CopyFormula()

  'Declare variables:
  Dim rngCopyFrm As Range
  Dim rngPasteTo As Range
  
  'Set the range objects:
  Set rngCopyFrm = Range("C6")  'cell containing formula to copy
  Set rngPasteTo = Selection
  
  'Re-set paste destination to blank cells only:
  Set rngPasteTo = rngPasteTo.SpecialCells(xlCellTypeBlanks)
  
  'Do the copy and paste special:
  rngCopyFrm.Copy
  rngPasteTo.PasteSpecial Paste:=xlPasteFormulasAndNumberFormats


End Sub

 

[Rick Rothstein]

I get a 400 error when I try to run the Macro.

And here's what I put in based off of reading your comments. I wanted to copy C6 and paste it to the selection I made (which I highlighted C7 to C33 as a test)

Sub CopyFormula()

  'Declare variables:
  Dim rngCopyFrm As Range
  Dim rngPasteTo As Range
  
  'Set the range objects:
  Set rngCopyFrm = Range("C6")  'cell containing formula to copy
  Set rngPasteTo = Selection
  
  'Re-set paste destination to blank cells only:
  Set rngPasteTo = rngPasteTo.SpecialCells(xlCellTypeBlanks)
  
  'Do the copy and paste special:
  rngCopyFrm.Copy
  rngPasteTo.PasteSpecial Paste:=xlPasteFormulasAndNumberFormats


End Sub

You should always tell us the error message as well as the error number. Was it, perhaps, "No cells were found."? If so, then the problem is that the selection is beyond the range of cells with data.. SpecialCells(xlCellTypeBlanks) only finds blanks within the UsedRange for the sheet it is processing.

 

[jmchowbizarre]

My apologies, the message it gave me was vague, it just said Microsoft Visual Basic 400

Error Message: 2015-02-23_13-35-34.png • Droplr

Selection: 2015-02-23_13-35-55.png • Droplr

VBA Code: 2015-02-23_13-36-21.png • Droplr

 

[Jonmo1]

Why must the formula be pasted from the clipboard?

You can just get the formula directly from the cell that originally contains it (say that was A6 for example)..

Range("C6:C18744").Formla = Range("A6").Formula

 

[jmchowbizarre]

I am definitely for trying new things. How would I modify the VBA code to incorporate the above? I've stared at it for a few minutes and am trying to figure out what it would replace. I appreciate your help.