Code to Resize a variable number of rows including target

Discussion in 'Excel Programming' started by L. Howard, Apr 20, 2014.

  1. L. Howard

    L. Howard Guest

    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
     
    L. Howard, Apr 20, 2014
    #1
    1. Advertisements

  2. L. Howard

    L. Howard Guest

    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
     
    L. Howard, Apr 20, 2014
    #2
    1. Advertisements

  3. L. Howard

    L. Howard Guest

    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
     
    L. Howard, Apr 20, 2014
    #3
  4. L. Howard

    GS Guest

    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
     
    GS, Apr 20, 2014
    #4
  5. L. Howard

    L. Howard Guest

    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
     
    L. Howard, Apr 20, 2014
    #5
  6. L. Howard

    GS Guest

    > 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
     
    GS, Apr 20, 2014
    #6
  7. L. Howard

    L. Howard Guest


    >
    > ..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
     
    L. Howard, Apr 20, 2014
    #7
  8. L. Howard

    GS Guest

    >>
    >> ..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
     
    GS, Apr 20, 2014
    #8
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Gunnar Johansson
    Replies:
    3
    Views:
    290
    Bob Phillips
    Jul 1, 2004
  2. Richard Buttrey

    Including a variable number of columns in Excel Sum formula

    Richard Buttrey, Jul 7, 2004, in forum: Excel Programming
    Replies:
    2
    Views:
    160
    Chris.F
    Jul 7, 2004
  3. Kevin McCartney
    Replies:
    3
    Views:
    176
    Kevin McCartney
    Apr 15, 2005
  4. Piranha

    Target As Excel.Range or Target As Range

    Piranha, Jun 2, 2005, in forum: Excel Programming
    Replies:
    2
    Views:
    147
    Piranha
    Jun 3, 2005
  5. Myles
    Replies:
    1
    Views:
    187
    Frederick Chow
    Mar 22, 2006
  6. Buffyslay
    Replies:
    1
    Views:
    260
    Bob Phillips
    Nov 15, 2006
  7. Conan Kelly
    Replies:
    1
    Views:
    308
    Jim Cone
    Nov 16, 2007
  8. joemeshuggah

    variable target address

    joemeshuggah, Dec 17, 2009, in forum: Excel Programming
    Replies:
    1
    Views:
    58
    Ryan H
    Dec 17, 2009
Loading...