4 Replies - 6335 Views - Last Post: 27 October 2006 - 05:38 PM Rate Topic: -----

#1 thomabui  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 27-October 06

Setting an Excel range dynamically

Posted 27 October 2006 - 02:22 PM

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.

Is This A Good Question/Topic? 0
  • +

Replies To: Setting an Excel range dynamically

#2 KeyWiz  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 8
  • View blog
  • Posts: 438
  • Joined: 26-October 06

Re: Setting an Excel range dynamically

Posted 27 October 2006 - 02:44 PM

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

Was This Post Helpful? 0
  • +
  • -

#3 thomabui  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 27-October 06

Re: Setting an Excel range dynamically

Posted 27 October 2006 - 03:26 PM

View PostKeyWiz, 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.
Was This Post Helpful? 0
  • +
  • -

#4 thomabui  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 5
  • Joined: 27-October 06

Re: Setting an Excel range dynamically

Posted 27 October 2006 - 03:43 PM

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.
Was This Post Helpful? 0
  • +
  • -

#5 KeyWiz  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 8
  • View blog
  • Posts: 438
  • Joined: 26-October 06

Re: Setting an Excel range dynamically

Post icon  Posted 27 October 2006 - 05:38 PM

View Postthomabui, 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.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1