Value before error in cell and corresponding cells

[RioDegenero]

I will appreciate help with VBA code to copy the value in the last cell of column D before errors (which in this case is D16), corresponding date in column A and change in column E to a different spreadsheet.
The last value before the errors in column D will not always be located in D16 as there may be more or less transactions.
Many thanks in advance

 

[DanteAmor]

You marked your own answer as solution. I am asking you to mark the answer that actually fixed your problem.

If you didn't get a solution, then you should unmark your answer as a solution and explain in detail what you need.

To be clearer with your requirement, you must provide clear and precise examples of what you have and what you want as a result, for that you can use the xl2bb tool.

Note XL2BB:
For the future, it would help greatly if you could give us the sample data in a form that we can copy to test with, rather that a picture.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[RioDegenero]

You marked your own answer as solution. I am asking you to mark the answer that actually fixed your problem.

If you didn't get a solution, then you should unmark your answer as a solution and explain in detail what you need.

To be clearer with your requirement, you must provide clear and precise examples of what you have and what you want as a result, for that you can use the xl2bb tool.

Note XL2BB:
For the future, it would help greatly if you could give us the sample data in a form that we can copy to test with, rather that a picture.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Apologies, please bear with me.


I will appreciate help to dynamically locate and copy the last values in column E before the errors and corresponding date in column A.
This is the amount highlighted in yellow i.e. cell E16 and in column A also highlighted in yellow i.e. cell A16 from Excel file with filename "Input" , tab Q1 into another Excel file named "Output" tab "Summary". The values should be copied to cells C3 and C5 in the Summary tab of "Output" file. Here's the path to the file: C:\Users\Maindesk\Desktop\Consumer\Statement

Data copied from Excel file with filename "Input" , tab Q1 - image below:

Data copied to Excel file name "Output" tab "Summary"; Cells C5 and C3 - image below

Thanks

 

[DanteAmor]

You don't mention if the 2 books are open, or if the 2 books are closed.

To finish the macro clearly responds to the following and in this order:
1. Is the input workbook open?
2. Is the output book open?
3. The input workbook is closed and the macro should open it?
4. The output workbook is closed and the macro should open it?
5. Is the input workbook going to contain the macro?
6. Will the macro be in a third book?

Please, once again I ask you to unmark your answer as a solution, wait for the macro to be delivered to you and then mark as a solution the answer that solved your request.

 

[RioDegenero]

You don't mention if the 2 books are open, or if the 2 books are closed. Apologies for that. My answers are in italics below

To finish the macro clearly responds to the following and in this order:
1. Is the input workbook open? Yes
2. Is the output book open? No
3. The input workbook is closed and the macro should open it? Input workbook is open
4. The output workbook is closed and the macro should open it? Not necessarily leave it open it if the figures (date and amount) can be copied in there without it being opened or close it after
5. Is the input workbook going to contain the macro? Contains about 5 different macros that has been used for different calculations and this is the last macro to copy the final answer and the date
6. Will the macro be in a third book? Not sure what you mean but there are only 2 workbook/files used here

Please, once again I ask you to unmark your answer as a solution, wait for the macro to be delivered to you and then mark as a solution the answer that solved your request. Okay, thanks in anticipation

 

[DanteAmor]

According to your comments in the previous post.
Before running the macro, please take care of the following:
1. Put the macro in the workbook input.
2. The workbook input must have the data in the "Q1" sheet.
3. The Output workbook must be closed.
4. The workbook Output should be in this folder: "C:\Users\Maindesk\Desktop\Consumer\Statement"
5. The Output workbook must have a sheet named "Summary"


Other considerations:
1. The macro opens the Output workbook.
2. The macro updates the data in the "Summary" sheet.
3. The macro saves the Output workbook and closes it.

Sub copyvalues()
  Dim wb2 As Workbook
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim f As Range
  Dim i As Long
  Dim sPath As String, sFile As String
  
  Application.ScreenUpdating = False
  
  sPath = "C:\Users\Maindesk\Desktop\Consumer\Statement\"
  sFile = "Output.xlsx"
  
  If Right(sPath, 1) <> "\" Then sPath = sPath & "\"
  
  If Dir(sPath & sFile) = "" Then
    MsgBox "Output Workbook does not exists", vbCritical
    Exit Sub
  End If
  
  Set sh1 = ThisWorkbook.Sheets("Q1")
  Set f = sh1.Range("E:E").Find("#N/A", , xlValues, xlWhole, , , False)
  If Not f Is Nothing Then
    i = f.Row - 1
  Else
    MsgBox "In column ""E"" there are no cells with the error ""#N/A"""
    Exit Sub
  End If
  
  Set wb2 = Workbooks.Open(sPath & sFile)
  Set sh2 = wb2.Sheets("Summary")
  sh2.Range("C3").Value = sh1.Range("A" & i).Value
  sh2.Range("C5").Value = sh1.Range("E" & i).Value
  wb2.Close True
  MsgBox "Completed"
End Sub

In my tests it works correctly, if you follow the recommendations you should not have problems.

Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

 

[RioDegenero]

According to your comments in the previous post.
Before running the macro, please take care of the following:
1. Put the macro in the workbook input.
2. The workbook input must have the data in the "Q1" sheet.
3. The Output workbook must be closed.
4. The workbook Output should be in this folder: "C:\Users\Maindesk\Desktop\Consumer\Statement"
5. The Output workbook must have a sheet named "Summary"


Other considerations:
1. The macro opens the Output workbook.
2. The macro updates the data in the "Summary" sheet.
3. The macro saves the Output workbook and closes it.

Sub copyvalues()
  Dim wb2 As Workbook
  Dim sh1 As Worksheet, sh2 As Worksheet
  Dim f As Range
  Dim i As Long
  Dim sPath As String, sFile As String
 
  Application.ScreenUpdating = False
 
  sPath = "C:\Users\Maindesk\Desktop\Consumer\Statement\"
  sFile = "Output.xlsx"
 
  If Right(sPath, 1) <> "\" Then sPath = sPath & "\"
 
  If Dir(sPath & sFile) = "" Then
    MsgBox "Output Workbook does not exists", vbCritical
    Exit Sub
  End If
 
  Set sh1 = ThisWorkbook.Sheets("Q1")
  Set f = sh1.Range("E:E").Find("#N/A", , xlValues, xlWhole, , , False)
  If Not f Is Nothing Then
    i = f.Row - 1
  Else
    MsgBox "In column ""E"" there are no cells with the error ""#N/A"""
    Exit Sub
  End If
 
  Set wb2 = Workbooks.Open(sPath & sFile)
  Set sh2 = wb2.Sheets("Summary")
  sh2.Range("C3").Value = sh1.Range("A" & i).Value
  sh2.Range("C5").Value = sh1.Range("E" & i).Value
  wb2.Close True
  MsgBox "Completed"
End Sub

In my tests it works correctly, if you follow the recommendations you should not have problems.

Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
----- --

Thanks Dante for all your help.
All works fine until it gets to the line of code "Set sh2 = wb2.Sheets("Summary")"

 

[DanteAmor]

Sorry, although I would like to, I can no longer help you, the information you provide is incomplete and wrong.

The macro is successful if you follow the instructions:

Before running the macro, please take care of the following:
1. Put the macro in the workbook input.
2. The workbook input must have the data in the "Q1" sheet.
3. The Output workbook must be closed.
4. The file name should be "Output.xlsx"
5. The workbook Output should be in this folder: "C:\Users\Maindesk\Desktop\Consumer\Statement"
6. The Output workbook must have a sheet named "Summary"

 

[RioDegenero]

Sorry, although I would like to, I can no longer help you, the information you provide is incomplete and wrong.

The macro is successful if you follow the instructions:

I have followed all the instructions, the only difference is the change I made when the code didn't work was to change "xlsx" to "xlsm" to see if that will help.

You have been very helpful Dante and I appreciate that.

 

[DanteAmor]

the code didn't work

until it gets to the line of code "Set sh2 = wb2.Sheets("Summary")"

It is not enough to put the "does not work" you must put here the error message sent by the macro. That way I can know the problem and can help you.

Check that in the output workbook you have a sheet with the name "Summary", check that the name of the sheet is spelled correctly and that it does not have spaces to the left or to the right. How do you do this? very easy, edit the name of the sheet and write the name "Summary" again, save the file and try the macro again.

 

[RioDegenero]

It is not enough to put the "does not work" you must put here the error message sent by the macro. That way I can know the problem and can help you.

Check that in the output workbook you have a sheet with the name "Summary", check that the name of the sheet is spelled correctly and that it does not have spaces to the left or to the right. How do you do this? very easy, edit the name of the sheet and write the name "Summary" again, save the file and try the macro again.

The code worked!!!
Thanks so much for your help. There was space to the right of the tab name before and once that was sorted it worked and works