Next Empty Cells

[Guest]

I have created a formula that generates about 1000 numbers and i need them to
filled into empty cells on another worksheet. For example:

The number is generated in sheet1 cell A13
I need that number to be placed in sheet 2 in the first empty cell, range
from A1 to H100

 

[Guest]

Dim r2 as Range, r3 as range
set r2 = worksheets("Sheet2").Range("A1:H100").Value

On Error Resume next
set r3 = r2.specialcells(xlBlanks)
On Error goto 0
if not r3 is nothing then
r3(1) = Worksheets("Sheet1").Range("A13").value
end if

 

[Guest]

Sub xCopy()
Sheets("Sheet1").Range("A13").Copy
Sheets("Sheet2").Range("A1:H100").Find(Empty, LookIn:=xlValues) '.Select
End Sub


"Reggie" skrev:

 

[Guest]

hmm only 3 lines in this macro (formating goes crazy here)

Sub xCopy()
Sheets("Sheet1").Range("A13").Copy
Sheets("Sheet2").Range("A1:H100").Find(Empty, LookIn:=xlValues)
End Sub


"excelent" skrev:

 

[Guest]

Thanks for the quick reply Tom. When i ran the code it errored out on this line

Set r2 = Worksheets("Sheet2").Range("A1:H100").Value - the error was
Run-time error '424' Object required.

 

[Dave Peterson]

Try dropping the .value:

set r2 = worksheets("Sheet2").Range("A1:H100")

 

[Guest]

I dropped the .value but nothing is being copied to sheet2

 

[Guest]

Dis regard my last responce when i dropped the .value it only copies the
value from sheet1 once and thats it. It copies the generated number into
Sheet2 cell A1 and if theres nothing there it fills in a value but if a value
is already there nothing happens.

 

[Guest]

for posting you might use

Sub xCopy()
Sheets("Sheet1").Range("A13").Copy _
Sheets("Sheet2").Range("A1:H100") _
.Find(Empty, LookIn:=xlValues)
End Sub

Excellent approach except it misses A1 initially. you might do

Sub xCopy()
Dim r as Range
set r = Sheets("sheet2").Range("A1:H100")
Sheets("Sheet1").Range("A13").Copy _
r.Find(Empty, After:=r(r.count), LookIn:=xlValues)
End Sub

 

[Guest]

Sub ycopy()
Dim r2 As Range, r3 As Range
Set r2 = Worksheets("Sheet2").Range("A1:H100")
On Error Resume Next
Set r3 = r2.SpecialCells(xlBlanks)
On Error GoTo 0
If Not r3 Is Nothing Then
r3(1) = Worksheets("Sheet1").Range("A13").Value
End If

End Sub

worked fine for me. Perhaps your cells are not blank. do they contain
formulas?

is A13 blank?

Are you expecting the screen to flash all over the place?

 

[Guest]

y Tom ur right bout the underscore_ help with format
cus the codeline has to be in 1 line to work right

ur sugestion modeling code sems to work same as my first


"Tom Ogilvy" skrev:

 

[Guest]

Thanks everyone for all your Help! It works great