Count rows b/w values and insert that # of rows elsewhere
 

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

Count rows b/w values and insert that # of rows elsewhere
 

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

Count rows b/w values and insert that # of rows elsewhere
 

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

.