• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 106
  • Last Modified:

Assigning variable in whatever the current row is in For each c loop

In the below code I am determining a range without selecting it.  In the For Each c it uses values to goto SAP and input them.

Two questions on this.  One, how would I assign SO (column A of whatever c is) and Line (which is column B of c)  ??

Second question is at the end of the procedure, I am grabbing a value from SAP and want to write that value back to column AD of whatever row c is?

'Not sure this will ever be empty now...  AC2 needs a value..  won't hurt to leave it there.
    If IsEmpty(Cells(2, 29)) Then  'row then column (2, 29)) = AC2
        Set rng = Range(Cells(2, 29), Cells(2, 29)) 'does not select range but remembers it
        'Add a failure mode to task list, PurReqNum not generated.
        Exit Sub  'Should never make it this far without being empty
        Set rng = Range(Cells(2, 29), Columns(29).End(xlDown)) 'range is now = rng
        For Each c In rng ' loops thru rng
            PurReqNumber = c  ' sets c = to PurReqNumber
            ' c is a row, need to assign whatever is in column A = SO  to use later in the procedure
            ' c is a row, need to assign whatever is in column B = Line  to use later in the procedure
             If c = "NoPurReqPassThru" Then  'Means this is a PassThru LnItem and wont get a PurReqNum
                session.findById("wnd[0]/tbar[0]/okcd").Text = "me23n"
                session.findById("wnd[0]").sendVKey 0
                session.findById("wnd[1]").sendVKey 4
                session.findById("wnd[2]/usr/tabsG_SELONETABSTRIP/tabpTAB017/ssubSUBSCR_PRESEL:SAPLSDH4:0220/sub:SAPLSDH4:0220/txtG_SELFLD_TAB-LOW[0,24]").Text = SO
                session.findById("wnd[2]/usr/tabsG_SELONETABSTRIP/tabpTAB017/ssubSUBSCR_PRESEL:SAPLSDH4:0220/sub:SAPLSDH4:0220/txtG_SELFLD_TAB-LOW[1,24]").Text = Line
                session.findById("wnd[2]/usr/tabsG_SELONETABSTRIP/tabpTAB017/ssubSUBSCR_PRESEL:SAPLSDH4:0220/sub:SAPLSDH4:0220/txtG_SELFLD_TAB-LOW[1,24]").caretPosition = 3
                session.findById("wnd[2]/usr/lbl[31,3]").caretPosition = 2
                session.findById("wnd[2]").sendVKey 2
                session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0015/subSUB0:SAPLMEGUI:0030/subSUB1:SAPLMEGUI:1105/txtMEPO_TOPLINE-EBELN").caretPosition = 0

Open in new window

  • 3
  • 2
  • 2
2 Solutions
NorieVBA ExpertCommented:
SO = Range("A" & c.Row).Value
Line = Range("B" & c.Row).Value

Open in new window

Not sure where in the code you are grabbing a value from SAP but to refer to the cell in column AD on the same row as c use Range("AD" & c.Row).
Rob HensonFinance AnalystCommented:
Withing your "For Each c" routine you can use:

R = c.Row
SO = Cells(R, 1)
Line = Cells(R, 2)

Then your cell in column AD can be populated with:

Cells(R, 30) = "Value from SAP"

Rob H
RWayneHAuthor Commented:
Thanks...  I did not add the part that grabs the value from SAP yet.  But lets say I name it. PTPurReqNum.  How would this change: Range("AD" & c.Row). ??    maybe: PTPurReqNum = Range("AD" & c.Row)   guessing.
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

RWayneHAuthor Commented:
or for Rob's version,  Cells(R, 30) = PTPurReqNum  ??
NorieVBA ExpertCommented:
If the value from SAP was in a variable called 'PTPurReqNum' then this would be the code to put it in column AD on the same row as c.
 Range("AD" & c.Row).Value = PTPurReqNum

Open in new window

Rob HensonFinance AnalystCommented:
Whichever version you use, it would have to be the way round that both imnorie and I have put it:

So either:
Cells(R, 30) = PTPurReqNum

Range("AD" & c.Row) = PTPurReqNum

Both say "Set cell AD# = to variable PTPurReqNum"

Putting it as:
PTPurReqNum = Range("AD" & c.Row)

Says "Set variable = to value of Cell"

If cell is blank, it would overwrite value of variable with blank.

Rob H
RWayneHAuthor Commented:
Thanks you both for your help with this.

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now