modification needed - stumped!
Results 1 to 6 of 6

Thread: modification needed - stumped!
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Dec 2016
    Posts
    214
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default modification needed - stumped!

    The code takes entry information ("entries") and prefills another worksheet ("Final_Wash") with that data. Works flawless.
    I would like to modify the code so that it looks at the "entries" worksheet, column I and only selects those records with a "W".
    Usually pretty good at modifying existing code, but stumped on this one. My guess is the modification is in the "create array from range"



    Code:
    
    Private Sub CommandButton8_Click() 'Wash Print Final
    Dim EntriesRange As Range, BasicRange As Range
        Dim PrintCell As Range, cell As Range
        Dim Data As Variant
        Dim i As Integer
        Dim msg
        
           
                
       msg = MsgBox("Confirm Print all records?", vbYesNo, "Confirm")
            If msg = vbYes Then
        With ThisWorkbook
    'basic sheet data entry range
            Set BasicRange = .Worksheets("Final_Wash").Range("B2,D2,E2,B5,C5,D5,E5,G5,J5")
            With .Worksheets("Entries")
                Set EntriesRange = .Range(.Range("A3"), .Range("A" & .Rows.Count).End(xlUp))
            End With
        End With
        
        For Each cell In EntriesRange.Cells
    'create array from range
            Data = Application.Transpose(cell.Resize(1, 9).Value2)
            i = 1
    'array elements to non-contiguous range
            For Each PrintCell In BasicRange.Cells
                PrintCell.Value = Data(i, 1)
    'increment to next array element
                i = i + 1
            Next PrintCell
            
    'print out record
          BasicRange.Parent.PrintPreview
        Next cell
        ElseIf msg = vbNo Then
    
                End If
        Exit Sub
    End Sub
    
    Last edited by Fluff; May 8th, 2019 at 10:51 AM. Reason: code tags

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,727
    Post Thanks / Like
    Mentioned
    458 Post(s)
    Tagged
    45 Thread(s)

    Default Re: modification needed - stumped!

    How about
    Code:
        For Each cell In EntriesRange.Cells
          If cell.Offset(, 8).Value = "W" Then
       'create array from range
               Data = Application.Transpose(cell.Resize(1, 9).Value2)
               i = 1
       'array elements to non-contiguous range
               For Each PrintCell In BasicRange.Cells
                   PrintCell.Value = Data(i, 1)
       'increment to next array element
                   i = i + 1
               Next PrintCell
               
       'print out record
             BasicRange.Parent.PrintPreview
          End If
        Next cell
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular
    Join Date
    Dec 2016
    Posts
    214
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: modification needed - stumped!

    Fluff it generates an "next without For" error. I see "For" in the line above.

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,727
    Post Thanks / Like
    Mentioned
    458 Post(s)
    Tagged
    45 Thread(s)

    Default Re: modification needed - stumped!

    How about
    Code:
    Private Sub CommandButton8_Click() 'Wash Print Final
    Dim EntriesRange As Range, BasicRange As Range
        Dim PrintCell As Range, cell As Range
        Dim Data As Variant
        Dim i As Integer
        Dim msg
        
           
                
       msg = MsgBox("Confirm Print all records?", vbYesNo, "Confirm")
            If msg = vbYes Then
        With ThisWorkbook
    'basic sheet data entry range
            Set BasicRange = .Worksheets("Final_Wash").Range("B2,D2,E2,B5,C5,D5,E5,G5,J5")
            With .Worksheets("Entries")
                Set EntriesRange = .Range(.Range("A3"), .Range("A" & .Rows.Count).End(xlUp))
            End With
        End With
        
       For Each cell In EntriesRange.Cells
          If cell.Offset(, 8).Value = "W" Then
       'create array from range
               Data = Application.Transpose(cell.Resize(1, 9).Value2)
               i = 1
       'array elements to non-contiguous range
               For Each PrintCell In BasicRange.Cells
                   PrintCell.Value = Data(i, 1)
       'increment to next array element
                   i = i + 1
               Next PrintCell
               
       'print out record
             BasicRange.Parent.PrintPreview
          End If
        Next cell
        End If
    End Sub
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  5. #5
    Board Regular
    Join Date
    Dec 2016
    Posts
    214
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: modification needed - stumped!

    Thanks Fluff works like a MVP!

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,727
    Post Thanks / Like
    Mentioned
    458 Post(s)
    Tagged
    45 Thread(s)

    Default Re: modification needed - stumped!

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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