Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compile error. Need Help with this UDF !!!!!!!!!!!!!!!
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compile error. Need Help with this UDF !!!!!!!!!!!!!!!
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compile error. Need Help with this UDF !!!!!!!!!!!!!!!
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compile error. Need Help with this UDF !!!!!!!!!!!!!!!
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Bubble sort Error (Compile Error: Type Mismtach) | Excel Programming | |||
VBAProject name compile error, not defined at compile time | Excel Programming | |||
error message: compile error, argument not optional | Excel Programming | |||
How do I get rid of "Compile error in hidden module" error message | Excel Discussion (Misc queries) |