Assign COUNTIF result a variable

[mguy27]

I am working with VBA in ExcelUltimately I am trying to take a huge amount of data (9500-10000 rows) and do a search for text including the words "Violated Door". I then want to take the rows (which contain Date in one cell, time in another, building name, room, etc) which contain that text in the description, copy and paste them into another workbook in Excel. I have created a program to do a simple "find, copy, paste, return, find next, copy, paste, return, etc," but I wanted to loop the program. So, I wanted to do a COUNTIF and find out how many rows contain the words "Violated Door", and then assign that number to a variable. I then want to take that variable and loop it that many times using a Do...Until and have it stop once it has reached that number.

So, I'm looking for help with 2 things:

1) how do I assign that COUNTIF result to a variable,
and 2) What will my Do Until line look like? I was thinking something like:

Dim i As Integer = 0
Do Until i = [variable]
Loop

Any help would be awesome. Thanks!

[kadghar]

Any help would be awesome. Thanks!

No wonder!

I am working with VBA in ExcelUltimately I am trying to take a huge amount of data (9500-10000 rows) and do a search for text including the words "Violated Door". I then want to take the rows (which contain Date in one cell, time in another, building name, room, etc) which contain that text in the description, copy and paste them into another workbook in Excel. I have created a program to do a simple "find, copy, paste, return, find next, copy, paste, return, etc," but I wanted to loop the program. So, I wanted to do a COUNTIF and find out how many rows contain the words "Violated Door", and then assign that number to a variable. I then want to take that variable and loop it that many times using a Do...Until and have it stop once it has reached that number.

So, I'm looking for help with 2 things:

1) how do I assign that COUNTIF result to a variable,
and 2) What will my Do Until line look like? I was thinking something like:

Dim i As Integer = 0
Do Until i = [variable]
Loop

well, first of all dont worry 10,000 rows is not that HUGE, so it'll be a simple task.

Seems like something interesting to do:

Lets say in column K is the description (where we'll search "Violated Door", no case sensitive ^.^ )

I'll also asume your table starts in Column A and in Row 1, and you dont have any blank spaces in Column A or in Row 1

I'll make N = 3. Please note you can also change the initial Row and Column.

[CODE=vb]Sub ViolatedDoor()
dim a
dim b(), c()
dim N as long, StCol as long, StRow as long
dim i as long, j as long, k as long
N = 3: StCol = 1: StRow = 1
a= range(cells(str ow,stcol).end(-4121), cells(strow,stc ol).end(-4161))
redim b(1 to ubound(a,2), 1 to 1): j=1
for i = 1 to ubound(a)
if instr(ucase(a(i ,N)),"VIOLATED DOOR") <> 0 then
for k = 1 to ubound(a,2)
b(k,j) = a(i,k)
next
j=j+1
redim preserve b(1 to ubound(a,2), 1 to j)
end if
next
redim c(1 to ubound(b,2), 1 to ubound(b))
for i = 1 to ubound (b,2)
for j = 1 to ubound(b)
c(i,j ) = b(j,i)
next
next
workbooks.add
workbooks(workb ooks.count).act ivate
range(cells(1,1 ),cells(ubound( c), ubound(c,2))) = c
end sub[/CODE]

Well, that'll give you a good idea. Hope it helps.

[mguy27]

That actually helped quite a bit. Thank you!

A co-worker came in, sat down, took 2 seconds to look at it with me, and suggested a filter macro, which entailed no coding (since I recorded it) and about 15 seconds to do. So, thank you again for your help! I'm sure I will use it again in the near future. =]