Macro basics
I'm trying to develop some macros. I'm not that versed
at it yet. Here is part of something: Dim iLastCol As Long With Worksheets("2006 Realized Gains") Cells.Select Selection.EntireColumn.Hidden = False .AutoFilterMode = False iLastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column Application.Goto Reference:= _ "INDEX(R1:R65536,COUNTA(C[1])-1,30)" See that "30" in the last line? That's Column 30 ("AD"). I tried to use my "iLastCol" variable instead, but could not. I tried all sorts of permutations; still no luck. What's the trick? Seconds question: How can I save the Application.Goto Reference for repeat use without inserting that statement again each time? E.g., later I will want to select the row below that row. How would I do that? Thanks for any help. Dallman Ross |
Macro basics
Dim iLastCol As Long
dim iLastRow as long With Worksheets("2006 Realized Gains") 'you have to select the sheet before you can select a range 'but you don't have to select either to get your code to work '.select '.Cells.Select .cells.EntireColumn.Hidden = False .AutoFilterMode = False iLastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column iLastRow = .cells(.rows.count,"A").end(xlup).row application.goto _ reference:=.cells(ilastrow + 1, ilastcol) ',scroll:=true end with I used column A to find the nextrow (iLastRow + 1) to go to. I don't understand the second question. Dallman Ross wrote: I'm trying to develop some macros. I'm not that versed at it yet. Here is part of something: Dim iLastCol As Long With Worksheets("2006 Realized Gains") Cells.Select Selection.EntireColumn.Hidden = False .AutoFilterMode = False iLastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column Application.Goto Reference:= _ "INDEX(R1:R65536,COUNTA(C[1])-1,30)" See that "30" in the last line? That's Column 30 ("AD"). I tried to use my "iLastCol" variable instead, but could not. I tried all sorts of permutations; still no luck. What's the trick? Seconds question: How can I save the Application.Goto Reference for repeat use without inserting that statement again each time? E.g., later I will want to select the row below that row. How would I do that? Thanks for any help. Dallman Ross -- Dave Peterson |
Macro basics
In , Dave Peterson
helped me out by fixing up my code some: Dim iLastCol As Long dim iLastRow as long With Worksheets("2006 Realized Gains") 'you have to select the sheet before you can select a range 'but you don't have to select either to get your code to work '.select '.Cells.Select .cells.EntireColumn.Hidden = False .AutoFilterMode = False iLastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column iLastRow = .cells(.rows.count,"A").end(xlup).row application.goto _ reference:=.cells(ilastrow + 1, ilastcol) ',scroll:=true end with Thanks, Dave! That's much better. A couple more questions: I used "Long" as the var type, and I chose that because you'd done so earlier (in another thread where you helped me). But why are we doing that? Why not Integer type? I used column A to find the nextrow (iLastRow + 1) to go to. Okay. I don't know if I need the application.goto stuff or not, but after lots of experimentation recording macros and inspecting the code and trying to bend it to my needs, I'd come up with that. Actually, I took the "+ 1" back out, because my next line in my macros is: Range(Selection, "A1").Sort _ Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes Maybe there's a better way to invoke that range for the sort. I don't understand the second question. The second question was how I can keep the value of the goto reference so I don't have to repeat application.goto _ reference:=.cells(ilastrow, ilastcol) a bunch of times when I do other things later in the macro. Probably if I knew more I wouldn't be using goto anyway, so the question wouldn't be needed. But I don't know other ways to set ranges for actions yet. The next thing I want to do, for example, is move the row defined as "iLastRow + 1" down X number of lines, where X is determied by the number of rows in another sheet that I've just refreshed data for. (The row I'm moving is a row of totals and other calculated fields.) (Then I'll want to fill the rows I've just inserted down to the new location of my bottom, totals, row.) ================================================== ============ Dallman Ross wrote: I'm trying to develop some macros. I'm not that versed at it yet. Here is part of something: Dim iLastCol As Long With Worksheets("2006 Realized Gains") Cells.Select Selection.EntireColumn.Hidden = False .AutoFilterMode = False iLastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column Application.Goto Reference:= _ "INDEX(R1:R65536,COUNTA(C[1])-1,30)" See that "30" in the last line? That's Column 30 ("AD"). I tried to use my "iLastCol" variable instead, but could not. I tried all sorts of permutations; still no luck. What's the trick? Seconds question: How can I save the Application.Goto Reference for repeat use without inserting that statement again each time? E.g., later I will want to select the row below that row. How would I do that? Thanks for any help. Dallman Ross |
Macro basics
Longs can go from -2,147,483,648 to 2,147,483,647.
Integers can go from -32,768 to 32,767 Since there are 64k rows in a worksheet, using Integer could cause trouble if you exceed 32,767 rows. And on top of that (from what I've read), modern computers will convert Integers to Longs before they work with them. So why waste their time! Instead of selecting the cells (using .select or application.goto), you can work directly with the range. For instance: dim iLastCol as long dim iLastRow as long dim myRng as range with worksheets("sheet9999") ilastcol = .cells(1,.columns.count).end(xltoleft).column ilastrow = .cells(.rows.count,"A").end(xlup).row set myrng = .range("a1",.cells(ilastrow,ilastcol) end with Then you can do what you want to that range with myrng .sort key1:=.columns(2), order1:=xlascending, header:=xlyes end with ..columns(2) belongs to myrng. It's the second column within that range--maybe not the second column in the worksheet (in this sample, it is, but it doesn't have to be). I'm not sure I'd keep track of any variable (sometimes, I do, though). I like to just come back and figure it out again: ilastrow = .cells(.rows.count,"A").end(xlup).row If I've done something that increase/decreased the number of rows in that worksheet. Dallman Ross wrote: In , Dave Peterson helped me out by fixing up my code some: Dim iLastCol As Long dim iLastRow as long With Worksheets("2006 Realized Gains") 'you have to select the sheet before you can select a range 'but you don't have to select either to get your code to work '.select '.Cells.Select .cells.EntireColumn.Hidden = False .AutoFilterMode = False iLastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column iLastRow = .cells(.rows.count,"A").end(xlup).row application.goto _ reference:=.cells(ilastrow + 1, ilastcol) ',scroll:=true end with Thanks, Dave! That's much better. A couple more questions: I used "Long" as the var type, and I chose that because you'd done so earlier (in another thread where you helped me). But why are we doing that? Why not Integer type? I used column A to find the nextrow (iLastRow + 1) to go to. Okay. I don't know if I need the application.goto stuff or not, but after lots of experimentation recording macros and inspecting the code and trying to bend it to my needs, I'd come up with that. Actually, I took the "+ 1" back out, because my next line in my macros is: Range(Selection, "A1").Sort _ Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes Maybe there's a better way to invoke that range for the sort. I don't understand the second question. The second question was how I can keep the value of the goto reference so I don't have to repeat application.goto _ reference:=.cells(ilastrow, ilastcol) a bunch of times when I do other things later in the macro. Probably if I knew more I wouldn't be using goto anyway, so the question wouldn't be needed. But I don't know other ways to set ranges for actions yet. The next thing I want to do, for example, is move the row defined as "iLastRow + 1" down X number of lines, where X is determied by the number of rows in another sheet that I've just refreshed data for. (The row I'm moving is a row of totals and other calculated fields.) (Then I'll want to fill the rows I've just inserted down to the new location of my bottom, totals, row.) ================================================== ============ Dallman Ross wrote: I'm trying to develop some macros. I'm not that versed at it yet. Here is part of something: Dim iLastCol As Long With Worksheets("2006 Realized Gains") Cells.Select Selection.EntireColumn.Hidden = False .AutoFilterMode = False iLastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column Application.Goto Reference:= _ "INDEX(R1:R65536,COUNTA(C[1])-1,30)" See that "30" in the last line? That's Column 30 ("AD"). I tried to use my "iLastCol" variable instead, but could not. I tried all sorts of permutations; still no luck. What's the trick? Seconds question: How can I save the Application.Goto Reference for repeat use without inserting that statement again each time? E.g., later I will want to select the row below that row. How would I do that? Thanks for any help. Dallman Ross -- Dave Peterson |
Macro basics
In , Dave Peterson
spake thusly: Longs can go from -2,147,483,648 to 2,147,483,647. Integers can go from -32,768 to 32,767 This is good stuff you're feeding me. Tks. :-) Since there are 64k rows in a worksheet, using Integer could cause trouble if you exceed 32,767 rows. And on top of that (from what I've read), modern computers will convert Integers to Longs before they work with them. So why waste their time! Okay, I see. Instead of selecting the cells (using .select or application.goto), you can work directly with the range. For instance: dim iLastCol as long dim iLastRow as long dim myRng as range Oh! with worksheets("sheet9999") ilastcol = .cells(1,.columns.count).end(xltoleft).column ilastrow = .cells(.rows.count,"A").end(xlup).row set myrng = .range("a1",.cells(ilastrow,ilastcol) end with Yup, that rocks! (Needed a matching closed paren at the end of the "set myrng" line, but I figured that out quickly.) I notice it the code doesn't give me a blatant error if I don't use "set" -- though I didn't try to run it without. But how do I know when to use set and when not to? I'm also getting confused about when to invoke a "with" and when to end it. E.g., in my macro I unhide columns before the sort (to make sure Col. A. was sorted, as it is normally hidden). Is that inside the 'With worksheets("name")' ? Then you can do what you want to that range with myrng .sort key1:=.columns(2), order1:=xlascending, header:=xlyes end with Good. "Key1:=.rows(2)", of course, but I know you're typing fast. :-) That puzzled me for a moment, but I decided to risk trying to run it with ".columns(2)" and guessed it wouldn't do anything horrible to my data, which it didn't. But that did tell me I wanted .rows, since nothing much happened with .columns, and they didn't make sense to me there anyway. :-) :-) I generally back up my stuff before changes, of course, but still . . . .columns(2) belongs to myrng. It's the second column within that range--maybe not the second column in the worksheet (in this sample, it is, but it doesn't have to be). I'm not sure I'd keep track of any variable (sometimes, I do, though). Well, if you know you're through with one, do you bother to free up env space? I like to just come back and figure it out again: ilastrow = .cells(.rows.count,"A").end(xlup).row If I've done something that increase/decreased the number of rows in that worksheet. Okay. In any case, I'm working fine so far. Now I refresh the data source on another sheet in this workbook. (My macro does that, but it stops and asks me to confirm the name. I don't know how to get it not to ask me. So that's one more question.) The next thing that happens is, the sheet we've been working on spits at me in the totals row over what is now a circular reference. That's because there are named ranges in use referring to the data sheet I've just refreshed and made longer, so the totals row now finds itself in the middle of the data it's totalling. Oops. So I need to tell the macro to shut up already about circular refs, because I'm about to move the totals row to the new end and fill down the new cells I'll insert. More coding help will be very much appreciated! ================================================== ================== Dallman Ross wrote: In , Dave Peterson helped me out by fixing up my code some: Dim iLastCol As Long dim iLastRow as long With Worksheets("2006 Realized Gains") 'you have to select the sheet before you can select a range 'but you don't have to select either to get your code to work '.select '.Cells.Select .cells.EntireColumn.Hidden = False .AutoFilterMode = False iLastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column iLastRow = .cells(.rows.count,"A").end(xlup).row application.goto _ reference:=.cells(ilastrow + 1, ilastcol) ',scroll:=true end with Thanks, Dave! That's much better. A couple more questions: I used "Long" as the var type, and I chose that because you'd done so earlier (in another thread where you helped me). But why are we doing that? Why not Integer type? I used column A to find the nextrow (iLastRow + 1) to go to. Okay. I don't know if I need the application.goto stuff or not, but after lots of experimentation recording macros and inspecting the code and trying to bend it to my needs, I'd come up with that. Actually, I took the "+ 1" back out, because my next line in my macros is: Range(Selection, "A1").Sort _ Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes Maybe there's a better way to invoke that range for the sort. I don't understand the second question. The second question was how I can keep the value of the goto reference so I don't have to repeat application.goto _ reference:=.cells(ilastrow, ilastcol) a bunch of times when I do other things later in the macro. Probably if I knew more I wouldn't be using goto anyway, so the question wouldn't be needed. But I don't know other ways to set ranges for actions yet. The next thing I want to do, for example, is move the row defined as "iLastRow + 1" down X number of lines, where X is determied by the number of rows in another sheet that I've just refreshed data for. (The row I'm moving is a row of totals and other calculated fields.) (Then I'll want to fill the rows I've just inserted down to the new location of my bottom, totals, row.) ================================================== ============ Dallman Ross wrote: I'm trying to develop some macros. I'm not that versed at it yet. Here is part of something: Dim iLastCol As Long With Worksheets("2006 Realized Gains") Cells.Select Selection.EntireColumn.Hidden = False .AutoFilterMode = False iLastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column Application.Goto Reference:= _ "INDEX(R1:R65536,COUNTA(C[1])-1,30)" See that "30" in the last line? That's Column 30 ("AD"). I tried to use my "iLastCol" variable instead, but could not. I tried all sorts of permutations; still no luck. What's the trick? Seconds question: How can I save the Application.Goto Reference for repeat use without inserting that statement again each time? E.g., later I will want to select the row below that row. How would I do that? Thanks for any help. Dallman Ross |
Macro basics
Sorry about missing the closing paren.
But you use Set when you want to make an assignment to an object variable. Dim Wks as worksheet set wks = worksheets(...) dim wkbk as workbook set wkbk = workbooks(...) dim pt as pivottable set pt = activesheet.pivottables(...) These objects have lots of properties that can be addressed. wks.name, wks.range(...), wks.protectcontents and lots more stuff. If the variable represents something simple (no properties), then you don't use the Set Dim myVal as long or string or boolean or... myval = 1234 myval = "qwer" myval = false ===================== In my sort sample, I did want key1:=.columns(2). This meant that my column to sort by was column 2 of that range (the dot (.) said that columns(2) belonged to the previous With statement. It wasn't an error. ===================== I like typing: with worksheets("sheet999") set myRng = .range(.cells(1,1),.cells(ilastrow, ilastcol)) end with But if you like you can qualify each range reference: set myrng = worksheets("sheet999").range(worksheets("sheet999" ).cells(1,1), _ worksheets("sheet999").cells(ilastrow, ilastcol)) (watch out for typos. I didn't count my ()'s. I think it makes the code easier to read and understand (and type!). ========= If I've ever bothered freeing any variable space, it was a mistake! Excel/VBA will release any memory used when the procedure ends running. (There are some bugs that cause a memory leak, but I don't worry about them either. You can google "memory leaks" if you're really interested. And if you automate excel from another application, you'll want to release your object variables: set wkbk = nothing set wks = nothing set xlapp = nothing But that's a different subject. ========== I'm not sure what you're refreshing that causes the warning, but lots of times, you can suppress warnings by: application.displayalerts = false 'your code that causes the warning message here application.displayalerts = true Another option would be to remove your subtotals, add your rows, and reinsert the subtotal rows. Or insert your new data above the subtotal rows. David McRitchie has some notes about not having to adjust formulas when additional rows are inserted: http://www.mvps.org/dmcritchie/excel...row.htm#offset Dallman Ross wrote: In , Dave Peterson spake thusly: Longs can go from -2,147,483,648 to 2,147,483,647. Integers can go from -32,768 to 32,767 This is good stuff you're feeding me. Tks. :-) Since there are 64k rows in a worksheet, using Integer could cause trouble if you exceed 32,767 rows. And on top of that (from what I've read), modern computers will convert Integers to Longs before they work with them. So why waste their time! Okay, I see. Instead of selecting the cells (using .select or application.goto), you can work directly with the range. For instance: dim iLastCol as long dim iLastRow as long dim myRng as range Oh! with worksheets("sheet9999") ilastcol = .cells(1,.columns.count).end(xltoleft).column ilastrow = .cells(.rows.count,"A").end(xlup).row set myrng = .range("a1",.cells(ilastrow,ilastcol) end with Yup, that rocks! (Needed a matching closed paren at the end of the "set myrng" line, but I figured that out quickly.) I notice it the code doesn't give me a blatant error if I don't use "set" -- though I didn't try to run it without. But how do I know when to use set and when not to? I'm also getting confused about when to invoke a "with" and when to end it. E.g., in my macro I unhide columns before the sort (to make sure Col. A. was sorted, as it is normally hidden). Is that inside the 'With worksheets("name")' ? Then you can do what you want to that range with myrng .sort key1:=.columns(2), order1:=xlascending, header:=xlyes end with Good. "Key1:=.rows(2)", of course, but I know you're typing fast. :-) That puzzled me for a moment, but I decided to risk trying to run it with ".columns(2)" and guessed it wouldn't do anything horrible to my data, which it didn't. But that did tell me I wanted .rows, since nothing much happened with .columns, and they didn't make sense to me there anyway. :-) :-) I generally back up my stuff before changes, of course, but still . . . .columns(2) belongs to myrng. It's the second column within that range--maybe not the second column in the worksheet (in this sample, it is, but it doesn't have to be). I'm not sure I'd keep track of any variable (sometimes, I do, though). Well, if you know you're through with one, do you bother to free up env space? I like to just come back and figure it out again: ilastrow = .cells(.rows.count,"A").end(xlup).row If I've done something that increase/decreased the number of rows in that worksheet. Okay. In any case, I'm working fine so far. Now I refresh the data source on another sheet in this workbook. (My macro does that, but it stops and asks me to confirm the name. I don't know how to get it not to ask me. So that's one more question.) The next thing that happens is, the sheet we've been working on spits at me in the totals row over what is now a circular reference. That's because there are named ranges in use referring to the data sheet I've just refreshed and made longer, so the totals row now finds itself in the middle of the data it's totalling. Oops. So I need to tell the macro to shut up already about circular refs, because I'm about to move the totals row to the new end and fill down the new cells I'll insert. More coding help will be very much appreciated! ================================================== ================== Dallman Ross wrote: In , Dave Peterson helped me out by fixing up my code some: Dim iLastCol As Long dim iLastRow as long With Worksheets("2006 Realized Gains") 'you have to select the sheet before you can select a range 'but you don't have to select either to get your code to work '.select '.Cells.Select .cells.EntireColumn.Hidden = False .AutoFilterMode = False iLastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column iLastRow = .cells(.rows.count,"A").end(xlup).row application.goto _ reference:=.cells(ilastrow + 1, ilastcol) ',scroll:=true end with Thanks, Dave! That's much better. A couple more questions: I used "Long" as the var type, and I chose that because you'd done so earlier (in another thread where you helped me). But why are we doing that? Why not Integer type? I used column A to find the nextrow (iLastRow + 1) to go to. Okay. I don't know if I need the application.goto stuff or not, but after lots of experimentation recording macros and inspecting the code and trying to bend it to my needs, I'd come up with that. Actually, I took the "+ 1" back out, because my next line in my macros is: Range(Selection, "A1").Sort _ Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes Maybe there's a better way to invoke that range for the sort. I don't understand the second question. The second question was how I can keep the value of the goto reference so I don't have to repeat application.goto _ reference:=.cells(ilastrow, ilastcol) a bunch of times when I do other things later in the macro. Probably if I knew more I wouldn't be using goto anyway, so the question wouldn't be needed. But I don't know other ways to set ranges for actions yet. The next thing I want to do, for example, is move the row defined as "iLastRow + 1" down X number of lines, where X is determied by the number of rows in another sheet that I've just refreshed data for. (The row I'm moving is a row of totals and other calculated fields.) (Then I'll want to fill the rows I've just inserted down to the new location of my bottom, totals, row.) ================================================== ============ Dallman Ross wrote: I'm trying to develop some macros. I'm not that versed at it yet. Here is part of something: Dim iLastCol As Long With Worksheets("2006 Realized Gains") Cells.Select Selection.EntireColumn.Hidden = False .AutoFilterMode = False iLastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column Application.Goto Reference:= _ "INDEX(R1:R65536,COUNTA(C[1])-1,30)" See that "30" in the last line? That's Column 30 ("AD"). I tried to use my "iLastCol" variable instead, but could not. I tried all sorts of permutations; still no luck. What's the trick? Seconds question: How can I save the Application.Goto Reference for repeat use without inserting that statement again each time? E.g., later I will want to select the row below that row. How would I do that? Thanks for any help. Dallman Ross -- Dave Peterson |
Macro basics
In , Dave Peterson
spake thusly: But you use Set when you want to make an assignment to an object variable. Dim Wks as worksheet set wks = worksheets(...) Ah! Gotcha, thanks. ===================== In my sort sample, I did want key1:=.columns(2). This meant that my column to sort by was column 2 of that range (the dot (.) said that columns(2) belonged to the previous With statement. It wasn't an error. Okay. I'm not yet clear on something about it. I guess it's because I was converting to your syntax from my Range(Selection, "A1").Sort _ Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes So I'm sorting based on Column 1. Okay, I'll change it to: With myRng .Sort key1:=.Columns(1), order1:=xlAscending, header:=xlYes End With Yes, I just tried it, and that works fine. Sorry for being dense! ===================== I like typing: with worksheets("sheet999") set myRng = .range(.cells(1,1),.cells(ilastrow, ilastcol)) end with But if you like you can qualify each range reference: set myrng = worksheets("sheet999").range(worksheets("sheet999" ).cells(1,1), _ worksheets("sheet999").cells(ilastrow, ilastcol)) Okay, I see what you like about it. I agree that it's cleaner. ========== I'm not sure what you're refreshing that causes the warning, but lots of times, you can suppress warnings by: application.displayalerts = false 'your code that causes the warning message here application.displayalerts = true I'll see how that works. I'm painstakingly building this macro line-by-line, and I'm not quite there yet. I had a macro earlier that worked, but I've completely changed my sheet around since then -- and that macro was not at all optimal, anyway. Another option would be to remove your subtotals, add your rows, and reinsert the subtotal rows. Well, it's formatted a certain way and has some complex calculated fields, not just totals, so I'd rather not use that approach. I also could temporarily move it to Row 1, then move it back. If I do, I think I'll want to turn off the display refresh during that part of the macro, because that will grate on my sensibilities for some reason I can't quite put into words. I suppose the reason is that I'm a purist at heart, but I know too little about what I'm doing with VBA to be able to live up to my high demands of myself for creating robust and reusable code. Or insert your new data above the subtotal rows. The problem is, I don't know how long the new data is until I refresh that sheet's data source. The source is a CSV file, so I can't use fancy VBA code to inspect it first, either. David McRitchie has some notes about not having to adjust formulas when additional rows are inserted: http://www.mvps.org/dmcritchie/excel...row.htm#offset Bookmarked! Thanks again. Dallman Ross [Prior stuff deleted] |
Macro basics
I've used this technique when I import files.
I get the data and put it in row 3. I add headers to row 2. I add autofilters to that range (A2:X9999). But I put all my subtotals in row 1. (and set rows to repeat at top to 1:2 and freeze panes so that rows 1 and 2 are always visible). I find it much easier and even more useful to have the subtotals at the top. If I use =subtotal()'s in my formulas, they react to the changes in my autofiltering. And I don't have to scroll through the worksheet to find those numbers. Another approach. Drop the subtotals completely and use data|pivottable to create a nice summary table. (Or use both the subtotals at the top and pivottables.) Dallman Ross wrote: In , Dave Peterson spake thusly: But you use Set when you want to make an assignment to an object variable. Dim Wks as worksheet set wks = worksheets(...) Ah! Gotcha, thanks. ===================== In my sort sample, I did want key1:=.columns(2). This meant that my column to sort by was column 2 of that range (the dot (.) said that columns(2) belonged to the previous With statement. It wasn't an error. Okay. I'm not yet clear on something about it. I guess it's because I was converting to your syntax from my Range(Selection, "A1").Sort _ Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes So I'm sorting based on Column 1. Okay, I'll change it to: With myRng .Sort key1:=.Columns(1), order1:=xlAscending, header:=xlYes End With Yes, I just tried it, and that works fine. Sorry for being dense! ===================== I like typing: with worksheets("sheet999") set myRng = .range(.cells(1,1),.cells(ilastrow, ilastcol)) end with But if you like you can qualify each range reference: set myrng = worksheets("sheet999").range(worksheets("sheet999" ).cells(1,1), _ worksheets("sheet999").cells(ilastrow, ilastcol)) Okay, I see what you like about it. I agree that it's cleaner. ========== I'm not sure what you're refreshing that causes the warning, but lots of times, you can suppress warnings by: application.displayalerts = false 'your code that causes the warning message here application.displayalerts = true I'll see how that works. I'm painstakingly building this macro line-by-line, and I'm not quite there yet. I had a macro earlier that worked, but I've completely changed my sheet around since then -- and that macro was not at all optimal, anyway. Another option would be to remove your subtotals, add your rows, and reinsert the subtotal rows. Well, it's formatted a certain way and has some complex calculated fields, not just totals, so I'd rather not use that approach. I also could temporarily move it to Row 1, then move it back. If I do, I think I'll want to turn off the display refresh during that part of the macro, because that will grate on my sensibilities for some reason I can't quite put into words. I suppose the reason is that I'm a purist at heart, but I know too little about what I'm doing with VBA to be able to live up to my high demands of myself for creating robust and reusable code. Or insert your new data above the subtotal rows. The problem is, I don't know how long the new data is until I refresh that sheet's data source. The source is a CSV file, so I can't use fancy VBA code to inspect it first, either. David McRitchie has some notes about not having to adjust formulas when additional rows are inserted: http://www.mvps.org/dmcritchie/excel...row.htm#offset Bookmarked! Thanks again. Dallman Ross [Prior stuff deleted] -- Dave Peterson |
Macro basics
In , Dave Peterson
spake thusly: I've used this technique when I import files. I get the data and put it in row 3. I add headers to row 2. I add autofilters to that range (A2:X9999). But I put all my subtotals in row 1. (and set rows to repeat at top to 1:2 and freeze panes so that rows 1 and 2 are always visible). I've thought about that, based on something someone else said last week, but I haven't yet succeeded in weaning myself from where they are now. I like them there! Oh, well. As for pivot tables, I haven't learned enough about them to get that to work for my needs. It's something I want to get to. But meanwhile, I have what I want; I just want to automate the refresh. Speaking of auto-filters, my plan with my macro is to turn them off -- I listed the code where I did that -- then do my various refreshes, etc., then turn them on. But maybe I should just leave them on but disable them. Not sure how to do that or if it makes much difference. My latest question is, how do I do a loop with two sheetnames. The two a myCSV = "2006 Realized - CSV Data" and myCSV = "Current - CSV Data" And what I'll do twice is: With Worksheets(myCSV) 'ActiveSheet.Visible = True ActiveSheet.Unprotect Selection.QueryTable.Refresh BackgroundQuery:=False ActiveSheet.Protect DrawingObjects:=True, _ Contents:=True, Scenarios:=True 'ActiveSheet.Visible = False End With Also, when I run that code above, the macro stops and waits me to hit the Enter key to accept the suggested name for the query refresh. I don't know why. (And I just got the circular-reference warning trying my half-complete macro up to this point. I'll have to try that warning-shut-off syntax you suggested might help.) Thanks for letting me pick your brain, Dave! Dallman Ross |
Macro basics
Maybe just doing the equivalent of Data|ShowAll would be sufficient.
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 Dallman Ross wrote: In , Dave Peterson spake thusly: I've used this technique when I import files. I get the data and put it in row 3. I add headers to row 2. I add autofilters to that range (A2:X9999). But I put all my subtotals in row 1. (and set rows to repeat at top to 1:2 and freeze panes so that rows 1 and 2 are always visible). I've thought about that, based on something someone else said last week, but I haven't yet succeeded in weaning myself from where they are now. I like them there! Oh, well. As for pivot tables, I haven't learned enough about them to get that to work for my needs. It's something I want to get to. But meanwhile, I have what I want; I just want to automate the refresh. Speaking of auto-filters, my plan with my macro is to turn them off -- I listed the code where I did that -- then do my various refreshes, etc., then turn them on. But maybe I should just leave them on but disable them. Not sure how to do that or if it makes much difference. My latest question is, how do I do a loop with two sheetnames. The two a myCSV = "2006 Realized - CSV Data" and myCSV = "Current - CSV Data" And what I'll do twice is: With Worksheets(myCSV) 'ActiveSheet.Visible = True ActiveSheet.Unprotect Selection.QueryTable.Refresh BackgroundQuery:=False ActiveSheet.Protect DrawingObjects:=True, _ Contents:=True, Scenarios:=True 'ActiveSheet.Visible = False End With Also, when I run that code above, the macro stops and waits me to hit the Enter key to accept the suggested name for the query refresh. I don't know why. (And I just got the circular-reference warning trying my half-complete macro up to this point. I'll have to try that warning-shut-off syntax you suggested might help.) Thanks for letting me pick your brain, Dave! Dallman Ross -- Dave Peterson |
Macro basics
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 |
Macro basics
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- |
Macro basics
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 |
Macro basics
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- |
Macro basics
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 |
Macro basics
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 |
Macro basics
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- |
Macro basics
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 |
Macro basics
The msgbox will popup a message that you'll have to dismiss.
The debug.print will appear in the immediate window of the VBE. Dave Peterson wrote: 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 -- Dave Peterson |
Macro basics
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 |
Macro basics
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 -- Dave Peterson |
Macro basics
ctrl = ctr1+1
(ctr-ell vs. ctr-one plus one). Dave Peterson wrote: 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 -- Dave Peterson -- Dave Peterson |
Macro basics
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 |
Macro basics
I didn't notice this before, but take a look at these two lines:
wsLastRows(iCtr) = .Cells(1, .Columns.Count).End(xlToLeft).Column wsLastCols(iCtr) = .Cells(.Rows.Count, "A").End(xlUp).Row Do you see anything wrong? Scroll down for an answer .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. .. wsLastRows(iCtr) = .Cells(1, .Columns.Count).End(xlToLeft).Column wsLastCols(iCtr) = .Cells(.Rows.Count, "A").End(xlUp).Row You're putting a column number in a "row" variable and a row number in a "col" variable. If the lastrow is greater than 256, then when you refer to that as a column, you'll get an error. wsLastCols(iCtr) = .Cells(1, .Columns.Count).End(xlToLeft).Column wsLastRows(iCtr) = .Cells(.Rows.Count, "A").End(xlUp).Row <<snipped |
Macro basics
In , Dave Peterson
spake thusly: I didn't notice this before, but take a look at these two lines: wsLastRows(iCtr) = .Cells(1, .Columns.Count).End(xlToLeft).Column wsLastCols(iCtr) = .Cells(.Rows.Count, "A").End(xlUp).Row Do you see anything wrong? Man! I looked and I looked, and I didn't see it. Scroll down for an answer :-) You're putting a column number in a "row" variable and a row number in a "col" variable. If the lastrow is greater than 256, then when you refer to that as a column, you'll get an error. Holy moly! That rocks, that you found that one. This thing is starting to be really cool for me. Thanks ever again, Dave! Dallman |
Macro basics
About three weeks ago In , Dave
Peterson spake thusly: [snip to quote of some lines from a macro of mine] wsLastRows(iCtr) = .Cells(1, .Columns.Count).End(xlToLeft).Column wsLastCols(iCtr) = .Cells(.Rows.Count, "A").End(xlUp).Row You're putting a column number in a "row" variable and a row number in a "col" variable. If the lastrow is greater than 256, then when you refer to that as a column, you'll get an error. wsLastCols(iCtr) = .Cells(1, .Columns.Count).End(xlToLeft).Column wsLastRows(iCtr) = .Cells(.Rows.Count, "A").End(xlUp).Row <<snipped The above was part of Dave's help offered over a couple of weeks earlier this month and last month getting me up-to-speed on a somewhat long macro. It now works, more or less doing what I want. I'm grateful. 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 |
Macro basics
Without reading all the code (too much for me!), this line looks funny:
..Range(.Cells(csvLastRows(iCtr)), .Cells(2)).Select I would think that you'd want some columns in there, too. Dallman Ross wrote: About three weeks ago In , Dave Peterson spake thusly: [snip to quote of some lines from a macro of mine] wsLastRows(iCtr) = .Cells(1, .Columns.Count).End(xlToLeft).Column wsLastCols(iCtr) = .Cells(.Rows.Count, "A").End(xlUp).Row You're putting a column number in a "row" variable and a row number in a "col" variable. If the lastrow is greater than 256, then when you refer to that as a column, you'll get an error. wsLastCols(iCtr) = .Cells(1, .Columns.Count).End(xlToLeft).Column wsLastRows(iCtr) = .Cells(.Rows.Count, "A").End(xlUp).Row <<snipped The above was part of Dave's help offered over a couple of weeks earlier this month and last month getting me up-to-speed on a somewhat long macro. It now works, more or less doing what I want. I'm grateful. 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 |
Macro basics
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 |
Macro basics
I put this in the immediate window:
Range(Cells(15), Cells(2)).Select and there were no columns selected. If you break down your code into smaller pieces and ask about those smaller pieces, I think a lot of people will jump in. 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. 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 |
Macro basics
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 |
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 |
All times are GMT +1. The time now is 10:26 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com