Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old November 25th 20, 09:56 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2018
Posts: 56
Default VBA Does not recognize cell contents

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.

  #2   Report Post  
Old November 26th 20, 02:24 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: May 2010
Posts: 9
Default VBA Does not recognize cell contents

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 -
  #3   Report Post  
Old November 26th 20, 09:48 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2018
Posts: 56
Default VBA Does not recognize cell contents


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.
  #4   Report Post  
Old November 27th 20, 03:00 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: May 2010
Posts: 9
Default VBA Does not recognize cell contents

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 -
  #5   Report Post  
Old November 27th 20, 06:45 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2018
Posts: 56
Default VBA Does not recognize cell contents


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.


  #6   Report Post  
Old November 27th 20, 08:56 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: May 2010
Posts: 9
Default VBA Does not recognize cell contents

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 -


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Using IF Function to Recognize Highlighted Cell Blythe Richardson Smith Excel Worksheet Functions 1 November 18th 20 02:31 AM
Recognize currency in a cell Marcin P Excel Programming 4 December 4th 10 02:38 AM
Recognize first emtpy Cell in column JohnDK[_3_] Excel Programming 5 November 5th 05 04:31 AM
How do I make a cell's contents equal to another cell's contents with macro program? mgmcdevitt[_10_] Excel Programming 2 September 15th 05 09:44 PM
recognize the cell Michael Excel Discussion (Misc queries) 0 June 21st 05 01:29 PM


All times are GMT +1. The time now is 08:31 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright ©2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017