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

 

[TitusandNero]

.

 

[mrmmickle1]

In the VBE Step through the code one line at a time using F8. When you get to the error what are the values of LDDR1 and LDDR2?

You can find out by hovering over the variables or using keyboard shortcut Ctrl + G and then typing in the immediate window:

?LDDR1 (Commit this statement using the enter key)

 

[TitusandNero]

OK I think we're getting there, it seems to have solved that problem and it appears to be accepting LDDR1 & LDDR2. However, now if I run it I get a 'Compile error: Sub or function not defined' and it highlights the word Sheet in blue.

Sheet (2).Range("$A$2:$R$LR$").AutoFilter Field:=13, Criteria1:=">= & LDDR1", Operator:=cland, Criteria2:="<= & LDDR2"

 

[mrmmickle1]

Need to add an s. ----> Sheets

 

[TitusandNero]

And the errors keep coming - loosing the will to live here. Compile error can't find project or library and this time the 'y ='in the line y= 2 is highlighted yellow. I've tried but I don't appear to have the ability to go through line by line in VBE. All I can do when I F8 or step in to is run.

 

[mrmmickle1]

How big is your file? Has it crashed recently?

This: Compile error can't find project or library

should not be occurring on the 'y = 2' line of code. Your file could be corrupt.

Can you please post a small block of code where this issue is happening?

 

[TitusandNero]

Hi Matt,
The file is 320kb and yes I did have a crash and had to ctrl+alt+del

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 search filter was: If sdheet.Cells(x,15) = Reports.LDDR1 then
'New search
  Sheets(2).Range("$A$2:$R$LR$").AutoFilter Field:=13, Criteria1:=">= & textbox1", Operator:=cland, Criteria2:="<= & textbox2"
  Sheets(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)

 

[mrmmickle1]

Not sure why your getting an error on the y = 2 line...

May need to copy your code into another file.

I do see an issue on this line:

  LR = Sheets(2).Cells(Rows.Count, "A").End(xlUp).Row

  Sheets(2).Range([COLOR=#ff0000][B]"$A$2:$R" & LR[/B][/COLOR]).AutoFilter Field:=13, Criteria1:=">= & textbox1", Operator:=[COLOR=#ff0000][B]xlAnd[/B][/COLOR], Criteria2:="<= & textbox2"
  Sheets(2).Range([COLOR=#ff0000][B]"$A$2:$R" & LR[/B][/COLOR]).AutoFilter Field:=15, Criteria1:=Reports.cbRepReport

 

[TitusandNero]

Hmm - I had that problem earlier on with a random number generator that went haywire and had to start all over again. This workbook has another userform, will I have to redo the whole lot or do you think I can get away with deleteing this userform and adding another?

 

[mrmmickle1]

Open old excel file along with a new one:

In the VBE you can simply drag the user form from one workbook to another. The code behind the userform will come along with it...