Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2015-01-26
7
Medium Priority
?
103 Views
Last Modified: 2015-02-07
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

0
Comment
Question by:RWayneH
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 35

Assisted Solution

by:Norie
Norie earned 1000 total points
ID: 40570776
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
 
LVL 33

Expert Comment

by:Rob Henson
ID: 40570780
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
 

Author Comment

by:RWayneH
ID: 40570807
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:RWayneH
ID: 40570850
or for Rob's version,  Cells(R, 30) = PTPurReqNum  ??
0
 
LVL 35

Expert Comment

by:Norie
ID: 40570862
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
 
LVL 33

Accepted Solution

by:
Rob Henson earned 1000 total points
ID: 40570877
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
 

Author Closing Comment

by:RWayneH
ID: 40595998
Thanks you both for your help with this.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question