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
    Else
        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[0]/tbar[1]/btn[17]").Press
                session.findById("wnd[1]").sendVKey 4
                session.findById("wnd[2]/usr/tabsG_SELONETABSTRIP/tabpTAB017").Select
                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]").SetFocus
                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]/tbar[0]/btn[0]").Press
                session.findById("wnd[2]/usr/lbl[31,3]").SetFocus
                session.findById("wnd[2]/usr/lbl[31,3]").caretPosition = 2
                session.findById("wnd[2]").sendVKey 2
                session.findById("wnd[1]/tbar[0]/btn[24]").Press
                session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0015/subSUB0:SAPLMEGUI:0030/subSUB1:SAPLMEGUI:1105/txtMEPO_TOPLINE-EBELN").SetFocus
                session.findById("wnd[0]/usr/subSUB0:SAPLMEGUI:0015/subSUB0:SAPLMEGUI:0030/subSUB1:SAPLMEGUI:1105/txtMEPO_TOPLINE-EBELN").caretPosition = 0
                session.findById("wnd[0]/tbar[0]/btn[15]").Press

Open in new window

RWayneHAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
Advertise Here
 
Rob HensonConnect With a Mentor Finance 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

or
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.

Thanks
Rob H
0
 
NorieConnect With a Mentor VBA ExpertCommented:
Perhaps.
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).
0
 
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"

Thanks
Rob H
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Go Premium
 
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.
0
 
RWayneHAuthor Commented:
or for Rob's version,  Cells(R, 30) = PTPurReqNum  ??
0
 
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

0
 
RWayneHAuthor Commented:
Thanks you both for your help with this.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.