Help Building Array

In building my array I have come across a function that allows me to combine 2 arrays to create a string. The problem with the function though is that it creates a seperate sub routine. Not sure how to use a function without it breaking up my sub routine. Below is the code. Any help on this is greatly appreciated as I am quickly running out of time to finish this.

Sub Date_Range()

    Sheet3.Select
    Dim ItemLookup3        As Long
    ItemLookup = Cells.Find(What:="*", _
    SearchDirection:=xlPrevious, _
    SearchOrder:=xlByRows).Row
    
    Sheet1.Select
    Dim ItemLookup1        As Long
    ItemLookup = Cells.Find(What:="*", _
    SearchDirection:=xlPrevious, _
    SearchOrder:=xlByRows).Row
    
    Dim MyArr3 As Variant
    Dim MyArr1 As Variant

    With Sheets(3): Set MyArrItem = .Range("a:a") ', .Cells(Rows.Count, "a").End(xlUp)) ': End With
    With Sheets(3): Set MyArrOrder = .Range("B:B")
    With Sheets(1): Set MyArr1 = .Range("a:a") ', .Cells(Rows.Count, "a").End(xlUp)) ': End With: i = 1
    With Sheets(1): Set MyArrOrder1 = .Range("B:B")

    Public Function CombineTwoDArrays(MyArrItem As Variant, _
    MyArrOrder As Variant) As Variant: End Function


    Public Function CombineTwoMoreDArrays(MyArr1 As Variant, _
    MyArrOrder1 As Variant) As Variant: End Function

    Sheet3.Select
    Dim LoopColumn As Long
    Dim LoopColumn2 As Long
    For LoopColumn = 2000 To 2 Step -1
        If Cells(LoopColumn & CombineTwoDArrays) = "CombineTwoMoreDArrays" Then
            Range(Cells(LoopColumn, 3), Cells(LoopColumn, 11)).Copy
            Sheet1.Select
          '  Range(Cells(, 1) = "010344-001R" And Cells(, 2)) = "Purchase requisition"
            Range(Cells("MyArr1", 4)).PasteSpecial Paste:=xlPasteValues
            'Selection.PasteSpecial Paste:=xlPasteValues
        End If
    Next LoopColumn
    End With: End With: End With: End With

End Sub

I'm not sure to be honest. If not, how should it look?

Read above code.

Thank you, Thank you, Thank you. Brad4444.

I really appreciate the help.

So, do you know anything about creating an array? As you can see from my code I have one specific Item number. Now, I need the system to compare hundreds of Items and Order Types from one sheet to a second sheet and where it finds match do the same copy and paste function as you see in the original code.

Thank you for the help. I really appreciate it.

This goes hand in hand with the thread located here, but it is only a small portion of it. Trying to build an array of Sheet 1 Column A & B and Sheet 3 Column A & B, then compare and where they match move the values from Sheet 3 to Sheet 1.

Unfortunately, I am still new to VBA and have a thread myself open on this forum asking for help with an array. So my question is do you have to use an array to do this? As in it was part of the assignment?
Also are you looking for exact matches?
I would read the values of sheet 1 into an array. Then read the values of sheet 3 into a different array. Then it should be fairly easy, I think, to compare them. If you find a match go to that specific cell and copy the values to where you need to go.
The code below is some stuff I was working on earlier this week and I think it might give you some ideas possibly.

EDIT: The code below is used to find the last rows of a column and also looping to do stuff on each row possibly. Sorry I couldn't be more help.

Dim LastRow As Integer
Dim iRow As Integer

'Find where data ends.
LastRow = Cells(65536, "A").End(xlUp).row
For iRow = 2 To LastRow
    Select Case Cells(iRow, "B").Value
        Case "X"
    Call CopyX(Range(Cells(iRow, "A"), Cells(iRow, "D")))
        Case "XX"
    Call CopyXX(Range(Cells(iRow, "A"), Cells(iRow, "D")))
        Case "XXX"
    Call CopyXXX(Range(Cells(iRow, "A"), Cells(iRow, "D")))
    End Select
Next iRow

End Sub

Sub CopyX(row As Range)
    'Defining data types.
    Dim LowerLeftCell As Long
    Dim Rng As Range

    row.Copy Destination:=Worksheets("Formulas").Range("G2:J2")

    LowerLeftCell = Sheets("Master_Inputs").Range("A" & Rows.Count).End(xlUp).row
    Set Rng = Sheets("Formulas").Range("A4:F61")
    Rng.Copy
    Sheets("Master_Inputs").Range("A" & LowerLeftCell + 1).PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    
    LowerLeftCell = Sheets("Master_Outputs").Range("A" & Rows.Count).End(xlUp).row
    Set Rng = Sheets("Formulas").Range("A63:F79")
    Rng.Copy
    Sheets("Master_Outputs").Range("A" & LowerLeftCell + 1).PasteSpecial xlPasteValues
    Application.CutCopyMode = False


End Sub

Ask your teacher for an extension and you might get it.

http://www.excelforum.com/excel-prog...correctly.html

My discussion on arrays. I can make the array just not use it correctly for my purpose.

Hi Brad,

Nope, not an assignment for School. Something I am working for my job. I have thousands of rows of data with various Devices on 2 different sheets. I need find how much we are shipping of one device and then paste it into the correct cell of another sheet.

The attached spreadhsheet shows a small portion of what I am looking for. The actual spreadsheet has hundreds of items on more than 20,000 rows of data.

Thanks for the help

Kelly

If your due date can wait till Monday I can look at it tonight. I need to learn VBA better anyways so it will give me something to do.

Hello All,

Still stuck on the Array function. I have the code all down to where if I identify a specific Item and Order Type then it runs perfectly, but what I need is it to do a mass comparison. This is really holding me up, if anyone can help I would appreciate it. Thanks

Kelly

Hello All,

The below code still doesn't work completely, but I think I am closer. The error I get now is 'Type Mismatch' at "If MyArr3 = MyArr1 Then". So not sure what the fix is for that. if anyone can help I would appreciate it.

Thanks

Sub Date_Range()

    Dim MyArr3 As Variant
    Dim MyArr1 As Variant
        
    Sheet3.Select
    Dim ItemLookup3        As Long
    ItemLookup = Cells.Find(What:="*", _
    SearchDirection:=xlPrevious, _
    SearchOrder:=xlByRows).Row
    
    Sheet1.Select
    Dim ItemLookup1        As Long
    ItemLookup = Cells.Find(What:="*", _
    SearchDirection:=xlPrevious, _
    SearchOrder:=xlByRows).Row
    

    With Sheets(3): Set MyArr3 = .Range(.[a2], .Cells(Rows.Count, "b").End(xlUp)): End With
    With Sheets(1): Set MyArr1 = .Range(.[a2], .Cells(Rows.Count, "b").End(xlUp)): End With: i = 1
    
    Sheet3.Select
    Dim LoopColumn As Variant
    Dim LoopColumn2 As Variant
    For LoopColumn = 2000 To 2 Step -1
        If MyArr3 = MyArr1 Then
            Range(Cells(LoopColumn, 3), Cells(LoopColumn, 11)).Copy
            Sheet1.Select
            Call Find_Part2
        End If
    Next LoopColumn

End Sub

Sub Find_Part2()

    Dim ItemLookup1        As Long
    ItemLookup = Cells.Find(What:="*", _
    SearchDirection:=xlPrevious, _
    SearchOrder:=xlByRows).Row

    Dim LoopColumn2 As Long
    For LoopColumn2 = 2000 To 2 Step -1
        If Cells(LoopColumn2, 1) = "MyArr3" And Cells(LoopColumn2, 2) = "MyArr1" Then
            Cells(LoopColumn2, 4).Select '.PasteSpecial Paste:=xlPasteValues
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        End If
    Next LoopColumn2

End Sub

hi, jkelly228, can you show real data sample file (100-200 rows will be enough) and result you are trying to obtain?

Hi Watersev,

Attached is a report with a lot more lines. The overall look and even the names of the Sheets match the ultimate look of the report. I apreciate any help you can provide. Thanks

Kelly

1. 7 - number of the column for April
2. 15 - number of the column for December
3. Post a sample of the data not working with the code, please

Hi Watersev,

It actually did work. I had to change the format of the cells from Date to General and it worked perfectly. Thank you so much for all of the help.

Kelly