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!
2 2184
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. - 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(strow,stcol).end(-4121), cells(strow,stcol).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(workbooks.count).activate
-
range(cells(1,1),cells(ubound(c), ubound(c,2))) = c
-
end sub
Well, that'll give you a good idea. Hope it helps.
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. =]
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Mike |
last post by:
Is there any way I can use a function to create a variable and assign
a value to it? I have a Perl script that returns some LDAP
information:
sn=Shore
givenname=Mike
logintime=20041008153445Z...
|
by: Rim |
last post by:
Hi,
I have been thinking about how to overload the assign operation '='.
In many cases, I wanted to provide users of my packages a natural
interface to the extended built-in types I created for...
|
by: Eric |
last post by:
How can I dynamically assign an event to an element?
I have tried :
(myelement is a text input)
document.getElementById('myelement').onKeyUp =
"myfnc(param1,param2,param3)";
...
|
by: Matt |
last post by:
If I assign Java variable a to javascript variable x, it is
fine.
<%
int a = 10;
%>
var x = <%= a %>;
alert(x);
But if I do the other way around, then it has 500 error. any ideas??
|
by: Jim via DotNetMonster.com |
last post by:
Hi,
How can I assign the result of a function to a variable. I need to get the
result so that I can query the database again.
What I'm trying is:...
|
by: david |
last post by:
I try to use "for" loop to retrieve and assign values in web form. The code
is in the following. But it can not be compiled.
What I want to do is:
txtQ1.Text =...
|
by: Hbalushi3 |
last post by:
How you going to apply countif in VB Editor in Excel sheet:
Example:
I create Userfrom In Excel sheet. So i want to apply countif in this userfrom with textbox opetion
Could you plase help...
|
by: =?Utf-8?B?R0c=?= |
last post by:
Pseudo code
Count if greater than value in a reference cell
Cell C3 would be:
=COUNTIF(A1:A8,">=$B$1")/8
|
by: jameswilkinsonfjs |
last post by:
Hi All,
Ok I have a table - it lists items with a unique reference
code; lets say there are 4 items :
Item RefCode
1 ABC1
2 ABC2
3 ...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |