Next Empty Cells
 

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

Next Empty Cells
 

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

Next Empty Cells
 

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

Next Empty Cells
 

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

Next Empty Cells
 

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

Next Empty Cells
 

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

Next Empty Cells
 

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

Next Empty Cells
 

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

Next Empty Cells
 

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

Next Empty Cells
 

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

Next Empty Cells
 

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

Next Empty Cells
 

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