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

 

[offthelip]

try this:

Sub test()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastrow, 4))
For i = lastrow To 1 Step -1
 If Not (IsError(inarr(i, 4))) Then
   Worksheets.Add
   Cells(1, 1) = inarr(i, 1)
   Cells(1, 2) = inarr(i, 4)
   Exit For
 End If
 Next i
 End Sub

 

[DanteAmor]

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

Again you do not indicate where to paste.

The following pastes into sheet2 in cell A2

Sub copyvalues_2()
  Dim f As Range
  Dim i As Long
  
  With Sheets("Sheet1")
    Set f = .Range("D:D").Find("#N/A", , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then
      i = f.Row - 1
      .Range("A" & i & ",D" & i & ":E" & i).Copy Sheets("Sheet2").Range("A2")
    End If
  End With
End Sub

 

[RioDegenero]

try this:

Sub test()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastrow, 4))
For i = lastrow To 1 Step -1
 If Not (IsError(inarr(i, 4))) Then
   Worksheets.Add
   Cells(1, 1) = inarr(i, 1)
   Cells(1, 2) = inarr(i, 4)
   Exit For
 End If
 Next i
 End S
[QUOTE="offthelip, post: 6027642, member: 419487"]
try this:
[CODE=vba]
Sub test()
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastrow, 4))
For i = lastrow To 1 Step -1
 If Not (IsError(inarr(i, 4))) Then
   Worksheets.Add
   Cells(1, 1) = inarr(i, 1)
   Cells(1, 2) = inarr(i, 4)
   Exit For
 End If
 Next i
 End Sub

[/QUOTE]

Again you do not indicate where to paste.

The following pastes into sheet2 in cell A2

Sub copyvalues_2()
  Dim f As Range
  Dim i As Long
 
  With Sheets("Sheet1")
    Set f = .Range("D:D").Find("#N/A", , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then
      i = f.Row - 1
      .Range("A" & i & ",D" & i & ":E" & i).Copy Sheets("Sheet2").Range("A2")
    End If
  End With
End Sub

Thanks for your response and I wanted to copy to sheet 2 cells C5, C8,C10 and C12.

The code above is giving me error the error "variable not defined". Any suggestions?

 

[offthelip]

Two possible solutions:
1: delete the option explicit statement
2: add statements
Dim lastrow
Dim inarr

 

[DanteAmor]

Thanks for your response and I wanted to copy to sheet 2 cells C5, C8,C10 and C12.

I guess the order A in C5, B in C8, C in C10 and E in C12
But you need to be more specific so we don't have to guess what you want.

Try this:

Sub copyvalues_2()
  Dim f As Range
  Dim i As Long
  Dim sh2 As Worksheet
  
  Set sh2 = Sheets("Sheet2")
  
  With Sheets("Sheet1")
    Set f = .Range("D:D").Find("#N/A", , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then
      i = f.Row - 1
      sh2.Range("C5").Value = .Range("A" & i).Value
      sh2.Range("C8").Value = .Range("B" & i).Value
      sh2.Range("C10").Value = .Range("C" & i).Value
      sh2.Range("C12").Value = .Range("E" & i).Value
    End If
  End With
End Sub

 

[RioDegenero]

I guess the order A in C5, B in C8, C in C10 and E in C12
But you need to be more specific so we don't have to guess what you want.

Try this:

Sub copyvalues_2()
  Dim f As Range
  Dim i As Long
  Dim sh2 As Worksheet
 
  Set sh2 = Sheets("Sheet2")
 
  With Sheets("Sheet1")
    Set f = .Range("D:D").Find("#N/A", , xlValues, xlWhole, , , False)
    If Not f Is Nothing Then
      i = f.Row - 1
      sh2.Range("C5").Value = .Range("A" & i).Value
      sh2.Range("C8").Value = .Range("B" & i).Value
      sh2.Range("C10").Value = .Range("C" & i).Value
      sh2.Range("C12").Value = .Range("E" & i).Value
    End If
  End With
End Sub

Thanks

 

[DanteAmor]

Hi @RioDegenero :
To let other readers know which answer fixed this thread, you should mark the answer that actually fixed your problem as a solution.

Regards.

 

[RioDegenero]

Thanks

Hi @RioDegenero :
To let other readers know which answer fixed this thread, you should mark the answer that actually fixed your problem as a solution.

Hi @RioDegenero :
To let other readers know which answer fixed this thread, you should mark the answer that actually fixed your problem as a solution.

Regards.

Not quite, I guess that was because I wasn't clear from my initial post - apologies for that. I have just reposted in another thread making it very clear of the location and also what I wanted. I really appreciate your help

 

[RioDegenero]

It's been pointed out to me that I cannot re-post question. Can I ask the question again being very clear?