I have a workbook "Optvol" having the word "put" in cell A10. In a macro:

If Workbooks("optvol.xlsx").Sheets("optvol").Range("A 10") = "put" Then

Range("c4") = "it works"

End If

C4 is in the active workbook, different from "Optvol". Nothing happens upon execution, and there is no error message

Clearly, for some reason the contents of Optvol A10 are not recognized.

Any idea what I'm doing wrong? Appreciate all help, thanks.

On Wed, 25 Nov 2020 12:56:43 -0800 (PST), programmernovice
wrote:

I have a workbook "Optvol" having the word "put" in cell A10. In a macro:

If Workbooks("optvol.xlsx").Sheets("optvol").Range("A 10") = "put" Then

Range("c4") = "it works"

End If

C4 is in the active workbook, different from "Optvol". Nothing happens upon execution, and there is no error message

Clearly, for some reason the contents of Optvol A10 are not recognized.

Any idea what I'm doing wrong? Appreciate all help, thanks.

Maybe a stupid question, but is the file optvol.xlsx open in Excel
when you test? Pretty sure it has to be for VBA to actually do the
If-test.
--
Jesper Kaas -

Maybe a stupid question, but is the file optvol.xlsx open in Excel
when you test? Pretty sure it has to be for VBA to actually do the
If-test.
--
Jesper Kaas -

Hi, Jesper, it is indeed open. And no, it's not a stupid question!

Can you help me out here? Many thanks for responding.

On Thu, 26 Nov 2020 12:48:26 -0800 (PST), programmernovice
wrote:


Maybe a stupid question, but is the file optvol.xlsx open in Excel
when you test? Pretty sure it has to be for VBA to actually do the
If-test.
--
Jesper Kaas -

Hi, Jesper, it is indeed open. And no, it's not a stupid question!

Can you help me out here? Many thanks for responding.

Try to change: If
Workbooks("optvol.xlsx").Sheets("optvol").Range("A 10") = "put" Then

Range("c4") = "it works"

to:
If Workbooks("optvol.xlsx").Sheets("optvol").Range("A 10") = "put" Then

Activesheet.Range("c4") = "it works"

That works for a simillar test here.

PS: To find out how to perform tasks in Excel via VBA, you can record
a macro while doing the stuff manually in Excel, and the check the
recorded code.

I can recommend John Walkenbach "Power programming with VBA".
--
Jesper Kaas -

Try to change: If
Workbooks("optvol.xlsx").Sheets("optvol").Range("A 10") = "put" Then

Range("c4") = "it works"
to:
If Workbooks("optvol.xlsx").Sheets("optvol").Range("A 10") = "put" Then
Activesheet.Range("c4") = "it works"

That works for a simillar test here.

PS: To find out how to perform tasks in Excel via VBA, you can record
a macro while doing the stuff manually in Excel, and the check the
recorded code.

I can recommend John Walkenbach "Power programming with VBA".
--
Jesper Kaas -

Many thanks Jesper. Is the Walkenbach book appropriate for beginners like myself?

Again, your help is greatly appreciated.

On Fri, 27 Nov 2020 09:45:16 -0800 (PST), programmernovice
wrote:

Is the Walkenbach book appropriate for beginners like myself?

I don't do much VBA in Excel, except that I often write a little sub
to solve a thing that maybe culd be better handled with a function
from Excel.
When I worked (now retired) it happened maybe once a year that
something that could be solved with a few hundred lines of VBA came
up. Everytime that happened, I had forgotten almost everything except
the most basic tricks, and had to start from scratch. There Walcenbach
came in very handy, as it is easy to find what you are looking for.
The whole book comes in a pdf, so you can search for what ever you
need.
I think the book is build so it is usefull for everyone from absolute
beginners to the more advanced.
--
Jesper Kaas -