Prev: Select Case
Next: sheet2 = sheet1
From: Ayo on 11 Apr 2010 17:48
I wrote a function that worked fine before and now all of a sudden I am
getting a "Compile error: Expected array." The problem seem to be with the
line
"startRow(marketNAME)" especially "startRow"
I can't figure out the problem. Please HELP.

Sub getSTARTEND_ROWS()
Dim c As Range, startRow As Long, endRow As Long
Dim marketNAME As String

Worksheets("Lookup Tables").Visible = True
Worksheets("Lookup Tables").Select
For Each c In Worksheets("Lookup Tables").Range("B19:B28")
marketNAME = c
c.Offset(0, 1) = startRow(marketNAME)
c.Offset(0, 2) = lastRow(marketNAME, c.Offset(0, 1))
Next c
Worksheets("Lookup Tables").Visible = False
End Sub

Function startRow(marketNAME As String) As Long
Set STLWS = Worksheets("Sites Task List")
STL_lRow = STLWS.Range("A65536").End(xlUp).Row

For Each c In STLWS.Range("A2:A" & STL_lRow)
If c = marketNAME Then
strRow = c.Row
Exit For
End If
Next c
firstRow = strRow
End Function
From: Dave Peterson on 11 Apr 2010 18:00
You've got a variable named startRow (as long) and a function named startRow.

I don't see where the variable is used. I deleted it and still couldn't test
since the LastRow function isn't there.

Ayo wrote:
>
> I wrote a function that worked fine before and now all of a sudden I am
> getting a "Compile error: Expected array." The problem seem to be with the
> line
> "startRow(marketNAME)" especially "startRow"
> I can't figure out the problem. Please HELP.
>
> Sub getSTARTEND_ROWS()
> Dim c As Range, startRow As Long, endRow As Long
> Dim marketNAME As String
>
> Worksheets("Lookup Tables").Visible = True
> Worksheets("Lookup Tables").Select
> For Each c In Worksheets("Lookup Tables").Range("B19:B28")
> marketNAME = c
> c.Offset(0, 1) = startRow(marketNAME)
> c.Offset(0, 2) = lastRow(marketNAME, c.Offset(0, 1))
> Next c
> Worksheets("Lookup Tables").Visible = False
> End Sub
>
> Function startRow(marketNAME As String) As Long
> Set STLWS = Worksheets("Sites Task List")
> STL_lRow = STLWS.Range("A65536").End(xlUp).Row
>
> For Each c In STLWS.Range("A2:A" & STL_lRow)
> If c = marketNAME Then
> strRow = c.Row
> Exit For
> End If
> Next c
> firstRow = strRow
> End Function

--

Dave Peterson
From: Ayo on 11 Apr 2010 20:21
Thanks Dave. I found it.

"Dave Peterson" wrote:

> You've got a variable named startRow (as long) and a function named startRow.
>
> I don't see where the variable is used. I deleted it and still couldn't test
> since the LastRow function isn't there.
>
> Ayo wrote:
> >
> > I wrote a function that worked fine before and now all of a sudden I am
> > getting a "Compile error: Expected array." The problem seem to be with the
> > line
> > "startRow(marketNAME)" especially "startRow"
> > I can't figure out the problem. Please HELP.
> >
> > Sub getSTARTEND_ROWS()
> > Dim c As Range, startRow As Long, endRow As Long
> > Dim marketNAME As String
> >
> > Worksheets("Lookup Tables").Visible = True
> > Worksheets("Lookup Tables").Select
> > For Each c In Worksheets("Lookup Tables").Range("B19:B28")
> > marketNAME = c
> > c.Offset(0, 1) = startRow(marketNAME)
> > c.Offset(0, 2) = lastRow(marketNAME, c.Offset(0, 1))
> > Next c
> > Worksheets("Lookup Tables").Visible = False
> > End Sub
> >
> > Function startRow(marketNAME As String) As Long
> > Set STLWS = Worksheets("Sites Task List")
> > STL_lRow = STLWS.Range("A65536").End(xlUp).Row
> >
> > For Each c In STLWS.Range("A2:A" & STL_lRow)
> > If c = marketNAME Then
> > strRow = c.Row
> > Exit For
> > End If
> > Next c
> > firstRow = strRow
> > End Function
>
> --
>
> Dave Peterson
> .
>
From: Ayo on 11 Apr 2010 20:28
Thanks Dave. Found it.

"Dave Peterson" wrote:

> You've got a variable named startRow (as long) and a function named startRow.
>
> I don't see where the variable is used. I deleted it and still couldn't test
> since the LastRow function isn't there.
>
> Ayo wrote:
> >
> > I wrote a function that worked fine before and now all of a sudden I am
> > getting a "Compile error: Expected array." The problem seem to be with the
> > line
> > "startRow(marketNAME)" especially "startRow"
> > I can't figure out the problem. Please HELP.
> >
> > Sub getSTARTEND_ROWS()
> > Dim c As Range, startRow As Long, endRow As Long
> > Dim marketNAME As String
> >
> > Worksheets("Lookup Tables").Visible = True
> > Worksheets("Lookup Tables").Select
> > For Each c In Worksheets("Lookup Tables").Range("B19:B28")
> > marketNAME = c
> > c.Offset(0, 1) = startRow(marketNAME)
> > c.Offset(0, 2) = lastRow(marketNAME, c.Offset(0, 1))
> > Next c
> > Worksheets("Lookup Tables").Visible = False
> > End Sub
> >
> > Function startRow(marketNAME As String) As Long
> > Set STLWS = Worksheets("Sites Task List")
> > STL_lRow = STLWS.Range("A65536").End(xlUp).Row
> >
> > For Each c In STLWS.Range("A2:A" & STL_lRow)
> > If c = marketNAME Then
> > strRow = c.Row
> > Exit For
> > End If
> > Next c
> > firstRow = strRow
> > End Function
>
> --
>
> Dave Peterson
> .
>
 | 
Pages: 1
Prev: Select Case
Next: sheet2 = sheet1