Need help this is buggy no matter how I build it.

[CLoos]

I have tried pastespecial also it gave me an object error. This value = value is giving me an object variable error for no set on row1. When I create a fictitious range in the set row the macro stops with some built in function when it opens the file. I am lost! Any help would be greatly appreciated.

Sub Update_Log_Status()
  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: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

 

[NARAYANK991]

Hi ,

Make the following 2 changes :

1. Dim row1 As Long

row1 is not supposed to be a Range type variable , since you are finally using it as a parameter to the Cells method.

2. Remove the following line of code :

Set row1 = ActiveSheet.Rows

This is unnecessary , and if the first change is made , incorrect as well ; since the next line of code is making the correct assignment to the row1 variable , this line is to be removed.

Narayan

 

[CLoos]

Hi ,

Make the following 2 changes :

1. Dim row1 As Long

row1 is not supposed to be a Range type variable , since you are finally using it as a parameter to the Cells method.

2. Remove the following line of code :

Set row1 = ActiveSheet.Rows

This is unnecessary , and if the first change is made , incorrect as well ; since the next line of code is making the correct assignment to the row1 variable , this line is to be removed.

Narayan

Thank you Narayan. This is actually how I started but I continually get a Object Variable error. I thought it was based on row1 variable but now I am wondering if it is the Range? Or possibly that it is calling cell B21 supposedly on an activeworkbook but it is not active anymore?

The error occurs on this line: row1 = sht1.Range("D:D").Find(Range("B21").Value, , xlValues, xlWhole).row

 

[Chihiro]

You should qualify "Range("B21").Value" with the sheet it belongs to (to avoid error when another sheet is active).

 

[CLoos]

Thanks for the tip Chihiro - Still getting an object variable not set error on this line row1 = sht1.Range("B:B").Find(sht2.Range("B21").Value, , xlValues, xlWhole).row..... I changed a few things to make the sheet active, no luck.

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 PO's\Manual PO Log\Manual PO Log.xlsx"
 
  'Stop updating the screen
  Application.ScreenUpdating = True
 
  Set mywb = ActiveWorkbook
  Set sht2 = ActiveWorkbook.Sheets("PO")
 
  'Open the workbook
  Set wrkbk1 = Workbooks.Open(filePath1)
 
  'Grab the first sheet in wrkbk
  Set sht1 = wrkbk1.Sheets(1)
 
  'Find value in B21 of active worksheet in colum D of closed workbook
  mywb.Activate
  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

 

[Chihiro]

Can you upload sample workbook(s)?

 

[CLoos]

Can you upload sample workbook(s)?

Yes, I have removed the sensitive information and attached the files. Here is the functionality, an analyst creates a PO which creates the Manual PO log, then the PO is emailed to an approver, the approver transfers the PO from their email into a folder on their desktop, the approver envokes the macro 'create and email pdf' which sends to the pdf to AP folks. At this point I need to go back to the Manual PO log and mark it as approved. I just chose to put an "A" in column J. But I need to find the correct Product# to mark the correct row. The last issue I am having is there is a duplicate product# hidden in row 3 so the range must start in cell D4 and search from there. The code resides in Approve PO2. All the code is working perfectly accept this status code "A".

 

[Chihiro]

Where is "PO" sheet located? I don't see it in your workbooks uploaded.

Also, which workbook should mywb be? Is it the Approve PO2 or Manual PO log?

I'm guessing "Approve PO2" for both. However, you have no sheets set up except the blank Macro1 sheet.

 

[CLoos]

Where is "PO" sheet located? I don't see it in your workbooks uploaded.

Also, which workbook should mywb be? Is it the Approve PO2 or Manual PO log?

I'm guessing "Approve PO2" for both. However, you have no sheets set up except the blank Macro1 sheet.

Thank you it seems to be working now. I don't understand why I receive the errors when I step through it but not when I run it. I would like to have this written well. So the flow is there is another workbook/macro that creates the PO and then sends it to the approver. The approver then moves it from their email to their desktop where they approve sign and send to the appropriate AP people. I put all the code in the "Approve PO2" sheet. If you have the worksheet PO open that was emailed to you, I want it to sign and send a copy of the PO plus send an approval status over to the share drive. The portion that was giving me so many problems was sending the status code over. Which brings me back to your question, mywb is the open file or the PO that needs a signature and approval. The name changes on every file so my workaround was to set it to mywb while it is open to use later before changing to filepath1 on the share drive.