Solved: Find last row, Array Worksheets via Function

[YellowLabPro]

I have a function to find the last row of a sheet, I want to use this to find the last row of each sheet that are setup in an array.
I am getting a ByRef error argument type mismatch.
It seems correct to me....


LastRow Line:
[VBA]
For ai = LBound(wsArray) To UBound(wsArray)
Set wsArray = Worksheets(wsArray(ai))
With wsArray
lrwSource = lr(wsArray(ai), 1)
[/VBA]
Function:
[VBA]
Function lr(ws As Worksheet, Col As Variant) As Long
Application.Volatile
If Not IsNumeric(Col) Then Col = Columns(Col).Column()
lr = ws.Cells(Rows.Count, Col).End(xlUp).Row
End Function
[/VBA]

[stanl]

shouldn't Col as Variant be Col as Long? .02

[YellowLabPro]

The function in non-array use works just fine. I believe Col as Variant is set up as to receive alpha or numeric input.
And is, as Long, after the arguments.

[malik641]

shouldn't Col as Variant be Col as Long? .02

Hey Stan,

The function handle's text values for columns like "AA" or the like.


Doug,

How is wsArray declared? (the Dim statement)
I know this line is hurting you the most:
[vba]Set wsArray = Worksheets(wsArray(ai))[/vba]
But I'd like to see how it's declared to give you a better explanation.

[mdmackillop]

Note the slight change in the Function. WS is Variant.
[vba]Option Explicit

Sub GetLastRows()
Dim wsArray(), lrArray(2)
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Dim WS3 As Worksheet
Dim ai, a, i As Long

Set WS1 = Sheets(1)
Set WS2 = Sheets(2)
Set WS3 = Sheets(3)

wsArray = Array(WS1, WS2, WS3)
For Each ai In wsArray
lrArray(i) = lr(ai, 1)
i = i + 1
Next

For Each a In lrArray
Debug.Print a
Next

End Sub

Function lr(ws, Col As Variant) As Long
Application.Volatile
If Not IsNumeric(Col) Then Col = Columns(Col).Column()
lr = ws.Cells(Rows.Count, Col).End(xlUp).Row
End Function
[/vba]

[YellowLabPro]

It was a mistake on the setup of the array worksheet variables. I still don't fully understand the exchange, but looking over other examples, I have it qualified correctly now.
Initially I had this...

[vba]Set wsArray = Worksheets(wsArray(ai))[/vba]
should have been:
[vba]
Set wsar = Worksheets(wsArray(ai))
[/vba]

Here is the full code, in case someone else comes along looking for an example and needs some help:
[vba]
Sub ColorSwapAR()
Dim wsFF As Worksheet, wsVB As Worksheet, wsar As Worksheet
Dim c As Range, rng As Range
Dim lrwSource As Long, ai As Long
Dim wsArray
Dim FF As String, VB As String, wbn As String
wbn = "MasterImportSheetWebStore.xls": FF = "PCCombined_FF": VB = "PCCombined_VB"
wsArray = Array(FF, VB)
For ai = LBound(wsArray) To UBound(wsArray)
Set wsar = Worksheets(wsArray(ai))
With wsar
lrwSource = lr(wsar, 1)
For Each c In Worksheets("Coltab").Range("Cls")
.Columns(14).Replace What:=c, Replacement:=c.Offset(, 1), LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Next c
End With
Application.ScreenUpdating = True
Application.ActiveSheet.Calculate
Next ai
End Sub
[/vba]

[YellowLabPro]

Thanks, Stan, Joseph and Malcolm.

[Norie]

Doug

This might be a stupid question but why do you need this function?

Oh, and why Application.Volatile?

[YellowLabPro]

Norie,
As one of my mentor's told me, when you can use a function for something and save the typing..... do it!
It also helps me to think about things that are common... e.g., finding the last row, well that is an easy one right, but in this case setting up an array and it failed, I have to think about the referencing and other details which helps continue my learning at the core level. Still need to get some finer details down.

Application.Volatile, Malik asked me the same thing. I could not give him a proven answer. I believe it is there to account for my workbooks being on manual calculation, due to the size and time it requires to process.

ps. I never told you, the little emoticon you used of the plant wacking the other guy was hilarious.... truly....

[Norie]

Doug

I'm not saying using the function is wrong, just seems to me a bit of overkill and it's seems to be causing you problems too.

I'm sure others will disagree.

As to the Application.Volatile, as far as I'm aware that's normally used to ensure user defined functions that are used on worksheets update automatically.

I don't see why you need it here since you aren't actually doing any calculation and the function isn't on the worksheet.

[malik641]

I'm not saying using the function is wrong, just seems to me a bit of overkill and it's seems to be causing you problems too.

I'm sure others will disagree.

I wouldn't say it's overkill. I use a function/subprocedure for anything that is done repititively. It's also easier to maintain, IMO. And since Doug is new to programming, I think it's good practice for him.


Doug,
If you need to calculate the sheet before performing the function, I would use the .Calculate method of the worksheet object. Using your function:
[vba]Function lr(ByRef ws As Worksheet, ByVal Col As Variant) As Long
ws.Calculate
If Not IsNumeric(Col) Then Col = Columns(Col).Column()
lr = ws.Cells(Rows.Count, Col).End(xlUp).Row
End Function[/vba]
Although for what you're doing I don't think you need to recalculate the worksheet since you are not using the value in the last row that is returned (you would use .Calculate if you thought a value in a cell might change that you were going to use...which could be necessary for the function in use). I also added ByRef and ByVal where I thought it was necessary. If you use these it will help you understand how the variables that are passed are treated (btw, ByRef is the default if these keywords are omitted).

[Norie]

Joseph

As far as I can see all Doug's function is doing is finding the last row - not calculating anything.

[malik641]

You're right. The .Calculate method doesn't really need to be in that function.

[rory]

I'd agree you don't need Calculate or Application.Volatile there. It seems to me that that is pretty much the texxtbook definition of what should be a separate function - a short generic routine with one purpose that can be called from any other routines as required; the only thing I would change about it is to give it a meaningful name so that it is clear what it does when you call it from other routines. Just my tuppence worth.

[malik641]

...the only thing I would change about it is to give it a meaningful name so that it is clear what it does when you call it from other routines. Just my tuppence worth.

I fully agree with that.

My variable/procedure/function names have grown in size since I started programming because I want it to be clear what's being called/used. I don't care if the variable's name is 15 characters long because it also means I don't have to put in that many comments either. The variable/procedure/function name is self-explanatory.