In , Dave Peterson
spake thusly:

Maybe just doing the equivalent of Data|ShowAll would be sufficient.

Okay, that's promising, but if all is already displayed, I get
an error. So I need an if-statement. Don't know how to formulate
it. Hoping you'll show me.

And you could use:

dim iCtr as long
dim wksNames as variant
wksnames = array(""2006 Realized - CSV Data", "Current - CSV Data")

for ictr = lbound(wksnames) to ubound(wksnames)
with worksheets(wksnames(ictr))
'do a bunch of stuff
end with
next ictr

Yes, that's excellent. It works well, once I figured out a
couple of critical things. First, I didn't know about

.Activate

but lucked out in finding it in the VBA Help pages when I
couldn't figure out why my directive to unprotect the sheet
wasn't working. Second, my query refresh wouldn't work and
caused a debug error once I inserted it into this "do stuff"
part of the loop. I flailed around for a while with Google
and help pages to no avail. When I was about to give up,
I finally tried this more or less by accident from the help
pages:

.QueryTables(1).Refresh BackgroundQuery:=False

and, lo! it worked. (What I'd ever want instead of (1),
I couldn't tell you.) :-)

N.B.: That was instead of this, which was suddenly barfing:
Selection.QueryTable.Refresh BackgroundQuery:=False

The macro still stops at the refresh and waits for me to
hit the Enter key to accept the name of the data source
that's correctly displayed in the pop-up window.
I would like it if it didn't wait for me to pound on the
Enter key. But that is not critical.

I suppose I also wouldn't mind a "do while" thing with the
variant instead of setting a counter, just as a matter
of style and to build my burgeoning macro know-how.

Okay, this thing is really starting to get somewhere!
Thanks, Dave.

Dallman Ross

In , Dallman Ross <dman@localhost.
spake thusly:

In , Dave Peterson
spake thusly:

Maybe just doing the equivalent of Data|ShowAll would be
sufficient.

Okay, that's promising, but if all is already displayed, I get an
error. So I need an if-statement. Don't know how to formulate
it. Hoping you'll show me.

I found this!

http://www.contextures.com/xlautofilter03.html

If ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End If

I actually had tried something very similar, but it hand't worked.
I had left off "ActiveSheet" and started with the dots.

-dman-

It sounds like you're in business.

Good luck with the tweaks.

Dallman Ross wrote:

In , Dallman Ross <dman@localhost.
spake thusly:

In , Dave Peterson
spake thusly:

Maybe just doing the equivalent of Data|ShowAll would be
sufficient.

Okay, that's promising, but if all is already displayed, I get an
error. So I need an if-statement. Don't know how to formulate
it. Hoping you'll show me.

I found this!

http://www.contextures.com/xlautofilter03.html

If ActiveSheet.FilterMode Then
ActiveSheet.ShowAllData
End If

I actually had tried something very similar, but it hand't worked.
I had left off "ActiveSheet" and started with the dots.

-dman-

--

Dave Peterson

In , Dave Peterson
spake thusly:

It sounds like you're in business.
Good luck with the tweaks.

I've been tweaking away. Uncovered and fixed a couple of bugs.
Now I'm trying to do more with the loop that uses an array of
worksheet names.

My main question for this part of the thread is: can I, in
VBA, set variable using dynamic names?

To explain, I'll show where I'm at now, with stuff before and after
left off:

-----------------
Sub RGUpdate()

'stuff deleted

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

'stuff deleted

Dim iCtr As Long
Dim wsNames As Variant

wsNames = Array(csvRG, csvUG)
For iCtr = LBound(wsNames) To UBound(wsNames)
With wsNames(iCtr)

.Activate 'this turns out to be necessary
.Visible = True
.Unprotect
Range("A1").Select 'just a "focus" thing

'still wish I didn't have to hit Enter to accept this query:
.QueryTables(1).Refresh BackgroundQuery:=False

.Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True

'code I want to add with dynamic var naming:
'== csvRGLastR = .Cells(.Rows.Count, "A").End(xlUp).Row
'^^^^^ {OR}
'== csvUGLastR = .Cells(.Rows.Count, "A").End(xlUp).Row
'^^^^^ Those are strings, but based on the nicknames for the sheets!

.Visible = False

End With
Next iCtr

'stuff deleted

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

Okay, is anything like that possible?

Thanks,
-dman-

Nope.

But you could define another variable.

Option Explicit
Sub RGUpdate()

Dim LastRows As Variant
Dim iCtr As Long
Dim wsNames As Variant
Dim csvRG As Worksheet
Dim csvUG As Worksheet

'stuff deleted

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

'stuff deleted

wsNames = Array(csvRG, csvUG)
ReDim LastRows(LBound(wsNames) To UBound(wsNames))

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

.Activate 'this turns out to be necessary
.Visible = True
.Unprotect
Range("A1").Select 'just a "focus" thing

'still wish I didn't have to hit Enter to accept this query:
'.QueryTables(1).Refresh BackgroundQuery:=False

.Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True

'code I want to add with dynamic var naming:

LastRows(iCtr) = .Cells(.Rows.Count, "A").End(xlUp).Row

.Visible = False

End With
Next iCtr

'stuff deleted

End Sub

Dallman Ross wrote:

In , Dave Peterson
spake thusly:

It sounds like you're in business.
Good luck with the tweaks.

I've been tweaking away. Uncovered and fixed a couple of bugs.
Now I'm trying to do more with the loop that uses an array of
worksheet names.

My main question for this part of the thread is: can I, in
VBA, set variable using dynamic names?

To explain, I'll show where I'm at now, with stuff before and after
left off:

-----------------
Sub RGUpdate()

'stuff deleted

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

'stuff deleted

Dim iCtr As Long
Dim wsNames As Variant

wsNames = Array(csvRG, csvUG)
For iCtr = LBound(wsNames) To UBound(wsNames)
With wsNames(iCtr)

.Activate 'this turns out to be necessary
.Visible = True
.Unprotect
Range("A1").Select 'just a "focus" thing

'still wish I didn't have to hit Enter to accept this query:
.QueryTables(1).Refresh BackgroundQuery:=False

.Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True

'code I want to add with dynamic var naming:
'== csvRGLastR = .Cells(.Rows.Count, "A").End(xlUp).Row
'^^^^^ {OR}
'== csvUGLastR = .Cells(.Rows.Count, "A").End(xlUp).Row
'^^^^^ Those are strings, but based on the nicknames for the sheets!

.Visible = False

End With
Next iCtr

'stuff deleted

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

Okay, is anything like that possible?

Thanks,
-dman-

--

Dave Peterson

Hi Dave, jsd219 here. i just saw that posted to here minutes ago so i
thought i would check inwith you. i am struggling with w script i am
trying to write and you wrote me something kinda similiar so i was
hoping you could help figure this out. it involves checking multiple
columns.

God bless
jsd219


Dave Peterson wrote:
Nope.

But you could define another variable.

Option Explicit
Sub RGUpdate()

Dim LastRows As Variant
Dim iCtr As Long
Dim wsNames As Variant
Dim csvRG As Worksheet
Dim csvUG As Worksheet

'stuff deleted

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

'stuff deleted

wsNames = Array(csvRG, csvUG)
ReDim LastRows(LBound(wsNames) To UBound(wsNames))

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

.Activate 'this turns out to be necessary
.Visible = True
.Unprotect
Range("A1").Select 'just a "focus" thing

'still wish I didn't have to hit Enter to accept this query:
'.QueryTables(1).Refresh BackgroundQuery:=False

.Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True

'code I want to add with dynamic var naming:

LastRows(iCtr) = .Cells(.Rows.Count, "A").End(xlUp).Row

.Visible = False

End With
Next iCtr

'stuff deleted

End Sub

Dallman Ross wrote:

In , Dave Peterson
spake thusly:

It sounds like you're in business.
Good luck with the tweaks.

I've been tweaking away. Uncovered and fixed a couple of bugs.
Now I'm trying to do more with the loop that uses an array of
worksheet names.

My main question for this part of the thread is: can I, in
VBA, set variable using dynamic names?

To explain, I'll show where I'm at now, with stuff before and after
left off:

-----------------
Sub RGUpdate()

'stuff deleted

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

'stuff deleted

Dim iCtr As Long
Dim wsNames As Variant

wsNames = Array(csvRG, csvUG)
For iCtr = LBound(wsNames) To UBound(wsNames)
With wsNames(iCtr)

.Activate 'this turns out to be necessary
.Visible = True
.Unprotect
Range("A1").Select 'just a "focus" thing

'still wish I didn't have to hit Enter to accept this query:
.QueryTables(1).Refresh BackgroundQuery:=False

.Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True

'code I want to add with dynamic var naming:
'== csvRGLastR = .Cells(.Rows.Count, "A").End(xlUp).Row
'^^^^^ {OR}
'== csvUGLastR = .Cells(.Rows.Count, "A").End(xlUp).Row
'^^^^^ Those are strings, but based on the nicknames for the sheets!

.Visible = False

End With
Next iCtr

'stuff deleted

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

Okay, is anything like that possible?

Thanks,
-dman-

--

Dave Peterson

Good stuff, Dave. That works out. (Had to figure out that the
values for iCtr in LastRows(iCtr) would be, in the case of my
meager two loops, 0 and 1, but that wasn't that hard to suss out.)

Speaking of figuring out, how to I tell VBA to print the
result to the screen so I can test things?

Also, what is the VBA equivalent for statements such
as "continue" or "break" in some other languages, e.g.,
for working with loops?

Dallman

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

Nope.

But you could define another variable.

Option Explicit
Sub RGUpdate()

Dim LastRows As Variant
Dim iCtr As Long
Dim wsNames As Variant
Dim csvRG As Worksheet
Dim csvUG As Worksheet

'stuff deleted

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

'stuff deleted

wsNames = Array(csvRG, csvUG)
ReDim LastRows(LBound(wsNames) To UBound(wsNames))

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

.Activate 'this turns out to be necessary
.Visible = True
.Unprotect
Range("A1").Select 'just a "focus" thing

'still wish I didn't have to hit Enter to accept this query:
'.QueryTables(1).Refresh BackgroundQuery:=False

.Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True

'code I want to add with dynamic var naming:

LastRows(iCtr) = .Cells(.Rows.Count, "A").End(xlUp).Row

.Visible = False

End With
Next iCtr

'stuff deleted

End Sub

Dallman Ross wrote:

In , Dave Peterson
spake thusly:

It sounds like you're in business.
Good luck with the tweaks.

I've been tweaking away. Uncovered and fixed a couple of bugs.
Now I'm trying to do more with the loop that uses an array of
worksheet names.

My main question for this part of the thread is: can I, in
VBA, set variable using dynamic names?

To explain, I'll show where I'm at now, with stuff before and after
left off:

-----------------
Sub RGUpdate()

'stuff deleted

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

'stuff deleted

Dim iCtr As Long
Dim wsNames As Variant

wsNames = Array(csvRG, csvUG)
For iCtr = LBound(wsNames) To UBound(wsNames)
With wsNames(iCtr)

.Activate 'this turns out to be necessary
.Visible = True
.Unprotect
Range("A1").Select 'just a "focus" thing

'still wish I didn't have to hit Enter to accept this query:
.QueryTables(1).Refresh BackgroundQuery:=False

.Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True

'code I want to add with dynamic var naming:
'== csvRGLastR = .Cells(.Rows.Count, "A").End(xlUp).Row
'^^^^^ {OR}
'== csvUGLastR = .Cells(.Rows.Count, "A").End(xlUp).Row
'^^^^^ Those are strings, but based on the nicknames for the sheets!

.Visible = False

End With
Next iCtr

'stuff deleted

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

Okay, is anything like that possible?

Thanks,
-dman-

You can use
msgbox sometextvariablehere
or
debug.print sometextvariablehere

And to exit a loop, you can use "exit for" or "exit do".

I try not to use Continue (like in Fortran????). I just include the equivalent
of an if statement within the loop:

do
if something = false then
'do nothing
else
'do something
end if
if somethingelse = true then
exit do
end if
loop


Dallman Ross wrote:

Good stuff, Dave. That works out. (Had to figure out that the
values for iCtr in LastRows(iCtr) would be, in the case of my
meager two loops, 0 and 1, but that wasn't that hard to suss out.)

Speaking of figuring out, how to I tell VBA to print the
result to the screen so I can test things?

Also, what is the VBA equivalent for statements such
as "continue" or "break" in some other languages, e.g.,
for working with loops?

Dallman

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

Nope.

But you could define another variable.

Option Explicit
Sub RGUpdate()

Dim LastRows As Variant
Dim iCtr As Long
Dim wsNames As Variant
Dim csvRG As Worksheet
Dim csvUG As Worksheet

'stuff deleted

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

'stuff deleted

wsNames = Array(csvRG, csvUG)
ReDim LastRows(LBound(wsNames) To UBound(wsNames))

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

.Activate 'this turns out to be necessary
.Visible = True
.Unprotect
Range("A1").Select 'just a "focus" thing

'still wish I didn't have to hit Enter to accept this query:
'.QueryTables(1).Refresh BackgroundQuery:=False

.Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True

'code I want to add with dynamic var naming:

LastRows(iCtr) = .Cells(.Rows.Count, "A").End(xlUp).Row

.Visible = False

End With
Next iCtr

'stuff deleted

End Sub

Dallman Ross wrote:

In , Dave Peterson
spake thusly:

It sounds like you're in business.
Good luck with the tweaks.

I've been tweaking away. Uncovered and fixed a couple of bugs.
Now I'm trying to do more with the loop that uses an array of
worksheet names.

My main question for this part of the thread is: can I, in
VBA, set variable using dynamic names?

To explain, I'll show where I'm at now, with stuff before and after
left off:

-----------------
Sub RGUpdate()

'stuff deleted

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

'stuff deleted

Dim iCtr As Long
Dim wsNames As Variant

wsNames = Array(csvRG, csvUG)
For iCtr = LBound(wsNames) To UBound(wsNames)
With wsNames(iCtr)

.Activate 'this turns out to be necessary
.Visible = True
.Unprotect
Range("A1").Select 'just a "focus" thing

'still wish I didn't have to hit Enter to accept this query:
.QueryTables(1).Refresh BackgroundQuery:=False

.Protect DrawingObjects:=True, _
Contents:=True, Scenarios:=True

'code I want to add with dynamic var naming:
'== csvRGLastR = .Cells(.Rows.Count, "A").End(xlUp).Row
'^^^^^ {OR}
'== csvUGLastR = .Cells(.Rows.Count, "A").End(xlUp).Row
'^^^^^ Those are strings, but based on the nicknames for the sheets!

.Visible = False

End With
Next iCtr

'stuff deleted

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

Okay, is anything like that possible?

Thanks,
-dman-


--

Dave Peterson