CODEWITHSUNDEEP
excelvbafor-loop
Tibor
Tibor

Reputation: 25

For loop to copy values from each row

I want to copy each value (part number) from the rows but the loop only copies one but as many times as there are part numbers.

I want to count all the rows that contain a part number and put that value in the SAP field but the problem is that it only picks one, but not the first, and does it many times.
I want to do that with all the part numbers one by one and put them in the SAP field here:

session.FindById("wnd[0]/usr/ctxtRC27M-MATNR").Text = Cells(NumRows, 1)

'Counts the rows until it finds an empty one
NumRows = Range("A3", Range("A3").End(xlDown)).Rows.Count

Range("A3").Select

'for loop
For i = 3 To NumRows
    'script that SAP made
    session.FindById("wnd[0]").ResizeWorkingPane 181, 24, False
    session.FindById("wnd[0]/tbar[0]/okcd").Text = "/nca01"
    session.FindById("wnd[0]").SendVKey 0
    session.FindById("wnd[0]/usr/ctxtRC27M-MATNR").Text = Cells(NumRows, 1)
    session.FindById("wnd[0]/usr/ctxtRC27M-WERKS").Text = "0072"
    session.FindById("wnd[0]/usr/ctxtRC27M-WERKS").SetFocus
    session.FindById("wnd[0]/usr/ctxtRC27M-WERKS").CaretPosition = 4
    session.FindById("wnd[0]").SendVKey 8
    session.FindById("wnd[0]/usr/tblSAPLCPDITCTRL_1400/txtPLPOD-VGW04[22,0]").Text = Cells(NumRows, 2)
    session.FindById("wnd[0]/usr/tblSAPLCPDITCTRL_1400/ctxtPLPOD-VGE04[23,0]").Text = Cells(NumRows, 3)
    session.FindById("wnd[0]/usr/tblSAPLCPDITCTRL_1400/ctxtPLPOD-VGE04[23,0]").SetFocus
    session.FindById("wnd[0]/usr/tblSAPLCPDITCTRL_1400/ctxtPLPOD-VGE04[23,0]").CaretPosition = 1
    session.FindById("wnd[0]/tbar[0]/btn[11]").Press
Next

Upvotes: 1

Views: 125

Answers (1)

BigBen
BigBen

Reputation: 50162

Probably easiest here to:

1: Find the last row, not the row count:

With ActiveSheet
    Dim lastRow As Long
    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With

2: Loop from 3 to the lastRow and use i inside the loop:

For i = 3 To lastRow
    ...
    session.FindById("wnd[0]/usr/ctxtRC27M-MATNR").Text = Cells(i, 1).Value
    ...
    session.FindById("wnd[0]/usr/tblSAPLCPDITCTRL_1400/txtPLPOD-VGW04[22,0]").Text = Cells(i, 2).Value
    session.FindById("wnd[0]/usr/tblSAPLCPDITCTRL_1400/ctxtPLPOD-VGE04[23,0]").Text = Cells(i, 3).Value
    ...
Next

Upvotes: 2

Related Questions