PDA

View Full Version : Solved: Find last row, Array Worksheets via Function



YellowLabPro
09-15-2007, 07:35 AM
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:

For ai = LBound(wsArray) To UBound(wsArray)
Set wsArray = Worksheets(wsArray(ai))
With wsArray
lrwSource = lr(wsArray(ai), 1)

Function:

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

stanl
09-15-2007, 07:59 AM
shouldn't Col as Variant be Col as Long? .02

YellowLabPro
09-15-2007, 08:05 AM
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
09-15-2007, 08:38 AM
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:
Set wsArray = Worksheets(wsArray(ai))
But I'd like to see how it's declared to give you a better explanation.

mdmackillop
09-15-2007, 08:42 AM
Note the slight change in the Function. WS is Variant.
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

YellowLabPro
09-15-2007, 08:57 AM
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...

Set wsArray = Worksheets(wsArray(ai))
should have been:

Set wsar = Worksheets(wsArray(ai))


Here is the full code, in case someone else comes along looking for an example and needs some help:

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

YellowLabPro
09-15-2007, 08:58 AM
Thanks, Stan, Joseph and Malcolm.

Norie
09-15-2007, 09:00 AM
Doug

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

Oh, and why Application.Volatile?:dunno

YellowLabPro
09-15-2007, 09:08 AM
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
09-15-2007, 09:38 AM
:turnip: :shark: :nooo: :conscious :fighting:

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
09-15-2007, 11:29 AM
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:
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
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
09-15-2007, 01:23 PM
Joseph

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

malik641
09-15-2007, 02:22 PM
You're right. The .Calculate method doesn't really need to be in that function.

rory
09-17-2007, 05:27 AM
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
09-17-2007, 05:43 AM
...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.