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]

What is the first column where you will have this data? Will it always start in K or A or somewhere else?

 

[sanantonio]

In this example it starts in K, but yeah it'll start in A in practice.

 

[NateSC]

I ask because if A is blank, this will not work. For testing, I just read the values into a temp array, so I don't know if the SendKeys work properly (I used your methodology for that part). Also, this should adjust for as many rows or columns of data that you have. Hope this helps:

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, 1).End(xlUp).Row
    myFirstRow = ws.Cells(myLastRow, 1).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 Mod 2 = 0 Then
        numStores = myLastCol / 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
        SendKeys ws.Cells(4, i * 2).Value
        SendKeys ("{TAB}")
        'Order Area
        SendKeys ws.Cells(6, i * 2).Value
        SendKeys ("{TAB}")
        'Date
        SendKeys ws.Cells(7, i * 2).Value
        SendKeys ("{TAB}")
        'Supplier
        SendKeys ws.Cells(5, i * 2).Value
        SendKeys ("{TAB}")
        
        'Loop 2, Then loops through items and quanities
        For j = 1 To (myLastRow - myFirstRow - 4)
            myCurrentRow = myFirstRow + 4 + j
            
            SendKeys ws.Cells(myCurrentRow, i * 2 - 1).Value 'Item code
            SendKeys ("{TAB}")
            SendKeys ("{TAB}")
            SendKeys ws.Cells(myCurrentRow, i * 2).Value '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]

Thanks! I'll give this a go when I'm next in the office and let you know how it goes! Tried it just now and it's erroring on the window finding portion that worked before :-/

 

[NateSC]

Thanks! I'll give this a go when I'm next in the office and let you know how it goes! Tried it just now and it's erroring on the window finding portion that worked before :-/

Good luck. I did comment that portion out, so there is a chance I messed it up when commenting or un-commenting. Let me know how it does.

 

[sanantonio]

Good luck. I did comment that portion out, so there is a chance I messed it up when commenting or un-commenting. Let me know how it does.

This works perfectly thank you so much! Seriously, super grateful for this!

Only thing I had to change was the rows a little as I think I got them mixed up when I first posted.

Only additional ask would be is there a way to get to this work if, for example, all the data started in B5 instead of A1 ?

 

[sanantonio]

And sorry one more is there a way 2 get it to skip any items that have 0s entered as quantity.

 

[NateSC]

And sorry one more is there a way 2 get it to skip any items that have 0s entered as quantity.

Assuming it has a code but no quantity:

 'SendKeys ws.Cells(myCurrentRow, i * 2).Value 'Item quantity 
If ws.Cells(myCurrentRow, i * 2).Value <> 0 Then SendKeys ws.Cells(myCurrentRow, i * 2).Value 'Item quantity

 

[sanantonio]

Great thanks, I'll give it a try in the morning! Any ideas for making it work if the data doesn't go into A1?