Computer

# My problem I use a variable that causes output values to ...

Customer Question
My problem: I use a...
My problem: I use a variable that causes output values to change and the changed values have to go in to different cells. For example the various variable numbers could be 25, 30, 40 and are in cells A1 to A3. When the variable is 30 is used I need the result in B2 without changing the result of the 25 variable that put a result in... Read full answer
My problem: I use a variable that causes output values to change and the changed values have to go in to different cells. For example the various variable numbers could be 25, 30, 40 and are in cells A1 to A3. When the variable is 30 is used I need the result in B2 without changing the result of the 25 variable that put a result in B1.

The following is an example where the variable 30 was selected and it created the correct value 355,648 and put it in the right cell but replaced the value in the cell below 25 with a 1 whereas before there was the correct value for the 25 variable. If I could get the result of the 25 variable to not be replaced every thing would be fine.

=IF(N86=''C:\Documents and Settings\EXCEL\TCE\VSF 08\[VariablesR.xls]Loan1''!\$E\$25,\$R73,1)

25     30     35     40     50     60
1     355,648     1     1     1     1
Thanks
Submitted: 11 years ago.Category: Computer
Show More
Show Less
2/6/2008
Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago
John D, BS Degree in Engineering.
Category: Computer
Satisfied Customers: 9,666
Experience: Bachelor of Science - Engineering Consultant
Verified

Hi,

The example you gave does not seem to be very consistent. While you are describing the cells in the range A1 through B6, your formula makes no reference to any cells in this range (N86?). Please try to make the question a bit more clear, and show in your example the results you are now getting versus the desired result that you would like to get.

.

Customer reply replied 11 years ago
=IF(N86=''C:\Documents and Settings\EXCEL\TCE\VSF 08\[VariablesR.xls]Loan1''!\$E\$25,\$R73,1)

25     30     35     40     50     60
1     355,648     1     1     1     1

I shouldn't have stated A1 - A3. I should have said N86 to S86 have the variables to be searched from an input in another sheet Loan1 \$E\$25. In the above case the \$E\$25 was 30 so it creates the 355,648 value and put it in O87. The problem is that I want to run the calculations for all the variable from 25 to 60 and get the corresponding result from O87 to S87.

Hopefull this clears things up.
Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago

That's better.

Now let's make sure we understand what that formula does. It compares a single cell (N86) with a 'range' of cells (E25:R73) in another workbook, which does not really make sense. The meaningful use of "=" single to compares one cell with another cell. If on the other hand you want to find a certain value in a range a cell then you need to use one of the LOOKUP family of functions. If this is what you are trying to do let me know and we will take it from there.

John D, BS Degree in Engineering.
Category: Computer
Satisfied Customers: 9,666
Experience: Bachelor of Science - Engineering Consultant
Verified
Customer reply replied 11 years ago
It uses the value from E73 (not a range) to find the appropriate value in N86 to S86 and takes the value created by the value in E73 and puts it in the appropriate cell of N87 to S87. In this case the variable from E73 was 40 so the result was put into Q87 which is fine. The problem is that I have to get the values created by all the variable E73 from 25 to 60 and have them in N87 to S87. The way it is now the each time a calculation is made it puts the result it in the appropriate cell but the other cells only show up as 1 even though other values have been created with the E73 variable. Hope you can understand this.
Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago

I see. You are right there is no range (misread \$E\$25,\$R73 as \$E\$25:\$R73). My mistake.

So in this case your formula:

=IF(N86=''C:\Documents and Settings\EXCEL\TCE\VSF 08\[VariablesR.xls]Loan1''!\$E\$25,\$R73,1)

which is in cell O87, checks to see if the value in N86 is equal to the value in cell E25. If it is, it will return the value in R73. Otherwise it will return 1.

Now in the example you gave since cell N86 (which is 25) is not equal to cell E25 (which is 40) it will return 1 in cell O87.

Not sure where E73 which you mentioned lately comes into this!

So that is all what the formula does. It makes no references to E73, nor is it supposed to put values in other cells.

You may be talking about something else, but one thing is clear and that is the formula above does exactly what I have just explained. If that is not what you expect it to do then we need to forget about this formula and setup something else.

Let me know.

John D, BS Degree in Engineering.
Category: Computer
Satisfied Customers: 9,666
Experience: Bachelor of Science - Engineering Consultant
Verified
Customer reply replied 11 years ago
E73 is a variable that I change when I want the resulting values from a different variable but at the moment I'm not able to keep the different values as you noticed, i.e. each time the variable is changed the previous calculated values are replaced by a 1. So what is required is a method of leaving the previous calculated values in place. Maybe one way to do this is to write a function that not only finds the E73 variable but also looks to see if there is a value other than 1 in the O87 to S87 row. Or maybe there is a way of automatically saving the created values.
Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago

HiCustomer

The problem is that you are making references to too many cells which makes the issue for someone who does not know the concept or purpose of this workbook impossible to understand. I really would like to help you and there is almost nothing that cannot be done with excel and its vba capabilities, but I really need to understand the issue first.

For example the cells that you have so far mentioned regarding this issue are::
N86 to S86
N87 to S87
E25
E73
R73

In cases like this it is best if you can simplify the problem by creating an independent sample sheet with the minimum number of cells that are related to the core issue. Then a model formula can be easily created which you can then project to your actual workbook.. Is this at all possible?

John D, BS Degree in Engineering.
Category: Computer
Satisfied Customers: 9,666
Experience: Bachelor of Science - Engineering Consultant
Verified
Customer reply replied 11 years ago
Can I send you an Excel attachment?
Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago

I do not mind looking at the file as long as it is a sample file that has no data other than the few cells in question.

John D, BS Degree in Engineering.
Category: Computer
Satisfied Customers: 9,666
Experience: Bachelor of Science - Engineering Consultant
Verified
Customer reply replied 11 years ago
I just sent a file. In column A there are 5 variables - 26 to 60. In C5, the 1000 is a value that is chnages (not here) when various variables are used and it puts it into C9 to H9
in the column corresponding to the selected variable. In B9 enter any of the variables and you will see this but as stated before it reaplces any previously entered values with 1.
Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago

John D, BS Degree in Engineering.
Category: Computer
Satisfied Customers: 9,666
Experience: Bachelor of Science - Engineering Consultant
Verified
Customer reply replied 11 years ago
What do you mean by a download link.Where do I het it?I have to go out now I'll
be back in 1 hr.
Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago

Here are the detailed instructions:

John D, BS Degree in Engineering.
Category: Computer
Satisfied Customers: 9,666
Experience: Bachelor of Science - Engineering Consultant
Verified
Customer reply replied 11 years ago
Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago

Ok I think I see what you mean. According to the formulas you put in C9 thru H9, the values of the cells that do not meet the condition will turn to 1. This is expected since a cell can take only one value at a time, so it will have to replace the current value with 1 when the previous condition is no more applicable.

Now if you want to keep track of previous values then one way to do it is to introduce these formulas in a series of rows (e.g. C9, C10, C11, etc) such that the new value will be written under the previous one.

Hope this helps.

Customer reply replied 11 years ago
I tried introducing the formulas to the cells as suggested and the file I sent shows the same results.

http://www.mediafire.com/?3d0gccog3ju
Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago

No it doesn't work like this. What you need to do is manually copy each result and paste it as value so it will not change and let the formulas in the lower cells to get the new values. I know it is not very practical, but formulas alone cannot do what you are expecting. For the cells results to be logged and not be overridden by the new values an embedded code must be created. It can be done but it will require programming work which I am afraid is not quite within the scope of this question.

Hope this helps.

Customer reply replied 11 years ago
I'm trying to automate the program which requires calaculating results with the 6 different variables. At the moment the only manual input required is selecting the variable and the progam produces 6 pages of output. I would just as soon not have to introduce another manual step. Do you have an idea how how much time would be required to automate this step. Or if you could explain the procedure that I could follow I would be pleased to pay for it.
Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago

Well the answer really depends on the extent of customization that is required. For example if you need someone to take your file and work on it to produce a complete solution, it certainly cannot be done here and you would need to hire a programmer for that. But if you are able to modify information from a sample file and apply it to your own workbook, then I think it would be possible to do it here for about the equivalence of 2 or 3 questions.

But in order to be clear about the sample file, what it will do is the following: As soon as a value is entered in cell B5, the program will automatically check the value of cell C9, and it finds a value there other that 1 it will take that value and plugs in a list it in a certain column (say in cell Z1). Then when another value is entered in B5 and again if the value of cell C9 changes, it will take the new value and plugs it under the first value, i.e in cell Z2. And so on building a list of values in column Z.

Hope I was able to make it clear.

Customer reply replied 11 years ago
I'm out of the office at the moment but will answer you when I get back there in the morning
Thanks
Customer reply replied 11 years ago
I presume you mean you first create a list that will have the results of the 6 variables. Is that correct? If the list won't include any 1's that is fine.

A couple of questions. When I wrote macros in Lotus many years ago I seem to remember a GOTO command where you could go to a different cell depending on a condition. If there is a similiar command in Excel it would work. Also, I see that Excel has a scenarios setup. Maybe that could be used.

Thanks
Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago

Yes, it will list all 6 variables. Here is what the result will look like:

FILE

Every time you enter a number in B9 that matches cells E7 thru H7, it will transfer the matching result (in this case 1000) to the appropriate column in the yellow list (columns L thru Q). That yellow list does not change it just keeps receiving new numbers at the end of each column.

Please confirm that this is indeed what you need so that I can start working on it.

As for your question regarding Goto, yes there is such a command in excel vba programming language, bu it cannot be used as part of a formula in the cells themselves.

As for Excel scenarios, no it cannot be used in a case like this one.

Let me know.

Customer reply replied 11 years ago
Reply to John D's Post: That looks fine. Why were there extra values, e.g in D.

Can you send me the list set up procedure. The last time I worked with list was in a web program.

This should do it. Maybe I should increase tour payment?
Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago
Customer that is the whole point of my detailed questions. If you read what I wrote, you will see that this is the way I understood the concept, i.e. every time you enter a legitimate value in B9, the value in cell C5 (which is 1000) will be "added" to the bottom of the list (yellow area). Since this new value is also 1000 it will be added to the list as such again. Wasn't this what you wanted, a complete log of the values produced? If not, please clarify.

Also I did mention in my previous post the amount of compensation (payment) expected for providing this solution.

Let me know.

Customer reply replied 11 years ago
I looked at your last two posts and didn't see a compensation amount.

As the values put into the list will be used in a Summary we only need one value in each column of the list. So, prior to using the program there will be a prompt to run the Summary program using the list. Which if Y it should clear out the list previous values and then create the new ones as the variables are selected. If N we will run the program using one varialble without out the need to put the value in the list.
Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago

That's fine. I will then use one cell in each column in the yellow list for the latest variable,.such that each new variable will REPLACE what was in that cell before. In other words the output will be in one row of cells only, i.e. cells L2 through Q2.

Here is what I said about compensation: /QUOTE/ "....then I think it would be possible to do it here for about the equivalence of 2 or 3 questions....." /UNQUOTE/ Meaning twice or three times the value of this question. But let's not worry about this now. I will leave it to you based on how satisfied you are with the program.

Unless I hear from you otherwise, I will start working on it this later afternoon, and hope to get it ready for by this evening.

Customer reply replied 11 years ago
That's fine.

Thanks
Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago

Okay, here goes:

FILE

Enter values in B9, and the amount in C5 will be transferred to L2 thru Q2.

Try it, and let me know so that I can give you instructions on how to customize it.

Customer reply replied 11 years ago
That's fine. Maybe I should explain in detail when we are doing - I should have done this in the begining.
Those variables 25 to 60 are write-off factors that create carious streams of revenue for various parties sharing the revenue. Each w/o variable creates a 6 page output from which various data is incorporated into a summary for each w/o scenario. There will be a prompt to run the summary output, i.e. get results from all 6 w/o variables. If the answer is know the user will select which w/o scenario.
I vae sent the file which now has a second value in C3 and it has been put in D15 as the 30 w/o variable was selected. I presume these values, along with many others required for the summary,
can be put into the one list in separate rows.
I have sent a new file showing the additional value.
http://www.mediafire.com/?cxtxmyltrxd
Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago

Hi again,

Haven't closely read or tried to understand your latest comments, but it looks like we have a slightly different requirement now.

Customer,as you know I was very specific and careful in reciting what the program will do before I started to work on it. In fact I spent quite a bit of time over a period of 2 days just making sure that the program that I will write is acceptable to you. Explained it details at least twice, sent you a sample file showing the expected result, asked for your confirmation, and got it. Now please read again my statements as to what the program will do and if you agree that what I sent you yesterday does exactly that then the fair thing to do is conclude that part before we go on any further.

May be your new requirements are not that different, but it will require some time for me to read and understand, and I am sure it will require some reprogramming to accomplish this task. But I would like to know your position on the above first.

Customer reply replied 11 years ago
I think what you have will work. The only other requirement is we need several results for each variable to go into the list or sepatrate lists it need be. If you can tell me what I owe you I'll will pay you and you can send me your code.
Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago

Glad to hear that it will work. I wasn't sure that this was the case from your last post.

I can certainly modify the code to include a list but I need to know where exactly to take the data from and where exactly to list. I initially did a multiple listing as you know but you told me to confine it to one figure for each column. It would help if you can show me this in a sample file by showing some 3 arbitrary input items and their corresponding result listed in the way you want them listed.

As you know this site works on an honor system. The customer decides how much to pay to compensate the expert. But you keep asking me how much and I keep giving you hints because I am not supposed to tell you how much to pay. It is entirely up to you. If you need to know how to pay, just click on the Accept button next to any of my answers. You are not charged, and the expert is not paid, until you click an accept button. Each time you click an accept the amount offered (in this case \$15) is charged to your account of which the expert gets half. You can click as many accepts as you like, and you can also add bonus if you wish. I hope this is clear now.

Customer reply replied 11 years ago
I'm sending a file with two values - C3 & C5, which will keep changing as the program is run but they are static in this file. There will be 1 different value for each of the 6 variables 25 - 60. So if they C3 values are in the 6 columns in 1 row and the C5 value is in the 6 columns in the 2 row that is fine.

Customer reply replied 11 years ago
Did you get the file?
Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago

Yes I got it but did not have a chance to look it at yet. I will do that now.

I do not see the output list as I mentioned before. Please make a separate table showing exactly how and where the program should list the values. Take my previous file and enter manually arbitrary figures in columns L through Q in as many rows as possible. Without this I cannot guess what your output list will look like.

Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago

Or is it that you want the output to be in the same cells where the result is shown and hence override the formula in that cell so that the value will not change any more. (I am referring to cells C14 to H14)?. If so then how do get back the formula for the next set?

Customer reply replied 11 years ago
I couldn't find your file but my file with a list is here:

http://www.mediafire.com/?dxjznbe19h0
Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago

Okay we seem to be going in circles.

I can see the columns where your formulas display the variable, but I do not see where these variables should be permamnently stored as fixed numbers. In my propsal, I created a "yellow list" to store the generated values. You need an outside area to store these values so that they will not be written over by the formulas.

Or is it that you want the output to be in the same cells where the result is shown and hence override the formula in that cell so that the value will not change any more. (I am referring to cells C14 to H14)?. If so then how do get back the formula for the next set?

If this does not make sense, then we must be talking about two completely different things.

Customer reply replied 11 years ago
Sorry about that your "yellow list" was the right way to go. it just need the values going into the next rows when the are calculated.
Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago

Great!

Can you now produce sample data (fake) in the yellow list showing the result of a number of trial values (say 3 trials) entered in cell C14. .In other words if you follow the steps below I will be able to understand what you want the yellow list to display and we can once and for all finalize this part and move to the next issue:

First, you need to show the "yellow list" in your last file (which is not currently there)

1- Enter 25 in cell C14 (in your last file). Then go to the yellow list and manually plug in what you should see there. Make the background colors of these cells that you plugged in the yellow list, in blue.

2- Next enter 40 in cell C14. Then go to the yellow list and manually plug in what you should see there. Make the background colors of these newly plugged cells red.

3- Next enter 60 in cell C14. Then go to the yellow list and manually plug in what you should see there. Make the background colors of thee newly plugged cells green.

This way I will be able to see the shape of the yellow list, the values that it needs to show, and most importantly using the color scheme I can now the sequence of events, and hence be able to replicate the above 3 step process.

Customer reply replied 11 years ago
I screwed up my Excel. I get back afr\ter a restore.
Customer reply replied 11 years ago
Can you send me you file that works?
Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago

Do you mean the file that I sent you last night which has the macro?. Yes I sure can, but that file cannot be used for the 3 steps I mentioned above, simply because I need you to manually plug in the desired values, and that file will automatically override what you plug in.

Customer reply replied 11 years ago
I changed my file to reflect values 25, 40 & 60 and put them in the list with colors. This does not reflect thr real world as when this program is used it will create the values sequentially.

http://www.mediafire.com/?5vxayn7gjirDid you get my last file?
Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago

We're getting close.

Problem!.... the results of 1st case (i.e. 25) should be associated the blue color, but in your file it is not!  Or am I missing something here?

Also, do you need the results to show in two rows only, or how many rows should that J to O list have?

Customer reply replied 11 years ago
The results for 25 were put in column 1 which I thought was to be blue. I changed the values in C3 & C5 to get different values for the variables 40 & 60.

A thought. Inasmuch as these values are going to be created sequentially may using VB to go to the next column as a new value is created would be the way to go.
Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago

I see now... you have used different values in C3 and C5. That's fine.

What about my last question "....Also, do you need the results to show in two rows only, or how many rows should that J to O list have?"

Customer reply replied 11 years ago
Probably 10...
Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago

Are you sure you want 10, that will require a much more complicated code (you never said that many rows are needed before!).

Anyway, before l start working on it, please confirm the following two statements.

1-The results will be displayed in the block J15:O24, that is 6 columns by 10 rows.

2- Lets take column 1 as an example: Every time you enter the number 25, the new pair of results will be plugged in column 1 under the previous results in that column, such that when the 10 rows have filled then it stops plugging in any results until you manually clear the 1 rows in column 1.

Customer reply replied 11 years ago
The following is a file that shows why we need the many results. As I suggested in an earlier em maybe using "goto" in VB is the way to go.

http://www.mediafire.com/?71cbbxgczxg

The next file is a draft of how the program is to operate. Open the Run tab. It will allow for creating the Summary or individual w/o scenarios.
http://www.mediafire.com/?fxbe4ljjfy9
Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago

All I need is answers to my questions in order to proceed. Sorry i do not have the time to keep looking at files studying them and trying to understand your application. It was agreed before that I will not get into your program or try to customize it. For that you need to hire a programmer. I will be happy to continue and provide you with a sample program that works. If you think my last proposal will not help you. let me know. Thanks.

Customer reply replied 11 years ago
If your program can handle 10 rows without a manual delete, fine. Otherwise I'll have to go another way.
Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago

Yes it can be done for 10 rows. Give me an hour or so and I will have it ready for you.

Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago

Need some clarification.

In my 2nd assumption I said the data will be taken in pairs (i.e.. from D15 and D16) and plugged as pairs under column 1. Just making sure that this is what you want and not taken from the six rows in D (i.e. D15 through D20) and plugged in as 6 rows at a time in column 1.

Customer reply replied 11 years ago
The way I envisioned it working was when the varialble, say 25, finished its calculations it would put the necessary 10 values in D16, 17, etc. and your code would put them in column 1 of the list and so for the other variables.

Bear in mind that the calculated values will be picked up from various sheets. The numbers we are using here were plugged in.
Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago

Hi,

See if this is ok, or if it needs any fine tuning.

FILE

Customer reply replied 11 years ago
That's not right. There are still 1 in rows 15 & 16.

I have had enough for the day as I'm sure you have. I'm going out for dinner. I get back to you tomorror.
Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago

I think you are a bit confused. I have not touched the formulas in rows 15 and 16. The formulas that you put there are meant to display 1's in the cells that match the IF condition.

My work is confined to columns J thru O. So I am not touching the 1's.

Could it be that the 'dummy' values that I filled in below the 1's for testing the program are causing the confusion. Here is the same version but without the 'dummy' values.

FILE

I agree, let's leave this till tomorrow.

.

Customer reply replied 11 years ago
Let's go back to square one. In my file when the 25 variable is selected it puts the values 999 & 233 into D15 & D16 which is fine. What has to happen is those values have to be transferred somewhere where they won't be changed when the next variable is used. I'm not sure this can be done with Excel commands but I know it can be done with a VB macro as it provides a "goto" command which would allow one to go to the appropriate cell when the next variable is selected and put in the new value, i.e not disturbing the first values.
I haven't written in VB for many years and I'm hoping I can find someone that can write the code. It would be very simple and take very little time.
If you agree with me let's go the VB route.
Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago

Hi again,

Ok, let's go back a little. I want to help you get it done and as I said before there is almost nothing that can't be done with excel and vba. Writing the code is not a problem, the problem is understanding what needs to be done.

First, let's look at the sample file that you sent me. Remember the blue, red and green highlights for the 3 trial input values. Please take a look at it and compare it with the results that you get from the last file that I sent you. Where and how does the new file differ from that? Once we can pin point the problem using these two situation it will be easier to understand and modify the code.

As for the Goto statement, this statement does not in anyway go to another cell. It merely causes the execution of the vb program to go to another line in the code. In fact I am using the goto statement in the vb code of this file I am also using the appropriate commands that make the output "go" to other cells to print the results (I think this is what you mean by the goto).

The code in this file is indeed written in VB. between excel and its vb capabilities any logical concept has to work. We just need to understand the logic behind the requirement.

Customer reply replied 11 years ago
I looked at the last file you sent which has two lists which has two values in each of 1st columns from the 25 variable.I can't run it to see what would happen with the 30 variable. If those first two values stay in the first column in one of the lists, then it works.
Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago

Ok I think we're getting close now.

I suggest that you try it with 10 variables and see how it goes. To do this, fill down the formulas in cells D15 through I34, then in each row of formulas make reference to a different value (other than C3 and C5). If you want I can set this up for you.

By the way, yesterday I got around the problem of 10 value limitation. You can now add as many rows as you want.

Customer reply replied 11 years ago
I inserted a total of 10 different values and adjusted the values in the rows and it inserts the values in the approprite rows. When I ran the 30 variable (there are only 5) the values went into column E but nothing went into Column 2. The adjusted file is:

http://www.mediafire.com/?ex1mvlsuc22
Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago

In that file it works exactly as expected.

Type 40 in cell B14, and hit enter. You will see all 10 results listed in column4. Try that and let me know. I need to know what you get when you do that, and what your opinion is regarding the 10 output figures in column4.

Customer reply replied 11 years ago
When the 40 variable is used the 10 values go into Column G with the heading 40. Nothing into the other list.
Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago

Yes it does. The list should come up in Column M. Here is a screenshot of what I got when I entered 40 in cell B14

Now if you are not getting this, then the most likely explanation is that your Excel setting are not allowing you to run macros. Click Tools > Macro > Security, and choose Low. Exit all excel workbooks, and restart

Customer reply replied 11 years ago
I set the security to low but still only have two values J Column 1. I think you have put in some code that I don't have. More importantly, while the 40 variable values are in the correct column M Header Column 4 ther are no other values in any other columns. They should be filled each time the variale is changed.
Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago

At least we know now that your excel is not running the macro.

Which version of Excel are you using.

Also can you do the following:

Open the file and make sure the output area in columns J thru O is all empty. Then enter 40 in cell B14 and hit enter. Save that file as file 1 and send it to me.

Next Open the file and again make sure the output area in columns J thru O is all empty Now without using cell B14, type in manually the items that should have appeared in column M when you have enetered 40 in the first file (do not enter anything in B14 in this file). Save the file as file 2 and send it to me.

This way I will be able to see exactly what the automated result is giving you, and what the result you expect to have.

Customer reply replied 11 years ago
The reason it wasn't working there was some text in B14. I movw it and used the 40 variable all the values went in properly.

http://www.mediafire.com/?exkmguiewwl
Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago

Don't want to keep you waiting, but II am working on something else now and I will look at the file and get back to you in a couple of hours. Thanks.

Customer reply replied 11 years ago
Not a problem....
Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago

Ok I looked at the file. Looks like it's working exactly the way I intended it to work. Do you have any remarks on the functionality or displayed output?

BTW, I noticed that some of your formulas need to be corrected. The only correct set of formulas is for variable 40 (i.e. column G).

.

Customer reply replied 11 years ago
Fine it works. I fixed the formulas.

http://www.mediafire.com/?8yt11jvrvbr

The above is working when the variables are inputed manually.

What has to happen is the variables have to be selected automatically in the file below where it will run and create the 10 values, for each variable, needed to go into you program.

I think I can make that happen by using a counter. If you have time at the file (tab Run) you'll see that if the entry in G11 is Y it will then select the appropriate variable from A31 to B36 and put it in G19. Maybe I can use your code for this?

If you get a chance to look at the file you might give an opinion. In the meantime I'll work on it. I would like to pay you and I don't know how many times I've clicked Accept but I'll do that again and maybe it will tell me the total and I'll adjust it accordingly. If I have to call on you to make it automatic in file below I will adjust it further.

Thanks

http://www.mediafire.com/?fxbe4ljjfy9I asked how much I paid. All I got was a balance in my account. I don't want the balance - it should go to you. I asked what made up my balance but I haven't heard.
Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago

Thank you very much. Yes I have been notified about your 4 accepts, so all 4 must have been processed. No need to click any more accepts.

I will look at your last file now and get back to you in a moment.

Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago

Okay, let me first explain how this program's code works. The code is not a standard macro module which is usually found in the excel vb modules. It is in fact embedded in the worksheet itself. The reason for this is because regular macros need the user's input to invoke the macro (by a clicking a button, or pressing control+keys commands, etc). In this case you wanted the macro to start automatically without issuing any commands. For this to happen the code has to be embedded in the worksheet properties. This way, excel will keep any eye on whatever is entered on that sheet, and it will automatically invoke a macro as soon as it detects a change in a specified cell (in this case B14). To access the code, right click on the Sheet's tab at the bottom, and choose View Code. Once you get to that code you should be able to customize it in the way you like. For example you can change the B14 cell to another one, or change the location where the output data is to be displayed. If you need specific help in this please feel to ask.

One more thing in this regard, for excel to detect a change in a cell, either that cell must be changed by the user (a new value entered) , or recalculation command (F9) is issued. If you prefer to have the code executed by the user on demand (instead of letting excel automatically detect it), let me know and I can convert it to a regular macro which can be run by clicking a button.

Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago

I had a look at your application file, but I did not see any formulas that use the value that is entered in the Run sheet. Looks like you have not setup the formulas yet.

If you can setup the formulas and highlight all the cells that have significance in this exercise, I will try to help you with applying the code.

Customer reply replied 11 years ago
I haven't set up anything as yet. There are two options. If one selects the 1st option by entering Y (G12)to:Run all Scenarios:(Y or N)
The program will then have to select all the variables in B31 to B36 entering them in succession into G20 which will create the values for your program. From which I will set up the summary sheet to gather them automatically.

If one selects the 2nd option and enters a variable into G19 (N would still be in G12)the program will run just the results from that particular variable. Hopefully this is clear.
Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago

1- Do you want the program to cycle thru the 6 variables automatically when Y is entered (without the user having to enter them one by one in cell G19)?

2 For each variable (25, 30 etc) please indicate the exact location of cells where the values are copied from and where they will be listed as fixed output.

Customer reply replied 11 years ago
1. Yes - no user entry

2. The results, different for each variable will show up in cells in different sheets. These cells will be identified for use in your program. Just like the cells in Column C in the sample program.
Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago

But you have to tell me where the data is and where to output to. In the previous example you gave me cells D15 to I24 as the source of data, and you gave me cells J15 and O24 as the output area.

I am afraid without this specific information I will not be able to do anything. In fact I will also need you to mimic the operation manually and show me the results (just like the blue, red, green example) so that I can replicate them in the code. If you as the proponent do not have this information how do you expect me to know it?

Customer reply replied 11 years ago
C3 to C15 is the source data, which is static at the moment. These cells will be replaced with the calculated data and the cell id's will be put into columns D to I and as the different data is collected it will be put into columns J to O. Once it is there you don't have to do anything as the summary sheet will reference the various cells in columns J to O.

I've mimiced the file below by puting values in Column D to represent values created by variable 30. In reality these new values would be entered in Column C

http://www.mediafire.com/?99uetez9ejd
Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago

It seems we are talking about two different files. From the time you sent me the "application file" (the file that has the 'Run' sheet and the Yes and No cell) I was talking about that file. All my comments and questions were about that file as I could not see any source data or formulas in that file, hence all my questions.

Now it seems you are answers are referring to the sample file which we have been working on.

So let's start over again, specifically from the post when you said "Fine it works. I fixed the formulas....". Now what modifications do you need to the code in that file?

Customer reply replied 11 years ago
There application file doesn't have any source data. In the "Run" sheet when a variable is entered into G20 the all the calculations are carried out in the file below. It starts in Write-off with F10 getting a value from Loan1 (in the "application" file)the value being determined by the variable selected in G20 Run.

If you enter one variables 25, etc. and then another you can look at one of the sheets, say Summary, and you will see the different results.

It the G20 in Run that has to be cycled through which will create the different values that will foin into C3 to C12 in your program.

http://www.mediafire.com/?0zblwb0liwp
Customer reply replied 11 years ago
I have found that I need 16 values in each column instead of the 10 I thought I needed.
Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago
Customer that file seems to be part of other linked files which apparently make up your application package. In order to be able to help you with your last post I would need to understand the application by going through the different formulas in the different sheets of this file as well as other linked files that form this package. As you know this will take quite a bit of time which as I told you before is beyond the scope of this exercise. If you recall I tired to make this perfectly clear from the beginning in order that we do not get into any possible misunderstanding later.

Please understand that while i would very much like to help you and I am prepared to go the extra mile to do so, but I prefer not to get into or try to customize your applications for the reasons explained above. We have agreed in the beginning that the task was to give you the tool (macro in a sample file) that should help you apply to your project. Now if you want any assistance in understanding or modifying the macro I will be happy to do so. But at the same time I do not want to keep you hanging there with sample file that you cannot use, so I am prepared to continue assisting you with that file provided we stay within the scope of the sample file.

Customer reply replied 11 years ago
If I can get the variables, 25,30,etc., to be entered consecutively into G20 in the Run sheet the resulting values with each iteration will go into C column of your program which will then put into the appropriate columns of your program when it reads the variable in B14. The B14 would reference the G20 in Run.

As mentioned earlier I'm more than willing to compensate you for your time.
Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago

You already have, and I thank you for that. And please do not click anymore accepts as long as we are on the same issue.

So you're saying that you already got it to work with the Run sheet of your program. Now if that is so then you may only need some fine tuning to do. If you can tell me what exactly needs to be done now I will try to help with that.

Customer reply replied 11 years ago
It runs fine now manually, i.e. each time one enters a value in G19 in Run e.g. 25 or 30 it creates 5 pages of results for presentation purposes. As well, these values are auotmatically entered into another summary file. So if I can get the appropriate variables to automatically go into G20 in Run the results will go into your program where they can be extracted into the summary file. When I need a presentation for a particular scenario I will enter manually the appropriate variable.
Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago

Okay, see if this will do it.

FILE

Here is an overview of what it does.

In the Run sheet, you will find a drop down list in cell G12 from which you can choose 'Yes' or 'No' .(you need to clik on that cell to see it)

If 'Yes' is selected it will be automatically be detected by an embedded code which will call a regular macro called 'Cycle'

'Cycle' then takes over and starts plugging the 6 variables in G19 with a break in between each to allow the user to know which variable is being used and to allow for global recalculation which will trigger the execution of our original code.

Check it out and I hope it is ok.

Customer reply replied 11 years ago
That's fine. Now all that has to be done is to have B14 in your other program to read G19 in Run so it can gather the data and put it into the apropriate columns. I mentioned earlier that we need 16 rows of data. Do you want me to do that. I'll just use the plugged numbers for the moment which will be replaced with the correct values from the appripriate cells.
Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago

Okay, here are two files linked together. As before the Cycle routine is triggered by cell G12 in the Run sheet, but in this version it will execute the commands in the other file. Make sure you rename the files in the macro to suit your own file names.

Hope this helps.

Customer reply replied 11 years ago
Sorry about being late getting back - I've been out
I would just as soon not fool around with the macros at this time. Is it alright to add more values in the C column of Formula Output which will show up in Column 1 2 etc.- I need 16. Also, I assume that the values C can be from references to cells in various sheets.
Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago

Sure. If you want more than 16 rows tell me now. I can add as many rows as you like. Let me know.

Customer reply replied 11 years ago
When I run Formula Main the the cells F10, G10 & H10 (Write-off file TCE3530R) are not getting using the G19 value. Is that because I haven't renamed the file names?

=VLOOKUP([VariablesR.xls]Run!\$G\$19,[VariablesR.xls]Loan1!\$E\$18:\$H\$23,2)
Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago

"..... Is it alright to add more values in the C column of Formula Output which ...". Yes, you cam add up to 20 values. The code you have already can handle 20 rows.

:...Also, I assume that the values C can be from references to cells in various sheets..." . Yes, the values in C can be anywhere. However, the formulas in columns D to I will have to make reference to the new cells (no change in codes required)

"...When I run Formula Main the cells F10, G10 & H10 (Write-off file TCE3530R) are not getting using the G19 value. Is that because I haven't renamed the file names? ...". That;s right, you need to change the file name in the macro named Cycle in the Main file. Replace the "Formula Output.xls" in the first line of code with your file name.

Customer reply replied 11 years ago
I just figured that out:

Sub Cycle()
Windows("Formula Output.xls").Activate
Range("\$b\$14").Value = 25

Before it goes here it has to go to: XXXXX XXXXX the folder VSF 08, file Debt TCE 3530R tab Write-off
This will create all the values we need.

Does it just need the file name? How does it then get to Formula Output? Excuse my ignorance.
Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago

In the current Cycle macro, it goes to the file "Formula Output.xls" workbook to change the value in cell B14. When it does that it invokes the embedded macro which reads the calculated values in columns D thru I. So in fact Neither code access cells C3:C14 directly. The data in these cells is read by your formulas in columns D thru I.

So what you need to do is either use the cells C3:C15 to reference the values that they should get from another workbook, or change the formulas in columns D thru I to make reference to the cells in the other workbook (i.e. replacing the reference to C in these formulas by the cells in the other workbook)

I hope it is clear.

Windows("Formula Output.xls").Activate
Range("\$b\$14").Value = 25

Customer reply replied 11 years ago
I guess I wasn't clear. What has to happen is that as each variable is called from D19 in Formula Main it has to to the Write-off in the file as follows:
=VLOOKUP('C:\Documents and Settings\Richard\Excel RWH\VSF\VSF 08\[VariablesR.xls]Run'!\$G\$19,'C:\Documents and Settings\Richard\Excel RWH\VSF\VSF 08\[VariablesR.xls]Loan1'!\$E\$18:\$H\$23,2)

The above goes into Col. F10 then 11 & 12. from there all calculations are done. At that point it should go to Formula Output and put all the calculated values in Column C (I'll will name the cells where the values are)and the Column J. It should then change the variable in B14 which will then create new values that will go into column C and put them into column H and so on.

I hope this is clear.
Customer reply replied 11 years ago
Maybe the following file makes things clearer.
It shows that a value in C1 is from cell \$M\$23 in Summary. The problem is that \$M\$23 is not being calculated because Formula Main goes directlt to Formula Output rather than to the file Debt TCE3530R where the calculations are done.

http://www.mediafire.com/?d2vrc9jzgcc
Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago
Customeryou lost me there. Most of the references in your last two posts either are not there or have no relevance to the issue

- D19 has no values and no relevance!,
- Which sheet are "Col. F10 then 11 & 12" in, besides non of the sheets in your file has meaningful values in these ranges..
- No idea about any of the references or file names in the vlookup formula
- C1 has no values and no relevance!,,
- Could not find any reference to \$M\$23 or Summary

I was struggling to try to make sense of these, but I am sorry I just was not able to follow through.

Customer reply replied 11 years ago
Sorry about that. The G19 I referred to is in my Run program and gets the variable manually entered 25, 30, etc.

In the first file below if you look at the Write-off tab at cells F,G&H row 10 you will see 3 values that were taken from the second file,tab Loan1 cells H18 to H23. The values entered are based on the variable called 25, 30, etc.

http://www.mediafire.com/?czdtux4tmys

http://www.mediafire.com/?fgldjexd1gs

If you look at M23 in the Summary tab of the File Debt TCE3530R you will see one on the results we need. Every time the values in F, G & H row 10 are changed in Write-off different results are obtained. So we have to get your program to go there.
Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago

Files do not load properly because of missing links - I hope it has no significance on the issue.

Ok I see the 3 cells in row 10, but I do not know what the question is? Please give enough details. I am going to take one more shot at this, and if I am not able to understand the issue or I cannot resolve it in this go, I will have to excuse myself from continuing further with this exercise which is essentially application related now, Please understand it is not a matter of further compensation at all. I already spent a significant amount of time on this project, and you have sufficiently compensated me within the scope and limitations of this site, and I thank you for that. But I just do not have the time right now especially since I am currently involved with other matters.

So go ahead and send me the details and I hope I will be able to help.

Customer reply replied 11 years ago
The 3 cells in row 10 get their values from the File VariablesR by reading E25 on the Loan1 sheet. If you change that input you will see the values in row 10 change to the correspond values from E19 to H22 in Loan1.

So all we have to do is get your program to enter the variables 25,30,etc. into E25 in Loan1. Then I think the rest will work as new values will be put in the C Column of Formula Output.
Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago

A few posts back you wanted to have the macro to automatically enter the 6 values in cell B14 of the Formula Output.xls file, and I rewrote the macros to do that. From what you are saying now, you want to apply this to another file (i.e. you want the macro and code to be transferred to the file VariablesR.xls so that it will change the values of cell E25 in this file)?

Customer reply replied 11 years ago
Yes.
The 25,30 variables have to go to E25 in Loan1. This will cause the 16 values to be created that we need for each variable. Formula Output will get the values from the various sheets in the Debt TCE3530R file, that you have, and put them into Column C of Formula Output. Each iteration will collect new values and Formula Output will put them in Columns J to O. Does this makes sense?I should have added in my last respose that after the first iteration the value should be changed in B14 of Formula Output and send it to E25 in Loan1.
Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago

Slightly!

I want to know which ones are the final files that the macros/code will address.Will Formula Output.xls file have any roll in this (i.e. am I going to completely disregard this file after moving the macro/code to Debt TCE3530R ?

What about the new VariableR file, am I going to recreate the codes there, and the Yes/No drop down list?

Customer reply replied 11 years ago
I think the way it should work is that from Formula Main it should take the first value 25, put into E25 of Loan1 which will create the first set of values and then gather them in Forumla Output which will then put the 2nd value 30 into E25 and so on.

If you can take a phone call mayebe we could dave some time.
Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago

That does not answer my main question about the final files. It seems that you are talking about at least four files to be involved in this operation?

Sorry it is against the site rules to communicate with customers by phone or email.

Richard, I have to attend to some other business matters now. Will get back to you later.

Customer reply replied 11 years ago
I would thought it would only take one more formula and adjustment to Formula Output. We communicate later.
Customer reply replied 11 years ago
I was thinking that maybe there is an easier way to solve the current problem.

As I understand your program you are going from to Formula Output if G12 in Formula One = Y where it cycles thru the variables - 25,30,etc. and pick up the values in col. C.

Would it not be possible for Formula Outout to go to E25 in Loan1 (which will cause the calculations) before is changes the value in B14 in Formula Output. It would then get the next variable (30) and out of in B14 and put it in E25 in Loan1 which would create a new set of values which woul go in Column C of Formula Output for collection.
Customer reply replied 11 years ago
I've thought of another easier way. Why not have E25 on Loan1 read B14 in Formula Output. I'll try that and let you know if it works.
Customer reply replied 11 years ago
It worked. One last chore, if you have time. In Formula Main - Module1 Code in msgbox you have the notice "Variable in use is now 25".

Would it be easy to have this be flashing for say 5 seconds without any action necessary to be done by the user i.e. clicking.

The following file shows the results of running the 6 variables that produced 6 results in C1. I'll be adding the other required cells to reference.

http://www.mediafire.com/?8yx2m5vjjno

I know you have put a lot of effort into this and would be pleased to compensate you further even though you have said that is no necessary. Maybe you will have some use for the procedure at a later date.

When I have other projects, should I contact you?
Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago

I'm sure we can do something about that message box. Will get back to you with the modification tonight or tomorrow morning.

Customer reply replied 11 years ago
Thank you very much.
Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago

Hi,

This is the Output file without the message boxes. For functionality reasons I created a time delay between the calculations and inserted a 'beep' .If you do not want the beeps just remove all 6 beep lines.

FILE

Let me know if you need other adjustments.

Customer reply replied 11 years ago
I only see the message boxes in Formula One. I downloaded your file and it still runs with with the " Variable in use now..."
Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago

You're right, you need both files for it to work..My bad. Here they are:

FILES

Customer reply replied 11 years ago
It works except for the beep. Don't worry about it unless it's a fast fix.

Thanks for everything.
Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago

Do you mean the beep does not work, or that you do not want it to beep?

If it is not working, the sound volume on your computer may be set to low or muted.

If you do not want the beep, just delete the beep lines in the Cycle code in the Main file.

Customer reply replied 11 years ago
My sound is working but don't worry about.
Customer reply replied 11 years ago
One last question re Can the Value = 25. Can this and the other values be variables. If so just send the code and I'll insert it.
Thanks XXXXX()
Windows("Formula Output.xls").Activate
Range("\$B\$14").Select

PauseApp 1
Beep
Range("\$b\$14").Value = 25
Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago

Just replace the 25 in the formula by another number or by the reference of another cell which contains the variable.

Say, the variable is in cell K6, the 3 line would read:
PauseApp 1
Beep
Range("\$b\$14").Value = Range("K6").Value

Let me know if you have any other question

Customer reply replied 11 years ago
A bug in the Program. All the files to run the program have been sent. When running all scenarios by selecting Yes from the drop down it calculates the 6 scenarios and enters the results in J15 to O27 in Output. It is supposed to get the values from C1 to 13. The last results are there and you will notice that they are slightly different from the numbers in in O15 to O27.

http://www.mediafire.com/?0zj2xgm21yg

http://www.mediafire.com/?ezudgckyfeg

http://www.mediafire.com/?cjgc30lmtym

http://www.mediafire.com/?8xer1021gbj
Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago
Customer I have no idea what there is in these files or what changes you have made to the code to adapt it to them. But I will be happy to check the code for bugs if you can point out the problem using only the two files on which I installed the code in, namely. 'Formula Main' and 'Formula Output'.

Customer reply replied 11 years ago
The 2nd & 3rd files I sent are your programs - Main & Output. The only thing Output does is read the results in column C1 to 15 which used to have pluged numbers in there. Now it is reading the calculations from the file Debt 3530R which I sent. If you select Yes in Main you will see the calcualtions being carried out in put into C1 to 15 and then into J15 to O27.

Hopefully this helps.
Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago

I understand. But you said there was a bug in the program. And if there is one, it would certainly my responsibility to fix it. But in order to determine if there is a bug in my program you need to point to it is in my original set of files.( i.e. BEFORE making changes and linking them to other files). So I would ask you again to take my original files and test them and let me know if there is any problem in the codes there.

Customer reply replied 11 years ago
There wasn't any problem with your earlier files.

Whta I have found out is that the values in J,K,L,M are correct but the last values in O are incorrect.

Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago

Ok, I'll try to check them out for you and I will let you if I can find anything,

Customer reply replied 11 years ago
I'm still looking but what is strange is that the values ib C are correct but the O numbers are wrong. Where they are getting them from I don't know. O is not reading C. Do you think O is running a calculation with old variables?
Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago

I haven't the slightest idea of you are talking about! I am still trying to figure out the error which is preventing the macros from running. Looks like the files you named in the macros do not match the files that you sent me. I will look at it again this evening.

Customer reply replied 11 years ago
I have sent the 4 files again. There are not any other files needed that I know of. Can you run the program?

I presume you can see the Values in C,(Output) which are the last values created via the cycle and they are the correct values but the are different from the values in O

http://www.mediafire.com/?7vtyy32brtm

http://www.mediafire.com/?7fwrmrxtydy

http://www.mediafire.com/?4yxr32dvz3x

http://www.mediafire.com/?4btozxvmswj
Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago

In the Cycle macro, 3rd line from the bottom, change the 59 to 60 and you're good to go.

The name of one of the files you sent me was misspelled.in the macro. It's ok now.

As for the column O issue see my last post (right above this one).

Customer reply replied 11 years ago
Thanks
Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago

You're welcome.

Good luck!

Customer reply replied 11 years ago
It isn't working. Below are the 4 files. The values in C and J15 to O27 are incorrect values.
If you run Yes in Main you will see the results and the value 359740 in O15. If you open file Debt TCE3530R and the Summary sheet and look at M23 you will see the value 362798 which is the correct value. Maybe you corrected this in the macro but I never received it. Hopefully that solves the problem.

http://www.mediafire.com/?edgmyjytz2t

http://www.mediafire.com/?dk2m0gdddtt

http://www.mediafire.com/?62kbbmmhmlm

http://www.mediafire.com/?4lueh5gxyng
Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago

Okay I will take a look at it when I have time hopefully today. Just so that you know, yesterday it took me over 20 minutes to load your files, fix the inconsistency in file names, look up the formulas, test the program. find the problem and fix it. And this as you know has been going quite frequently for the past 10 days (I have spent to what amounts to 12 hours on this project, in fact this thread will soon become one of the longest on this site).

As expected the cause of the error turned out to be a wrong value that you have mistyped in the macro.

I am sure this time also the problem will turn out to be related to your attempts to customize the program. If it is, I would kindly ask you to close this question thread, and post your future questions in a new question threads so that I as well as other experts would participate if they find they can be of help and if it is feasible for them to do so.

Once again I continue to support the macro solution that I gave you, and will always be ready to correct any issue with regard to my version of the program, But when the problem is not a result of an error in the program, it should be posted and dealt with as a separate issue.

Please let me know if you agree.

Thank you.

Customer reply replied 11 years ago
Firstly I want to pay you for your time - I don't know what I have paid to date but I guess I can get that from the site and when I do I will add whatever you think is appropriate.

Here is a comment from you yesterday:

The name of one of the files you sent me was misspelled.in the macro. It's ok now.

As for the column O issue see my last post (right above this one).

I don't recall changing any names in a macro. When you say above that it is ok now can you send that file and maybe that will solve the problem.
Customer reply replied 11 years ago
Don't do anything now. The program works at home.
When I took to the office this am I changed some of the calculation in my programs that somehow cause your program to fail. I'll try to fiqure out why and let you know.

Thanks
Customer reply replied 11 years ago
New project. The program works fine. Thanks.

I want to send the 4 file to a party in a way that they can’t see the code. I have read that the files that have VB code can be a problem when hiding. I presume I need to apply a password XXXXX I can unhide the code at my discretion.
Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago

Hi and welcome back again.

I'm sure glad to hear that.

Ok, usually a new project or a new question should be started in a new question thread. But I guess it's ok for now, I will answer it and if the answer satisfactory you can click the accept button next to this answer.

Yes you can hide and prevent access to vb code in excel by applying a password. Here is how:

- Open the excel workbook that has the code
- Press Alt + F11 to bring up the vb window
- In the left pane right click on Modules of that workbook, and choose VBAProject Properties
- Click on the Protection tab
- Place a check in th box "Lock project from viewing"
- Enter a password XXXXX confirm it in the fields at the bottom.
- Click OK., and save the workbook.

Next time you open the fie it will require a password XXXXX see (and modify) the code.

Hope this helps.

Customer reply replied 11 years ago
Does that hide all my formulas?
Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago

It will hide all the vb codes listed in the Modules. If you want to hide the formulas in the worksheet you need to use the sheet Protection menu command.

Customer reply replied 11 years ago
Thanks. I'll backup before I try it.
Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago

You're welcome, and don't forget to click the accept button. Thanks.

Customer reply replied 11 years ago
I presume there is a way of removing the VB protection.
Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago

Yes there is. Go to the VB window and click on the VBAProject (that has your file name). It will ask you for the password. After entering the correct password XXXXX click on the Modules then choose VBAProject Properties, then click on the Protection tab, remove a check in the box "Lock project from viewing" and delete the passwords.

Customer reply replied 11 years ago
Thanks.
Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago
You're most welcome. Hope to see you back soon (on a new question thread!)
Customer reply replied 11 years ago
I have another question for you. If I start a new questuin can I direct it to yo?
Tech Support Specialist: John D, BS Degree in Engineering. replied 11 years ago

Sure. Just include the words For John D in the subject.

Customer reply replied 11 years ago
I entered a question addressed to John D in the text. I didn't see a subject line.
Similar Q&As
Need to figure out formula us use Excel as an adding
Need to figure out formula us use Excel as an adding calculator. 1+2 in A column and sum is 3 in B next to 2, then 5 under 2 in A and sum 8 under 3 in B. Something like that.… read more
Micky
High School or GED
122 satisfied customers
if(K5 L5,40%, if(K5 L5 ”95.63”, 41%, if(K5 ”95.63” ”96.25”,
if(K5=L5,40%, if(K5=”95.63”, 41%, if(K5>”95.63””96.25 >=”96.88”, 43%, if(K5>”96.88”>=”97.50”, 44%, if(K5>”97.50”>= “98.13”, 45%, if(K5>”98.13” >=”98.75”, 46%))))))) Why is my n… read more
John D
BS Degree in Engineering.,
Bachelor Degree in Engineering
9,666 satisfied customers
Hi I am creating conditional formats using a formula. In the
Hi I am creating conditional formats using a formula. In the cell B1 I created a formula if A1=0 then change the colour of the font to red for cell B1. This applies to only B1. If you then copy B1 usi… read more
Wei C.
Bachelor's Degree
979 satisfied customers
Formula 03 Excel - return row 2 value, when row 1 lastmonth
Good evening all Does anyone know of formula that can return a single value from row 2 when row 1 = last month. Example below. (Row 1) A1 = Jul-10, A2 = Aug-10, A3 = Sep-10, A4 = Oct-10, A5 = Nov-10 (… read more
Jess
Bachelor's Degree
2,637 satisfied customers
I have a label printout that I want to get into Excel. I copy
I have a label printout that I want to get into Excel. I copy it and past it into a workbook and it lists the addresses vertically. I can go in and seperate the listings with an empty line but do not … read more
Jason Jones
AAS Information Technology
12,079 satisfied customers
Is it possible to change a column of negative numbers (copied
Is it possible to change a column of negative numbers (copied from Quicken via the clipboard) to a column of positive numbers, without having to change each number in the column?… read more
hah2110
Bachelor's Degree
17 satisfied customers
Using the nested IF and AND OR functions, I need a formula
Using the nested IF and AND OR functions, I need a formula to compute family leave time for a group of employees based on their Status, “FT”(full time) or "PT" (part time) and Years employed. Rules fo… read more
John D
BS Degree in Engineering.,
Bachelor Degree in Engineering
9,666 satisfied customers
I have a list of data, in an excel spreadshhet, that contains
I have a list of data, in an excel spreadshhet, that contains an employeees start date and their leaving date. Is it possible for excel to work out their length of service from the difference between … read more
Russell Vroom
Software Maintenance Engineer,
Bachelor's Degree
44 satisfied customers
I need excel to calculate time correctly as follows. example
I need excel to calculate time correctly as follows. example 08:00-07:40 equals 00:20 (ok in excel) divide result by 2 equals 00:10(ok in excel) then multiply the 2 results together i.e 00:20*00:10 wh… read more
jgrabsky
Project Manager,
Bachelor's Degree
6 satisfied customers
Is there a simple way in Excel to Vlookup or similar function
Is there a simple way in Excel to Vlookup or similar function the value amounts associated with a series of code numbers in one column which meet the specified codes in another column? e.g. Col A Row … read more
John D
BS Degree in Engineering.,
Bachelor Degree in Engineering
9,666 satisfied customers
I am trying to create a schedule and put in formulas that
I am trying to create a schedule and put in formula's that will calculate the time worked.… read more
John D
BS Degree in Engineering.,
Bachelor Degree in Engineering
9,666 satisfied customers
I have MS Excel 2002. I need the formula to check the text
I have MS Excel 2002. I need the formula to check the "text values" of B2:B700. I am looking for only two values (BEX or PEX). Any cell that contains any of those two values I want to change to "PEX".… read more
John D
BS Degree in Engineering.,
Bachelor Degree in Engineering
9,666 satisfied customers
Im trying to use Conditional Formatting to change the color
I'm trying to use Conditional Formatting to change the color of cells based on date criteria. Here's how my spreadsheet is setup and what I'm trying to do: I enter a date in cell T3. Other cells get d… read more
John D
BS Degree in Engineering.,
Bachelor Degree in Engineering
9,666 satisfied customers
If I put 100 in B1 and want to divide it by A2, I ...
If I put 100 in B1 and want to divide it by A2, I understand the formula is =B1/A2. If I want to divide B1 by A3,A4,A5,... all the way to A30, how do I copy the formula into the entire column so it wi… read more
John D
BS Degree in Engineering.,
Bachelor Degree in Engineering
9,666 satisfied customers
I have a invoice spreadsheet, i want to create a % column, ...
I have a invoice spreadsheet, i want to create a % column, that will automatically take a % of Number A column, and put into Number B column, no matter how offen i change the % number, Number B should… read more
Dave K
Charter Captain & I.S. Director,
Master's Degree
1,028 satisfied customers
John D In Formula Output the Variables from 35 to 60
John D: In Formula Output the Variables from 35 to 60 appear in If Range \$B\$15....can this be in Formula One say in \$B\$24… read more
John D
BS Degree in Engineering.,
Bachelor Degree in Engineering
9,666 satisfied customers
Is there a way to transpose cells from one sheet to ...
Is there a way to transpose cells from one sheet to another and keep the links intact so that the cells still refer back to the correct cell on the first tab. Example. I have columns that calculate pe… read more
John D
BS Degree in Engineering.,
Bachelor Degree in Engineering
9,666 satisfied customers
Building a Table to price out a computer using various ...
Building a Table to price out a computer using various components. What I was trying for is =SUM((A1*B1):(A100*B100)) but sum dosn't work this way. Column A is QTY while B is Price. I've resorted to +… read more
bytestream
computing consultant,
Master's Degree
354 satisfied customers
Disclaimer: Information in questions, answers, and other posts on this site ("Posts") comes from individual users, not JustAnswer; JustAnswer is not responsible for Posts. Posts are for general information, are not intended to substitute for informed professional advice (medical, legal, veterinary, financial, etc.), or to establish a professional-client relationship. The site and services are provided "as is" with no warranty or representations by JustAnswer regarding the qualifications of Experts. To see what credentials have been verified by a third-party service, please click on the "Verified" symbol in some Experts' profiles. JustAnswer is not intended or designed for EMERGENCY questions which should be directed immediately by telephone or in-person to qualified professionals.

Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.

## What Customers are Saying:

My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed.

One Happy CustomerNew York

I am very happy with my very fast response. Eric is very knowledgeable in the subject area. Thank you!

RPAustin, TX

Hi John, Thank you for your expertise and, more important, for your kindness because they make me, almost, look forward to my next computer problem. After the next problem comes, I'll be delighted to correspond again with you. I'm told that I excel at programing. But system administration has never been one of my talents. So it's great to have an expert to rely on when the computer decides to stump me. God bless, Bill

The Expert answered my Mac question and was patient. He answered in a thorough and timely manner, keeping the response on a level that could understand. Thank you!

Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help.

Mary C.Freshfield, Liverpool, UK

This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!!

AlexLos Angeles, CA

Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult.

GPHesperia, CA

< Previous | Next >

## Meet the Experts:

Andy

Computer Consultant

5,316 satisfied customers

11yr exp, Comp Engg, Internet expert, Web developer, SEO

Engineer John C.

Computer Science Engineer

3,231 satisfied customers

Computer Science Engineer with 10 years of experience in Computer Support, and Microsoft, A+ and Cisco certified

Ryan H.

Computer Support Specialist

1,741 satisfied customers

A+ Certified Technician - 10 Years experience working with all types of computer systems.

Jane Lefler

Sr Prog Analyst / Technician

3 satisfied customers

Computer Programmer / Technician/ Consultant 16+ years

Frederick S.

Computer Specialist

7,241 satisfied customers

Computer technician and founder of a home PC repair company.

Sudipto

Hardware Engineer

4,370 satisfied customers

Certificate Course In Computer Maintenance

lifesaver

Computer Software Engineer

4,288 satisfied customers

Engineering degree in Computer science,Microsoft Certified Professional.JA computer expert.

< Previous | Next >

Disclaimer: Information in questions, answers, and other posts on this site ("Posts") comes from individual users, not JustAnswer; JustAnswer is not responsible for Posts. Posts are for general information, are not intended to substitute for informed professional advice (medical, legal, veterinary, financial, etc.), or to establish a professional-client relationship. The site and services are provided "as is" with no warranty or representations by JustAnswer regarding the qualifications of Experts. To see what credentials have been verified by a third-party service, please click on the "Verified" symbol in some Experts' profiles. JustAnswer is not intended or designed for EMERGENCY questions which should be directed immediately by telephone or in-person to qualified professionals.

Show MoreShow Less

David L.

Computer Expert

20 years experience as a Computer Engineer. Microsoft & Cisco Certified.

5,752 Satisfied Customers

Pearl, Assistant