CONDITIONAL ARRAYS

[Greg]

Hi All,

I have a Word document in which I have cobbled together some arrays with help from this forum. I now wish to expand the usefulness of my document to include a conditional result and a hidden variable. The document is attached.

I'll explain. My Userform includes a drop-down list with the numbers 1 to 6 in it. In the document I have added a bookmark called "NumberDefendants" into which the selected number is inserted. Depending on the number selected there is a corresponding amount inserted into a bookmark called "LawyerCosts".

I don't want a bookmark for NumberDefendants as I don't want that number to appear on the page. I suppose I could make the number hidden but I expect that there is neater way to achieve this that does away with the bookmark altogether whilst retaining the result for LawyerCosts.

Secondly, I want the LawyerCosts to appear as $00.00 if the amount of the claim is less than $7,500.00. The amount of the claim is in a bookmark called "Amount". I have no idea about how this can be achieved.

I hope I have explained myself sufficiently well.

Can anyone help?

Regards,

Greg.

[Greg]

Woops, you'll want that file zipped.

[fumei]

Have not looked at the file yet, but this seems straight-forward logic to me.

Select Case Amount
Case < 7500
LawyerCost = 0.00
Else
LawyerCosts = Amount
End Select
Logic.

I am a little confused.

"I have added a bookmark called "NumberDefendants" "

seems conflicting with:

"I don't want a bookmark for NumberDefendants "

If you do not want the number (selected on the userform) of NumberDefendents to be on the page, then...do not put it on the page. It is just a number. If you are going to use it as a piece of logic value, then just use it. Again, this is simply logic.

"Depending on the number selected there is a corresponding amount inserted into a bookmark called "LawyerCosts".


Select Case NumberDefendents
Case 1
LawyersCosts = whatever
Case 2
LawyerCosts = whatever
etc.
End Select

This is logic, and can all be done in the userform.

[Greg]

Thanks Gerry,

I have modified and inserted your code but upon execution of the Userform I get a message; "Compile error: Can't assign to array" and the words "LawyerCosts" are highlighted. I have emboldened the relevant words in the code below.

Select Case Amount
Case Is < 7500
LawyerCosts = 0
Else
LawyerCosts = Amount
End Select


As I don't understand the message would you please point me in the right direction.

The modified document is attached.

Regards,

Greg.

[fumei]

I will have to look at the file, but if LawyerCosts is an array, then clearly

LawyerCosts = 0

will fail. If it is an array, then you have to give an item of the array.

LawyerCosts(x) = 0

However...why is it an array?

[Greg]

Hi again Gerry,

I think I am getting closer and whilst I haven't followed your suggestions to the letter I have followed them closely.

I have implemented "Select Case Amount" as you suggested and I have introduced an argument into the LawyerCosts Array to deal with the "Amount" (see the attached form).

However, when I run this code through the Userform I get Compile error: "Statements and labels invalid between Select Case and first Case". I searched the help files but didn't find an answer to my problem.

Are you able to help once more?

Greg.

[fumei]

The (I assume) variable Amount, as in:

Select Case Amount
Case Is < 7500
LawyerCosts = 0
Else

is neither declared nor given a value. Further, LawyerCosts = 0 fails, as VBA is trying to assign a value to an array.

You are using the same name for two DIFFERENT things.

"Amount" is a bookmark.
"LawyerCosts" is a bookmark.

Now LawyerCosts is also declared as an array. Technically, this is legal, as a variable within a module is, in fact, different from a bookmark name in the document.

You could:
Select Case ActiveDocument.Boomarks("Amount").Range.Text
Case Is < 7500
Call FillABookmark("LawyerCosts", 0)
Else
Call FillABookmark("LawyerCosts", _
ActiveDocument.Boomarks("Amount").Range.Text
End Select

Or, make a code variable of the amount....
' a bookmark range text IS a string
Dim Amount As String

....
Amount = ActiveDocument.Boomarks("Amount").Range.Text
....

Select Case Amount
Case Is < 7500
Call FillABookmark("LawyerCosts", 0)
Else
Call FillABookmark("LawyerCosts", Amount)
End Select


However, again, if you are declaring the array LawyerCosts, AND assigning values to that array:
LawyerCosts = Array("$330.00", "$374.00", _
"$418.00", "$462.00", _
"$506.00", "$550.00")

Why are you doing things like LawyerCosts = 0?

In fact, you are not. You are, I think, trying to assign a value to a bookmark.

And what is with:

Case 4
LawyerCosts = 462

Huh?????? What exactly are you trying to here?

[Greg]

Hi Gerry,

I had no joy with any of your code so I changed it to read as follows:

Dim Amount As String
Amount = ActiveDocument.Bookmarks("Amount").Range.Text
If Amount < 7500 Then
Call FillABookmark("LawyerCosts", 0)
Else
Call FillABookmark("LawyerCosts", Amount)
End If


Whilst the change avoided the compile errors etc., it still didn't solve my problem.

I wanted (and got) an array for LawyerCosts which varied depending on how many defendants were selected on the Userform.

I also wanted (and didn't get) the LawyerCosts to be zero if the "Amount" on the Userform was less than $7,500. Achieving that result is my only problem at this point.

Do you have any further suggestions?

Greg.

[fumei]

"I also wanted (and didn't get) the LawyerCosts to be zero if the "Amount" on the Userform was less than $7,500."

I have already stated that Amount is not declared. OK, you declared it...BUT you made its value the bookmark.

That is not the same as Amount on the userform.

Use the matching objects. If you want to use Amount on the userform, then use that. If you want to use the bookmark Amount in the document, then use that. You are trying to use both. As I stated, you are using the same name for two DIFFERENT things.

I want you to read your own words again.

"I also wanted (and didn't get) the LawyerCosts to be zero if the "Amount" on the Userform was less than $7,500. Achieving that result is my only problem at this point."

OK....WHICH LawyerCosts??????????

The array, or the bookmark? Decide.

I have asked twice...why do you have an array, on the userform, called LawyerCosts? You are setting the values on the array...then trying to change those values...for some reason.

If you want to make LawyersCosts, the bookmark reflect some logic from Amount (either the bookmark Amount, or something on the userform), then just do so.

[Greg]

Gerry,

I have copied, more or less, the code you described above. Mine is as follows:

Dim Amount As String
Amount = ActiveDocument.Bookmarks("Amount").Range.Text
Select Case Amount
If Amount < 7500 Then
Call FillABookmark("LawyerCosts", 0)
Else
LawyerCosts = Array("$330.00", "$374.00", "$418.00", "$462.00", "$506.00", "$550.00")
End If
End Selec

However, upon executing the Userform I get the following message:
"Statement and labels invalid between Select Case and first Case".

I am trying to learn this stuff on the run and apart from the help files (which are not an easy read) I don't have a textbook to refer to.

Although your Socratic method is working I will need a little more help.

Please advise.

Greg.

[Greg]

Sorry Gerry, I don't get it. You'll have to explain it further.

[Greg]

Sorry Gerry. I don't get it. You'll have to explain further.

[fumei]

OK, I will try.

You have this thing "Amount"..but what is it? Is it a variable on the userform? Is it a control on the userform? Is it a bookmark in the document?

Same thing with "LawyerCosts". You have two things.

An array named LawyerCosts.

A bookmark named LawyerCosts.

You have to use the proper "thing" for whatever you are trying to use it for.

Logic contained within scope of the userform is independent of values in the document.

In other words, you can manipulate, by logic, all you want while the userform is active.

So, if you have a variable Amount within scope of the userform, you can set it value any way you want. You can then further use logic to set other values.

This can be a logic determination of which item in an array is to be used; or to write a value into a bookmark in the document.

You MUST write out, explain, percisely what the logic is.

1. "I want the LawyerCosts to appear as $00.00 if the amount of the claim is less than $7,500.00. "

2. "Depending on the number selected there is a corresponding amount inserted into a bookmark called "LawyerCosts".

This conflicts, logically. They certainly do not need to, but YOU have to determine precisely what the logic is. I do not understand the logic. WHAT determines - in the end - the value of showing in the bookmarks LawyerCosts in the document?

The amount of the claim (#1 above), or the number of defendents (#2 above)? Or, in some way...both. If it is both, then you must explicitly determine and write the logic. Frankly, it should not be that hard. I trhink your problem is you are confusing using values and setting values.

As you state that you do NOT want the actual number of defendents 9selected on the userform) to show in the document. Fair enough, and a common enough issue.

That means the value of the combobox NumberDefendents is ONLY being used logically within scope of the userform. You use it to determine and set the value of LawyersCosts which IS in the document.

Do you understand? You use the value of NumberDefendents to [b]set - LOGICALLY - the value of LawyersCosts, a bookmark in the document.

Say 6 is selected as NumberDefendents. Is this value independent of what is going in Amount? Is "Amount" mean the bookmark Amount in the document, or some variable Amount used within scope of the userform. Where the heck is Amount coming from????

Could you have 6 defendents...but 0 cost? Hey, you could be doing pro bono! Heck, I don't know.

I still do not understand why you have an array for LawyerCosts.

What exactly is the purpose of "Array("$330.00", "$374.00", "$418.00", "$462.00", "$506.00"...etc.)?

If LawyerCosts(2) = "$418.00" - which it is, as you set it as an item in the array - and (somehow an "Amount" becomes (I think) $418.00 - or...is it??? - does NumberDefendents = 6 make ANY difference from NumberDefendents = 1?

I have no idea.

But I DO know that if you clearly state EXACTLY what the logic is supposed to be - and where it is supposed to be happening! - we can get the code working in a jiffy.

[Greg]

Hi Gerry,

By way of clarification I need to achieve the following:

1. I want to enter the number of defendants via the Userform but I don't want the number chosen to appear in type on the document.

2. I want the LawyerCosts to vary depending on the number of defendants selected in (1) above.

3. If the "Amount" of the claim (also entered via the Userform) is less than $7,500 the LawyerCosts must be Nil regardless of how many defendants there are.

That, I think. is all that needs to be said about my objectives.

I have managed to vary the LawyerCosts with the number of defendants selected but I haven't been able to vary those costs to nil if the "Amount" of the claim is less than $7,500.

Also, I haven't worked our how to enter the number of defendants without having a bookmark for them on the document itself.

I hope you can help.

Greg.