if then problem

[simonlee11]

I am trying to use if then and end if but get this error

Error description: Missing Keyword: end sub
Occurred on line: 185
End If

but I have

end if
end sub

at the end of the code block

please explain

 

[klaus]

You must post at least the code of the whole subroutine where you have the If Then / End If structure to enable us to help you.

Best regards.

 

[kickaha]

Taking an educated guess based on your other thread, you are trying to call a subroutine with

sub SubRoutineName

Look for any stray "Sub" statements, and see my reply to your other thread.

 

[simonlee11]

I am no programer but i am trying to write a program for my tablet pc for my work. Please read this code and tell me how can I stop the "if material" statement executing as it seems to carry on even when the condition is met "end if" just puts up the error i have shown. I realise this may be a long winded way to do it but as i say i am only learning. If anyone can help please do qtytxt1 will i hope eventualy contain sundrie material values eg cement or sand etc. Could i call a seperate sub when a value is met perhaps



If material = "Single Bricks" Then qtyunround = (sumtotm2 * divider)
qtytxt.text = Round2(qtyunround,1)
qtytxt.text = qtytxt.Text &" "& material
qtytxt1.text= "bricks"


If material = "Single Blocks" Then qtyunround = (sumtotm2 * divider)
qtytxt.text = Round2(qtyunround,1)
qtytxt.text = qtytxt.text &" "& material
qtytxt1.text= "blocks"
If material = "Single Paving Blocks" Then qtyunround = (sumtotm2 * divider)
qtytxt.text = Round2(qtyunround,1)
qtytxt.text = qtytxt.text &" "& material

If material = "Single Flags 900x600" Then qtyunround = (sumtotm2 / divider)
qtytxt.text = Round2(qtyunround,1)
qtytxt.text = qtytxt.text &" "& material
If material = "Single Flags 750x600" Then qtyunround = (sumtotm2 / divider)
qtytxt.text = Round2(qtyunround,1)
qtytxt.text = qtytxt.text &" "& material
If material = "Single Flags 600x600" Then qtyunround = (sumtotm2 / divider)
qtytxt.text = Round2(qtyunround,1)
qtytxt.text = qtytxt.text &" "& material
If material = "Single Flags 450x450" Then qtyunround = (sumtotm2 / divider)
qtytxt.text = Round2(qtyunround,1)
qtytxt.text = qtytxt.text &" "& material
If material = "Single Flags 400x400" Then qtyunround = (sumtotm2 / divider)
qtytxt.text = Round2(qtyunround,1)
qtytxt.text = qtytxt.text &" "& material
If material = "Single Flags 300x300" Then qtyunround = (sumtotm2 / divider)
qtytxt.text = Round2(qtyunround,1)
qtytxt.text = qtytxt.text &" "& material

If material = "Single Plasterbord 2400x1200" Then qtyunround = (sumtotm2 / divider)
qtytxt.text = Round2(qtyunround,1)
qtytxt.text = qtytxt.text &" "& material



If material = "Single Plasterbord 1800x900" Then qtyunround = (sumtotm2 / divider)
qtytxt.text = Round2(qtyunround,1)
qtytxt.text = qtytxt.text &" "& material


If material = "Single Plasterbord 2400x900" Then qtyunround = (sumtotm2 / divider)
qtytxt.text = Round2(qtyunround,1)
qtytxt.text = qtytxt.text &" "& material

If material = "Single Plasterbord 1220x900" Then qtyunround = (sumtotm2 / divider)
qtytxt.text = Round2(qtyunround,1)
qtytxt.text = qtytxt.text &" "& material





If material = "None m2 only" Then qtytxt.Text = ""
'Convert m3 to tonnes for appropriate materials
If material = "m3 Concrete" Then qtyunround = (sumtotm3)
qtytxt.text = Round2(qtyunround,1)
qtytxt.text = qtytxt.text &" "& material

If material = "Tonnes Sand" Then qtyunround = (sumtotm3 * divider)
qtytxt.text = Round2(qtyunround,1)
qtytxt.text = qtytxt.text &" "& material
If material = "Tonnes Soil" Then qtyunround = (sumtotm3 * divider)
qtytxt.text = Round2(qtyunround,1)
qtytxt.text = qtytxt.text &" "& material
If material = "Tonnes Tarmac" Then qtyunround = (sumtotm3 * divider)
qtytxt.text = Round2(qtyunround,1)
qtytxt.text = qtytxt.text &" "& material
If material = "Tonnes MotType1" Then qtyunround = (sumtotm3 * divider)
qtytxt.text = Round2(qtyunround,1)
qtytxt.text = qtytxt.text &" "& material

 

[kickaha]

Looking at your code, you are right it is long winded. I think you have a misconception. From your code

If material = "Single Bricks" Then qtyunround = (sumtotm2 * divider) 
qtytxt.text = Round2(qtyunround,1)
qtytxt.text = qtytxt.Text &" "& material
qtytxt1.text= "bricks"

I assume that you want ALL them statements to run ONLY If material = "Single Bricks"
If that is the case, you need an End If, and there should not be a stetement on the same line as the If.. Then

If material = "Single Bricks" Then 
qtyunround = (sumtotm2 * divider) 
qtytxt.text = Round2(qtyunround,1)
qtytxt.text = qtytxt.Text &" "& material
qtytxt1.text= "bricks"
End If

The reason is that if you include code ON THE SAME LINE AS the If ... Then statement, it is assumed that you only want that line actioned if the If statement is satisfied, so no "End If" is needed.
Whereas, if there is NO code on the same line it is assumed that you want ALL THE FOLLOWING LINES UP TO AN "End If" actioned if the If statement is satisfied.

Sorry for the "shouty" bits but I thought they were worth stressing.

 

[mistermentality]

You can do an If End If routine, for example

If material = "Single Bricks" Then
' do whatever you want to do if the material is single bricks
End If

Or alternatively you can use an If Then Else If routine which may be better in this case.

Dave

 

[simonlee11]

thanks guys,
I will try different suggestions and come back if i have problems.

Thanks for taking the time.

 

[simonlee11]

by the way if any one knows a simpler way to acheive the required result please post.


thanks

 

[kickaha]

Presuming I was right and your code was meant to be

If material = "Single Bricks" Then 
   qtyunround = (sumtotm2 * divider) 
   qtytxt.text = Round2(qtyunround,1)
   qtytxt.text = qtytxt.Text &" "& material
   qtytxt1.text= "bricks"
End If

If material = "Single Blocks" Then 
   qtyunround = (sumtotm2 * divider) 
   qtytxt.text = Round2(qtyunround,1)
   qtytxt.text = qtytxt.text &" "& material
   qtytxt1.text= "blocks"
End If

If material = "Single Paving Blocks" Then 
   qtyunround = (sumtotm2 * divider) 
   qtytxt.text = Round2(qtyunround,1)
   qtytxt.text = qtytxt.text &" "& material
End If

If material = "Single Flags 900x600" Then 
   qtyunround = (sumtotm2 / divider) 
   qtytxt.text = Round2(qtyunround,1)
   qtytxt.text = qtytxt.text &" "& material
End If

If material = "Single Flags 750x600" Then 
   qtyunround = (sumtotm2 / divider) 
   qtytxt.text = Round2(qtyunround,1)
   qtytxt.text = qtytxt.text &" "& material
End If

If material = "Single Flags 600x600" Then 
   qtyunround = (sumtotm2 / divider) 
   qtytxt.text = Round2(qtyunround,1)
   qtytxt.text = qtytxt.text &" "& material
End If

If material = "Single Flags 450x450" Then 
   qtyunround = (sumtotm2 / divider) 
   qtytxt.text = Round2(qtyunround,1)
   qtytxt.text = qtytxt.text &" "& material
End If

If material = "Single Flags 400x400" Then 
   qtyunround = (sumtotm2 / divider) 
   qtytxt.text = Round2(qtyunround,1)
   qtytxt.text = qtytxt.text &" "& material
End If

If material = "Single Flags 300x300" Then 
   qtyunround = (sumtotm2 / divider) 
   qtytxt.text = Round2(qtyunround,1)
   qtytxt.text = qtytxt.text &" "& material
End If

If material = "Single Plasterbord 2400x1200" Then 
   qtyunround = (sumtotm2 / divider) 
   qtytxt.text = Round2(qtyunround,1)
   qtytxt.text = qtytxt.text &" "& material
End If

If material = "Single Plasterbord 1800x900" Then 
   qtyunround = (sumtotm2 / divider) 
   qtytxt.text = Round2(qtyunround,1)
   qtytxt.text = qtytxt.text &" "& material
End If

If material = "Single Plasterbord 2400x900" Then 
   qtyunround = (sumtotm2 / divider) 
   qtytxt.text = Round2(qtyunround,1)
   qtytxt.text = qtytxt.text &" "& material
End If

If material = "Single Plasterbord 1220x900" Then 
   qtyunround = (sumtotm2 / divider) 
   qtytxt.text = Round2(qtyunround,1)
   qtytxt.text = qtytxt.text &" "& material
End If

If material = "None m2 only" Then 
   qtytxt.Text = ""
   'Convert m3 to tonnes for appropriate materials
End If

If material = "m3 Concrete" Then 
   qtyunround = (sumtotm3) 
   qtytxt.text = Round2(qtyunround,1)
   qtytxt.text = qtytxt.text &" "& material
End If

If material = "Tonnes Sand" Then 
   qtyunround = (sumtotm3 * divider) 
   qtytxt.text = Round2(qtyunround,1)
   qtytxt.text = qtytxt.text &" "& material
End If

If material = "Tonnes Soil" Then 
   qtyunround = (sumtotm3 * divider) 
   qtytxt.text = Round2(qtyunround,1)
   qtytxt.text = qtytxt.text &" "& material
End If

If material = "Tonnes Tarmac" Then 
   qtyunround = (sumtotm3 * divider) 
   qtytxt.text = Round2(qtyunround,1)
   qtytxt.text = qtytxt.text &" "& material
End If

If material = "Tonnes MotType1" Then 
   qtyunround = (sumtotm3 * divider) 
   qtytxt.text = Round2(qtyunround,1)
   qtytxt.text = qtytxt.text &" "& material
End If

It can be simplified to

' All these materials have the same code with only qtyunround varying, so put the code in a Sub and send it 
' the qtyunround calculation as a passed value
' Being clever we can then return that value to store in qtyunround.
' If qtyunround is only used for these calcs, we could simplify by not having qtyunround at all, and not have the sub return anything

If material = "Single Bricks" Then ' Using the multiline If..Then as we need to set qtytxt1.text
   qtyunround = CalcMaterial (sumtotm2 * divider) ' Note we send it the calculation for qtyunround to use, and get it back in the return
   qtytxt1.text= "bricks"
End If

If material = "Single Blocks" Then ' Using the multiline If..Then as we need to set qtytxt1.text
   qtyunround = CalcMaterial (sumtotm2 * divider) 
   qtytxt1.text= "blocks"
End If

If material = "Single Paving Blocks" Then qtyunround = CalcMaterial (sumtotm2 * divider)' Single line If... Then as we only need CalcMaterial
If material = "Single Flags 900x600" Then qtyunround = CalcMaterial (sumtotm2 / divider)
If material = "Single Flags 750x600" Then qtyunround = CalcMaterial (sumtotm2 / divider)
If material = "Single Flags 600x600" Then qtyunround = CalcMaterial (sumtotm2 / divider)
If material = "Single Flags 450x450" Then qtyunround = CalcMaterial (sumtotm2 / divider)
If material = "Single Flags 400x400" Then qtyunround = CalcMaterial (sumtotm2 / divider)
If material = "Single Flags 300x300" Then qtyunround = CalcMaterial (sumtotm2 / divider) 
If material = "Single Plasterbord 2400x1200" Then qtyunround = CalcMaterial (sumtotm2 / divider)
If material = "Single Plasterbord 1800x900" Then qtyunround = CalcMaterial (sumtotm2 / divider) 
If material = "Single Plasterbord 2400x900" Then qtyunround = CalcMaterial (sumtotm2 / divider) 
If material = "Single Plasterbord 1220x900" Then qtyunround = CalcMaterial (sumtotm2 / divider)

If material = "None m2 only" Then qtytxt.Text = ""
'Convert m3 to tonnes for appropriate materials

If material = "m3 Concrete" Then qtyunround = CalcMaterial (sumtotm3)
If material = "Tonnes Sand" Then qtyunround = CalcMaterial (sumtotm3 * divider)
If material = "Tonnes Soil" Then qtyunround = CalcMaterial (sumtotm3 * divider)
If material = "Tonnes Tarmac" Then qtyunround = CalcMaterial (sumtotm3 * divider)
If material = "Tonnes MotType1" Then qtyunround = CalcMaterial (sumtotm3 * divider)

' sub used 

Sub CalcMaterial (Value As Double)' value is qtyunround from your earlier code so we can set it here
   qtytxt.text = Round2(Value,1)
   qtytxt.text = qtytxt.text &" "& material
   Return (Value)' Send back value so qtyunround gets the correct value
End Sub

I have commented the code, if you do not understand what I have done, or I have not understood the code required then post here or PM me.

 

[kickaha]

A more elegant solution is to use Select Case:

Select material

' Select workds by taking the variable given and comparing it to the value(s) listed after the case statement
' If there is a match, the code up to the next case is excecuted, then it skips to the code after End Select
' As you have the same operations on lots of materials, we can group them together
' to minimise the lines of code

   Case "Single Bricks"
      qtyunround = CalcMaterial (sumtotm2 * divider) 
      qtytxt1.text= "bricks"

   Case "Single Blocks"
      qtyunround = CalcMaterial (sumtotm2 * divider) 
      qtytxt1.text= "blocks"

   Case "Single Paving Blocks"
      qtyunround = CalcMaterial (sumtotm2 * divider)
      
   Case "Single Flags 900x600", "Single Flags 750x600", "Single Flags 600x600", "Single Flags 450x450", "Single Flags 400x400", _
   "Single Flags 300x300", "Single Plasterbord 2400x1200", "Single Plasterbord 1800x900", "Single Plasterbord 2400x900", _
   "Single Plasterbord 1220x900"
      qtyunround = CalcMaterial (sumtotm2 / divider)
      
   Case "None m2 only"
      qtytxt.Text = ""

   Case "m3 Concrete"
      qtyunround = CalcMaterial (sumtotm3)

   Case "Tonnes Sand", "Tonnes Soil", "Tonnes Tarmac", "Tonnes MotType1"
       qtyunround = CalcMaterial (sumtotm3 * divider)
      
End Select

' sub used 

Sub CalcMaterial (Value As Double)
   qtytxt.text = Round2(Value,1)
   qtytxt.text = qtytxt.text &" "& material
   Return (Value)
End Sub

This allows you to group materials that are treated the same into one block.
Read through it, and have a look in the IDE explanation of Select (type in "Select (" without the quotes and study the popup)

 

[klaus]

Here is another way doing it:

[FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2] material = [/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000]"Single Bricks" [/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff]OR[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2] material = [/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000]"Single Blocks" [/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff]OR[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2] material = [/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000]"Single Paving Blocks" [/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff]Then[/COLOR][/SIZE][/FONT]
[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2]  qtytxt1.text= material.SubString2([/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080]7[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2],material.Length-[/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080]7[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2]) [/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#008000][FONT=Courier New][SIZE=2][COLOR=#008000][FONT=Courier New][SIZE=2][COLOR=#008000]' extracts the second part of material[/COLOR][/SIZE][/FONT]
[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2]  qtyunround = sumtotm2 * divider[/SIZE][/FONT]
[/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff]Else [/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2] material.SubString2([/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080]0[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2],[/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080]12[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2])=[/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000]"Single Flags" [/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff]OR[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2] material.SubString2([/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080]0[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2],[/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080]18[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2])=[/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000]"Single Plasterbord" [/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff]Then[/COLOR][/SIZE][/FONT]
[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2]  qtyunround = sumtotm2 / divider[/SIZE][/FONT]
[/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff]Else [/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2] material.SubString2([/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080]0[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2],[/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080]6[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2])=[/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000]"Tonnes" [/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff]Then[/COLOR][/SIZE][/FONT]
[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2]  qtyunround = (sumtotm3 * divider)[/SIZE][/FONT]
[/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff]Else [/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2] material = [/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000]"None m2 only" [/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff]Then[/COLOR][/SIZE][/FONT]
[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#008000][FONT=Courier New][SIZE=2][COLOR=#008000][FONT=Courier New][SIZE=2][COLOR=#008000]'Convert m3 to tonnes for appropriate materials[/COLOR][/SIZE][/FONT]
[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff]  Return[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#008000][FONT=Courier New][SIZE=2][COLOR=#008000][FONT=Courier New][SIZE=2][COLOR=#008000]' needed if you want to avoid the execution of the last line[/COLOR][/SIZE][/FONT]
[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff]Else [/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2] material = [/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000]"m3 Concrete"[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff]Then[/COLOR][/SIZE][/FONT]
[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2]  qtyunround = sumtotm3[/SIZE][/FONT]
[/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff]Else[/COLOR][/SIZE][/FONT]
[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff]  Return[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#008000][FONT=Courier New][SIZE=2][COLOR=#008000][FONT=Courier New][SIZE=2][COLOR=#008000]' quits the routine if no match[/COLOR][/SIZE][/FONT]
[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff]End [/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][/FONT]
[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#008000][FONT=Courier New][SIZE=2][COLOR=#008000][FONT=Courier New][SIZE=2][COLOR=#008000]' qtytxt.text = Round2(qtyunround,1)[/COLOR][/SIZE][/FONT]
[SIZE=2][FONT=Courier New][COLOR=#008000]' qtytxt.text = qtytxt.Text &" "& material[/COLOR][/FONT][/SIZE]
[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2]qtytxt.text = [/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff][FONT=Courier New][SIZE=2][COLOR=#0000ff]Round2[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2](qtyunround,[/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080][FONT=Courier New][SIZE=2][COLOR=#800080]1[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2]) &[/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000][FONT=Courier New][SIZE=2][COLOR=#800000]" "[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][FONT=Courier New][SIZE=2][FONT=Courier New][SIZE=2]& material [/SIZE][/FONT][/SIZE][/FONT][FONT=Courier New][SIZE=2][COLOR=#008000][FONT=Courier New][SIZE=2][COLOR=#008000][FONT=Courier New][SIZE=2][COLOR=#008000]'the two previous lines in one line[/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT][/COLOR][/SIZE][/FONT]

The code after the End If statement is comon to all matched items.

The advantage of this is code is whenever you add another
"Single Flags xxx",
"Single Plasterboard xxx" or
"Tonnes xxx"
item no need to chage the code.
With the Select/ Case code you would need to add the new ones in your code.

Best regards.

 

[kickaha]

Klaus,

There is a mistake in your code:
qtytxt1.text is being set to "Blocks" for "Single Paving Blocks" - that is not in the original code

That apart, you are right that the code you give does get the required result in a more compact form, but to me it loses some of its readability and flexibility, just personal preference though :sign0098:

 

[klaus]

qtytxt1.text is being set to "Blocks" for "Single Paving Blocks" - that is not in the original code

I had seen this but I found it more 'precise' having "Paving Blocks" instead of "Blocks".

... does get the required result in a more compact form, but to me it loses some of its readability and flexibility ...

I agree with you for the readability, on the other hand with new items in a known category no code change needed. For me this advantage overcomes the lack readability. But as you say, this is also personal preference.
I posted the code also to show that there are different ways to solve the same problem.

Best regards.

 

[stevel05]

Hi Klaus,

Wouldn't it be safer to use startswith rather than substr2 in lines 4 & 6 as if the length of material is less than 18 or 12 or 6 you'll get an out of bounds exception.

Steve

 

[klaus]

I fully agree with you .
'Old' habit, I am used to SubStr2 or SubString since VB.
StartsWith is new to me and not yet 'automatic'.

Best regards.

 

[kickaha]

I had seen this but I found it more 'precise' having "Paving Blocks" instead of "Blocks".
........................
Best regards.

You are misunderstanding me, qtytxt1.text is not set to anything for "Single Paving Blocks" in the original code. Quoting the relevant snip of the original:

If material = "Single Paving Blocks" Then qtyunround = (sumtotm2 * divider) 
qtytxt.text = Round2(qtyunround,1)
qtytxt.text = qtytxt.text &" "& material

 

[simonlee11]

thanks