For loop to copy values from each row

Question

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

`i` is not contained inside your loop. There is nothing to change during each iteration. — Darrell H, Jun 08 '21 at 15:54
You're also finding the last row incorrectly - see [this](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) - plus you're mixing the number of rows and the row number. — BigBen, Jun 08 '21 at 15:55
@BigBen wich one im mixing tbh i dont get it cuz im new in vba doint this for like a week , so basically i need the rownumber not the number of rows? — Tibor, Jun 08 '21 at 20:13
@DarrellH I see thanks for your reply but i dont see where i can use it. Or can i use the `i` to refer to a specific cell? And if yes how can I do it? — Tibor, Jun 08 '21 at 20:15

score 2 · Accepted Answer · answered Jun 08 '21 at 20:25

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

I see thats where I messed up I dint know how i can refer to `i` in the loop, with cells. And the row count seems like easy one but it didn't come in my mind it works now well thank you for your help! — Tibor, Jun 09 '21 at 06:10

For loop to copy values from each row

1 Answers1