I have a spreadsheet that is being populated by external data. The data is
grouped together by company and between each company there are a couple of
blank lines to allow for the insertion of some code. I am looking for a way
to find the first blank line so I can add the formulas using code and then
once hte formulas are added move onto the next group etc. Can anyone help me
with this

Thanks

I like to pick out a column that always has data in it if that row is used:

Dim NextRow as long
with worksheets("Sheet999")
Nextrow = .cells(.rows.count,"X").end(xlup).row + 1

.cells(nextrow,"A").value = "Used to be empty!!"

end with



Nigel wrote:

I have a spreadsheet that is being populated by external data. The data is
grouped together by company and between each company there are a couple of
blank lines to allow for the insertion of some code. I am looking for a way
to find the first blank line so I can add the formulas using code and then
once hte formulas are added move onto the next group etc. Can anyone help me
with this

Thanks

--

Dave Peterson

If you have gaps in your data, maybe you can start at the top and work down:

Nextrow = .cells(1,"X").end(xldown).row + 1

I don't know what "move on" means.

If you mean find the next row, then just do it again.

Nigel wrote:

Ok that one went to the very end of the data, I need it to find the first
blank line enter the expression and then move on

"Dave Peterson" wrote:

I like to pick out a column that always has data in it if that row is used:

Dim NextRow as long
with worksheets("Sheet999")
Nextrow = .cells(.rows.count,"X").end(xlup).row + 1

.cells(nextrow,"A").value = "Used to be empty!!"

end with



Nigel wrote:

I have a spreadsheet that is being populated by external data. The data is
grouped together by company and between each company there are a couple of
blank lines to allow for the insertion of some code. I am looking for a way
to find the first blank line so I can add the formulas using code and then
once hte formulas are added move onto the next group etc. Can anyone help me
with this

Thanks

--

Dave Peterson


--

Dave Peterson

I'd use the first suggestion (change "X" to "H")

Dim NextRow as long
with worksheets("Sheet999")
Nextrow = .cells(.rows.count,"H").end(xlup).row + 1
.cells(nextrow,"I").value = "Used to be empty!!"
end with

You can use the same technique to find the next empty row--or you could just add
one after you fill that cell:

nextrow = nextrow + 1

Either should work.

Nigel wrote:

This may be a better example

My data starts in Row

THere are 5 rows of data for the customer then 2 blank rows then another
customers data will begin

I am looking for something that will search row H anf find the first blank
line and then enter an expression in cell I in the blank row.

Once that expression is entered it needs to loop and find the next blank
line and so on

thanks
"Dave Peterson" wrote:

If you have gaps in your data, maybe you can start at the top and work down:

Nextrow = .cells(1,"X").end(xldown).row + 1

I don't know what "move on" means.

If you mean find the next row, then just do it again.

Nigel wrote:

Ok that one went to the very end of the data, I need it to find the first
blank line enter the expression and then move on

"Dave Peterson" wrote:

I like to pick out a column that always has data in it if that row is used:

Dim NextRow as long
with worksheets("Sheet999")
Nextrow = .cells(.rows.count,"X").end(xlup).row + 1

.cells(nextrow,"A").value = "Used to be empty!!"

end with



Nigel wrote:

I have a spreadsheet that is being populated by external data. The data is
grouped together by company and between each company there are a couple of
blank lines to allow for the insertion of some code. I am looking for a way
to find the first blank line so I can add the formulas using code and then
once hte formulas are added move onto the next group etc. Can anyone help me
with this

Thanks

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

You can loop through those cells if you want. But are you sure you want to loop
through all 64k (or 1Meg) rows?

Dim iRow as long
with activesheet
for irow = 1 to .cells.specialcells(xlcelltypelastcell).row
if .cells(irow,"H").value = "" then
'put something in that cell
end if
next irow
end with

Nigel wrote:

OK I have narrowed this down

Dim NextRow As Long
Set rngB = ActiveSheet.UsedRange.Columns("H:H")
For R = 1 To rngB.Rows.Count
MsgBox ActiveSheet.Cells(R, 8).Value, vbOKOnly
If ActiveSheet.Cells(R, 8).Value Like "" Then
With Worksheets("Sheet3")
NextRow = .Cells(.Rows.Count, "H").End(xlUp).Row + 1

.Cells(NextRow, "I").Value = "Used to be empty!!"
End With
End If
Next R

the value in next wrong is going to the very last row instead of to the
blank row so if there are 53 rows of data and the first blank row is at row
10 the value in nextrow is 54 instead of 10

not sure how to correct that

"Dave Peterson" wrote:

I'd use the first suggestion (change "X" to "H")

Dim NextRow as long
with worksheets("Sheet999")
Nextrow = .cells(.rows.count,"H").end(xlup).row + 1
.cells(nextrow,"I").value = "Used to be empty!!"
end with

You can use the same technique to find the next empty row--or you could just add
one after you fill that cell:

nextrow = nextrow + 1

Either should work.

Nigel wrote:

This may be a better example

My data starts in Row

THere are 5 rows of data for the customer then 2 blank rows then another
customers data will begin

I am looking for something that will search row H anf find the first blank
line and then enter an expression in cell I in the blank row.

Once that expression is entered it needs to loop and find the next blank
line and so on

thanks
"Dave Peterson" wrote:

If you have gaps in your data, maybe you can start at the top and work down:

Nextrow = .cells(1,"X").end(xldown).row + 1

I don't know what "move on" means.

If you mean find the next row, then just do it again.

Nigel wrote:

Ok that one went to the very end of the data, I need it to find the first
blank line enter the expression and then move on

"Dave Peterson" wrote:

I like to pick out a column that always has data in it if that row is used:

Dim NextRow as long
with worksheets("Sheet999")
Nextrow = .cells(.rows.count,"X").end(xlup).row + 1

.cells(nextrow,"A").value = "Used to be empty!!"

end with



Nigel wrote:

I have a spreadsheet that is being populated by external data. The data is
grouped together by company and between each company there are a couple of
blank lines to allow for the insertion of some code. I am looking for a way
to find the first blank line so I can add the formulas using code and then
once hte formulas are added move onto the next group etc. Can anyone help me
with this

Thanks

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Dave I actually managed to solve the problem,

I used part of your code and some of mine here was my solution


Dim NextRow As Long
lastrow = ActiveSheet.Cells(Rows.Count, "AH").End(xlUp).Row
Set rngB = ActiveSheet.UsedRange.Columns("AH:AH")

For r = 1 To rngB.Rows.Count

If ActiveSheet.Cells(r, 34).Value = "1" Then

Range("J" & r) = "test"
Range("k" & r) = "test"
Range("l" & r) = "test"
Range("m" & r) = "test"
Range("n" & r) = "test"
Range("o" & r) = "test"
Range("p" & r) = "test"
Range("q" & r) = "test"
End If
If ActiveSheet.Cells(r, 34).Value = "2" Then
Range("J" & r) = "test 2"
Range("k" & r) = "test 2"
Range("l" & r) = "test 2"
Range("m" & r) = "test 2"
Range("n" & r) = "test 2"
Range("o" & r) = "test 2"
Range("p" & r) = "test 2"
Range("q" & r) = "test 2"

End If

Next r


if column 34 I have a calculation that looks to see if the row is blank, if
it is it puts a 1 in the cell if not it puts a 0, it also looks to see if
the value in the previous cell is 1 and if so it puts a 2, this allows me to
add to different formulas in the 2 blank rows

it may seem a little be of a long way of going about it but it appears to be
doing what I wanted.

I really appreciate your help as you managed to push me in the right
direction and put more thought into it

Thanks

Nigel
"Dave Peterson" wrote:

You can loop through those cells if you want. But are you sure you want to loop
through all 64k (or 1Meg) rows?

Dim iRow as long
with activesheet
for irow = 1 to .cells.specialcells(xlcelltypelastcell).row
if .cells(irow,"H").value = "" then
'put something in that cell
end if
next irow
end with

Nigel wrote:

OK I have narrowed this down

Dim NextRow As Long
Set rngB = ActiveSheet.UsedRange.Columns("H:H")
For R = 1 To rngB.Rows.Count
MsgBox ActiveSheet.Cells(R, 8).Value, vbOKOnly
If ActiveSheet.Cells(R, 8).Value Like "" Then
With Worksheets("Sheet3")
NextRow = .Cells(.Rows.Count, "H").End(xlUp).Row + 1

.Cells(NextRow, "I").Value = "Used to be empty!!"
End With
End If
Next R

the value in next wrong is going to the very last row instead of to the
blank row so if there are 53 rows of data and the first blank row is at row
10 the value in nextrow is 54 instead of 10

not sure how to correct that

"Dave Peterson" wrote:

I'd use the first suggestion (change "X" to "H")

Dim NextRow as long
with worksheets("Sheet999")
Nextrow = .cells(.rows.count,"H").end(xlup).row + 1
.cells(nextrow,"I").value = "Used to be empty!!"
end with

You can use the same technique to find the next empty row--or you could just add
one after you fill that cell:

nextrow = nextrow + 1

Either should work.

Nigel wrote:

This may be a better example

My data starts in Row

THere are 5 rows of data for the customer then 2 blank rows then another
customers data will begin

I am looking for something that will search row H anf find the first blank
line and then enter an expression in cell I in the blank row.

Once that expression is entered it needs to loop and find the next blank
line and so on

thanks
"Dave Peterson" wrote:

If you have gaps in your data, maybe you can start at the top and work down:

Nextrow = .cells(1,"X").end(xldown).row + 1

I don't know what "move on" means.

If you mean find the next row, then just do it again.

Nigel wrote:

Ok that one went to the very end of the data, I need it to find the first
blank line enter the expression and then move on

"Dave Peterson" wrote:

I like to pick out a column that always has data in it if that row is used:

Dim NextRow as long
with worksheets("Sheet999")
Nextrow = .cells(.rows.count,"X").end(xlup).row + 1

.cells(nextrow,"A").value = "Used to be empty!!"

end with



Nigel wrote:

I have a spreadsheet that is being populated by external data. The data is
grouped together by company and between each company there are a couple of
blank lines to allow for the insertion of some code. I am looking for a way
to find the first blank line so I can add the formulas using code and then
once hte formulas are added move onto the next group etc. Can anyone help me
with this

Thanks

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

I'm glad you found a solution.

Nigel wrote:

Dave I actually managed to solve the problem,

I used part of your code and some of mine here was my solution

Dim NextRow As Long
lastrow = ActiveSheet.Cells(Rows.Count, "AH").End(xlUp).Row
Set rngB = ActiveSheet.UsedRange.Columns("AH:AH")

For r = 1 To rngB.Rows.Count

If ActiveSheet.Cells(r, 34).Value = "1" Then

Range("J" & r) = "test"
Range("k" & r) = "test"
Range("l" & r) = "test"
Range("m" & r) = "test"
Range("n" & r) = "test"
Range("o" & r) = "test"
Range("p" & r) = "test"
Range("q" & r) = "test"
End If
If ActiveSheet.Cells(r, 34).Value = "2" Then
Range("J" & r) = "test 2"
Range("k" & r) = "test 2"
Range("l" & r) = "test 2"
Range("m" & r) = "test 2"
Range("n" & r) = "test 2"
Range("o" & r) = "test 2"
Range("p" & r) = "test 2"
Range("q" & r) = "test 2"

End If

Next r

if column 34 I have a calculation that looks to see if the row is blank, if
it is it puts a 1 in the cell if not it puts a 0, it also looks to see if
the value in the previous cell is 1 and if so it puts a 2, this allows me to
add to different formulas in the 2 blank rows

it may seem a little be of a long way of going about it but it appears to be
doing what I wanted.

I really appreciate your help as you managed to push me in the right
direction and put more thought into it

Thanks

Nigel
"Dave Peterson" wrote:

You can loop through those cells if you want. But are you sure you want to loop
through all 64k (or 1Meg) rows?

Dim iRow as long
with activesheet
for irow = 1 to .cells.specialcells(xlcelltypelastcell).row
if .cells(irow,"H").value = "" then
'put something in that cell
end if
next irow
end with

Nigel wrote:

OK I have narrowed this down

Dim NextRow As Long
Set rngB = ActiveSheet.UsedRange.Columns("H:H")
For R = 1 To rngB.Rows.Count
MsgBox ActiveSheet.Cells(R, 8).Value, vbOKOnly
If ActiveSheet.Cells(R, 8).Value Like "" Then
With Worksheets("Sheet3")
NextRow = .Cells(.Rows.Count, "H").End(xlUp).Row + 1

.Cells(NextRow, "I").Value = "Used to be empty!!"
End With
End If
Next R

the value in next wrong is going to the very last row instead of to the
blank row so if there are 53 rows of data and the first blank row is at row
10 the value in nextrow is 54 instead of 10

not sure how to correct that

"Dave Peterson" wrote:

I'd use the first suggestion (change "X" to "H")

Dim NextRow as long
with worksheets("Sheet999")
Nextrow = .cells(.rows.count,"H").end(xlup).row + 1
.cells(nextrow,"I").value = "Used to be empty!!"
end with

You can use the same technique to find the next empty row--or you could just add
one after you fill that cell:

nextrow = nextrow + 1

Either should work.

Nigel wrote:

This may be a better example

My data starts in Row

THere are 5 rows of data for the customer then 2 blank rows then another
customers data will begin

I am looking for something that will search row H anf find the first blank
line and then enter an expression in cell I in the blank row.

Once that expression is entered it needs to loop and find the next blank
line and so on

thanks
"Dave Peterson" wrote:

If you have gaps in your data, maybe you can start at the top and work down:

Nextrow = .cells(1,"X").end(xldown).row + 1

I don't know what "move on" means.

If you mean find the next row, then just do it again.

Nigel wrote:

Ok that one went to the very end of the data, I need it to find the first
blank line enter the expression and then move on

"Dave Peterson" wrote:

I like to pick out a column that always has data in it if that row is used:

Dim NextRow as long
with worksheets("Sheet999")
Nextrow = .cells(.rows.count,"X").end(xlup).row + 1

.cells(nextrow,"A").value = "Used to be empty!!"

end with



Nigel wrote:

I have a spreadsheet that is being populated by external data. The data is
grouped together by company and between each company there are a couple of
blank lines to allow for the insertion of some code. I am looking for a way
to find the first blank line so I can add the formulas using code and then
once hte formulas are added move onto the next group etc. Can anyone help me
with this

Thanks

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

You may find this more efficient
Sub valuecells()
r = 2 'you didn't say what r was
If Cells(r, 34) = 2 Then ext = 2
Range(Cells(r, "j"), Cells(r, "q")) = "test " & ext
End Sub

--
Don Guillett
SalesAid Software

"Nigel" wrote in message
...
Dave I actually managed to solve the problem,

I used part of your code and some of mine here was my solution


Dim NextRow As Long
lastrow = ActiveSheet.Cells(Rows.Count, "AH").End(xlUp).Row
Set rngB = ActiveSheet.UsedRange.Columns("AH:AH")

For r = 1 To rngB.Rows.Count

If ActiveSheet.Cells(r, 34).Value = "1" Then

Range("J" & r) = "test"
Range("k" & r) = "test"
Range("l" & r) = "test"
Range("m" & r) = "test"
Range("n" & r) = "test"
Range("o" & r) = "test"
Range("p" & r) = "test"
Range("q" & r) = "test"
End If
If ActiveSheet.Cells(r, 34).Value = "2" Then
Range("J" & r) = "test 2"
Range("k" & r) = "test 2"
Range("l" & r) = "test 2"
Range("m" & r) = "test 2"
Range("n" & r) = "test 2"
Range("o" & r) = "test 2"
Range("p" & r) = "test 2"
Range("q" & r) = "test 2"

End If

Next r


if column 34 I have a calculation that looks to see if the row is blank,
if
it is it puts a 1 in the cell if not it puts a 0, it also looks to see if
the value in the previous cell is 1 and if so it puts a 2, this allows me
to
add to different formulas in the 2 blank rows

it may seem a little be of a long way of going about it but it appears to
be
doing what I wanted.

I really appreciate your help as you managed to push me in the right
direction and put more thought into it

Thanks

Nigel
"Dave Peterson" wrote:

You can loop through those cells if you want. But are you sure you want
to loop
through all 64k (or 1Meg) rows?

Dim iRow as long
with activesheet
for irow = 1 to .cells.specialcells(xlcelltypelastcell).row
if .cells(irow,"H").value = "" then
'put something in that cell
end if
next irow
end with

Nigel wrote:

OK I have narrowed this down

Dim NextRow As Long
Set rngB = ActiveSheet.UsedRange.Columns("H:H")
For R = 1 To rngB.Rows.Count
MsgBox ActiveSheet.Cells(R, 8).Value, vbOKOnly
If ActiveSheet.Cells(R, 8).Value Like "" Then
With Worksheets("Sheet3")
NextRow = .Cells(.Rows.Count, "H").End(xlUp).Row + 1

.Cells(NextRow, "I").Value = "Used to be empty!!"
End With
End If
Next R

the value in next wrong is going to the very last row instead of to the
blank row so if there are 53 rows of data and the first blank row is at
row
10 the value in nextrow is 54 instead of 10

not sure how to correct that

"Dave Peterson" wrote:

I'd use the first suggestion (change "X" to "H")

Dim NextRow as long
with worksheets("Sheet999")
Nextrow = .cells(.rows.count,"H").end(xlup).row + 1
.cells(nextrow,"I").value = "Used to be empty!!"
end with

You can use the same technique to find the next empty row--or you
could just add
one after you fill that cell:

nextrow = nextrow + 1

Either should work.

Nigel wrote:

This may be a better example

My data starts in Row

THere are 5 rows of data for the customer then 2 blank rows then
another
customers data will begin

I am looking for something that will search row H anf find the
first blank
line and then enter an expression in cell I in the blank row.

Once that expression is entered it needs to loop and find the next
blank
line and so on

thanks
"Dave Peterson" wrote:

If you have gaps in your data, maybe you can start at the top and
work down:

Nextrow = .cells(1,"X").end(xldown).row + 1

I don't know what "move on" means.

If you mean find the next row, then just do it again.

Nigel wrote:

Ok that one went to the very end of the data, I need it to find
the first
blank line enter the expression and then move on

"Dave Peterson" wrote:

I like to pick out a column that always has data in it if
that row is used:

Dim NextRow as long
with worksheets("Sheet999")
Nextrow = .cells(.rows.count,"X").end(xlup).row + 1

.cells(nextrow,"A").value = "Used to be empty!!"

end with



Nigel wrote:

I have a spreadsheet that is being populated by external
data. The data is
grouped together by company and between each company there
are a couple of
blank lines to allow for the insertion of some code. I am
looking for a way
to find the first blank line so I can add the formulas
using code and then
once hte formulas are added move onto the next group etc.
Can anyone help me
with this

Thanks

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson