+ Reply to Thread
Results 1 to 4 of 4

Object Variable or with block not set

Hybrid View

KeithMale Object Variable or with block... 10-16-2013, 07:02 PM
Andy Pope Re: Object Variable or with... 10-17-2013, 04:13 AM
NigelFrost Re: Object Variable or with... 10-17-2013, 04:16 AM
KeithMale Re: Object Variable or with... 10-18-2013, 04:46 PM
  1. #1
    Registered User
    Join Date
    05-27-2013
    Location
    Buellton, California
    MS-Off Ver
    Excel 2007
    Posts
    26

    Object Variable or with block not set

    I am having an issue setting the value of a cell with some variables. I am getting the error in the post title, but I cannot find where this error is coming from.


    Dim FirstFourRunNo As String, LastTwoRunNo As String, GrowthPrefix As String
    Dim wsWafer As Worksheet
    Dim RunNo As String
    RunNo = Mid(ThisWorkbook.Name, 17, 6)
    GrowthPrefix = InputBox("Please enter the wafer prefix (SPV, SPW, or SPL)", "Wafer Prefix")
    FirstFourRunNo = Mid(RunNo, 1, 4)
    LastTwoRunNo = Mid(RunNo, 5, 2)
    Range("W27:Y27").Value = GrowthPrefix & FirstFourRunNo & "-" & LastTwoRunNo & wsWafer & "Fine X1"
    Am I using too many variables for the .value part? Is this method not accepted in VBA?
    Any ideas?

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,480

    Re: Object Variable or with block not set

    What is wsWafer?
    You have declared it as a worksheet object but not assigned it a reference.
    Cheers
    Andy
    www.andypope.info

  3. #3
    Registered User
    Join Date
    10-14-2013
    Location
    Perth, Australia
    MS-Off Ver
    Excel 2003, 2013
    Posts
    25

    Re: Object Variable or with block not set

    It's because you haven't set wsWafer to anything it is a worksheet object = Nothing at the moment. I assume you want to print the name, set the worksheet first then use wsWafer.Name

  4. #4
    Registered User
    Join Date
    05-27-2013
    Location
    Buellton, California
    MS-Off Ver
    Excel 2007
    Posts
    26

    Re: Object Variable or with block not set

    I forgot to include that part about wsWafer. There are 700+ lines in the macro and I only wanted to include the part that was relevant to the error.

    Dim FirstFourRunNo As String, LastTwoRunNo As String, GrowthPrefix As String
    Dim wsWafer As Worksheet
    Dim RunNo As String
    RunNo = Mid(ThisWorkbook.Name, 17, 6)
    GrowthPrefix = InputBox("Please enter the wafer prefix (SPV, SPW, or SPL)", "Wafer Prefix")
    FirstFourRunNo = Mid(RunNo, 1, 4)
    LastTwoRunNo = Mid(RunNo, 5, 2)
    
    For Each wsWafer In Sheets(Array("A-G", "A-N", "B-G", "B-N", "C-G", "C-N", "D-G", "D-N", "E-G", "E-N", "F-G", "F-N", _
                                        "G-G", "G-N", "H-G", "H-N", "I-G", "I-N", "J-G", "J-N"))
    
    Range("W27:Y27").Value = GrowthPrefix & FirstFourRunNo & "-" & LastTwoRunNo & wsWafer & "Fine X1"
    
    Next wsWafer
    That is with the lines declaring wsWafer. I just tried wsWafer.Name and it worked!. Thanks NigelFrost

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 6
    Last Post: 12-21-2012, 08:03 AM
  2. Object variable or With block variable not set Error during sorting program
    By BlahBlahBlahBlahBlah in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-17-2012, 07:57 AM
  3. Error 91 object variable block variable not set when importing a CVS file
    By ReportBuilder2011 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-13-2011, 11:48 AM
  4. Object Variable or With Block Variable Not Set- Error on Another User Machine
    By dailem in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-01-2010, 09:28 AM
  5. Find Method error: -Object variable with block set variable not set
    By bambino_32 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 06-02-2010, 01:18 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1