Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-09-2017, 01:52 PM
CLoos CLoos is offline set row object variable error Windows 7 64bit set row object variable error Office 2010 64bit
Novice
set row object variable error
 
Join Date: Mar 2017
Posts: 4
CLoos is on a distinguished road
Unhappy 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
Reply With Quote
  #2  
Old 03-10-2017, 11:52 AM
NoSparks NoSparks is offline set row object variable error Windows 7 64bit set row object variable error Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

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.
Reply With Quote
  #3  
Old 03-10-2017, 12:32 PM
CLoos CLoos is offline set row object variable error Windows 7 64bit set row object variable error Office 2010 64bit
Novice
set row object variable error
 
Join Date: Mar 2017
Posts: 4
CLoos is on a distinguished road
Default

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
Attached Files
File Type: xlsx Manual PO 063869_B002507001 _ 07-Mar-17 13-21-59_2.xlsx (15.1 KB, 8 views)
File Type: xlsm Approve PO2.xlsm (44.0 KB, 8 views)
File Type: xlsx Manual PO Log2.xlsx (14.1 KB, 8 views)
Reply With Quote
  #4  
Old 03-10-2017, 02:26 PM
NoSparks NoSparks is offline set row object variable error Windows 7 64bit set row object variable error Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

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
Reply With Quote
  #5  
Old 03-10-2017, 02:54 PM
CLoos CLoos is offline set row object variable error Windows 7 64bit set row object variable error Office 2010 64bit
Novice
set row object variable error
 
Join Date: Mar 2017
Posts: 4
CLoos is on a distinguished road
Default

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.
Attached Files
File Type: docx Function Error.docx (444.7 KB, 7 views)
Reply With Quote
  #6  
Old 03-10-2017, 03:50 PM
NoSparks NoSparks is offline set row object variable error Windows 7 64bit set row object variable error Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

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.
Reply With Quote
  #7  
Old 03-10-2017, 04:48 PM
CLoos CLoos is offline set row object variable error Windows 7 64bit set row object variable error Office 2010 64bit
Novice
set row object variable error
 
Join Date: Mar 2017
Posts: 4
CLoos is on a distinguished road
Default

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
Reply With Quote
Reply

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
set row object variable error Run-time error 91 object variable or with block variable not set JUST ME Word VBA 4 03-25-2014 06:56 AM
set row object variable error 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

Other Forums: Access Forums

All times are GMT -7. The time now is 07:24 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft