Solved

Demo Macro Script

Posted on 2013-11-26
5
212 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
  • 3
  • 2
5 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
Comment Utility
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
Comment Utility
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 500 total points
Comment Utility
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
Comment Utility
Thank you! Works great..... I can build with it.

Have a great Thanksgiving!
0
 

Author Comment

by:Bright01
Comment Utility
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

763 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now