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

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

--
Regards,
Tom Ogilvy



"Reggie" wrote:

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

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


"Reggie" skrev:

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

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:

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


"Reggie" skrev:

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

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.

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy



"Reggie" wrote:

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

Try dropping the .value:

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



Reggie wrote:

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.

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy



"Reggie" wrote:

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


--

Dave Peterson

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

"Dave Peterson" wrote:

Try dropping the .value:

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



Reggie wrote:

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.

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy



"Reggie" wrote:

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


--

Dave Peterson

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.

"Dave Peterson" wrote:

Try dropping the .value:

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



Reggie wrote:

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.

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy



"Reggie" wrote:

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


--

Dave Peterson

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

--
Regards,
Tom Ogilvy


"excelent" wrote:

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:

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


"Reggie" skrev:

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

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?

--
Regards,
Tom Ogilvy


"Reggie" wrote:

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

"Dave Peterson" wrote:

Try dropping the .value:

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



Reggie wrote:

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.

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy



"Reggie" wrote:

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


--

Dave Peterson

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:

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

--
Regards,
Tom Ogilvy


"excelent" wrote:

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:

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


"Reggie" skrev:

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

Thanks everyone for all your Help! It works great

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy


"excelent" wrote:

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:

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


"Reggie" skrev:

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