In , Dave Peterson
spake thusly:

I have "Option Explicit" at the top of each of my modules.
Inside the VBE, tools|Options|Editor Tab|Check "require variable
declaration"

Okay, great explanation, Dave. I've put that in now too. I'm
beginning to see the light! :-)


But I'm betting that you want wsRng to be an array of ranges. If
that's the case, then:

Set wsRng(iCtr) = .Range("A1", .Cells(wsLastRows(iCtr), wsLastCols(iCtr)))

Yes, certainly correct on your bet. And, again, good explanation!
[Snipped here; cavilers and pundits can look down below for the
full article again. -dr]


I've polished it up again. The bad news is, that line still
doesn't work. :-( Only now it gives a different sort of error:
Application-defined or object-defined error ("1004"). I'll
state for the record that this is Excel 2002 under XP Pro.

Here's that section now:
---------------------------------------------------
Option Explicit
Sub RGUpdate()
'
' Update Realized Gains and Restate Current Positions
' Macro by Dallman Ross w/ massive helpful kibitzing from Dave Peterson
' Last edited 30-Oct-2006

Dim wsRG As Worksheet
Dim wsUG As Worksheet
Dim csvRG As Worksheet
Dim csvUG As Worksheet

Set wsRG = Worksheets("2006 Realized Gains")
Set wsUG = Worksheets("Current Positions")
Set csvRG = Worksheets("2006 Realized - CSV Data")
Set csvUG = Worksheets("Current - CSV Data")

' Loop through regular worksheets
Dim iCtr As Long
Dim wsNames As Variant
Dim wsLastRows As Variant
Dim wsLastCols As Variant
Dim wsRng As Variant

wsNames = Array(wsRG, wsUG)
ReDim wsLastRows(LBound(wsNames) To UBound(wsNames))
ReDim wsLastCols(LBound(wsNames) To UBound(wsNames))
ReDim wsRng(LBound(wsNames) To UBound(wsNames))

For iCtr = LBound(wsNames) To UBound(wsNames)
With wsNames(iCtr)

.Activate
wsLastRows(iCtr) = .Cells(1, .Columns.Count).End(xlToLeft).Column
wsLastCols(iCtr) = .Cells(.Rows.Count, "A").End(xlUp).Row

'Debug.Print iCtr, wsLastRows(iCtr), wsLastCols(iCtr)

'Below line still barfs. :-(
Set wsRng(iCtr) = .Range("A1", .Cells(wsLastRows(iCtr), wsLastCols(iCtr)))

.Cells.EntireColumn.Hidden = False

If .FilterMode Then
.ShowAllData
End If
End With

With wsRng(iCtr)
.Sort key1:=.Columns(1), order1:=xlAscending, header:=xlYes
End With
Next iCtr

' bottom half of macro snipped

End Sub
---------------------------------------------------

Muchas gracias,
Dallman


================================================== ======
In , Dave Peterson
spake thusly:

I have "Option Explicit" at the top of each of my modules.

Inside the VBE, tools|Options|Editor Tab|Check "require variable
declaration"

Without that "option explicit" at the top of the module, you
don't have to declare any variable. But that means that you can
spend hours trying to find why this doesn't work the way you
want:

ctrl = ctr1+1
(ctr-ell vs. ctrl-one).

There are other benefits to declaring your variables, too.

===========

First, wslastrows is an array, same with wslastcols. You only
want to use the lastrow of that worksheet you're processing.

So you'd want something like:
wsRng(iCtr) = .Range("A1", .Cells(wsLastRows(iCtr), wsLastCols(iCtr)))

But I'm betting that you want wsRng to be an array of ranges. If
that's the case, then:

Set wsRng(iCtr) = .Range("A1", .Cells(wsLastRows(iCtr), wsLastCols(iCtr)))

Without the Set in that statement, wsRng would be an array of
arrays of values.

dim temp as variant
temp = range("a1:c9").value
would produce a 9 column by 3 row array of the values in that range

set Temp = range("a1:c9")
would produce a range variable (with all its properties).


Dallman Ross wrote:

In , Dave Peterson
spake thusly:

Dallman Ross wrote:

[H]ow to I tell VBA to print the
result to the screen so I can test things?

You can use
msgbox sometextvariablehere
or
debug.print sometextvariablehere

Okay, I'll try these. I saw your further clarification as
well. Thanks much.

My question about break/continue, etc., isn't from the FORTRAN
world, though I did take a FORTRAN class in college in 1980.
(I can't remember a thing about it.) But I am a Unix scripter.
So I have quite a fair bit of experience with syntax from,
e.g., the Bourne shell under Unix.

By the way, in you sample code snippet you put in declarations:

Dim csvRG As Worksheet
Dim csvUG As Worksheet

'stuff deleted

Set csvRG = Worksheets("2006 Realized - CSV Data")
Set csvUG = Worksheets("Current - CSV Data")

I have a question about it. I actually had that originally,
then took out the Dim statements because in testing I found it
worked fine without them and with just the Sets. You put
them back. So I, also, put them back. But how come it
works without them?

Now I've started to make another loop higher up in the macro.
I've run into trouble and need help to get it working.

Previously, we had "Dim whatever As Range"; but now I want
that to be in a loop as well. So I tried:

'snip stuff

Dim wsRG As Worksheet
Dim wsUG As Worksheet

Set wsRG = Worksheets("2006 Realized Gains")
Set wsUG = Worksheets("Current Positions")

' Loop through regular worksheets
Dim iCtr As Long
Dim wsNames As Variant
Dim wsLastRows As Variant
Dim wsLastCols As Variant
Dim wsRng As Variant

wsNames = Array(wsRG, wsUG)
ReDim wsLastRows(LBound(wsNames) To UBound(wsNames))
ReDim wsLastCols(LBound(wsNames) To UBound(wsNames))
ReDim wsRng(LBound(wsNames) To UBound(wsNames))

For iCtr = LBound(wsNames) To UBound(wsNames)
With wsNames(iCtr)

.Activate
wsLastRows(iCtr) = .Cells(1, .Columns.Count).End(xlToLeft).Column
wsLastCols(iCtr) = .Cells(.Rows.Count, "A").End(xlUp).Row

' next line barfs <======================
wsRng(iCtr) = .Range("A1", .Cells(wsLastRows, wsLastCols))

'snip rest


Okay, so how do I fix that type mismatch?

Dallman Ross