Solved

Demo Macro Script

Posted on 2013-11-26
5
233 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
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 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel VBA get Access table names with ADO 2 20
how to transpose my example data using VBA 9 33
Data Copy 4 22
Excel - remove duplicates 1 11
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;…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

772 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