VBA Userform: Need help completing coding to run a report please.

[TitusandNero]

Hello (operating on Excel 2007)
Hope someone can help a newbie here. I've set up a userform(named Reports) to run a report from an Excel database(named Data) which will then populate an excel spreadsheet(named By Representative) in the same workbook. On the userform(Reports) I have 3 search parameters in text boxes: Name, date1 and date2. By entering a name and date range and clicking 'run report' I'd like it to open the 'By Representative' spreadsheet and populate it.
The Names on the 'Reports' userform are in a dropdown combobox (named cbRep) and will find its match in column O or 15 in the 'Data' database
The date1 and date2 will find their match in column M or 13 in the 'Data' database. Here's my problem - I'm a newbie and I'm learning as I go. I need your help to complete my coding so that it:
A) Opens the By Representative spreadsheet after the click
B) Matches the value in column O in (Data) with the value in the combobox (cbReP) - can't seem to find the right code
C) Searches between two dates

I'd really appreciate any assistance: Here is my coding so far (which I copied from a youtube video). It is incomplete and obviously not working.

Private Sub CommandButton1_Click()
Dim sdsheet As Worksheet, ersheet As Worksheet
Set sdsheet = ThisWorkbook.sheets ("Data")
set ersheet = ThisWorkbook.Sheets ("By Representative")

If sdsheet.cells(Rows.Count, 1).End(xlUp).row = 1 Then
sdLR = 2
Else
sdLR = sdsheet.Cells(Rows.Count, 1).End(xlUp).Row
End If

y = 2 'starting row

For x = 2 To sdLR
If sdsheet.Cells(y, 1) = "Reports.cbRep" Then **There is an error here. It doesn't like "Reports.cbRep", I've tried different variations but can't seem to get it right**
ersheet.cells (y, 1) = sdsheet.cells (x, 15)
ersheet.Cells (y, 2) = sdsheet.Cells (x, 1)
ersheet.Cells (y, 3) = sdsheet.Cells (x, 10)
etc, etc..

y = y +1
End If
Next x

End Sub

Thanks you

 

[mrmmickle1]

Try this:

[COLOR=#0000ff][B]If [/B][/COLOR][COLOR=#333333]sdsheet.Cells(y, 1) = Reports.cbRep [/COLOR][COLOR=#0000ff][B]Then[/B][/COLOR][COLOR=#333333]

[/COLOR][B][COLOR=#008000]'or[/COLOR][/B][COLOR=#333333]

[/COLOR][COLOR=#0000ff][B]If [/B][/COLOR][COLOR=#333333]sdsheet.Cells(y, 1) = Reports.cbRep.Value [/COLOR][COLOR=#0000ff][B]Then[/B] [/COLOR]

Both should work.

 

[TitusandNero]

Thanks MrMMickle1 - that's perfect, and works. Also just realised I'd gotten my x's and y's mixed up. It should read:

If sdseet.cells (x, 15) = Reports.cbRep Then

Now this bit of coding works I don't suppose you could help me with the code to narrow the search down the search for instances between the dates?

Thanks again.

 

[mrmmickle1]

Do you just want to filter a large datasheet based on your criteria and then copy and paste the data to the appropriate sheet name based on the Reports.cbRep Value?

 

[TitusandNero]

Yes I do. So for example Jack Smith has 'x' many incidences between 'this date' and 'that date'. The code above/below finds all the incidences for Jack Smith, I now want to narrow it down.
So the code to find him is now (after your correction):

Private Sub CommandButton1_Click()
Dim sdsheet As Worksheet, ersheet As Worksheet
Set sdsheet = ThisWorkbook.sheets ("Data")
set ersheet = ThisWorkbook.Sheets ("By Representative")

If sdsheet.cells(Rows.Count, 1).End(xlUp).row = 1 Then
sdLR = 2
Else
sdLR = sdsheet.Cells(Rows.Count, 1).End(xlUp).Row
End If

y = 2 'starting row

For x = 2 To sdLR
If sdsheet.Cells(x, 15) = Reports.cbRep Then
ersheet.cells (y, 1) = sdsheet.cells (x, 15)
ersheet.Cells (y, 2) = sdsheet.Cells (x, 1)
ersheet.Cells (y, 3) = sdsheet.Cells (x, 10)
etc, etc..

y = y +1
End If
Next x

End Sub

In addition to the Name (cbRep) there's 2 other text boxes to narrow the search criteria tbDate1 and tbDate2 which are also on the 'Reports' userform

 

[mrmmickle1]

Try something like this:

SWAP OUT YOUR RANGE AND EDIT THE FIELD #'s IF NECESSARY

[COLOR=#008000][B] 
    'Filter[/B][/COLOR]
    Sheets(1).Range([COLOR=#ff0000][B]"$A$1:$C$8"[/B][/COLOR]).AutoFilter [COLOR=#ff0000][B]Field:=2[/B][/COLOR], Criteria1:=">=" & tbDate2, _
        Operator:=xlAnd, Criteria2:="<=" & tbDate2
    Sheets(1).Range([COLOR=#FF0000][B]"$A$1:$C$8"[/B][/COLOR]).AutoFilter [B][COLOR=#ff0000]Field:=1[/COLOR][/B], Criteria1:=Reports.cbRep

[COLOR=#008000][B]    'Copy and Paste[/B][/COLOR]
    Range("A1:C8").Copy Range("D1:F8")

 

[TitusandNero]

Oh bother Matt, It's all going wrong. Could I ask again for help?
Where exactly do I insert this piece of code in the above and when you say range and field - could you explain that a bit more to me? Sorry. Also now I'm in work the pc I'm running it from doesn't like the code 'LR' which is an abbreviation for last row. Is there another way of coding that. It doesn't like 'last row' either. Get compile error can't find project or library. Thanks again.

 

[mrmmickle1]

Example:

Excel 2010
	A	B	C
1	Name	Date	Volume
2	Matt	3/1/2016	5
3	TitusandNero	3/2/2016	6
4	Matt	3/3/2016	7
5	TitusandNero	3/4/2016	8
6	TitusandNero	3/25/2016	9
7	TitusandNero	3/21/2016	10
8	Matt	3/25/2016	11
Sheet2

Range = a group of cells in a worksheet (in this case A1:C8)
Field = In this case a Column (Column A, B & C are fields) If we define our range as A1:C8. Excel knows these columns as ----> Column A = Field 1, Column B = Field 2, Column C = Field 3

So if I want to filter on the date (Column B... i.e. Field 2) I would use:

[COLOR=#0000ff][/COLOR][COLOR=#008000][B]'Filter[/B][/COLOR][COLOR=#0000ff][/COLOR]
    Sheets(1).Range("$A$1:$C$8").AutoFilter Field:=2, Criteria1:=">=" & tbDate1, _
        Operator:=xlAnd, Criteria2:="<=" & tbDate2

For the LR issue... try declaring it at the top of your code

last row doesn't work because variables cannot have spaces in them.

[B][COLOR=#0000ff]Dim [/COLOR][/B]LR [COLOR=#0000ff][B]As Long[/B][/COLOR]

If you continue to have issues post the current code your working with so I can understand where it's going wrong.

i.e. Where the code breaks (Where the Error Occurs)... i.e. what line of code is highlighted yellow

 

[TitusandNero]

Ok that makes sense. When I get home I'll play around with it see if I can't get it to work. Thanks again.

 

[TitusandNero]

Hi Matt - Right so here is my code now taking into account field and range. I've made LR As Long. Not sure if I need to make sdLR and erLR As Long too? Also the computer at work didn't like the Calendar control so had to substitute it with SEP Date Control LDDate. In red is the compile error it's thrown up so far and green some of my comments so I know what each part is meant to do. Appreciate your patience. Alex.

Private Sub CommandButton1_Click()
Dim sdsheet As Worksheet, ersheet As Worksheet, LR As Long
Set sdsheet = ThisWorkbook.Sheets("Data")"Sheet2
Set ersheet = ThisWorkbook.Sheets("By Representative") 'Sheet5

'Running the report
If sdsheet.Cells(Row.Count, 1).End(xlUp).Row = 1 Then
sdLR = 2
Else
sdLR = sdsheet.Cells(Rows.Count, 1).End(xlUp).Row
End If

If ersheet.Cells(Row.Count, 1).End(xlUp).Row = 1 Then
erLR = 2
Else
erLR = ersheet.Cells(Rows.Count, 1).End(xlUp).Row
End

Me.Hide

"Clear last report
ersheet.Range("A2:e" & erLR).ClearContents 'take A2 to E and then the last row

y = 2 'Starting row


For x = 2 To sdLR
'Old filter was: If sdheet.Cells(x,15) = Reports.LDDR1 Then

'New filter: Name is in Column/Field O/15 and Date is in Column/Field M/13
Sheet(2).Range("$A$2:$R$LR$).Autofilter field:=13,criteria1:">="& LDDR1, operator:=cland,criteria2:="<=" & LDDR2 'Compile error: Expected; list separator or )
Sheet(2).Range("$A$2:$R$LR$").AutoFilter Field:=15, Criteria1:=Reports.cbRepReport

'put on By Representative Sheet(5)
ersheet.Cells(y, 1) = sdsheet.Cells(x, 15)
ersheet.Cells(y, 2) = sdsheet.Cells(x, 1)
ersheet.Cells(y, 3) = sdsheet.Cells(x, 10)
ersheet.Cells(y, 4) = sdsheet.Cells(x, 12)
ersheet.Cells(y, 5) = sdsheet.Cells(x, 13)
ersheet.Cells(y, 6) = sdsheet.Cells(x, 14)
ersheet.Cells(y, 7) = sdsheet.Cells(x, 16)
ersheet.Cells(y, 8) = sdsheet.Cells(x, 17)
y = y + 1
End If

Next x

End Sub