Excel Vba Range Select Error 1004
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About select method of range class failed vba excel Us Learn more about Stack Overflow the company Business Learn more about hiring vba error 1004 application-defined or object-defined developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the
Vba Select Method Of Worksheet Class Failed
Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up VBA error 1004 - select method
Vba Error 1004 Range Of Object _global
of range class failed up vote 13 down vote favorite 2 First time poster, so if there is any formatting, or guidelines I failed to adhere to, please let me know so that I can fix it. So I am basically asking the user for the file directory of the excel file, then I setup some variables (originally set at public as project variables, since these select method of range class failed c# were being used and changed in other places). I have also added the lines to set these variables to nothing (just in case, I do not think that it should matter). I then set these variables to the excel file, workbook, and sheets that I want to access. Dim filepath as String filePath = CStr(fileDialog) 'ask file dir, set to string Dim sourceXL As Variant 'these three were orig project variables Dim sourceBook As Variant Dim sourceSheet As Variant Dim sourceSheetSum As Variant Set sourceXL = Nothing 'set to nothing in case...? Set sourceBook = Nothing Set sourceSheet = Nothing Set sourceSheetSum = Nothing Set sourceXL = Excel.Application 'set to the paths needed Set sourceBook = sourceXL.Workbooks.Open(filePath) Set sourceSheet = sourceBook.Sheets("Measurements") Set sourceSheetSum = sourceBook.Sheets("Analysis Summary") Dim measName As Variant 'create variable to access later Dim partName As Variant sourceSheetSum.Range("C3").Select 'THIS IS THE PROBLEM LINE measName = sourceSheetSum.Range(Selection, Selection.End(xlDown)).Value sourceSheetSum.Range("D3").Select partName = sourceSheetSum.Range(Selection, Selection.End(xlDown)).Value So I created two different sheet variables 'sourceSheets' and 'sourceSheetsSum', the code works if i use 'sourceSheets', but error 1004 occurs if i use 'sourceSheetsSum'. I have also tried the code with the variable 'sourceSheet' removed completely, in case that was overriding 'sourceSh
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site
Unable To Get The Select Property Of The Range Class
About Us Learn more about Stack Overflow the company Business Learn more about run time error 1004 select method of range class failed hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join method select of object _worksheet failed the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Run Time Error '1004': http://stackoverflow.com/questions/10949893/vba-error-1004-select-method-of-range-class-failed Select method of Range Class failed using ThisWorkbook up vote 1 down vote favorite During the process of running a script if I manually remove focus from the Workbook containing the macro I get the error quoted. If I don't click on anything it works without issue. Script errors out only when I'm trying to place selection back into A1 from the "Input" sheet. Break point http://stackoverflow.com/questions/36165887/run-time-error-1004-select-method-of-range-class-failed-using-thisworkbook is on following line: ThisWorkbook.Sheets("Input").Range("A1").Select If I debug and place focus back on macro Worksheet the script completes without issue. Previous line: ThisWorkbook.Sheets("Input").Cells.Delete runs without error so I'm guessing its the range that is falling out of scope but don't quite understand why as it should be defined by the previous scope notations. Can someone explain why that line is falling out of scope? Shouldn't the ThisWorkbook define fairly explicitly the Workbook that my code is referencing? Any guidance is greatly appreciated. excel excel-vba share|improve this question asked Mar 22 at 21:48 nbayly 1,2741212 Interesting. Would you do me a favor and test one thing? Replace ThisWorkbook.Sheets("Input").Range("A1").Select with shtInputSheetCodeName.Range("A1").Select and try it again. Of course you'll have to exchange the CodeName for the real one ? ThisWorkbook.Sheets("Input").CodeName Another thing would be to Activate the sheet before .Selecting anything on the sheet. So, try to place a ThisWorkbook.Sheets("Input").Activate just before you Select. –Ralph Mar 22 at 22:03 Also see How to avoid using Select in Excel VBA macros –Siddharth Rout Mar 23 at 7:12 Same issue using CodeName. Note that it had the generic Sheet1 CodeName which is
Forums Excel Questions VBA: Error 1004: Select Method of Range class failed Results 1 to 5 of 5 VBA: Error 1004: Select Method of Range class failedThis is a discussion on VBA: Error 1004: Select Method of Range http://www.mrexcel.com/forum/excel-questions/326306-visual-basic-applications-error-1004-select-method-range-class-failed.html class failed within the Excel Questions forums, part of the Question Forums category; I'm writing a macro to retrieve data from an Excel add-in. It's only a few lines of code, and I've ... LinkBack LinkBack URL About LinkBacks Bookmark & Share Digg this Thread!Add Thread to del.icio.usBookmark in TechnoratiTweet this thread Thread Tools Show Printable Version Display Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Jun 23rd, 2008,03:42 PM #1 error 1004 --ndn.diva-- New Member Join Date Jun 2008 Posts 12 VBA: Error 1004: Select Method of Range class failed I'm writing a macro to retrieve data from an Excel add-in. It's only a few lines of code, and I've written it several times and it has worked perfectly. However, this time, I get an error message: Select Method of Range class failed. The section of code that is causing the problem is: If (Range("Month") = "9") select method of Then Sheets("Analysis May 08 NLD").Activate Range("A2:B51").Select 'code that is causing the problem Application.Run ("EssmenuRetrieve") Sheets("Analysis May 08 LDD").Activate Range("A2:B51").Select Application.Run ("EssmenuRetrieve") MsgBox ("Data Retrieved") The value in the cell "Month" is 9, so that part of the code is working properly. I've tried changing the value in that cell to another number, and running the code, after changing the sheet name to the corresponding month, however, I got an error at the same point. I've made sure that everything is typed in correctly (as i said, i've written this bit of code before). Any suggestions? Thanks!! Share Share this post on Digg Del.icio.us Technorati Twitter Reply With Quote Jun 23rd, 2008,03:52 PM #2 Norie Board Regular Join Date Apr 2004 Location Stirling Posts 70,024 Re: VBA: Error 1004: Select Method of Range class failed You might have typed everything in correctly, but are you sure you are referencing everything correctly? Without a worksheet reference here VBA will be looking at the range on what it regards as the active sheet. Code: Range("A2:B51").Select Share Share this post on Digg Del.icio.us Technorati Twitter If posting code please use code tags. Reply With Quote Jun 23rd, 2008,04:00 PM #3 --ndn.diva-- New Member Join Date Jun 2008 Posts 12 Re: VBA: Error 1004: Select Method of Range class failed What do you mean by "w