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.