Solved: Complie Error: Expected End Of Statement

[coliervile]

I'm receiving a Compile Error when I try to use this worksheet "Leave Request" in the following code and I can't figure out how to code the worksheet to make it run correctly:

Set rSearch = "Leave Request".Range("A2", Range("A65536").End(xlUp))


Here's the whole code: I'm searching for "AA" in column "A" on worksheet "Leave Request" and display all rows that match in listbox1

Private Sub ListBox1_Change()
Dim FirstAddress As String
Dim strFind As String 'what to find
Dim rSearch As Range 'range to search
Dim fndA, fndB, fndC, fndD, fndE As String
Dim head1, head2, head3, head4, head5 As String 'heading s for list
Dim i As Integer
i = 1
Set rSearch = "Leave Request".Range("A2", Range("A65536").End(xlUp))
strFind = ("AA")
With rSearch
Set c = .Find(strFind, LookIn:=xlValues)
If Not c Is Nothing Then 'found it
c.Select
'load the headings
head1 = Range("A1").Value
head2 = Range("B1").Value
head3 = Range("C1").Value
head4 = Range("D1").Value
head5 = Range("E1").Value
With Me.ListBox1
MyArray(0, 0) = head1
MyArray(0, 1) = head2
MyArray(0, 2) = head3
MyArray(0, 3) = head4
MyArray(0, 4) = head4
End With
FirstAddress = c.Address
Do
'Load details into Listbox
fndA = c.Value
fndB = c.Offset(0, 1).Value
fndC = c.Offset(0, 2).Value
fndD = c.Offset(0, 3).Value
fndD = c.Offset(0, 4).Value
MyArray(i, 0) = fndA
MyArray(i, 1) = fndB
MyArray(i, 2) = fndC
MyArray(i, 3) = fndD
MyArray(i, 4) = fndD
i = i + 1
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
End With
'Load data into LISTBOX
Me.ListBox1.List() = MyArray
End Sub

[coliervile]

Okay I figured that out now I'm stuck on the strFind and I receive this Compile error: Variable not defined

strFind = findFeature.Text.Value = ("AA")

I want to set what is exactly being searched for and in this case "AA" in column "A"......

[Bob Phillips]

What is FindFeature? If it is a control, grab the Text property or the Value property, don't try and grab the Value property of the Text property (there isn 't one).

[coliervile]

It was somthing I had read some where...if I could get a headache I would have a huge one. How do you folks do this day in a day out??? :bug:

I think I figured that part out but I ran into this Compile error: Variable not defined at c=

Private Sub cmdAA_Click()
Dim FirstAddress As String
Dim strFind As String 'what to find
Dim rSearch As Range 'range to search
Dim fndA, fndB, fndC, fndD, fndE As String
Dim head1, head2, head3, head4, head5 As String 'heading s for list
Dim i As Integer
i = 1

Set rSearch = Worksheets("Leave Request").Range("A2", Range("A65536").End(xlUp))
strFind = "AA"
With rSearch
Set c = .Find(strFind, LookIn:=xlValues)
If Not c Is Nothing Then 'found it
c.Select
'load the headings
head1 = Range("A1").Value
head2 = Range("B1").Value
head3 = Range("C1").Value
head4 = Range("D1").Value
head5 = Range("E1").Value
With Me.ListBox1
MyArray(0, 0) = head1
MyArray(0, 1) = head2
MyArray(0, 2) = head3
MyArray(0, 3) = head4
MyArray(0, 4) = head4
End With
FirstAddress = c.Address
Do
'Load details into Listbox
fndA = c.Value
fndB = c.Offset(0, 1).Value
fndC = c.Offset(0, 2).Value
fndD = c.Offset(0, 3).Value
fndD = c.Offset(0, 4).Value
MyArray(i, 0) = fndA
MyArray(i, 1) = fndB
MyArray(i, 2) = fndC
MyArray(i, 3) = fndD
MyArray(i, 4) = fndD
i = i + 1
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
End With
'Load data into LISTBOX
Me.ListBox1.List() = MyArray
End Sub

[mikerickson]

The first question in your OP

Set rSearch = ThisWorkbook.Sheets("Leave Request").Range("A2", Range("A65536").End(xlUp))

The issue with c is that it isn't declared. A Dim statement is needed.

[lucas]

Your question at post #4:
Guessing but maybe because you're using Option explicit and you didn't


Dim c as variant

[coliervile]

Here's my whole coding and I still recieve the following: Run-time error '1004: Application-defined or object-defined error at this location in my code Set rSearch = ThisWorkbook.Worksheets("Leave Request").Range("A2", Range("A65536").End(xlUp))

Please take a look and see if you see where I went wrong....

Option Explicit
Dim MyArray(6, 4)
Public MyData As Range, c As Range


Private Sub cmdAA_Click()
Dim FirstAddress As String
Dim strFind As String 'what to find
Dim rSearch As Range 'range to search
Dim fndA, fndB, fndC, fndD, fndE As String
Dim head1, head2, head3, head4, head5 As String 'heading s for list
Dim c As Variant
Dim i As Integer
i = 1

Set rSearch = ThisWorkbook.Worksheets("Leave Request").Range("A2", Range("A65536").End(xlUp))
strFind = "AA"
With rSearch
Set c = .Find(strFind, LookIn:=xlValues)
If Not c Is Nothing Then 'found it
c.Select
'load the headings
head1 = Range("Name").Value
head2 = Range("Requested").Value
head3 = Range("Type").Value
head4 = Range("Start").Value
head5 = Range("End").Value

With Me.ListBox1
MyArray(0, 0) = head1
MyArray(0, 1) = head2
MyArray(0, 2) = head3
MyArray(0, 3) = head4
MyArray(0, 4) = head5
End With

FirstAddress = c.Address

Do
'Load details into Listbox
fndA = c.Value 'Employees Name
fndB = Format(c.Offset(0, 1).Value, "hh:mm:ss mmm-dd-yyyy") 'Hour and Date Stamp
fndC = c.Offset(0, 2).Value 'Type of Leave
fndD = Format(c.Offset(0, 3).Value, "mmm-dd-yyyy") 'Start Date
fndD = Format(c.Offset(0, 4).Value, "mmm-dd-yyyy") 'End Date
MyArray(i, 0) = fndA
MyArray(i, 1) = fndB
MyArray(i, 2) = fndC
MyArray(i, 3) = fndD
MyArray(i, 4) = fndD
i = i + 1
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
End With
'Load data into LISTBOX
Me.ListBox1.List() = MyArray


End Sub
Private Sub cmdClose_Click()
Unload Me
End Sub

[coliervile]

Here's a copy of "Leave Request" worksheet....

[lucas]

Charlie, are you absolutly sure the name of the sheet in the workbook is Leave Request? If it is then you need to upload your workbook....for me to have a shot at it at least..

[lucas]

On the line in question try changing this:

ThisWorkbook.Worksheets("Leave Request").


to this:


ThisWorkbook.Sheets("Leave Request").

[coliervile]

Good evening to you sir. You can access the command through the "Dashboard"...."View Individual Request" userform and click on the the commandbutton "AA" to activate the coding. I did change Din MyArray to (0, 4)...I think that's correct.

[mikerickson]

The two argument form of Range can't be used as a property. This will set rSearch to the cells from A2 to the bottom of the column.

With ThisWorkbook.Worksheets("Leave Request")
Set rSearch = Range(.Range("A2"), .Range("A65536").End(xlUp))
End With

[coliervile]

"mikerickson" thanks for responding. I changed the code and ran it and nothing was displayed in the listbox. I also changed this part of the code ( strFind = "AA") to this ( strFind = Me.ComboBox1.Value ) and added a comobox thinking that part of code was incorrect and still nothing displayed???? What am I missing here???

Option Explicit
Dim MyArray(0, 4)
Public MyData As Range, c As Range
Private Sub cmdAA_Click()
Dim FirstAddress As String
Dim strFind As String 'what to find
Dim rSearch As Range 'range to search
Dim fndA, fndB, fndC, fndD, fndE As String
Dim head1, head2, head3, head4, head5 As String 'heading s for list
Dim c As Variant
Dim i As Integer
i = 1
With ThisWorkbook.Worksheets("Leave Request")
Set rSearch = Range("A2", Range("A65536").End(xlUp))
End With

strFind = Me.ComboBox1.Value
With rSearch
Set c = .Find(strFind, LookIn:=xlValues)
If Not c Is Nothing Then 'found it
c.Select
'load the headings
head1 = Range("Name").Value
head2 = Range("Requested").Value
head3 = Range("Type").Value
head4 = Range("Start").Value
head5 = Range("End").Value

With Me.ListBox1
MyArray(0, 0) = head1
MyArray(0, 1) = head2
MyArray(0, 2) = head3
MyArray(0, 3) = head4
MyArray(0, 4) = head5
End With

FirstAddress = c.Address

Do
'Load details into Listbox
fndA = c.Value 'Employees Name
fndB = Format(c.Offset(0, 1).Value, "hh:mm:ss dd-mmm-yy") 'Hour and Date Stamp
fndC = c.Offset(0, 2).Value 'Type of Leave
fndD = Format(c.Offset(0, 3).Value, "dd-mmm-yy") 'Start Date
fndD = Format(c.Offset(0, 4).Value, "dd-mmm-yy") 'End Date
MyArray(i, 0) = fndA
MyArray(i, 1) = fndB
MyArray(i, 2) = fndC
MyArray(i, 3) = fndD
MyArray(i, 4) = fndD
i = i + 1
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
End With
'Load data into LISTBOX
Me.ListBox1.List() = MyArray

End Sub
Private Sub cmdClose_Click()
Unload Me
End Sub

Private Sub UserForm_Initialize()
ComboBox1.Value = ""
With ComboBox1
.AddItem "AA"
.AddItem "BB"
.AddItem "CC"
.AddItem "DD"
.AddItem "EE"
.AddItem "FF"
.AddItem "GG"
.AddItem "HH"
.AddItem "II"
.AddItem "JJ"
.AddItem "KK"
.AddItem "LL"
.AddItem "MM"
.AddItem "NN"
.AddItem "OO"
.AddItem "PP"
.AddItem "QQ"
.AddItem "RR"
.AddItem "SS"
.AddItem "TT"
.AddItem "UU"
.AddItem "VV"
.AddItem "WW"
.AddItem "XX"
.AddItem "YY"
.AddItem "ZZ"
End With
End Sub

[Bob Phillips]

What is cmdAA? DO you have a button for each selection possibility (presumably not)? Shouldn't you be using ComboBox1_Click event?

[coliervile]

Good day to you Bob. I did have command buttons but I swithced to a combobox to make the selection....I figured that would be better. I tried the ComboBox1_Click and CommandButton1_Click and neither one worked???

[Bob Phillips]

Tell us what buttons to press, what info to enter, what results you would expect.

[coliervile]

You can access the command through the worksheet "Dashboard"....click on "View Individual Request"...the userform "frmIndLeaveRequest"...and use the combobox to make selection ("AA") and click on "Search". The results should display this:

AA Mar-16-2008 21:03:36 Prime Time Mar-16-2008 Mar-20-2008

AA Mar-19-2008 07"58/;27 Annual Apr-07-2008 Apr-11-2008

all instances of employee "AA" and display all rows of information.

[Bob Phillips]

All driven from the Combob Click



Option Explicit

Public MyData As Range, c As Range
Private myArray As Variant

Private Sub ComboBox1_Click()
Dim FirstAddress As String
Dim strFind As String 'what to find
Dim rSearch As Range 'range to search
Dim fndA, fndB, fndC, fndD, fndE As String
Dim head1, head2, head3, head4, head5 As String 'heading s for list
Dim c As Variant
Dim i As Integer
i = 1
With ThisWorkbook.Worksheets("Leave Request")
Set rSearch = .Range("A2", .Range("A65536").End(xlUp))
End With

strFind = Me.ComboBox1.Value
With rSearch
Set c = .Find(strFind, LookIn:=xlValues)
If Not c Is Nothing Then 'found it
'head1 = .Range().Value
'head2 = .Range().Value
'head3 = .Range().Value
'head4 = .Range().Value
'head5 = .Range().Value

ReDim myArray(0 To 4, 0 To 0)
With Me.ListBox1
myArray(0, 0) = "Name"
myArray(1, 0) = "Requested"
myArray(2, 0) = "Type"
myArray(3, 0) = "Start"
myArray(4, 0) = "End"
End With

FirstAddress = c.Address

Do
'Load details into Listbox
fndA = c.Value 'Employees Name
fndB = Format(c.Offset(0, 1).Value, "hh:mm:ss dd-mmm-yy") 'Hour and Date Stamp
fndC = c.Offset(0, 2).Value 'Type of Leave
fndD = Format(c.Offset(0, 3).Value, "dd-mmm-yy") 'Start Date
fndD = Format(c.Offset(0, 4).Value, "dd-mmm-yy") 'End Date

i = UBound(myArray, 2) + 1
ReDim Preserve myArray(0 To 4, 0 To i)
myArray(0, i) = fndA
myArray(1, i) = fndB
myArray(2, i) = fndC
myArray(3, i) = fndD
myArray(4, i) = fndD
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress

End If
End With
'Load data into LISTBOX
Me.ListBox1.List() = Application.Transpose(myArray)

End Sub

Private Sub cmdClose_Click()
Unload Me
End Sub

Private Sub UserForm_Initialize()
ComboBox1.Value = ""
With ComboBox1
.AddItem "AA"
.AddItem "BB"
.AddItem "CC"
.AddItem "DD"
.AddItem "EE"
.AddItem "FF"
.AddItem "GG"
.AddItem "HH"
.AddItem "II"
.AddItem "JJ"
.AddItem "KK"
.AddItem "LL"
.AddItem "MM"
.AddItem "NN"
.AddItem "OO"
.AddItem "PP"
.AddItem "QQ"
.AddItem "RR"
.AddItem "SS"
.AddItem "TT"
.AddItem "UU"
.AddItem "VV"
.AddItem "WW"
.AddItem "XX"
.AddItem "YY"
.AddItem "ZZ"
End With
End Sub

[Bob Phillips]

BTW, all of your form launch macros do not need to be in individual modules. I would have them all in one module.

[coliervile]

Thanks Bob for the information and I'll tke care of that part of this a bit later. I ran your code and the listbox loads up with "Name" (header) and "AA" (employees initial), but the rest of the information doesn't load up as it should....e.g.

The results should display this the information in Blue as well as "AA":

AA Mar-16-2008 21:03:36 Prime Time Mar-16-2008 Mar-20-2008

AA Mar-19-2008 07:58:27 Annual Apr-07-2008 Apr-11-2008

[Bob Phillips]

That is because you haven't set the ColumnCount property of the listbox to 4.

[coliervile]

Thanks Bob it worked like a charm. I did learn how to set the

.ColumnCount = 5 and also the column widths

.ColumnWidths = "80;100;75;75;75"

Pretty cool stuff. Thanks again for your help and have a good evening.