Excel - VBA - Unable To Set Cell Value - Stack Overflow

The 2024 Developer Survey results are live! See the results

1. 1. Home
   2. Questions
   3. Tags
   4. Users
   5. Companies
   6. Labs
   7. Jobs
   8. Discussions
   9. Collectives

   10. Communities for your favorite technologies. Explore all Collectives

2. Teams

   Now available on Stack Overflow for Teams! AI features where you work: search, IDE, and chat.

   Learn more Explore Teams
3. Teams

4. Ask questions, find answers and collaborate at work with Stack Overflow for Teams. Explore Teams

Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Learn more about Collectives

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Learn more about Teams

Get early access and see previews of new features.

Learn more about Labs VBA - Unable to set cell value Ask Question Asked 5 years, 2 months ago Modified 5 years, 2 months ago Viewed 2k times 0

I am using the code below in a master workbook to open workbooks listed in the range E12:E24.

• Once I have opened these workbooks, I need to count the number of open workbooks (in addition to the master workbook) and assign the number to cell E2 in the Portfolio Results sheet.
• The code below works just as I would like except I get an error message on the line Worksheets("Portfolio Results").Range("E2") = nFields

It's unclear to me why this is the case. Thanks for any help.

Sub SkipBlankCells2() Dim cell As Range, rng As Range, FName As String, nFields As Integer Set rng = Range("E12:E24") Application.DefaultFilePath = ActiveWorkbook.Path nFields = 0 For Each cel In rng If Len(cel) >= 1 Then FName = cel.Value Workbooks.Open Filename:=FName nFields = nFields + 1 End If Next cel Debug.Print nFields Worksheets("Portfolio Results").Range("E2") = nFields End Sub

• excel
• vba

Share Improve this question Follow edited May 11, 2019 at 18:31 Samuel Hulla 6,91977 gold badges4040 silver badges7777 bronze badges asked May 11, 2019 at 15:58 reidarbbreidarbb 2144 bronze badges 2

• add wirkbbok name before Thisworkbook.Worksheets("Portfolio Results").Range("E2") = nFields – Techie Commented May 11, 2019 at 16:03
• That did it :) Thanks. – reidarbb Commented May 11, 2019 at 16:14

Add a comment |

1 Answer 1

Sorted by: Reset to default Highest score (default) Trending (recent votes count more) Date modified (newest first) Date created (oldest first) 0

You should (almost always) have Option Explicit as your first line of the code.

• Your current code contains an error, where you declared the variable

  Dim cell as Range

• but then in your For loop you're using an undeclared variable cel which defaults to a Variant data-type.

• with Option Explicit enabled, the compiler will warn you about these kind of errors.

As to the actual answer, when switching between objects (be it Worksheet or Workbook) it's always a good programming practice to explicitly declare them.

Easiest way to access them is to store them inside a variable:

Dim wb as Workbook: Set wb = Workbooks("Static name") Dim ws as Worksheet: Set ws = wb.Sheets("Your sheet name") 'later in the code If wb.ws.Cells(1, 9) = "banana" Then '...

Not only this makes for a more read-able code for somebody else (because if somebody inherits your project, they can't know which workbook or worksheet intended the author to work with), but it also prevents these unnecessary kind of errors where a different Workbook or Worksheet is selected.

As a final note, if you don't know which Workbook might be open at the moment, but want to reference "this" specific one, then use ThisWorkbook instead

Share Improve this answer Follow answered May 11, 2019 at 18:27 Samuel HullaSamuel Hulla 6,91977 gold badges4040 silver badges7777 bronze badges 2

• Thanks for the good advice Rawrplus. I will modify my code accordingly. – reidarbb Commented May 12, 2019 at 12:17
• @reidarbb yeah no problem, would be nicei f you accepted the answer then please though, if all the problems have been solved. So the question can be 'closed' – Samuel Hulla Commented May 13, 2019 at 14:02

Add a comment |

Your Answer

Reminder: Answers generated by artificial intelligence tools are not allowed on Stack Overflow. Learn more

Thanks for contributing an answer to Stack Overflow!

• Please be sure to answer the question. Provide details and share your research!

But avoid …

• Asking for help, clarification, or responding to other answers.
• Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

Draft saved Draft discarded

Sign up or log in

Sign up using Google Sign up using Email and Password Submit

Post as a guest

Name Email

Required, but never shown

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged

• excel
• vba

or ask your own question.

• Featured on Meta
• Announcing a change to the data-dump process
• We've made changes to our Terms of Service & Privacy Policy - July 2024

Related

1 Code works great, except needs to set values not formula 0 VBA not setting cell text in excel spreadsheet 1 VBA Excel - Run-time Error '1004' when trying to assign a value to a Cell 0 Object variable not set error? 0 error 1004 when trying to set cell value to value of string 2 Assignment of value to variable not working in VBA 0 My VBA isn't returning any errors but wont set the value of a cell in MS-Excel 0 can not set a formula in specific cell 0 Error 1004 setting cell value 1 Cannot change the cells value

Hot Network Questions

• Foundations and contradictions of Scholze's work: the category of presentable infinity categories contains itself
• DC motor pump always transports in same direction, regardless of polarity
• How do Trinitarians distinguish whether the Hebrew bible is referring to the "being" or "person[s]" of God in the verses that declare oneness?
• Compact rotatable connection that supports pull forces?
• What is the difference of "limiting reactant" and "limiting reagent"?
• 🧩CRYPTICROSS🧩
• Five numbers with median and mean
• How will a very short undergrad impact PhD applications?
• Is believing the role of man in John 3:16? Is believing not by Holy Spirit work?
• Explain why it's bad to use a middleware to coat error messages as exceptions
• How to make an operator form of Part[] to use with // (Postfix)
• Can multi-threading improve performance of an IO-bound process?
• Are there non-religious variants of moral realism that defend the existence of objectively evil thoughts, intentions, and desires?
• Are there any signs that the laptop fan is broken during use?
• Jurisdiction: Can police officers open mail addressed to a stranger?
• Does spring stop applying force as soon as we cut it?
• "make / work / find one's way to somewhere"
• Is a Fizban's dragonborn's breath weapon magical?
• Is it correct to say: "To solve an addiction"?
• Do strawberry seeds have different DNA within the same fruit?
• Ubuntu 24.04 LTS Repositories what is the difference between noble vs noble-updates vs noble-security vs noble-backports
• How does Wild Shape interact with the clone spell?
• The existence of an eigenvalue of a finite-dim matrix, in a vector space over an arbitrary field.
• Adding another author to a paper that hasn’t contributed?

more hot questions Question feed Subscribe to RSS Question feed

To subscribe to this RSS feed, copy and paste this URL into your RSS reader.

lang-vb