Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to Resize a variable number of rows including target
I have the ?? hard coded in my macro but sometimes the number of rows to include vary.
I know I have hit all around it but can't nail the proper xldown.rows.count or Rows.Count).End(xlUp)scheme to replace the ??. I thought for sure I would have it in my cheat-sheet but... If Not aCell Is Nothing Then aCell.Resize(??, 1).Copy Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp)(2) End If Thanks, Howard |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to Resize a variable number of rows including target
It occurs to me that being in For Each / Next worksheet statement it may be different than if a single sheet was the case.
So the variable rows code needs to key off the cell aCell on a particular worksheet. For Each ws In ActiveWorkbook.Worksheets If ws.Name < "Sheet1" Then Set aCell = ws.UsedRange.Find(what:=strSearch, LookIn:=xlValues, _ LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) If Not aCell Is Nothing Then aCell.Resize(5, 1).Copy Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp)(2) Set sName = ws.Range("XFD1") MsgBox sName End If End If Next '/ ws Thanks. Howard |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to Resize a variable number of rows including target
I did solve my query. Here's the entire code.
Sub SheetsScan() Dim ws As Worksheet Dim strSearch As String Dim aCell As Range Dim sName As Variant Dim lrC As Long On Error GoTo Err strSearch = InputBox("Find For This.", "Find This") For Each ws In ActiveWorkbook.Worksheets If ws.Name < "Sheet1" Then Set aCell = ws.UsedRange.Find(what:=strSearch, LookIn:=xlValues, _ LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False) If Not aCell Is Nothing Then lrC = aCell.End(xlDown).Row aCell.Resize(lrC, 1).Copy Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp)(2) Set sName = ws.Range("XFD1") MsgBox sName End If End If Next '/ ws Exit Sub Err: MsgBox Err.Description End Sub Howard |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to Resize a variable number of rows including target
You should be aware that the word "Err" is a keyword as in your line...
MsgBox Err.Description ...which refs the errors colection, and so I'm curious why you also use it as a GoTo label. Better this way... On Error GoTo ErrExit '..code NormalExit: Exit Sub ErrExit: MsgBox Err.Description ...so your code is easier to understand what's going on. You might think it's good for now but 6 months down the road will not recall your reasoning. Also, another developer has to 'figure out' what you're code's intent is. (Albeit this is fairly simple, bad habits can cause greater problems!) -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to Resize a variable number of rows including target
On Sunday, April 20, 2014 9:23:49 AM UTC-7, GS wrote:
You should be aware that the word "Err" is a keyword as in your line... MsgBox Err.Description ..which refs the errors colection, and so I'm curious why you also use it as a GoTo label. Better this way... On Error GoTo ErrExit '..code NormalExit: Exit Sub ErrExit: MsgBox Err.Description ..so your code is easier to understand what's going on. You might think it's good for now but 6 months down the road will not recall your reasoning. Also, another developer has to 'figure out' what you're code's intent is. (Albeit this is fairly simple, bad habits can cause greater problems!) -- Garry Did not know that was a keyword. I was browsing for some example code and came across one that had a little of what I was looking for. The err handler was part of that macro and I left it intact, and it was useful to a degree as I puttered with the code. I'll either change as you suggest or eliminate it altogether. Howard Seemed like a good idea at the time so |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to Resize a variable number of rows including target
Did not know that was a keyword. I was browsing for some example
code and came across one that had a little of what I was looking for. The err handler was part of that macro and I left it intact, and it was useful to a degree as I puttered with the code Well I'm sure VB[A] could easily figure out the procedure has a label named "Err:", but it's just not good practice to use (or misuse) built-in keywords that way. That's not much different than writing... On Error GoTo Error OR On Error GoTo GoTo ...and should have been readily recognized as bad coding when you saw the lines... Err: MsgBox Err.Description ...IMO!<g That's the problem with using 'found code' *as is* without revising it to your own standards or coding conventions (if you even have those in place yet)!<g -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to Resize a variable number of rows including target
..IMO!<g That's the problem with using 'found code' *as is* without revising it to your own standards or coding conventions (if you even have those in place yet)!<g <<revising it to your own standards or coding conventions Therein lies my problem. I have 'standards and conventions' they are sadly third rate.<g Kinda like biting the ears off the chocolate Easter bunnies and giving the rest to the kids. Howard |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to Resize a variable number of rows including target
..IMO!<g That's the problem with using 'found code' *as is* without revising it to your own standards or coding conventions (if you even have those in place yet)!<g <<revising it to your own standards or coding conventions Therein lies my problem. I have 'standards and conventions' they are sadly third rate.<g Sorry about my not-so-subtle hint!<g Though I'd call it 2nd or 3rd hand, not "third rate"! Fact is, we all learn by colecting and studying code samples/examples, but we realy don't begin to effectively apply them to our projects until we revise them to our own 'style' (as it were), if for no other reason than to further our own understanding of how to best approach our projects! Kinda like biting the ears off the chocolate Easter bunnies and giving the rest to the kids. Ha, ha! I just ate the head (ears and all) off mine, and I'm saving the rest for later...<bg! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
variable number of rows. | Excel Programming | |||
Create a production v's target report in excel including graphs | Excel Discussion (Misc queries) | |||
Get number of rows that data uses, including blank rows | Excel Discussion (Misc queries) | |||
Get number of rows that data takes up, including blank rows | Excel Worksheet Functions | |||
Including a variable number of columns in Excel Sum formula | Excel Programming |