[thomabui]

Hi,

I'm building a simple VB program to control an excel spreadsheet, the idea is, you click a button and it adds 1 to a counter in the database. Which field the counter adds to is dependant on the time of day, as I'm counting activities done per hour.

What I want to do is assign the cell to add to dynamically. I've made the program work but only with a rather ugly if / elseif tree, going from hour to hour.

The idea I had looks like this, this is my procedure to assign the values already in the table to my counters when the form opens.

 
Public Sub UserForm_Initialize()

t = (Mid(time, 1, 2))  ' set the value of t to the current time

If t = "08" Or "09" Then ' if the "t" variable contains a 0, remove it
t = Right$(t, 1)
End If
ccount = Range("B" & t).Value ' set the values of the activity counters to what is
pcount = Range("C" & t).Value ' currently in the cells
End Sub

The 't' variable is set in public space, and the '0 stripper' works fine, as I'm only doing between 8am and 9pm on a 24hrs clock. The problem I'm getting is a type mismatch on the lines

 
ccount = Range("B" & t).Value 
pcount = Range("C" & t).Value 

Does anyone have any suggestions on how to do this? Once I know how to do it for this procedure, I can port the fix to my button click procedures.

Thanks,

Thomas.

[KeyWiz]

try ccount = Val(Range("B" & t).Value)
and pcount = Val(Range("C" & t).Value)

your way is passing a reference, my way is passing the value of the reference, I assume you declared the ccount and pcount as long.

This post has been edited by KeyWiz: 27 October 2006 - 02:48 PM

[thomabui]

KeyWiz, on 27 Oct, 2006 - 02:44 PM, said:

try ccount = Val(Range("B" & t).Value)
and pcount = Val(Range("C" & t).Value)

your way is passing a reference, my way is passing the value of the reference, I assume you declared the ccount and pcount as long.

Excellent! It works!

The problem was that I had my two variables set as strings, I think. Doh. But I've used your method anyway 'cuz it's nicer.

Now I've come across another problem... the bit of code I thought was working... isn't!

t = (Mid(time, 1, 2))
If t = "08" Or "09" Then
t = Right$(t, 1)
End If

It's stripping the first character out of the 't' variable even if t is equal to 18, or 20, or whatever. Have I missed something obvious?

Thanks,

Tom.

[thomabui]

Never mind, I fixed it.

It seems that VB doesn't like the 'or' statement very much. I split the code into

If t = "08" Then ' if the "t" variable contains a 0, remove it
t = Right$(t, 1)
End If
If t = "09" Then
t = Right$(t, 1)
End If

and now it works just fine! Yay!

Thanks for the help!

Tom.

[KeyWiz]

thomabui, on 27 Oct, 2006 - 03:43 PM, said:

Never mind, I fixed it.

It seems that VB doesn't like the 'or' statement very much. I split the code into

If t = "08" Then ' if the "t" variable contains a 0, remove it
t = Right$(t, 1)
End If
If t = "09" Then
t = Right$(t, 1)
End If

and now it works just fine! Yay!

Thanks for the help!

Tom.

just so you know, in an IF statement, you always have to pair a comparitor with a variable
ie


IF (a=1) or (a=2) then

or

IF (a=1) or (b=1) then

the same is true with all the Boolean functions.