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

Query Data within Excel

P: 21
Hello everyone,

I am fairly new to VBA. I have a worksheet in Excel, named "Data Sheet" from which I am trying to query data. This worksheet contains the following data: unit (column A), date (column B), time range (column C), day of the week (column D), event (column E), and occurrences (column F). I want to be able to identify records that meet user set criteria. I then want to add the number in column F to another number in a different worksheet. I believe I can do the second part (contained in another sub), but I am having difficulty figuring out how to write the query portion. Any ideas or suggestions are welcome. Here is what I have so far:

Sub FindData_Click()
'Load data based on query fields
Dim LastRow As Long
LastRow = Sheets("Data Sheet").Cells.SpecialCells(xlCellTypeLastCell).Row

Do Until LastRow
If Sheets("Data Sheet").Range("B3:B") >= Sheets("Query Form").Range("from") And _
Sheets("Data Sheet").Range("B3:B") <= Sheets("Query Form").Range("to") Then

Selection.Row
Dim dataRow As Integer

dataRow = Selection.Row
If Sheets("Query Form").Range("qevent") = "All" Then
If Sheets("Data Sheet").Range("A" & dataRow) = Sheets("Query Form").Range("unit1") Or _
Sheets("Data Sheet").Range("A" & dataRow) = Sheets("Query Form").Range("unit2") Or _
Sheets("Data Sheet").Range("A" & dataRow) = Sheets("Query Form").Range("unit3") Then
Call placeDOW
End If

Else
If Sheets("Data Sheet").Range("A" & dataRow) = Sheets("Query Form").Range("unit1") Or _
Sheets("Data Sheet").Range("A" & dataRow) = Sheets("Query Form").Range("unit2") Or _
Sheets("Data Sheet").Range("A" & dataRow) = Sheets("Query Form").Range("unit3") And _
Sheets("Data Sheet").Range("E" & dataRow) = Sheets("Query Form").Range("qevent") Then
Call placeDOW
End If
End If
End If
Loop

End Sub

Thanks for your help!
Apr 7 '08 #1
Share this Question
Share on Google+
2 Replies


kadghar
Expert 100+
P: 1,295
Hello everyone,

I am fairly new to VBA. I have a worksheet in Excel, named "Data Sheet" from which I am trying to query data. This worksheet contains the following data: unit (column A), date (column B), time range (column C), day of the week (column D), event (column E), and occurrences (column F). I want to be able to identify records that meet user set criteria. I then want to add the number in column F to another number in a different worksheet. I believe I can do the second part (contained in another sub), but I am having difficulty figuring out how to write the query portion. Any ideas or suggestions are welcome. Here is what I have so far:
Thanks for your help!
Well,
For what i can see, you have this 6 column table and 6 query parameters:
unit1, unit2, unit3, qevent, from, to.

First, i didnt understood your loop, but anyway, working inside Excel it's not exactly the most efficient way, i'll suggest you to create a Variant, when you asign an Excel's range to a Variant, it turns into an array (instead of a Range), which is pretty cool, since working with arrays can make our life easier.

then do something like this:

Expand|Select|Wrap|Line Numbers
  1. Dim a 'this is our variant
  2. with worksheet("whatever")
  3.     a = range(.cells(1,1), .cells(1,1).end(-4121).end(-4161)) 
  4. end with
  5. 'here use whatever you want to define the range, note i used -4121 and -4161 which are the numeric values for xldown and xlright, but you can use the Excel's constants (i just dont like them), or the specialcells as you did before.
Now 'a' is our array ^.^

we could create another array for the results, but i'll rather write them in the Excel's sheet (even if its slower and requires more coding, it might be a nice exercise for you)

i wont use the qevent, i'll asume it's set to ALL, and i'll use 5 variables instead of your in-cell parameters.
(U1, U2, U3, VarFROM, VarTO)

Expand|Select|Wrap|Line Numbers
  1. dim i as long
  2. dim k as long
  3. dim j as integer
  4.  
  5. for i = VarFrom to VarTo
  6.     if a(i, 1) = u1 or a(i,1) = u2 or a(i,1) = u3 then
  7.         k=k+1
  8.         with Worksheets("results")
  9.             for j = 1 to 6
  10.                  cells(k,j) = a(i,j)
  11.             next
  12.          end with
  13.      end if
  14. next
you see, it was easier and faster.
HTH
Apr 7 '08 #2

P: 21
Hi kadghar,

Thank you very much for your response. Unfortunately, I am not very familiar with using arrays. Will arrays allow you to search a tables of varying lengths? Data will be added to the table that I want to search. Thus my sad attempt to make a loop that will search for the specified data and count it until it reaches the last row.

So, if I understand your array instructions correctly, the variant represents the range of cells I want to search. You assign VarFROM and VarTO to indicate the date range that I am searching for. I do not understand the section beginning with line 9. Would you please interpret?

Thank you.
Apr 8 '08 #3

Post your reply

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