Cell Reference is Worksheet Name in VB Code

**Mikeice** · 06-09-2005, 11:17 PM

Just posting this again as desperate to have this work.

I need the cell B3 which displays month to refer to the worksheet name in vbcode.

Just want to say you guys are great and thanks for all you help thus far.
So the cell B3 needs to populate below where it says ("Jan")

Set Summary = Worksheets("Jan")

With Summary
Set LastCol _
= .Cells(myToRow(LBound(myToRow)), .Columns.Count).End(xlToLeft)
If IsEmpty(LastCol) Then
NextColNum = LastCol.Column

**mangesh_yadav** · 06-09-2005, 11:48 PM

Set Summary = Worksheets(Range("B3"))

or

Set Summary = Worksheets(worksheets("Sheet_name").Range("B3"))

Mangesh

**Mikeice** · 06-10-2005, 12:18 AM

Thx for the reply
I have tried both of your possible solutions and both give an error 9 Subscript out of range and that line is highlighted:

Summary = Worksheets(Range("B3"))

Any ideas?

**mangesh_yadav** · 06-10-2005, 12:30 AM

Set Summary = Worksheets("" & Range("b3") & "")

**mangesh_yadav** · 06-10-2005, 12:31 AM

I presume that sheet "Jan" exists.

Mangesh

**Mikeice** · 06-10-2005, 12:38 AM

Sheets Jan Feb - Dec all exist.

I have forwarded the whole command for you to look at. THx for the help.

Private Sub CommandButton1_Click()

Dim Summary As Worksheet
Dim myFromAddr As Variant
Dim myToRow As Variant
Dim iCtr As Long
Dim LastCol As Range
Dim NextColNum As Long

myToRow = Array(2, 3, 4, 5, 6, 7, 8, _
12, 13, 15, 16, 18, 19, _
22, 23, 24, 27, 28, _
31, 32, 33, 34, 35, _
40, 44, 45, 46, 47, 48, 49, 50, _
55, 56, 57, 58, 59, 60, 61, 62)

myFromAddr = Array("B2", "B3", "B4", "B5", "B6", "d2", "e3", _
"d10", "e10", "d17", "e17", "d23", "e23", _
"D36", "D37", "e36", "D42", "E42", _
"D47", "D48", "D49", "D50", "E47", _
"E59", "d63", "D64", "d65", "d66", "d67", "d68", "e63", _
"D73", "D74", "D75", "D76", "d77", "D78", "D79", "E73")

If UBound(myToRow) <> UBound(myFromAddr) Then
MsgBox "Design error--not same number of cells!"
Exit Sub
End If

If IsEmpty(Me.Range(myFromAddr(LBound(myFromAddr)))) Then
MsgBox "Please fill in cell: " & myFromAddr(LBound(myFromAddr))
Exit Sub
End If

Set Summary = Worksheets("" & Range("b3") & "")

With Summary

Set LastCol _
= .Cells(myToRow(LBound(myToRow)), .Columns.Count).End(xlToLeft)
If IsEmpty(LastCol) Then
NextColNum = LastCol.Column
Else
NextColNum = LastCol.Column + 1
End If

For iCtr = LBound(myToRow) To UBound(myToRow)
.Cells(myToRow(iCtr), NextColNum).Value _
= Me.Range(myFromAddr(iCtr)).Value
Me.Range(myFromAddr(iCtr)).ClearContents
Next iCtr

**mangesh_yadav** · 06-10-2005, 12:52 AM

Just before I start going through your code, did you try my earlier suggestion:

Set Summary = Worksheets("" & Range("b3") & "")

Does it work..?

Mangesh

**Mikeice** · 06-10-2005, 12:56 AM

Hi Mangesh

Yes tried both your suggestions and both give error 9

**mangesh_yadav** · 06-10-2005, 12:58 AM

Hi,

I tried your code. Is it complete. The following line:

Me.Range(myFromAddr(iCtr)).ClearContents

clears the cells the first time, and the code runs without any problem. But when I run it second time, since the cells which hold Jan, Feb are empty, i get the run-time error... subscript out of range.

So probably the above line is the culprit. Is it intended.

Mangesh

**Mikeice** · 06-10-2005, 01:07 AM

HI Mangesh

Yes the code is required to clear the data in the Me.Range(myFromAddr(iCtr)).Value on worksheet Quality Scorecard

That myfrom addr does need to be cleared.

**mangesh_yadav** · 06-10-2005, 01:16 AM

Hi Mikeice,

I don't know what you have currently in your worksheet, and so I can't simulate your case here. When I run your code at my end, with values in Jan and Feb in cells B2 and B3, it works the first time (by work I mean it does not throw any error). At this point it also clears the cell B2 and B3 which hold the values Jan and Feb.

The second time I run the macro, the cells B2 and B3 are empty, and so the line
Set Summary = Worksheets("" & Range("b3") & "")
generates an error, as there Range("B3") is blank, and there is no sheet with a blank name.

What is it that you expect when you run the code the second time.

Mangesh

**Mikeice** · 06-10-2005, 01:52 AM

HI

I need a blank worksheet (AS I am using it as a form)

So that I can fill it out again.

I fill out the sheet Quality Scorecard then hit command button at the bottom.

I copy first part into array and save into the b3 worksheet Jan - Dec
then clear cells that are stated in the array on the originating sheet Quality Scorecard.

**mangesh_yadav** · 06-10-2005, 01:55 AM

Maybe I could have a look at your sheet. you could mail it to me at [email protected]. remove no spam. Before sending the sheet, save it at the stage just before you would click the button which generates the error. So when I open the sheet and press the concerned button, I get the error you mention.

Mangesh

Tom Ogilvy · 06-10-2005, 08:05 AM

Add some diagnostic code like below:

Private Sub CommandButton1_Click()

Dim Summary As Worksheet
Dim myFromAddr As Variant
Dim myToRow As Variant
Dim iCtr As Long
Dim LastCol As Range
Dim NextColNum As Long

myToRow = Array(2, 3, 4, 5, 6, 7, 8, _
12, 13, 15, 16, 18, 19, _
22, 23, 24, 27, 28, _
31, 32, 33, 34, 35, _
40, 44, 45, 46, 47, 48, 49, 50, _
55, 56, 57, 58, 59, 60, 61, 62)

myFromAddr = Array("B2", "B3", "B4", "B5", "B6", "d2", "e3", _
"d10", "e10", "d17", "e17", "d23", "e23", _
"D36", "D37", "e36", "D42", "E42", _
"D47", "D48", "D49", "D50", "E47", _
"E59", "d63", "D64", "d65", "d66", "d67", "d68", "e63", _
"D73", "D74", "D75", "D76", "d77", "D78", "D79", "E73")

If UBound(myToRow) <> UBound(myFromAddr) Then
MsgBox "Design error--not same number of cells!"
Exit Sub
End If

If IsEmpty(Me.Range(myFromAddr(LBound(myFromAddr)))) Then
MsgBox "Please fill in cell: " &
myFromAddr(LBound(myFromAddr))
Exit Sub
End If

set rng = Range("B3")
msgbox rng.Address(external:=True) & " contains the value " & vbNewline & _
"-->" & rng.Text & "<--"

Set Summary = Worksheets("" & Range("b3") & "")

With Summary

Set LastCol _
= .Cells(myToRow(LBound(myToRow)), .Columns.Count).End(xlToLeft)
If IsEmpty(LastCol) Then
NextColNum = LastCol.Column
Else
NextColNum = LastCol.Column + 1
End If

For iCtr = LBound(myToRow) To UBound(myToRow)
Cells(myToRow(iCtr), NextColNum).Value _
= Me.Range(myFromAddr(iCtr)).Value
Me.Range(myFromAddr(iCtr)).ClearContents
Next iCtr

--
Regards,
Tom Ogilvy

"Mikeice" <[email protected]> wrote in
message news:[email protected]...
>
> Sheets Jan Feb - Dec all exist.
>
> I have forwarded the whole command for you to look at. THx for the
> help.
>
> Private Sub CommandButton1_Click()
>
> Dim Summary As Worksheet
> Dim myFromAddr As Variant
> Dim myToRow As Variant
> Dim iCtr As Long
> Dim LastCol As Range
> Dim NextColNum As Long
>
>
>
> myToRow = Array(2, 3, 4, 5, 6, 7, 8, _
> 12, 13, 15, 16, 18, 19, _
> 22, 23, 24, 27, 28, _
> 31, 32, 33, 34, 35, _
> 40, 44, 45, 46, 47, 48, 49, 50, _
> 55, 56, 57, 58, 59, 60, 61, 62)
>
>
> myFromAddr = Array("B2", "B3", "B4", "B5", "B6", "d2", "e3", _
> "d10", "e10", "d17", "e17", "d23", "e23", _
> "D36", "D37", "e36", "D42", "E42", _
> "D47", "D48", "D49", "D50", "E47", _
> "E59", "d63", "D64", "d65", "d66", "d67", "d68", "e63", _
> "D73", "D74", "D75", "D76", "d77", "D78", "D79", "E73")
>
> If UBound(myToRow) <> UBound(myFromAddr) Then
> MsgBox "Design error--not same number of cells!"
> Exit Sub
> End If
>
> If IsEmpty(Me.Range(myFromAddr(LBound(myFromAddr)))) Then
> MsgBox "Please fill in cell: " &
> myFromAddr(LBound(myFromAddr))
> Exit Sub
> End If
>
> Set Summary = Worksheets("" & Range("b3") & "")
>
>
> With Summary
>
> Set LastCol _
> = .Cells(myToRow(LBound(myToRow)), .Columns.Count).End(xlToLeft)
> If IsEmpty(LastCol) Then
> NextColNum = LastCol.Column
> Else
> NextColNum = LastCol.Column + 1
> End If
>
> For iCtr = LBound(myToRow) To UBound(myToRow)
> Cells(myToRow(iCtr), NextColNum).Value _
> = Me.Range(myFromAddr(iCtr)).Value
> Me.Range(myFromAddr(iCtr)).ClearContents
> Next iCtr
>
>
> --
> Mikeice
> ------------------------------------------------------------------------
> Mikeice's Profile:
http://www.excelforum.com/member.php...o&userid=22467
> View this thread: http://www.excelforum.com/showthread...hreadid=377971
>

**mangesh_yadav** · 06-10-2005, 08:10 AM

Hi Tom,

The problem was that the cell contained a date which was formatted as mmm. And this was being used to find the sheet which was non-existent.

Mangesh

Tom Ogilvy · 06-10-2005, 10:05 AM

Guess he should have used the Text property instead of the Value property.

Thanks for the feedback.

--
Regards,
Tom Ogilvy

"mangesh_yadav" <[email protected]>
wrote in message
news:[email protected]...
>
> Hi Tom,
>
> The problem was that the cell contained a date which was formatted as
> mmm. And this was being used to find the sheet which was non-existent.
>
> Mangesh
>
>
> --
> mangesh_yadav
> ------------------------------------------------------------------------
> mangesh_yadav's Profile:
http://www.excelforum.com/member.php...o&userid=10470
> View this thread: http://www.excelforum.com/showthread...hreadid=377971
>