#1
|
|||
|
|||
set row object variable error
I have been struggling with this for days and I have tried multiple ways of getting this task done but it's always buggy. Either I get an object error with the pastespecial method or I get the object variable error with value = value, referring to the row1. If I set row1 to a range, I get the to the open.workbooks and the code stops due to a built in function. I do not know enough to fix this and I would sure appreciate some help and understanding. I just want to copy a value from an open workbook then open a closed workbook, find the value, and add an "A" six cells to the right, then close the workbook. Here's the mess I have. Please ignore the variables with "2's" I am going to delete them.
Sub Update_Log_Status2() Dim wrkbk1 As Workbook Dim sht1 As Worksheet Dim row1 As Range 'Path to closed file Const filePath1 As String = "J:\Manual PO's\Manual PO Log\Manual PO Log.xlsx" 'Stop updating the screen Application.ScreenUpdating = False ' 'Open the workbook Set wrkbk1 = Workbooks.Open(filePath1) 'Grab the first sheet in wrkbk Set sht1 = wrkbk1.Sheets(1) Set row1 = ActiveSheet.Rows 'Find value in B21 of active worksheet in colum D of closed workbook row1 = sht1.Range("D").Find(Range("B21").Value, , xlValues, xlWhole).row sht1.Cells(row1, 10).Value = "A" 'Close workbook & save wrkbk1.Close True 'Set the screen to update Application.ScreenUpdating = True End Sub |
#2
|
|||
|
|||
Your code is errant in the declaration of row1, it should be Long and the Set row1= line should be removed.
What workbook is your code in? Your verbiage says to first copy a value from an open workbook.... I don't see any attempt to do this. The B21 you're using is on the same sheet as you are looking for it in the just opened workbook. |
#3
|
|||
|
|||
Thank you and yes, I have already changed the row1 back to long and removed the set row1 = statement. Maybe it is the 3rd sheet that is causing the problem? I have an open workbook called "Approve PO" where the code resides. Then I have another sheet open that is called "Manual PO with a variable date", lastly I am opening another sheet called "Manual PO log" where I want to put a status code of "A". I have attached sheets and below is the changes I have made to try and get this to work, unsuccessfully.
Sub Update_Log_Status() Dim wrkbk1 As Workbook Dim sht1 As Worksheet Dim row1 As Long Dim mywb As Workbook Dim sht2 As Worksheet 'Path to closed file Const filePath1 As String = "J:\Manual POs\Manual PO Log\Manual PO Log2.xlsx" 'Stop updating the screen Application.ScreenUpdating = True Set mywb = ActiveWorkbook 'Open the workbook Set wrkbk1 = Workbooks.Open(filePath1) 'Grab the first sheet in wrkbk Set sht1 = wrkbk1.Sheets(1) mywb.Activate Set sht2 = ActiveWorkbook.Sheets("PO") 'Find value in B21 of active worksheet in colum D of closed workbook row1 = sht1.Range("B:B").Find(sht2.Range("B21").Value, , xlValues, xlWhole).row sht1.Cells(row1, 10).Value = "A" 'Close workbook & save wrkbk1.Close True 'Set the screen to update Application.ScreenUpdating = True End Sub |
#4
|
|||
|
|||
This works for me.
Code:
Sub Update_Log_Status() Dim wrkbk1 As Workbook Dim sht1 As Worksheet Dim row1 As Long Dim mywb As Workbook Dim sht2 As Worksheet 'Path to closed file Const filePath1 As String = "J:\Manual POs\Manual PO Log\Manual PO Log2.xlsx" 'Const filePath1 As String = "D:\Forum Stuff\2017\2017_03_01\CLoos\Manual PO Log2.xlsx" '<~~ test path 'Stop updating the screen Application.ScreenUpdating = False Set mywb = ActiveWorkbook 'Open the workbook Set wrkbk1 = Workbooks.Open(filePath1) 'Grab the first sheet in wrkbk Set sht1 = wrkbk1.Sheets(1) mywb.Activate Set sht2 = ActiveWorkbook.Sheets("PO") 'Find value in B21 of active worksheet in colum D of closed workbook On Error Resume Next '<~~ incase not found row1 = sht1.Range("D:D").Find(sht2.Range("B21").Value, , xlValues, xlWhole).Row On Error GoTo 0 '<~~ turn error notification back on If row1 > 3 Then sht1.Cells(row1, 10).Value = "A" End If 'Close workbook & save wrkbk1.Close True 'Set the screen to update Application.ScreenUpdating = True End Sub |
#5
|
|||
|
|||
Hi - Thank you but unfortunately it still has one of the issues I was dealing with. Any ideas? I attached some screen prints. I am testing it with F8 step through. I get to the highlighted line it flashes and the function macro opens, seemingly killing my macro and never opening the log.
|
#6
|
|||
|
|||
Afraid I don't know what you mean the function macro opens.
If you are referring to the debug dialogue... The only way I get the macro to error on that line is by using the wrong file path and then the debug dialogue tells me what the problem is Run-time error 1004 "J:\Manual POs\Manual PO Log\Manual PO Log2.xlsx" could not be found. When I use the file path to the file on my own computer, which I know is right, there is no error. My only guess is your file path is not right. |
#7
|
|||
|
|||
It's interesting because it works fi I run it but if I try and step through it, I get an error. Anyway, I am going to put this one to bed! Thank you so much everyone for your help
|
Tags |
closed workbook, excel vba, object variable error |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Object Variable or With block variable not set | Raza | Excel Programming | 8 | 01-15-2015 12:19 AM |
Run Time Error '91': Object variable or With block variable not set using Catalogue Mailmerge | Berryblue | Mail Merge | 1 | 11-13-2014 05:36 PM |
Run-time error 91 object variable or with block variable not set | JUST ME | Word VBA | 4 | 03-25-2014 06:56 AM |
Run-time error '91': Object variable or With block variable not set | tinfanide | Excel Programming | 2 | 06-10-2012 10:17 AM |
XML parsing & Object variable not set (Error 91) | tinfanide | Excel Programming | 0 | 12-29-2011 08:43 AM |