Results 1 to 9 of 9

How to fill an Array with values from Range of cells, skip cells then add more values.

Threaded View

jrtraylor How to fill an Array with... 08-01-2017, 01:55 AM
Akuini Re: How to fill an Array with... 08-01-2017, 04:44 AM
jrtraylor Re: How to fill an Array with... 08-01-2017, 10:55 AM
Akuini Re: How to fill an Array with... 08-02-2017, 06:15 AM
jrtraylor Re: How to fill an Array with... 08-02-2017, 11:08 AM
Akuini Re: How to fill an Array with... 08-03-2017, 02:29 AM
Akuini Re: How to fill an Array with... 08-03-2017, 02:36 AM
jrtraylor Re: How to fill an Array with... 08-03-2017, 01:19 PM
Akuini Re: How to fill an Array with... 08-04-2017, 06:27 PM
  1. #1
    Forum Contributor
    Join Date
    04-15-2012
    Location
    California, USA
    MS-Off Ver
    Microsoft 365 Subscription
    Posts
    120

    How to fill an Array with values from Range of cells, skip cells then add more values.

    I have 2 workbooks, Array Source and Array Destination, right now the button (Create Purchase Request) in Array source will look at a the values in column R, and fill an Array (C to K) based on that value. Then insert that Array of Data into the Array Destination workbook. I would like to take the current Array of values but then skip columns and include column S in the Array. The Array destination workbook will get populated in columns C to K but then the added value from the S column will be in the X column. The code I that I am currently using is listed below.

        Set ws1 = ThisWorkbook.Sheets("Parts Summary")
        Set wb2 = Workbooks.Open(fpath)                                             'Open the Purchase Request Template in SharePoint
        Set ws2 = wb2.Sheets("Purchase Req")
                ws2.Range("C10:K335").ClearContents
                ws2.Range("X10:X335").ClearContents
        Set ws3 = ThisWorkbook.Sheets("Legend Plates")
        Set ws4 = wb2.Sheets("Legend Plates")
            ws4.Range("A13:A112").ClearContents
            ws4.Range("C13:M112").ClearContents
        Set R1 = ws1.Range("C10:K344")
        Set R2 = ws2.Range("C10:K344")
        Set R3 = ws3.Range("A13:M132")
        Set R4 = ws4.Range("A13:M132")
        PRNum = ws1.Range("PSPRNo") + 1
    '    ws1.Activate
    
        PSLastRow = ws1.Range("R" & ws1.Rows.Count).End(xlUp).Row
        DataArr = ws1.Range("C10:R" & PSLastRow).Value
        DataArr2 = ws1.Range("C10:T" & PSLastRow).Value
        OrderCnt = Application.CountIf(ws1.Range("R:R"), "Ready to Order")
    
        For Rw = LBound(DataArr, 1) To UBound(DataArr, 1)
    
            If DataArr(Rw, 16) = "Ready to Order" Then 'If the status column = Ready to Order
    
                If IsArray(TData) Then
                    ReDim Preserve TData(8, UBound(TData, 2) + 1)
                    For i = 0 To 8
                        TData(i, UBound(TData, 2)) = DataArr(Rw, i + 1)
                    Next
                Else
                    ReDim TData(8, 0)
                    For i = 0 To 8
                        TData(i, 0) = DataArr(Rw, i + 1)
                    Next
                End If
            End If
            If DataArr(Rw, 16) = "Return" And DataArr2(Rw, 18) = "" Then
                If IsArray(TData) Then
                    ReDim Preserve TData(8, UBound(TData, 2) + 1)
                    For i = 0 To 8
                        TData(i, UBound(TData, 2)) = DataArr(Rw, i + 1)
                    Next
                Else
                    ReDim TData(8, 0)
                    For i = 0 To 8
                        TData(i, 0) = DataArr(Rw, i + 1)
                    Next
                End If
            End If
            
       Next Rw
    
    If IsEmpty(TData) Then
        MsgBox "There are no items marked as Ready to Order, please set the Line Item status for each item you want on the current purchase request to continue"
        wb2.Close
        Exit Sub
    End If
    
        PRLastRow2 = ws2.Range("C" & ws2.Rows.Count).End(xlUp).Row + 1
        
        ws2.Range("C" & PRLastRow2).Resize(UBound(TData, 2) + 1, 9).Value = Application.Transpose(TData)
              
        With ws1
    
            'Set the first and last row to loop through
            PSFirstrow = 10
            PSLastRow = Cells(.Rows.Count, "R").End(xlUp).Row
            
            'loop from Lastrow to Firstrow (bottom to top)
            For PSLrow = PSLastRow To PSFirstrow Step -1
    
                With .Cells(PSLrow, "R")
    
                    If Not IsError(.Value) Then
    
                        If .Value = "Ready to Order" Then
                            .Value = "Requested"
                            .Offset(, 2).Value = PRNum
                        End If
                        If .Value = "Return" And .Offset(, 2) = "" Then
                            .Offset(, 2).Value = PRNum
                        End If
    
                    End If
    
                End With
    
            Next PSLrow
    
        End With
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 07-08-2017, 01:41 PM
  2. Skip x cells and fill (Fill/create weekly average from 7 days and fill down)
    By tunafishes in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-18-2016, 03:43 AM
  3. Replies: 2
    Last Post: 05-21-2014, 02:11 PM
  4. Using Range / Array to fill a set of cells
    By penfold1992 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-18-2013, 10:14 AM
  5. Replies: 1
    Last Post: 04-11-2013, 01:02 PM
  6. [SOLVED] Skip loop if cells equal same values
    By adam2308 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 07-02-2012, 01:19 PM
  7. VBA Average Cells Skip Null Values
    By nguyeda in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 06-02-2011, 07:46 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1