Solved

Shorten VBA to one statement instead of three?

Posted on 2014-10-08
6
98 Views
Last Modified: 2014-10-08
Is there a way to combine Ln2 thru 4?  I do not believe I have to select the sheet tab each time as long as it is active.  I just want to put the value collected for Ln1 in a specific cell location on a specific sheet tab.

    MatlDescValue = session.findbyid("wnd[0]/usr/tabsTABSPR1/tabpSP02/ssubTABFRA1:SAPLMGMM:2004/subSUB1:SAPLMGD1:1002/txtMAKT-MAKTX").Text 'Matl Description
    Sheets("BasicData1").Select
    Range("B5").Select
    ActiveCell.FormulaR1C1 = MatlDescValue

Open in new window

0
Comment
Question by:RWayneH
  • 4
  • 2
6 Comments
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 40368382
Sheets("BasicData1").Range("B5").Select
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 40368385
     MatlDescValue = session.findbyid("wnd[0]/usr/tabsTABSPR1/tabpSP02/ssubTABFRA1:SAPLMGMM:2004/subSUB1:SAPLMGD1:1002/txtMAKT-MAKTX").Text 'Matl Description
    Sheets("BasicData1").Range("B5").Select
    ActiveCell.FormulaR1C1 = MatlDescValue

Open in new window

0
 
LVL 25

Accepted Solution

by:
ProfessorJimJam earned 500 total points
ID: 40368389
i did not read your question correctly.

now here you go.  to put them all in one line see code below


 Sheets("BasicData1").Range("B5").Value = session.findbyid("wnd[0]/usr/tabsTABSPR1/tabpSP02/ssubTABFRA1:SAPLMGMM:2004/subSUB1:SAPLMGD1:1002/txtMAKT-MAKTX").Text 'Matl Description

Open in new window

0
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 

Author Comment

by:RWayneH
ID: 40368395
Does it have to select B5?  Could I do something like:
Sheets("BasicData1").Range("B5") = MatlDescValue    ?  I just would like to eliminate lines that are not needed.
0
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 40368400
the last code i posted, is not selecting anything. it is using the ".Value" Property .

you cannot use Sheets("BasicData1").Range("B5") = MatlDescValue

instead you can use

Sheets("BasicData1").Range("B5").Value = MatlDescValue
0
 

Author Closing Comment

by:RWayneH
ID: 40368424
Yup worked great...   I need to focus on combining lines of code... speeds up the process and makes them easy to read.
THANKS!
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Don't Convert Time to Time-of-Day in Mail Merge 2 24
VBA copy column paste as value 5 21
Auto Populate Day Month  2 digit Date 4 16
Excel Cell Total 3 20
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
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…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

776 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