finding an error within a range and copying the first 2 values of that row onto a specific cell on another sheet

Gundisalvus

New Member
Joined
Jan 27, 2023
Messages
4
Office Version
  1. 365
Platform
  1. Windows
Hello all, I am pretty new at VBA and I have been given what I find to be a challenge.
I have to find a #N/A value within a column and when I find that value I have to copy the first 2 values of the row (ex: F85 - #N/A then copy A85, B85) and then paste it onto a diferent sheet onto a list and paste on the first blank cell.

the code I have is
VBA Code:
Sub NAFilter()
'Declare variables
    Dim DataRg As Range
    Dim A As Long
    Dim B As Long
'Set variables
    A = Worksheets("JE Royalty detail").UsedRange.Rows.Count
    C = Worksheets("DB").UsedRange.Rows.Count
'Start from A1
    If Application.WorksheetFunction.CountA(Worksheets("DB").UsedRange) = 0 Then C = 0
'Set range for Sheet1
    Set DataRg = Worksheets("JE Royalty detail").Range("F3:F93" & A)
    On Error Resume Next
    Application.ScreenUpdating = False
'For loop
    For B = 1 To DataRg.Count
'Value to be searched
        If CStr(DataRg(B).Value) = "#N/A" Then
'Copy cells
            DataRg(B).Copy Destination:=Worksheets("DB").Range("K" & C + 1)
            C = C + 1
        End If
    Next
    Application.ScreenUpdating = True
End Sub

but I think I'm not even close.
I need that he copy and pastes the values for each #N/A values, being those values formula errors and not strings.

Thank you for all the help
 

Attachments

  • fATgn95kJQ.jpg
    fATgn95kJQ.jpg
    209.1 KB · Views: 7
  • EXCEL_gcs89nL0sx.jpg
    EXCEL_gcs89nL0sx.jpg
    106.3 KB · Views: 7

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
VBA Code:
Sub Copy_NA_Values()

Dim rng As Range
Dim firstBlank As Range
Dim shtSource As Worksheet
Dim shtDestination As Worksheet

Set shtSource = ThisWorkbook.Sheets("Sheet1") 'Change to the name of the source sheet
Set shtDestination = ThisWorkbook.Sheets("Sheet2") 'Change to the name of the destination sheet

Set rng = shtSource.Range("F:F").SpecialCells(xlCellTypeFormulas, xlErrors)

For Each cell In rng
    If cell.Value = "#N/A" Then
        Set firstBlank = shtDestination.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
        cell.Resize(1, 2).Copy firstBlank
    End If
Next cell

End Sub
 
Upvote 0
really appreciated and this has helped me a long way but it is not copying the cells that I needed, it is copying the error cells but the ones I need are column A and B from that row. But this has really come a long way in helping me and I'm highly thankful
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,044
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top