Hello,
I have a worksheet as follows:
ColA ColB ColC
A value value
value value
value value
L value value
value value
I value value
value value
value value
TOTAL

The number of rows between A and L and between L and I and between I and
TOTAL will vary. (A to L usually 10-15, L-I usually 10-15, I to TOTAL usually
30-150)

I'm trying to run VBA (CommandButton) to count the number of rows between
each identifier-
Dim intRowCountA As Integer
Dim intRowCountL As Integer
Dim intRowCountI As Integer

and use those numbers to insert that many rows in certain ranges on another
worksheet-
rgWTBa
rgWTBl
rgWTBi

I'm pretty good with Access VBA but am new to using it in Excel.
Thank you in advance for any help,
Robin

try something like this. The find method alsop has an after (to find
something after a certain cell) . I'm not sure if A,L,I occurs multiple
number of times and you need to use After.

With Sheets("sheet1")
Found = True
set c1 = .columns("A").find(what:="A", _
lookin:=xlvalues,lookat:=xlwhole)
if c1 is nothing then
found = false
else
intRowCountA = c1.row
end if

set c2 = .columns("A").find(what:="L", _
lookin:=xlvalues,lookat:=xlwhole)
if c2 is nothing then
Found = false
else
intRowCountL = c2.row
end if

set c3 = .columns("A").find(what:="I", _
lookin:=xlvalues,lookat:=xlwhole)
if C3 is nothing then
Found = false
else
intRowCountI = c3.row
end if

if Found = true then

end if

AtoL = intRowCountL - intRowCountA + 1
end with


with sheets("sheet2")

StartRow = 10

.rows(StartRow & ":" & AtoL).insert
end with


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=163841

Microsoft Office Help

Thank you Joel.

At first glance I was a little confused because you weren't actually
"counting." But I now get that you are taking row numbers of the particular
values and subtracting for the previous values' rows. Very clever. This will
work!

Thank you again,
Robin

"joel" wrote:


try something like this. The find method alsop has an after (to find
something after a certain cell) . I'm not sure if A,L,I occurs multiple
number of times and you need to use After.

With Sheets("sheet1")
Found = True
set c1 = .columns("A").find(what:="A", _
lookin:=xlvalues,lookat:=xlwhole)
if c1 is nothing then
found = false
else
intRowCountA = c1.row
end if

set c2 = .columns("A").find(what:="L", _
lookin:=xlvalues,lookat:=xlwhole)
if c2 is nothing then
Found = false
else
intRowCountL = c2.row
end if

set c3 = .columns("A").find(what:="I", _
lookin:=xlvalues,lookat:=xlwhole)
if C3 is nothing then
Found = false
else
intRowCountI = c3.row
end if

if Found = true then

end if

AtoL = intRowCountL - intRowCountA + 1
end with


with sheets("sheet2")

StartRow = 10

.rows(StartRow & ":" & AtoL).insert
end with


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=163841

Microsoft Office Help

.