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

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

Question by:RWayneH
  • 3
  • 2
  • 2
LVL 33

Assisted Solution

Norie earned 250 total points
ID: 40570776
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).
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"

Rob H

Author Comment

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.
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center


Author Comment

ID: 40570850
or for Rob's version,  Cells(R, 30) = PTPurReqNum  ??
LVL 33

Expert Comment

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

LVL 33

Accepted Solution

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

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

Author Closing Comment

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

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

789 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