VBA Loop Help - A doozy for a Friday Afternoon!

[sanantonio]

Hi All,

I suck at loops. I can never get them to work. Ive been sat trying to recycle someone's loop for a couple hours and I can't get it to work

SO,

I need it to loop a Sendkey macro.

I work for a distributor, we have a system that requires the manual keying in of orders by a Human reading from a spreadsheet. Efficiency wise this is of course awful. I'm trying to automate this process.

I'm trying to get the loops to work with the data in the format that it comes to us, but if there's a recommendation that makes everything work but the data needs to be presented in a different format that can be arranged.

My macro needs to take this data and put it into this antiquated module:

Currently my code looks like:

(I've inserted comments using the ')

Sub testing2()

Dim myAlo As Range
Dim myRow, myCount As Long
Dim myWindow As String
Dim myItem, myQuantity As Range
Dim mySlot As Variant
Dim hWnd As Long
Dim Row1 As Long, Row2 As Long, Num1 As Long, Counter1 As Long
Dim Item As Range, Items As Range
Dim ItemCode As String
Dim Window1 As String, Window2 As String, Window3 As String

'Select MDE Module
Window1 = "[MDE000] - MDE Module - DB: USWH00" & Sheets("Cover").Range("J13").Value & "L (USWH00" & Sheets("Cover").Range("J13").Value & "L)  Schema: WAWIADM Role: R_WAWI"
Window2 = "[MDE007] Manual Picklist"
hWnd = FindWindow(vbNullString, Window1)
SetForegroundWindow hWnd
If hWnd > 0 Then
Else
MsgBox ("MDE Module cannot be found.")
myCancel = "Cancel"
Exit Sub
End If
 
'Loop 1, enters store, order area, date, and supplier
'Currently as you can see in the screenshot above are separated by words, if a different format is necessary please recommend! I can easily build something that reformats.

        'Store
        SendKeys ActiveSheet.Range("L4").Value
        SendKeys ("{TAB}")
        'Order Area
        SendKeys ActiveSheet.Range("L6").Value
        SendKeys ("{TAB}")
        'Date
        SendKeys ActiveSheet.Range("L7").Value
        SendKeys ("{TAB}")
        'Supplier
        SendKeys ActiveSheet.Range("L5").Value
        SendKeys ("{TAB}")
        
'Loop 2, Then loops through items and quanities

        'Items - Currently this just runs for the first 2 items. The sendkeys need 2 happen after each item / quantity is entered but the cell reference needs 2 loop
        
        SendKeys ActiveSheet.Range("K9").Value 'First item code
        SendKeys ("{TAB}")
        SendKeys ("{TAB}")
        SendKeys ActiveSheet.Range("L9").Value 'First item quantity
        SendKeys ("{TAB}")
          SendKeys (" ")
        SendKeys ActiveSheet.Range("K10").Value 'Second item code
        SendKeys ("{TAB}")
        SendKeys ("{TAB}")
        SendKeys ActiveSheet.Range("L10").Value 'Second item quantity
         SendKeys ("{TAB}")
           SendKeys (" ")
                 
         'Saves once all items are complete END OF LOOP 2
      SendKeys ("{F3}")
      
      '<> Starts next Store/Supplier/OrderArea/Date
      
      'Back in2 Loop 2.

End Sub

There's a lot of redundant code in there, because like I say I'm trying to retrofit another macro.

Is this double loop process possible? Where it'll loop thru store/order area/supplier/date, then loop through items, then jump to the next store/order area/supplier/date, then loop through items, then jump to the next store/order area/supplier/date etc. ?

Anyone able to help me out?

 

[NateSC]

Yeah, my fault. Sorry. I had it coded to work with the sample data I created. Both of the loops needed references to the myFirstRow and myFirstColumn to make it dynamic. I re-added the use of the Temp values to allow for better debugging, and I think this should work. (You can monitor these through a msgbox or the watch window if you want to make sure they are pulling the right data.)

Sub testing2()

Dim myAlo As Range
Dim myRow, myCount As Long
Dim myWindow As String
Dim myItem, myQuantity As Range
Dim mySlot As Variant
Dim hWnd As Long
Dim Row1 As Long, Row2 As Long, Num1 As Long, Counter1 As Long
Dim Item As Range, Items As Range
Dim ItemCode As String
Dim Window1 As String, Window2 As String, Window3 As String

    'Select MDE Module
    Window1 = "[MDE000] - MDE Module - DB: USWH00" & Sheets("Cover").Range("J13").Value & "L (USWH00" & Sheets("Cover").Range("J13").Value & "L)  Schema: WAWIADM Role: R_WAWI"
    Window2 = "[MDE007] Manual Picklist"
    hWnd = FindWindow(vbNullString, Window1)
    SetForegroundWindow hWnd
    If hWnd > 0 Then
        Else
            MsgBox ("MDE Module cannot be found.")
            myCancel = "Cancel"
    Exit Sub
    End If


' Updated code below this point !!!
Dim numStores As Integer
Dim i As Integer
Dim j As Integer
Dim temp(10) As Variant
Dim ws As Worksheet
Dim myLastCol As Integer
Dim myFirstCol As Integer
Dim myLastRow As Integer
Dim myFirstRow As Integer
Dim myCurrentRow As Integer

    Set ws = ActiveSheet
    
    ' Finds first and last rows and columns with data
    myLastRow = ws.Cells(Rows.Count, 4).End(xlUp).Row
    myFirstRow = ws.Cells(myLastRow, 4).End(xlUp).Row
    myLastCol = ws.Cells(myFirstRow, Columns.Count).End(xlToLeft).Column
    myFirstCol = ws.Cells(myFirstRow, myLastCol).End(xlToLeft).Column
    
    'Figure out how many stores to loop across
    If (myLastCol - myFirstCol + 1) Mod 2 = 0 Then
        numStores = (myLastCol - myFirstCol + 1) / 2
    Else
        temp(1) = MsgBox("The number of columns with store data was not an even number. " _
        & "Please review the data and re-run the macro.", vbOKOnly, "Error in the Data Table")
        Exit Sub
    End If

    
    'Loop 1, enters store, order area, date, and supplier
    For i = 1 To numStores
        
        'Store
        
        temp(1) = ws.Cells(myFirstRow, myFirstCol - 1 + i * 2).Value
        SendKeys temp(1)
        SendKeys ("{TAB}")
        'Order Area
        temp(2) = ws.Cells(myFirstRow + 2, myFirstCol - 1 + i * 2).Value
        SendKeys temp(2)
        SendKeys ("{TAB}")
        'Date
        temp(3) = ws.Cells(myFirstRow + 3, myFirstCol - 1 + i * 2).Value
        SendKeys temp(3)
        SendKeys ("{TAB}")
        'Supplier
        temp(4) = ws.Cells(myFirstRow + 1, myFirstCol - 1 + i * 2).Value
        SendKeys temp(4)
        SendKeys ("{TAB}")
        
        'Loop 2, Then loops through items and quanities
        For j = 1 To (myLastRow - myFirstRow - 4)
            myCurrentRow = myFirstRow + 4 + j
            
            temp(5) = ws.Cells(myCurrentRow, myFirstCol - 1 + i * 2 - 1).Value
            SendKeys temp(5) 'Item code
            SendKeys ("{TAB}")
            SendKeys ("{TAB}")
            temp(6) = ws.Cells(myCurrentRow, myFirstCol - 1 + i * 2).Value
            SendKeys temp(6) 'Item quantity
            SendKeys ("{TAB}")
            SendKeys (" ")
        
        Next j
        
        'Saves once all items are complete END OF LOOP 2
        SendKeys ("{F3}")
    
        '<> Starts next Store/Supplier/OrderArea/Date
    
    Next i

End Sub

 

[sanantonio]

Beautiful! Works like a charm now!! Thank you so much for your efforts and patience! This will make such a positive difference!

One final ask, and please tell me to go away (!), as part of the loop (At the end of each store) is there a way to get it drop an X or a 1, or anything really into the cell above the store number. So when all my data starts in D5, it'll drop this "marker" in E4, then G4 and so on...
My goal is conditionally format the column as the stores orders are sent.

 

[NateSC]

Sadly, I like this stuff.

Just above "Next i" add the following:

' Mark Store as complete - Change the "Loaded" to what ever you want it to be
        ws.Cells(myFirstRow - 1, myFirstCol - 1 + i * 2).Value = "Loaded"

 

[sanantonio]

haha! I enjoy VBA as well, until I get stuck. But I'm learning all the time!

If you're on a roll: a cool feature would be for the loop to skip any store that had "loaded" at the top

 

[NateSC]

haha! I enjoy VBA as well, until I get stuck. But I'm learning all the time!

If you're on a roll: a cool feature would be for the loop to skip any store that had "loaded" at the top

This should get you there. 2 notes:
1) If you change "Loaded" to something else, change what the "If statement" is looking for (just below "For i= 1 to numStores")
2) You could alternatively change it to check IF .... = "" Then and it would only process that store if the space above the store was blank.
Your choice if you want to change it. Hope it works out.

Sub testing2()

Dim myAlo As Range
Dim myRow, myCount As Long
Dim myWindow As String
Dim myItem, myQuantity As Range
Dim mySlot As Variant
Dim hWnd As Long
Dim Row1 As Long, Row2 As Long, Num1 As Long, Counter1 As Long
Dim Item As Range, Items As Range
Dim ItemCode As String
Dim Window1 As String, Window2 As String, Window3 As String

    'Select MDE Module
    Window1 = "[MDE000] - MDE Module - DB: USWH00" & Sheets("Cover").Range("J13").Value & "L (USWH00" & Sheets("Cover").Range("J13").Value & "L)  Schema: WAWIADM Role: R_WAWI"
    Window2 = "[MDE007] Manual Picklist"
    hWnd = FindWindow(vbNullString, Window1)
    SetForegroundWindow hWnd
    If hWnd > 0 Then
        Else
            MsgBox ("MDE Module cannot be found.")
            myCancel = "Cancel"
    Exit Sub
    End If


' Updated code below this point !!!
Dim numStores As Integer
Dim i As Integer
Dim j As Integer
Dim temp(10) As Variant
Dim ws As Worksheet
Dim myLastCol As Integer
Dim myFirstCol As Integer
Dim myLastRow As Integer
Dim myFirstRow As Integer
Dim myCurrentRow As Integer

    Set ws = ActiveSheet
    
    ' Finds first and last rows and columns with data
    myLastRow = ws.Cells(Rows.Count, 4).End(xlUp).Row
    myFirstRow = ws.Cells(myLastRow, 4).End(xlUp).Row
    myLastCol = ws.Cells(myFirstRow, Columns.Count).End(xlToLeft).Column
    myFirstCol = ws.Cells(myFirstRow, myLastCol).End(xlToLeft).Column
    
    'Figure out how many stores to loop across
    If (myLastCol - myFirstCol + 1) Mod 2 = 0 Then
        numStores = (myLastCol - myFirstCol + 1) / 2
    Else
        temp(1) = MsgBox("The number of columns with store data was not an even number. " _
        & "Please review the data and re-run the macro.", vbOKOnly, "Error in the Data Table")
        Exit Sub
    End If

    
    'Loop 1, enters store, order area, date, and supplier
    For i = 1 To numStores
        If ws.Cells(myFirstRow - 1, myFirstCol - 1 + i * 2).Value <> "Loaded" Then
        
            'Store
            
            temp(1) = ws.Cells(myFirstRow, myFirstCol - 1 + i * 2).Value
            SendKeys temp(1)
            SendKeys ("{TAB}")
            'Order Area
            temp(2) = ws.Cells(myFirstRow + 2, myFirstCol - 1 + i * 2).Value
            SendKeys temp(2)
            SendKeys ("{TAB}")
            'Date
            temp(3) = ws.Cells(myFirstRow + 3, myFirstCol - 1 + i * 2).Value
            SendKeys temp(3)
            SendKeys ("{TAB}")
            'Supplier
            temp(4) = ws.Cells(myFirstRow + 1, myFirstCol - 1 + i * 2).Value
            SendKeys temp(4)
            SendKeys ("{TAB}")
            
            'Loop 2, Then loops through items and quanities
            For j = 1 To (myLastRow - myFirstRow - 4)
                myCurrentRow = myFirstRow + 4 + j
                
                temp(5) = ws.Cells(myCurrentRow, myFirstCol - 1 + i * 2 - 1).Value
                SendKeys temp(5) 'Item code
                SendKeys ("{TAB}")
                SendKeys ("{TAB}")
                temp(6) = ws.Cells(myCurrentRow, myFirstCol - 1 + i * 2).Value
                SendKeys temp(6) 'Item quantity
                SendKeys ("{TAB}")
                SendKeys (" ")
            
            Next j
            
            'Saves once all items are complete END OF LOOP 2
            SendKeys ("{F3}")
        
        
            ' Mark Store as complete - Change the "Loaded" to what ever you want it to be
            ws.Cells(myFirstRow - 1, myFirstCol - 1 + i * 2).Value = "Loaded"
            
            '<> Starts next Store/Supplier/OrderArea/Date
            
        End If
    
    Next i

End Sub

 

[NateSC]

Thoughts on comparing to the data values on the other sheet, read the other table into an array before making all the loops. During the inner loop, compare your item against the array and check its value. Color code the cell or skip that cell as needed. Build and test your macro in pieces and you should be fine. Let me know if you need anything.