Macro basics

The bad news is just eyeballing the code isn't enough--well, for me anyway.
Things can look perfect and there could be something as simple as a typo that
causes trouble.

Dallman Ross wrote:

In , Dave Peterson
spake thusly:

I put this in the immediate window:

Range(Cells(15), Cells(2)).Select

and there were no columns selected.

See? Now, I didn't even know that one could type things into
the immediate window to test them. So you taught me something
else useful.

I figured it out: I don't need a range at all. A cells ref will
do for this.

With wsNames(iCtr)
.Activate
Application.Run "HideCols"

'Go to bottom, Column "B"
.Cells(csvLastRows(iCtr) + 1, 2).Select
End With

If you break down your code into smaller pieces and ask about
those smaller pieces, I think a lot of people will jump in.

Okay. I worry about things like how to show all the Dim
statements, etc., when I do that. But I guess I'll deal
with that as I think of specific questions to ask.

But to set up test data (with or without problem data) and to try
to generally debug the procedure is a task many won't undertake.

I wasn't actually expecting people to mock up data to use
for testing the code in this case; rather, just eyeballing
and saying stuff like, "hey, that algorithm doesn't seem
like the best approach there," or "it would be better
to limit what's going on here to the active sheet, which
you could do thus," and so on. But I agree that it's so
long that people aren't likely going to want to jump in.

I'm used to this kind of kibitzing with Unix shell scripts,
which I know a lot more about. That's the kind of thing some of
us do in another group. But I am not wanting to cause someone
who is a good-guy volunteer (such as you!) many long minutes of
head-scratching if more concise questions are better for me to
ask. I'll see over the next days what I can think of to ask more
directly.

I don't really understand all the ReDim, With, and so on,
stuff. I kind of have a vague idea and guess a lot. I
try it, and if it works, I think maybe I'm on the right
track. That's the kind of stuff I was hoping for a big
brother or sister with. :-)

Thanks,
Dallman

==================================================
Dallman Ross wrote:

In , Dave Peterson
spake thusly:

Without reading all the code (too much for me!), this line looks
funny:

Okay, I can't look a gift horse in the mouth; but if you did
find the time, the last half is the scaggiest for me. :-)
The third loop (of four) on. Maybe others here care to
comment on the style or any obvious weaknesses in the
algorithm I chose.

.Range(.Cells(csvLastRows(iCtr)), .Cells(2)).Select

I would think that you'd want some columns in there, too.

Hmm. I thought the last expression was for columns. I
think I don't quite know how to read the syntax. I did
it all mostly by trial-and-error until it worked (more or
less).

Thanks for any clues.

-dman-

================================================== ================
Dallman Ross wrote:
I'm grateful [about lots of help received so far, mostly from
Dave Peterson, with a macro]. But I'm still fuzzy on enough
parts of it to want to post the whole thing here as it now
stands. My hope is, Dave or someone else will give me further
crib notes on what parts of it might be improved, code-wise,
or might be outright illogical or wrong. Suggestions gladly
entertained!


One thing I can say is, part of it near the end is supposed to
place me in the bottom data-row, but doesn't seem to work. That's
just for looks when the macro is ending, so I don't have to scroll
down by hand in my sheet. But I can't figure out why that part
isn't working right.

The theory behind this macro is: unfilter two main sheets in
my workbook and unhide their hidden cols; sort to set a keyed
reference column to its proper order; go to the two normally hidden
sheets referenced from the two main sheets; unhide/unprotect them
and refresh their data queries; update ranges; rehide/re-protect;
go back to the two main sheets and update the row count to fit
the newly updated referenced data sheets; pull down formulas and
references; convert formulas to values for speed, in all but the
first row; re-sort to desired nominal view; re-auto-filter; set the
active cells to the bottom data rows (doesn't work right).

-dman-

------------------------ start of macro -------------------------

Option Explicit
Sub RGUpdate()
'
' Update Realized Gains and Restate Current Positions
' Macro by Dallman Ross w/ massive kibitzing by Dave Peterson
' Last edited 18-Nov-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")

Dim iCtr As Long
Dim wsNames As Variant
Dim wsLastRows As Variant
Dim wsLastCols As Variant
Dim wsRng As Variant
'************************************************* ********

' Loop through regular worksheets

'************************************************* ********
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
.Cells.EntireColumn.Hidden = False

If .FilterMode Then
.ShowAllData
End If

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

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

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

Debug.Print "We're done with the first loop."

'************************************************* ********
Set csvRG = Worksheets("2006 Realized - CSV Data")
Set csvUG = Worksheets("Current - CSV Data")
Dim csvLastRows As Variant
'************************************************* ********

' Loop through csv source worksheets

'************************************************* ********
wsNames = Array(csvRG, csvUG)
ReDim csvLastRows(LBound(wsNames) To UBound(wsNames))
'************************************************* ********

Application.DisplayAlerts = False
For iCtr = LBound(wsNames) To UBound(wsNames)
With wsNames(iCtr)

.Visible = True
.Activate
.Range("A1").Select 'set focus
.Unprotect

.QueryTables(1).Refresh BackgroundQuery:=False

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

csvLastRows(iCtr) = .Cells(.Rows.Count, "A").End(xlUp).Row - 2
.Visible = False
End With
Next iCtr

Debug.Print "We're done with the second loop."

' Loop again through regular worksheets _
adding or deleting rows, as needed

'************************************************* ********
Dim AdjustRows As Long
wsNames = Array(wsRG, wsUG)
'************************************************* ********

For iCtr = LBound(wsNames) To UBound(wsNames)
With wsNames(iCtr)
.Activate
AdjustRows = csvLastRows(iCtr) - wsLastRows(iCtr)
Debug.Print AdjustRows

If AdjustRows Then

' Add or delete rows
If AdjustRows 0 Then
.Range(Rows(wsLastRows(iCtr) + 1), _
Rows(wsLastRows(iCtr)). _
Offset(AdjustRows, 0)).EntireRow.Insert
Else
.Range(Rows(wsLastRows(iCtr) + AdjustRows + 1), _
Rows(wsLastRows(iCtr)). _
Offset(0, 0)).EntireRow.Delete

End If

' Copy first data row
.Range("A2", .Cells(2, wsLastCols(iCtr))).Copy

' Paste to fill out sheet range; _
convert to values as of Row 3

.Range("A3", .Cells(csvLastRows(iCtr), _
wsLastCols(iCtr))).Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End If

.Range("A1", .Cells(wsLastRows(iCtr) + AdjustRows, _
wsLastCols(iCtr))).Select
Selection.Sort key1:=Range("B2"), order1:=xlAscending, _
Key2:=Range("J2"), Order2:=xlAscending, _
Key3:=Range("M2"), Order3:=xlDescending, _
header:=xlYes
End With
Next iCtr

Debug.Print "We're done with the range-adjustment loop."

' Sort and pre-filter main Realized Gains sheet

With wsRG
.Select
Selection.Sort key1:=Range("B2"), order1:=xlAscending, _
Key2:=Range("P2"), Order2:=xlAscending, _
Key3:=Range("I2"), Order3:=xlAscending, _
header:=xlYes
Selection.AutoFilter Field:=6, Criteria1:="-"
Selection.AutoFilter Field:=23, Criteria1:="1000"
End With

'************************************************* ********
wsNames = Array(wsRG, wsUG)
'************************************************* ********

' Final loop through regular worksheets _
hiding rows, positioning cursor

For iCtr = LBound(wsNames) To UBound(wsNames)
With wsNames(iCtr)
.Activate
Application.Run "HideCols"

'Go to bottom -- doesn't work right for some reason
.Range(.Cells(csvLastRows(iCtr)), .Cells(2)).Select
End With
Next iCtr

Debug.Print "We're done with HideCols loop."

Application.DisplayAlerts = True
End Sub



--

Dave Peterson