?
Solved

Demo Macro Script

Posted on 2013-11-26
5
Medium Priority
?
256 Views
Last Modified: 2013-11-26
EE Pros,

I'm putting together a Demo tab so as to pre-load information concerning a particular ability to demonstrate using 3 columns of data.  Upon selecting the Demo to run, the data should automatically populate the other two tabs.  Change the demo, and the data should also change.

That's it!  Thank you in advance.

B.
Demo-Script.xlsm
0
Comment
Question by:Bright01
[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
5 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39678422
Copy this to F9 of customer box and from there copy it to each of the other boxes

=INDEX(Demos!$E$8:$G$13,MATCH(E9,Demos!$C$8:$C$13,0),MATCH(Demos!$D$3,Demos!$E$6:$G$6,0))
0
 

Author Comment

by:Bright01
ID: 39678499
Ssaqibh,

Thanks for the quick reply.  I'm looking for a macro ("Upon selecting the Demo to run...."), that when fired, will populate the Customer and Pricing Tabs.  This is because, without firing the Macro for the Demo, the user can enter their own information (so I can't use a formula in the cell).

Make sense?

B.
0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 2000 total points
ID: 39678639
This goes to the sheet3 (Demo) module

Private Sub Worksheet_Change(ByVal Target As Range)
If Sheets("Demos").Range("D3") <> "" Then
    formla = "=if(rc[-1]="""","""",INDEX(Demos!R8C5:R13C7,MATCH(RC[-1],Demos!R8C3:R13C3,0),MATCH(Demos!R3C4,Demos!R6C5:R6C7,0)))"
    With Sheets("Customer").Range("F9:F13")
        .FormulaR1C1 = formla
        .Value = .Value
    End With
    With Sheets("Price").Range("F9:F13")
        .FormulaR1C1 = formla
        .Value = .Value
    End With
End If
End Sub

Open in new window

0
 

Author Closing Comment

by:Bright01
ID: 39678728
Thank you! Works great..... I can build with it.

Have a great Thanksgiving!
0
 

Author Comment

by:Bright01
ID: 39678838
Ssaqibh,

Actually, While this works, I have some issues as I incorporate this solution.  I'll be authoring a fix question.

The major issues are I cannot work easily with the RC approach as reference points.  What's missing here is the macro doesn't use the Reference Cells which is why I put them in the Demos Tab.

Hope you will pick it up.

Thanks,

B.
0

Featured Post

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.

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.
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 …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

777 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