Converting strings to Sum worksheet function and control variables
 

I'm having trouble with a couple things.

Dim range1 as string
Range1 = "Worksheets(""" & WS & """).Range(""" & "B91:B" & g &
""")"

I built a string that gives a complete range but when I try to plug it in it
fails:

MsgBox Application.WorksheetFunction.Sum(Range1)

with

msgbox Range1

I can see it's a complete string. Why is it failing?


Another thing is I have a bunch of labels on a form for days of the month and
one for balances on that day.

The labels on the form called Balance1 are called D1, D2, D3, ...D31 and the
balances are called B1, B2...B31

on the worksheet, I'm pulling a day(selection.value) to get 30 for example and
want to transfer the balance say two cells over to B30. Is there a way to do
that rather than a large Select Case hardcoding the day

L1 = "B" & Day(selection.value)
Set Lab1 = Balance1.Controls(L1).Caption
'MsgBox "Caption: " & Balance1.Controls(Lab1).Name
how do I make it show
Balance1.Controls(Lab1).Name = selection.offset(-2,0).value

Converting strings to Sum worksheet function and control variables
 

Hi
for the first one try
....
Dim range1 as range
set range1 = worksheets("WS").range("B91:B" & g)
MsgBox Application.WorksheetFunction.Sum(Range1)
....

For the second one I'm not quite sure what you want to achieve


--
Regards
Frank Kabel
Frankfurt, Germany

Spammastergrand wrote:
I'm having trouble with a couple things.

Dim range1 as string
Range1 = "Worksheets(""" & WS & """).Range(""" & "B91:B"
& g & """)"

I built a string that gives a complete range but when I try to plug
it in it fails:

MsgBox Application.WorksheetFunction.Sum(Range1)

with

msgbox Range1

I can see it's a complete string. Why is it failing?


Another thing is I have a bunch of labels on a form for days of the
month and one for balances on that day.

The labels on the form called Balance1 are called D1, D2, D3, ...D31
and the balances are called B1, B2...B31

on the worksheet, I'm pulling a day(selection.value) to get 30 for
example and want to transfer the balance say two cells over to B30.
Is there a way to do that rather than a large Select Case hardcoding
the day

L1 = "B" & Day(selection.value)
Set Lab1 = Balance1.Controls(L1).Caption
'MsgBox "Caption: " & Balance1.Controls(Lab1).Name
how do I make it show
Balance1.Controls(Lab1).Name = selection.offset(-2,0).value

Converting strings to Sum worksheet function and control variables
 

WS is a variable not a sheet name. I don;t want to hard code the sheet name
since they'll be working with various months like Jan Feb, etc

Converting strings to Sum worksheet function and control variables
 

Hi
change it to
Dim range1 as range
set range1 = worksheets(cstr(WS)).range("B91:B" & g)
MsgBox Application.WorksheetFunction.Sum(Range1)

--
Regards
Frank Kabel
Frankfurt, Germany

Spammastergrand wrote:
WS is a variable not a sheet name. I don;t want to hard code the
sheet name since they'll be working with various months like Jan Feb,
etc

Converting strings to Sum worksheet function and control variables
 

Thanks. I should have dimmed range1 as a range object and not a string. Good to
know.

Converting strings to Sum worksheet function and control variables
 

Dim range1 as range
Dim ws as String
ws = "Sheet1"
set range1 = worksheets(WS).range("B91:B" & g)
MsgBox Application.WorksheetFunction.Sum(Range1)

or

Dim range1 as Range
Dim ws as Worksheet
set ws = Worksheets("Sheet1")
set range1 = WS.range("B91:B" & g)
MsgBox Application.WorksheetFunction.Sum(Range1)

--
Regards,
Tom Ogilvy


"Spammastergrand" wrote in message
...

WS is a variable not a sheet name. I don;t want to hard code the sheet
name
since they'll be working with various months like Jan Feb, etc