Changing Values
 

Hi All

I have adapted the code below off info gleaned on this forum and it works OK
, however a question was asked of me what would happen if I was on Holiday or
absent through sickness etc - how would we change the values listed below.
Private Sub Add100_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Customers Prices")
Worksheets("Customers Prices").Select
Range("A3").Select
Tb100.Value = Application.CountIf(Range("J3:J600"), "30")
Tb101.Value = Tb100.Value * 30
Tb102.Value = Application.CountIf(Range("J3:J600"), "15")
Tb103.Value = Tb102.Value * 15
Tb106.Value = Application.CountIf(Range("J3:J600"), "13")
Tb107.Value = Tb106.Value * 13
Tb108.Value = Application.CountIf(Range("J3:J600"), "10")
Tb109.Value = Tb108.Value * 10
Tb110.Value = Application.CountIf(Range("J3:J600"), "5")
Tb111.Value = Tb110.Value * 5
Tb112.Value = Application.CountIf(Range("J3:J452"), "CAR*")
Tb114.Value = Application.CountIf(Range("J3:J452"), "FREE*")
Tb104.Value = (Val(Trim(Tb100.Value))) + (Val(Trim(Tb102.Value))) +
(Val(Trim(Tb106.Value))) + (Val(Trim(Tb108.Value))) +
(Val(Trim(Tb110.Value))) + (Val(Trim(Tb112.Value))) + (Val(Trim(Tb114.Value)))
Tb105.Value = (Val(Trim(Tb101.Value))) + (Val(Trim(Tb103.Value))) +
(Val(Trim(Tb107.Value))) + (Val(Trim(Tb109.Value))) + (Val(Trim(Tb111.Value)))
Tb105.Value = FormatCurrency(Tb105.Value, 2)
Tb103.Value = FormatCurrency(Tb103.Value, 2)
Tb101.Value = FormatCurrency(Tb101.Value, 2)
Tb107.Value = FormatCurrency(Tb107.Value, 2)
Tb109.Value = FormatCurrency(Tb109.Value, 2)
Tb111.Value = FormatCurrency(Tb111.Value, 2)
End Sub

Any help or suggestions most helpful??
--
Many thanks

hazel

Changing Values
 

By change the values I assume you mean the 30 and the 15 and the... There are
2 ways to go about this and it depends on how likely it is the values will
need to be changed.

If the values are constants that you do not reasonable forsee needing to be
changed then you can make them constants in your program by declaring them as
const(often done in a seperate module or at the top of existing modules).
This puts all of the constants in one easy to get at spot in the unlikely
event that they do need to be changed. This is not something that your end
users can be expected to do but which any programmer with a bit of knowledge
will be able to easily fix. The value of doing this is that you change the
constant in the one spot then evey line of code that requires taht constant
will be instantly updated.

If the values are relatively constant but you do forsee that they will
change in the future then you may want to set up a Variables sheet in you
workbook where you set out all of these values. By giving your end users a
facility to expose this page you allow relatively unsophisticated end user to
change the values. Then in your program you can just reference the values on
that sheet.
--
HTH...

Jim Thomlinson


"Hazel" wrote:

Hi All

I have adapted the code below off info gleaned on this forum and it works OK
, however a question was asked of me what would happen if I was on Holiday or
absent through sickness etc - how would we change the values listed below.
Private Sub Add100_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Customers Prices")
Worksheets("Customers Prices").Select
Range("A3").Select
Tb100.Value = Application.CountIf(Range("J3:J600"), "30")
Tb101.Value = Tb100.Value * 30
Tb102.Value = Application.CountIf(Range("J3:J600"), "15")
Tb103.Value = Tb102.Value * 15
Tb106.Value = Application.CountIf(Range("J3:J600"), "13")
Tb107.Value = Tb106.Value * 13
Tb108.Value = Application.CountIf(Range("J3:J600"), "10")
Tb109.Value = Tb108.Value * 10
Tb110.Value = Application.CountIf(Range("J3:J600"), "5")
Tb111.Value = Tb110.Value * 5
Tb112.Value = Application.CountIf(Range("J3:J452"), "CAR*")
Tb114.Value = Application.CountIf(Range("J3:J452"), "FREE*")
Tb104.Value = (Val(Trim(Tb100.Value))) + (Val(Trim(Tb102.Value))) +
(Val(Trim(Tb106.Value))) + (Val(Trim(Tb108.Value))) +
(Val(Trim(Tb110.Value))) + (Val(Trim(Tb112.Value))) + (Val(Trim(Tb114.Value)))
Tb105.Value = (Val(Trim(Tb101.Value))) + (Val(Trim(Tb103.Value))) +
(Val(Trim(Tb107.Value))) + (Val(Trim(Tb109.Value))) + (Val(Trim(Tb111.Value)))
Tb105.Value = FormatCurrency(Tb105.Value, 2)
Tb103.Value = FormatCurrency(Tb103.Value, 2)
Tb101.Value = FormatCurrency(Tb101.Value, 2)
Tb107.Value = FormatCurrency(Tb107.Value, 2)
Tb109.Value = FormatCurrency(Tb109.Value, 2)
Tb111.Value = FormatCurrency(Tb111.Value, 2)
End Sub

Any help or suggestions most helpful??
--
Many thanks

hazel

Changing Values
 

Hi Jim

Thank you for the quick response - It would probably be best to put the
values on a Variables sheet so that I could leave the information within a
folder on the Desk Top -- maybe hide the sheet and perhaps use a combobox
aligned to RowSource they could then update the values if necessary. Would
that be possible and still be able to have the CountIf procedure?
If you could point me in the right direction will have a go at it -- it was
the "30" & "15" etc -- that was the values I was referring to.
--
Many thanks

hazel


"Jim Thomlinson" wrote:

By change the values I assume you mean the 30 and the 15 and the... There are
2 ways to go about this and it depends on how likely it is the values will
need to be changed.

If the values are constants that you do not reasonable forsee needing to be
changed then you can make them constants in your program by declaring them as
const(often done in a seperate module or at the top of existing modules).
This puts all of the constants in one easy to get at spot in the unlikely
event that they do need to be changed. This is not something that your end
users can be expected to do but which any programmer with a bit of knowledge
will be able to easily fix. The value of doing this is that you change the
constant in the one spot then evey line of code that requires taht constant
will be instantly updated.

If the values are relatively constant but you do forsee that they will
change in the future then you may want to set up a Variables sheet in you
workbook where you set out all of these values. By giving your end users a
facility to expose this page you allow relatively unsophisticated end user to
change the values. Then in your program you can just reference the values on
that sheet.
--
HTH...

Jim Thomlinson


"Hazel" wrote:

Hi All

I have adapted the code below off info gleaned on this forum and it works OK
, however a question was asked of me what would happen if I was on Holiday or
absent through sickness etc - how would we change the values listed below.
Private Sub Add100_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Customers Prices")
Worksheets("Customers Prices").Select
Range("A3").Select
Tb100.Value = Application.CountIf(Range("J3:J600"), "30")
Tb101.Value = Tb100.Value * 30
Tb102.Value = Application.CountIf(Range("J3:J600"), "15")
Tb103.Value = Tb102.Value * 15
Tb106.Value = Application.CountIf(Range("J3:J600"), "13")
Tb107.Value = Tb106.Value * 13
Tb108.Value = Application.CountIf(Range("J3:J600"), "10")
Tb109.Value = Tb108.Value * 10
Tb110.Value = Application.CountIf(Range("J3:J600"), "5")
Tb111.Value = Tb110.Value * 5
Tb112.Value = Application.CountIf(Range("J3:J452"), "CAR*")
Tb114.Value = Application.CountIf(Range("J3:J452"), "FREE*")
Tb104.Value = (Val(Trim(Tb100.Value))) + (Val(Trim(Tb102.Value))) +
(Val(Trim(Tb106.Value))) + (Val(Trim(Tb108.Value))) +
(Val(Trim(Tb110.Value))) + (Val(Trim(Tb112.Value))) + (Val(Trim(Tb114.Value)))
Tb105.Value = (Val(Trim(Tb101.Value))) + (Val(Trim(Tb103.Value))) +
(Val(Trim(Tb107.Value))) + (Val(Trim(Tb109.Value))) + (Val(Trim(Tb111.Value)))
Tb105.Value = FormatCurrency(Tb105.Value, 2)
Tb103.Value = FormatCurrency(Tb103.Value, 2)
Tb101.Value = FormatCurrency(Tb101.Value, 2)
Tb107.Value = FormatCurrency(Tb107.Value, 2)
Tb109.Value = FormatCurrency(Tb109.Value, 2)
Tb111.Value = FormatCurrency(Tb111.Value, 2)
End Sub

Any help or suggestions most helpful??
--
Many thanks

hazel