Cell Reference is Worksheet Name in VB Code

M

Mikeice

Just posting this again as desperate to have this work.


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

Just want to say you guys are great and thanks for all you help thu
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.Colum
 
M

Mikeice

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?
 
M

Mikeice

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
 
M

mangesh_yadav

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

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

Does it work..?


Mangesh
 
M

mangesh_yadav

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
 
M

Mikeice

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.
 
M

mangesh_yadav

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
 
M

Mikeice

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.
 
M

mangesh_yadav

Maybe I could have a look at your sheet. you could mail it to me at
(e-mail address removed). 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
 
T

Tom Ogilvy

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
 
M

mangesh_yadav

Hi Tom,

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

Manges
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Cell Ref to identify worksheet 1
Need to hide date 2
Getting random cells entering date please help 6
Pivot Table Code - Is there a better way? 2
SELECT PART OF A TEXT IN A CELL 1
WHy does it only work on Menu SHeet 2
help! Why my code can't work in excel 97 4
find first empty cell in column and start transpose next row in that cell 6

Top