Consulting

Results 1 to 5 of 5

Thread: VBA code does not perform any action

  1. #1
    VBAX Regular
    Joined
    Mar 2022
    Posts
    26
    Location

    VBA code does not perform any action

    Hello,
    I have a module with a code I have used several times in other sheets, a simple copy/paste. But for some reason I can't understand it just does not do anything. It doesn't return any error but it doesn't perform the instructed actions either.
    Help is appreciated.

    The code is as follows:
    Sub CopyPH()
    Dim wsc As Worksheet 'worksheet copy
    Dim wsd As Worksheet 'worksheet destination
    Dim lrow As Long 'last row of worksheet copy
    Dim crow As Long 'copy row
    Dim drow As Long 'destination row
    Set wsc = Sheets("1.2 Post Harvest Plan")
    Set wsd = Sheets("Consolidated Data")
    lrow = wsc.ListObjects("PostHarvest_Plan").Range.Columns(11).Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    crow = 4
    drow = 4
    With wsc
    For crow = 4 To lrow 'starts at 4 because of the header row
    wsd.Cells(drow, 2).Value = .Cells(crow, 11).Value
    wsd.Cells(drow, 3).Value = .Cells(crow, 20).Value
    wsd.Cells(drow, 4).Value = .Cells(crow, 17).Value
    wsd.Cells(drow, 5).Value = .Cells(crow, 26).Value
    wsd.Cells(drow, 6).Value = .Cells(crow, 34).Value
    wsd.Cells(drow, 7).Value = .Cells(crow, 35).Value
    wsd.Cells(drow, 10).Value = .Cells(crow, 41).Value
    drow = drow + 1 'increasing the row in worksheet destination
    Next crow
    End With
    End Sub
    Last edited by SamT; 04-28-2022 at 02:56 AM.

  2. #2
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,615
    Location
    What is the value of lrow when you run the code? If it's less than 4 the code won't do anything.
    Be as you wish to seem

  3. #3
    VBAX Expert
    Joined
    Mar 2008
    Location
    Kent, England
    Posts
    747
    Location
    Have a look at the attachment, you will see it is doing something (maybe not what you want). Maybe you could fill out the result as you wish to see it on the attached and then attach back it here again.

    I think at this point it will be faster to see the result and design our own code to solve the problem.
    Attached Files Attached Files
    If things don't change they stay the same
    Quite often there is a picnic problem (problem in chair not in computer)
    "We were not told it was impossible, so we did it."

  4. #4
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,469
    MS prevented this in the listobject design.
    Avoid reading more than once from a worksheet.
    Exploit Excel's built-in options like advancedfilter.

    This will suffice:
    Sub M_snb()
      Sheet2.Cells(1, 2).Resize(, 7) = Application.Index(Sheet1.ListObjects(1).HeaderRowRange.Value, 1, Array(11, 20, 17, 26, 34, 35, 41))
      Sheet1.ListObjects(1).Range.AdvancedFilter 2, , Sheet2.Cells(1, 2).CurrentRegion
    End Sub
    Attached Files Attached Files
    Last edited by snb; 04-28-2022 at 03:41 AM.

  5. #5
    VBAX Regular
    Joined
    Mar 2022
    Posts
    26
    Location
    Quote Originally Posted by snb View Post
    MS prevented this in the listobject design.
    Avoid reading more than once from a worksheet.
    Exploit Excel's built-in options like advancedfilter.

    This will suffice:
    Sub M_snb()
      Sheet2.Cells(1, 2).Resize(, 7) = Application.Index(Sheet1.ListObjects(1).HeaderRowRange.Value, 1, Array(11, 20, 17, 26, 34, 35, 41))
      Sheet1.ListObjects(1).Range.AdvancedFilter 2, , Sheet2.Cells(1, 2).CurrentRegion
    End Sub

    Thank you

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •