By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,730 Members | 1,153 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,730 IT Pros & Developers. It's quick & easy.

Assign COUNTIF result a variable

P: 5
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:

Expand|Select|Wrap|Line Numbers
  1. Dim i As Integer = 0
  2. Do Until i = [variable]
  3. Loop
  4.  
Any help would be awesome. Thanks!
Mar 26 '08 #1
Share this Question
Share on Google+
2 Replies


kadghar
Expert 100+
P: 1,295
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:

Expand|Select|Wrap|Line Numbers
  1. Dim i As Integer = 0
  2. Do Until i = [variable]
  3. Loop
  4.  
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.

Expand|Select|Wrap|Line Numbers
  1. Sub ViolatedDoor()
  2. dim a
  3. dim b(), c()
  4. dim N as long, StCol as long, StRow as long
  5. dim i as long, j as long, k as long
  6. N = 3: StCol = 1: StRow = 1
  7. a= range(cells(strow,stcol).end(-4121), cells(strow,stcol).end(-4161))
  8. redim b(1 to ubound(a,2), 1 to 1): j=1
  9. for i = 1 to ubound(a)
  10.     if instr(ucase(a(i,N)),"VIOLATED DOOR") <> 0 then
  11.         for k = 1 to ubound(a,2)
  12.             b(k,j) = a(i,k)
  13.         next
  14.         j=j+1
  15.         redim preserve b(1 to ubound(a,2), 1 to j)
  16.     end if
  17. next
  18. redim c(1 to ubound(b,2), 1 to ubound(b))
  19. for i = 1 to ubound (b,2)
  20.     for j = 1 to ubound(b)
  21.         c(i,j ) = b(j,i)
  22.     next
  23. next
  24. workbooks.add
  25. workbooks(workbooks.count).activate
  26. range(cells(1,1),cells(ubound(c), ubound(c,2))) = c
  27. end sub
Well, that'll give you a good idea. Hope it helps.
Mar 26 '08 #2

P: 5
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. =]
Mar 26 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.