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.
Solved
Assigning variable in whatever the current row is in For each c loop
Posted on 2015-01-26
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?
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
[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
Active today
Perhaps.
SO = Range("A" & c.Row).Value
Line = Range("B" & c.Row).Value
Active today
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
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
Active today
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.
Active today
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
Active today
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
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
Featured Post
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…
Suggested Courses
Course of the Month11 days, 5 hours left to enroll
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.