Converting strings to Sum worksheet function and control variables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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



(e-mail address removed)
 
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
 
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
(e-mail address removed)
 
Hi
change it to
Dim range1 as range
set range1 = worksheets(cstr(WS)).range("B91:B" & g)
MsgBox Application.WorksheetFunction.Sum(Range1)
 
Thanks. I should have dimmed range1 as a range object and not a string. Good to
know.
(e-mail address removed)
 
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)
 
Back
Top