Solved

Problem with the Integration of Macro for Demo Currency Program

Posted on 2014-01-18
12
270 Views
Last Modified: 2014-01-21
EE Pros,

I have a fairly complex macro that Gowflow has produced for me that works until I added one thing.  On the Demos WS, I provided the ability to select one of three conditions (Conservative, Standard or Aggressive) which then results in a specific value being selected.  When you do this, I get 1 error (Script out of range) that I cannot troubleshoot.  Can you help?

The code stops in the Demo WS at;

Sheets(ws).Range(Rng).Value = Intersect(cel.EntireRow, dc.EntireColumn).Value

Try it out by changing the Demo to one of the options.

Thank you in advance,

b.
Problem-with-the-DEMO-of-Currenc.xlsm
0
Comment
Question by:Bright01
  • 6
  • 6
12 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 39790876
yes you got this error but who wrote this code and what is it supposed to do ? can't fix a code if  I don't know what you want
gowflow
0
 

Author Comment

by:Bright01
ID: 39790889
Well the demo selection code I got from another WB I created some time ago.  

You will recall that the code you gave me provided Currency conversion based on a selected Currency on the "Customer-Input" Tab.  That worked perfectly.  On the Demo Tab however, I simply added a small piece of code that, in addition to the existing conversion, allows you to select, from a table in the Demos Tab, a "Conservative, Standard or Aggressive value and it simply places that value in the cell that you had referenced in the code you sent me.  From there I thought it would simply convert it and hand it back like was built into your original code.

Then I got the error.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39790898
Well I fixed it and this is what I found:

1) In the Column D form Demo you inadverttantly put
=Customer - Inputs!C9

NOTE the space before and after the hyphen where in fact your sheet name is
Customer-Inputs
So I simply removed the spaces for all 3 items and this one solved a part.

2) The second part is that you loop on all cells in the range D8 to D91 where in fact you should loop on all values that are in that range as when you hit a blank cell you would have got an other error on these 2 lines
ws = Mid(cel, 2, InStr(cel, "!") - 2)
Rng = Right(cel, Len(cel) - InStr(cel, "!"))


and this was fixed by replacing this line
For Each cel In Range("d8:d91")

by this
For Each cel In Range("d8:d91").SpecialCells(xlCellTypeConstants)

Try this version
gowflow
Problem-with-the-DEMO-of-Currenc.xlsm
0
 

Author Comment

by:Bright01
ID: 39790936
Got it.  

But now the update in Col. I on the Currency Table doesn't happen.  Try it out and look at the new Conversion value.

b
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39790956
FOR SURE IT IS NOT GOING TO WORK !!!!

grrrrrrrr


If you are disorganized that is what happens !!!

2 questions back I gave you a solution, but seemed you used the wrong version of UpdateCurrency in this and the past question which is the reason for this problem.

Here it is I kept the version that you attached and renamed it to UpdateCurrency_OLD and put the one I had given you 2 questions back.

Check this file and make sure you always use the latest version of a macro
CHEERS !!!
:)

gowflow
Problem-with-the-DEMO-of-Currenc.xlsm
0
 

Author Comment

by:Bright01
ID: 39791875
Gowflow,

Sorry for any confusion here. This works except:

Change the selection in D3 on Demos and you will see what I'm talking about.  Everything updates except it doesn't put the new value in Currency J10.  (Whenever a new value is added to H:H (Currency), it should pick it up as a "convert" value in "I".  

Does that make sense?

Thank you,

b.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 29

Expert Comment

by:gowflow
ID: 39794107
Really I don't know what your talking about.

First in file Currency you mention cell J10 it is an empty cell that have nothing there and you have reduced the column J to very small so I don't understand.

Then you say whenever a new value is added to H:H it should pick up a convert in value I and it does.

I am afraid that maybe you are using a different version ???
Pls try this one I changed the name to make sure no confusion.
gowflow
Problem-DEMO-of-Currenc-V02.xlsm
0
 

Author Comment

by:Bright01
ID: 39794231
Gowflow,

Thank you for the follow up!  I just took your latest copy.  Here's what I need you to try to do. Go to the Demos tab, add 3 values in Row 11 (as I am adding additional numbers).  You will see by adding the values, it doesn't trigger an update to the conversion value.

That's the problem.  Hope it's a simple change to update the macro to include the ability to update (it can be either automatic or manual).

B.
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 39794943
Well for sure !!!

In Demos
Col I and J Just drag now the formulas from the last existing formula down to as much as you feel you will need to input new rows see  I dragged till row 20

In Currency
Do the same for Col H I dragged also till Row 20

Then do you inputs as you like and check the results !!

I did not modify any macro in this version only dragged formulas.
gowlfow
Problem-DEMO-of-Currenc-V03.xlsm
0
 

Author Closing Comment

by:Bright01
ID: 39796617
A++++!!!  Now it is working with your coaching.....  I went line item by item until I found the flaw in my code.  It was in the module where you rewrote the code and put "OLD" in the Sub. I was using the OLD sub!  So, I put your code in and it works very well now.  Still integrating on this one so I may have another question soon.....but great work!

Are you located in the U.S. or abroad?

B.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 39796863
Btw sorry I kept the old which create the confusion DELETE THE OLD with no fear !!!

your welcome anytime. Pls post a link to the new question you may need help with.
Rgds/gowflow
0
 

Author Comment

by:Bright01
ID: 39797020
I am very sorry.... I did not give you the right design on the "reference" cells part of this Macro.

Here is the new post:  

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28343980.html
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
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…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

863 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

19 Experts available now in Live!

Get 1:1 Help Now