Solved

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

Posted on 2015-01-26
7
96 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 34

Assisted Solution

by:Norie
Norie earned 250 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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 

Author Comment

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

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 250 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

705 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