VBA Loop through Rows

L

Legacy 313594

Guest
All,

I need help using the loop function to move to the next row while always following the Column order of "I, A, B, D, C, E, F, G, H" Currently, I have alot of identical code, only difference is the row number is increasing. There must be a way to utilize 1 section of code and have it run from row 1 to end of data while following the above Column Copy order.

Here is my code.

Code: 
Dim sh As WorksheetSet sh = ThisWorkbook.Sheets("Muni Loading")


Dim k As Long
Dim G As Long




If IsEmpty(sh.Range("A1").End(xlDown)) = True Then
    k = 1 'Determines # of rows of Data w/Header in Worksheet


Else
    k = sh.Range("A1", sh.Range("A1").End(xlDown)).Rows.Count


    G = k - 1 'G Determines # of rows of Data minus header
End If


'Begin Loading Municipal Bond Securities
        Sess0.Screen.SendKeys ("<ctrl+m>")                  'Select Enter Button to bring cursur to APL Function Line
             Sess0.Screen.WaitHostQuiet (g_HostSettleTime)                   'Wait
                Sess0.Screen.SendKeys ("EDITSEC <ctrl+m>")                  'Enter Pneumonic
                    Sess0.Screen.WaitHostQuiet (g_HostSettleTime)                     'Wait
    Sheets("Muni Loading").Activate                            'Activate Loading Sheet
        Range("I2").Copy                                                'Copy Security from Excel
            Sess0.Screen.Paste                                          'Paste Security into EDITSEC Field
        Sess0.Screen.WaitHostQuiet (g_HostSettleTime)                   'Wait
            Sess0.Screen.SendKeys ("<ctrl+[>[B<ctrl+m>")               'Arrow down & Enter Yes to Add Security
                Sess0.Screen.WaitHostQuiet (g_HostSettleTime)                   'Wait
                    Sess0.Screen.SendKeys ("<ctrl+m>")                          'Arrow down & Enter Yes to Add Security
    Sess0.Screen.WaitHostQuiet (g_HostSettleTime)                   'Wait
            Sess0.Screen.SendKeys ("<tab><tab><ctrl+m>")                   'Arrow Down & Select Muni Option
                Sess0.Screen.WaitHostQuiet (g_HostSettleTime)                   'Wait
                    Sess0.Screen.SendKeys ("<ctrl+[>[B<ctrl+m>")                'Arrow Down & Select "Yes" I want to add this security now
                        Sess0.Screen.WaitHostQuiet (g_HostSettleTime)                   'Wait
    Sheets("Muni Loading").Activate                            'Initiate Loading Sheet
        Range("A2").Copy                                                'Copy Security Name from Excel
            Sess0.Screen.Paste                                          'Paste Security Name into APL
        Sess0.Screen.WaitHostQuiet (g_HostSettleTime)                   'Wait
            Sess0.Screen.SendKeys ("<tab><tab><tab>")                       'Tab 3 times to ISSTY Field
                 Sess0.Screen.WaitHostQuiet (g_HostSettleTime)               'Wait
                        Sess0.Screen.SendKeys ("<keypad 5=""><keypad 0="">")                  'Enter "50" for Muni
                            Sess0.Screen.WaitHostQuiet (g_HostSettleTime)               'Wait
                        Sess0.Screen.SendKeys ("<tab><tab>")                            'Tab 2x's to get to DTD Field
                Sess0.Screen.WaitHostQuiet (g_HostSettleTime)               'Wait
    Sheets("Muni Loading").Activate                                'Initiate Loading Sheet
         Range("B2").Copy                                            'Copy Dated Date from Excel
            Sess0.Screen.Paste                                              'Paste Security Name into APL
                    Sess0.Screen.WaitHostQuiet (g_HostSettleTime)               'Wait
                        Sess0.Screen.SendKeys ("<tab><tab>")                            'Tab 2x's to get to CLSEP Field
                                Sess0.Screen.WaitHostQuiet (g_HostSettleTime)               'Wait
    Sheets("Muni Loading").Activate                                'Initiate Loading Sheet
            Range("D2").Copy                                            'Copy PX_Last(Closing Price)from Excel
                Sess0.Screen.Paste                                              'Paste Closing Price into APL
                    Sess0.Screen.WaitHostQuiet (g_HostSettleTime)               'Wait
                        Sess0.Screen.SendKeys ("<tab><tab>")                            'Tab 2x's to get to MATDT Field
                            Sess0.Screen.WaitHostQuiet (g_HostSettleTime)               'Wait
    Sheets("Muni Loading").Activate                                'Initiate Loading Sheet
        Range("C2").Copy                                            'Copy Maturity Date from Excel
            Sess0.Screen.Paste                                              'Paste Maturity Date into APL
                Sess0.Screen.WaitHostQuiet (g_HostSettleTime)               'Wait
                    Sess0.Screen.SendKeys ("<tab><tab><tab><tab><tab><tab><tab>")   'Tab 7x's to get to MRATING Field
                        Sess0.Screen.WaitHostQuiet (g_HostSettleTime)               'Wait
    Sheets("Muni Loading").Activate                                'Initiate Loading Sheet
        Range("E2").Copy                                            'Copy RTG_MOODY (Moody's Rating) from Excel
            Sess0.Screen.Paste                                              'Paste Moody's Rating into APL
                    Sess0.Screen.WaitHostQuiet (g_HostSettleTime)               'Wait
                        Sess0.Screen.SendKeys ("<tab>")                                 'Tab 1 time to get to SNPRAT Field
                            Sess0.Screen.WaitHostQuiet (g_HostSettleTime)               'Wait
    Sheets("Muni Loading").Activate                                'Initiate Loading Sheet
        Range("F2").Copy                                            'Copy RTG_SP (Standard & Poors Rating) from Excel
            Sess0.Screen.Paste                                              'Paste Standard & Poors Rating into APL
                    Sess0.Screen.WaitHostQuiet (g_HostSettleTime)               'Wait
                        Sess0.Screen.SendKeys ("<tab>")                                 'Tab 1 time to get to CPNRT Field
                                Sess0.Screen.WaitHostQuiet (g_HostSettleTime)               'Wait
    Sheets("Muni Loading").Activate                                'Initiate Loading Sheet
        Range("G2").Copy                                            'Copy CPN (Coupon Rate) from Excel
            Sess0.Screen.Paste                                              'Paste Coupon Rate into APL
                Sess0.Screen.WaitHostQuiet (g_HostSettleTime)               'Wait
                    Sess0.Screen.SendKeys ("<tab><tab>")                           'Tab 2x's to get to MSTATE Field
                        Sess0.Screen.WaitHostQuiet (g_HostSettleTime)               'Wait
    Sheets("Muni Loading").Activate                                'Initiate Loading Sheet
        Range("H2").Copy                                            'Copy STATE_CODE (State of issued Security) from Excel
            Sess0.Screen.Paste                                              'Paste State Code into APL
                Sess0.Screen.WaitHostQuiet (g_HostSettleTime)               'Wait
                    Sess0.Screen.SendKeys ("<ctrl+m>")                    'Enter to solidify entry
                        Sess0.Screen.WaitHostQuiet (g_HostSettleTime)                   'Wait
                            Sess0.Screen.SendKeys ("<ctrl+[>[010q<ctrl+[>[B<ctrl+m>")   'Hit F10, Select Yes Option to save
                        Sess0.Screen.WaitHostQuiet (g_HostSettleTime)                   'Wait
                    Sess0.Screen.SendKeys ("<ctrl+[>[003q<ctrl+[>[B<ctrl+m>")   'Hit Escape, Select Yes to add another Security
            Sess0.Screen.WaitHostQuiet (g_HostSettleTime)                   'Wait

I heard using Activecell.offset(1,0).Select, is an option?</ctrl+m></ctrl+[></ctrl+[></ctrl+m></ctrl+[></ctrl+[></ctrl+m></tab></tab></tab></tab></tab></tab></tab></tab></tab></tab></tab></tab></tab></tab></tab></tab></tab></keypad></keypad></tab></tab></tab></ctrl+m></ctrl+[></ctrl+m></tab></tab></ctrl+m></ctrl+m></ctrl+[></ctrl+m></ctrl+m>
 
Sort by date Sort by votes
I could not test this code, but it should work well enough for you to tweak it to your satisfaction:

Code: 
Sub RevisedCode()

    Dim sh As Worksheet
    Set sh = ThisWorkbook.Sheets("Muni Loading")
    
    Dim k As Long
    Dim G As Long
    Dim lRowIndex As Long
    Dim aryCols As Variant
    Dim lColIndex As Long
    Dim aryTabs As Variant
    Dim lTabIndex As Long
    

    'Fix these =============
    Dim Sess0
    Const g_HostSettleTime = 10
    '===================
    

    '               I  A  B  D  C  E  F  G  H
    aryCols = Array(9, 1, 2, 4, 3, 5, 6, 7, 8)  'Column Copy Order
    aryTabs = Array(0, 0, 2, 2, 7, 1, 7, 2, 0)  '# tabs to get to next field for the corresponding column
    

    If IsEmpty(sh.Range("A1").End(xlDown)) = True Then
        k = 1 'Determines # of rows of Data w/Header in Worksheet
    Else
        k = sh.Range("A1", sh.Range("A1").End(xlDown)).Rows.Count   'This will stop at the first blank row
        G = k - 1 'G Determines # of rows of Data minus header
    End If
    

    For lRowIndex = 2 To G
    
        'Begin Loading Municipal Bond Securities
        Sess0.Screen.SendKeys ("")                              'Select Enter Button to bring cursur to APL Function Line
        Sess0.Screen.WaitHostQuiet (g_HostSettleTime)           'Wait
        
        Sess0.Screen.SendKeys ("EDITSEC ")                      'Enter Pneumonic
        Sess0.Screen.WaitHostQuiet (g_HostSettleTime)           'Wait
            
        'Copy Security from Excel
        For lColIndex = 1 To 9
    
            'Sheets("Muni Loading").Activate                    'Activate Loading Sheet
            'Cells(lRowIndex, lColIndex).Copy
            
            'Don't need to activate sheet, just copy cell
            Sheets("Muni Loading").Cells(lRowIndex, lColIndex).Copy
            
            'Different columns require different actions after the copy
            Select Case lColIndex
            Case 1  'Column I
                Sess0.Screen.Paste                              'Paste Security into EDITSEC Field
                Sess0.Screen.WaitHostQuiet (g_HostSettleTime)   'Wait
                
                Sess0.Screen.SendKeys ("[B")                    'Arrow down & Enter Yes to Add Security
                Sess0.Screen.WaitHostQuiet (g_HostSettleTime)   'Wait
                
                Sess0.Screen.SendKeys ("")                      'Arrow down & Enter Yes to Add Security
                Sess0.Screen.WaitHostQuiet (g_HostSettleTime)   'Wait
            
                Sess0.Screen.SendKeys ("")                      'Arrow Down & Select Muni Option
                Sess0.Screen.WaitHostQuiet (g_HostSettleTime)   'Wait
                
                Sess0.Screen.SendKeys ("[B")                    'Arrow Down & Select "Yes" I want to add this security now
                Sess0.Screen.WaitHostQuiet (g_HostSettleTime)   'Wait
                
            Case 2  'Column A
            
                Sess0.Screen.Paste                              'Paste Security Name into APL
                Sess0.Screen.WaitHostQuiet (g_HostSettleTime)   'Wait
                
                Sess0.Screen.SendKeys ("")                      'Tab 3 times to ISSTY Field
                Sess0.Screen.WaitHostQuiet (g_HostSettleTime)   'Wait
                 
                Sess0.Screen.SendKeys ("")                      'Enter "50" for Muni
                Sess0.Screen.WaitHostQuiet (g_HostSettleTime)   'Wait
                
                For lTabIndex = 1 To 2
                    Sess0.Screen.SendKeys "{TAB}"                   'Tab 2x's to get to DTD Field
                    Sess0.Screen.WaitHostQuiet (g_HostSettleTime)   'Wait
                Next
                
            Case 3, 4, 5, 6, 7, 8, 9    'Columns B(Dated Date), D(Closing Price), C(Maturity Date), E(Moody Rtg), F(S&P Rtg), G(Coupon Rate), H(STATE_CODE)
            
                Sess0.Screen.Paste                              'Paste Security Name into APL
                Sess0.Screen.WaitHostQuiet (g_HostSettleTime)   'Wait
                For lTabIndex = 1 To aryTabs(lColIndex)
                    Sess0.Screen.SendKeys "{TAB}"                   'Tab aryTabs(lColIndex) times to get to next Field
                                                                        'B=CLSEP  D=MATDT  C=MRATING  E=SNPRAT
                                                                        'F=CPNRT  G=MSTATE
                    Sess0.Screen.WaitHostQuiet (g_HostSettleTime)   'Wait
                Next
            End Select
                
        Next
        
        'Row finish after last column value pasted
        Sess0.Screen.SendKeys ("")                          'Enter to solidify entry
        Sess0.Screen.WaitHostQuiet (g_HostSettleTime)       'Wait
        
        Sess0.Screen.SendKeys ("[010q[B")                   'Hit F10, Select Yes Option to save
        Sess0.Screen.WaitHostQuiet (g_HostSettleTime)       'Wait
        
        Sess0.Screen.SendKeys ("[003q[B")                   'Hit Escape, Select Yes to add another Security
        Sess0.Screen.WaitHostQuiet (g_HostSettleTime)       'Wait
        
    Next

End Sub

SendKeys is sometimes quite fragile. If you are not connecting through a dumb terminal, using another method for communicating with the host may be preferred.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Can't get MailMerge to work through VBA
Replies
0
Views
349
DetroitJimBurke
D
Kristylee0228
  • Question Question
VBA Code to loop through Tabs in Excel WorkBook and Paste data in different Book
2
Replies
15
Views
2K
kevin9999
K
M
  • Solved
VBA Help - Duplicate & Unwanted Data Removal
Replies
6
Views
1K
MoonDancer
M
A
  • Question Question
VBA Help using loop
Replies
3
Views
920
HaHoBe
HaHoBe
Rob_010101
  • Solved
VBA Code Change
2
Replies
14
Views
2K
Rob_010101
Rob_010101

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top