Stop finding blanks in a range

[jomish]

I have this VBA code to copy and paste from cell K10 to C13-22 and it works okay but when it gets to 22 it comes up with a runtime error 91 - object variable or With Block variable not set. I assume this is because it does not know what to do when it gets to 22. I wondered if you knew a way of displaying a message to say something like ran out of space when it reaches the end instead of the default Excel message asking to debug OR alternatively just stop?

Here is the code I used:


Sub CopyBelow()
'
' macro to copy below
'

'
Range("K10").Select
Selection.Copy
Range("C12:C22").Find("").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

 

[Firefly2012]

It errors when it doesn't find the blank ("") within the specified range. What do you want to happen in that instance?

 

[jomish]

Can the macro either stop or display a msgbox to say something like you have ran out of space?

 

[Rick Rothstein]

Can the macro either stop or display a msgbox to say something like you have ran out of space?

Given what your originally posted code is doing, I assume there are no formulas in the range C12:C22. Given that, this shorter piece of code will do exactly what the code you posted does...

Sub CopyBelow()
  On Error Resume Next
  Range("K10").Copy Range("C12:C22").SpecialCells(xlCellTypeBlanks)(1)
  On Error GoTo 0
End Sub

Now, that fills in only one blank. If your ultimate goal is to fill all the blank cells in C12:C22 with the contents of K10, that can be done all at once just as simply by removing the (1) at the end of my code...

Sub CopyBelow()
  On Error Resume Next
  Range("K10").Copy Range("C12:C22").SpecialCells(xlCellTypeBlanks)
  On Error GoTo 0
End Sub

 

[jomish]

Thanks for that much simpler code

It nearly works for me but it copies the formula from the cell and not the value is needed. Do you know if I need to add xlPasteValues somewhere?

Thanks for the help so far - really useful!

 

[Rick Rothstein]

Thanks for that much simpler code

It nearly works for me but it copies the formula from the cell and not the value is needed. Do you know if I need to add xlPasteValues somewhere?

I made a guess that what was in K10 was not a formula. Since it is, I'll change the code slightly (it is still simple though). You did not say which of my two routines was the one that worked for you, so here are both again, modified to copy only the value... read the comments at the top of each to choose which one you acually need.

'  This changes only the first blank cell in the range
Sub CopyBelow()
  On Error Resume Next
  Range("C12:C22").SpecialCells(xlCellTypeBlanks)(1).Value = Range("K10").Value
  On Error GoTo 0
End Sub
 
'  This changes all the blank cells in the range at the same time.
Sub CopyBelow()
  On Error Resume Next
  Range("C12:C22").SpecialCells(xlCellTypeBlanks).Value = Range("K10").Value
  On Error GoTo 0
End Sub

 

[jomish]

Re: Stop finding blanks in a range [SOLVED]

Thanks very much you have helped a great deal and taught me a fit bits along the way! Works fine and code will come in use many times.