Use of variables...

[hudhwk]

Hi folks!

This may sound silly, but I need to do something that I know how to do in FoxPro very easly but just can't seem to find the right syntax in VB...

I have a Select statement:
Select Case Worksheets("Total").Range("G2")
Case "Data 1"
m_Data = Worksheets("Data 1")

Here is the thing. I need to save in the variable m_Data, not the contents but the words "Worksheets("Data 1")", the reason? Simple, because later I need to invoque the contents of m_Data to be used like this:

Worksheets("Total").Range("B9").Value = m_Data.Range("D4")

In this case, the contents of m_Data have to be "converted" into something like this:

Worksheets("Total").Range("B9").Value = Worksheets("Data 1").Range("D4")

In FoxPro I use a "&" command to invoque a regular content in a variable that can be a reserved word, but just can't seem to find anything like that in VB...

Any help will be very welcomed!!!

Thanks in advance folks...

HudHwk
"Here comes the Hawk!!!"

 

[PHV]

you may try this:
Dim m_Data As Worksheet
...
Set m_Data = Worksheets("Data 1")
...
Worksheets("Total").Range("B9").Value = m_Data.Range("D4")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[hudhwk]

Thanks for replying PH!

Now I am getting this error:

Run-time error '91':

Object variable or With block variable not set

Thanks in advance!

HudHwk
"Here comes the Hawk!!!

 

[PHV]

What is your code ?
Which line is highlighted when in debug mode ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

Another way you may try:

Dim m_Data As String
...
m_Data = "'Data 1'!"
...
Worksheets("Total").Range("B9").Value = Evaluate(m_Data & "D4")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[hudhwk]

PH,

The second option (use of string) is getting me a 0 value of D4.

In the first one, the error shows on line:

Worksheets("Total").Range("B9").Value = m_Data.Range("D4")

HudHwk
"Here comes the Hawk!!!

 

[PHV]

Yet another way:
Dim m_Data As String
...
m_Data = "Data 1"
...
Worksheets("Total").Range("B9").Value = Worksheets(m_Data).Range("D4")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[hudhwk]

Hi again PH,

Now I am receiving a Run-time error 9. Subscript out of range...

This is the original code (sorry for the spanish info)

Private Sub CommandButton1_Click()
Dim m_Ejecutivo As String
Select Case Worksheets("Resumen").Range("G2")
Case "Roberto Garza"
m_Ejecutivo = "Roberto Garza"
Case "Ejecutivo 2"
m_Ejecutivo = "Ejecutivo 2"
Case "Ejecutivo 3"
m_Ejecutivo = "Ejecutivo 3"
Case "Lorenzo Huerta Alvarado"
m_Ejecutivo = "Lorenzo Huerta Alvarado"
End Select

Select Case Worksheets("Resumen").Range("G2")
Case "Diciembre"
Worksheets("Resumen").Range("B9").Value = Worksheets(m_Ejecutivo).Range("D4")
...

The error is shown on the last line.

Any idea?

Thanks!

 

[PHV]

Are the m_Ejecutivo's and Resumen in the same workbook ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[hudhwk]

Sorry for the delay PH...

Resumen is a worksheet itself but m_Ejecutivo is a local variable....

Hudhwk...

 

[SkipVought]

[blue]Resumen is a worksheet (object)[/blue] itself but [red]m_Ejecutivo is a local (string) variable[/red]"

   [b][blue]Resumen[/blue][/b].Range("B9").Value = Worksheets("[b][red]m_Ejecutivo[/red][/b]").Range("D4")

Skip,
[sub]
[red][/red]
[/sub]

 

[hudhwk]

Thanks Skip...!!!

Still receiving the Subscript out of range error message on the same line folks...

Thanks!

HudHwk

 

[SkipVought]

Is Resumen, indeed, a Worksheet OBJECT?

Does m_Ejecutivo, indeed, contain the value of a valid Sheet Name?

If you activate the Watch Window, what do each of these variables show when you DEBUG?

Skip,
[sub]
[red][/red]
[/sub]

 

[SkipVought]

Just browsing the last code you posted,

Private Sub CommandButton1_Click()
Dim m_Ejecutivo As String
Select Case Worksheets("Resumen").Range("G2")[red][b]
Case "Roberto Garza"
    m_Ejecutivo = "Roberto Garza"
Case "Ejecutivo 2"
    m_Ejecutivo = "Ejecutivo 2"
Case "Ejecutivo 3"
    m_Ejecutivo = "Ejecutivo 3"
Case "Lorenzo Huerta Alvarado"
    m_Ejecutivo = "Lorenzo Huerta Alvarado"[/b][/red]
End Select

Select Case Worksheets("Resumen").Range("G2")
Case [b]"Diciembre"[/b]
    Worksheets("Resumen").Range("B9").Value = Worksheets(m_Ejecutivo).Range("D4")

if the VALUE in Worksheets("Resumen").Range("G2") is Diciembre, then the VALUE in m_Ejecutivo is either [red]UNDEFINED or INCORRECT[/red], since m_Ejecutivo never gets assigned.

Skip,
[sub]
[red][/red]
[/sub]

 

[hudhwk]

BIG TYPO ERROR!!!

Yes, you are right, the select code should be "G2" instead of "C2" I have the wrong cell assigned.

Select Case Worksheets("Resumen").Range("G2")

I have added the watch and it is now populated with the rigth info, but still getting the "Subscript out of range" error.

The original line is:
Worksheets("Resumen").Range("B9").Value = Worksheets(Lorenzo Huerta Alvarado).Range("D4")

I want to use a variable contents instead of the name of the worksheet, m_Ejecutivo.

Thanks again and sorry all this...

HudHwk
"Here comes the Hawk!!!...

 

[SkipVought]

Everything's OK now?

Skip,
[sub]
[red][/red]
[/sub]

 

[hudhwk]

=(

Nope... the error "Subscript out of range" remains.

Sorry...

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
HudHwk
"Here comes the Hawk!!!...

 

[SkipVought]

Did you inspect each variable/object at the point of the error, in DEBUG, via the Watch Window?

What did you find?

Skip,
[sub]
[red][/red]
[/sub]

 

[hudhwk]

Yes I did as you intructed me.

The variable now has the right information that I needed in it, but for some reason is still not retrieving the cell data from the chosen worksheet as I expected.

"Subscript out of range" still shows...

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
HudHwk
"Here comes the Hawk!!!...

 

[SkipVought]

That error only happens when an object is referenced with an invalid name.

What does the Watch Window say that each of these are???

Worksheets("Resumen")

Worksheets(m_Ejecutivo)

Skip,
[sub]
[red][/red]
[/sub]