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

Just browsing Stack Overflow? Help us improve your experience. Sign up for research

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

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

   Try Teams for free 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, 6 months ago Modified 5 years, 6 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 7,02977 gold badges4242 silver badges7979 bronze badges asked May 11, 2019 at 15:58 reidarbbreidarbb 2155 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 7,02977 gold badges4242 silver badges7979 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
• We’re (finally!) going to the cloud!
• More network sites to see advertising test [updated with phase 2]

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

• How to demystify why my degree took so long on my CV
• Why is the chi-square test giving unintuitive results?
• Why is this soldering iron red hot in the middle section?
• Implicit function theorem without manifolds (Steve Smale article)?
• The sum of multiple irrational numbers can be rational, even when they're not conjugates. Is this normal?
• Does ambigous license without a version refer to most recent? Is 'AGPL' currently equivalent to 'AGPL-3.0-only'?
• iMac 12,1 (mid 2011) - Wi-Fi module becomes slow and unstable after sleep on Windows 10
• What happened to the lifeboats in Star Trek: First Contact?
• How to identify unsafe trees for climbing stand?
• Outlet Wiring Gone Wrong
• TF-IDF Implementation in Java
• A Title "That in Aleppo Was"
• Person of interest/under investigation living abroad - what can the UK police do?
• Difference between English short stories and short English stories
• Reality check: energy source for power armour
• How to Prove This Elegant Integral Identity Involving Trigonometric and Square Root Terms
• Can Martial Characters use Spell Scrolls in D&D 2024?
• Embossing a model's texture onto its mesh
• Is ATL-98 Carvair still alive in the US?
• Baskervaldx doesn’t allow certain glyphs
• A roulette wheel? An AC Milan kit? Darth Maul's face?
• Is Trinitarian Christian theism a simple hypothesis?
• Does Naomi Nagata go to her grave thinking that she killed her son?
• Prospective employers tell me my field is obsolete. How can I reinvent myself?

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