0

I'm trying to use VBA to create a macro that searches the "Analysis" sheet, column P, for "#N/A" text values. In the event that "N/A" text values are found in Column P, it pulls the corresponding cell data from that row's Column A and then instead of just pasting this info in the first available row at the bottom of Column A of the "Template" sheet, I need to insert a row for the info because it's being pasted into a table that has sums at the bottom. I'm new to VBA, any help would be appreciated! Also the data in column P may not start in P1, it maybe P5 or P7 or something depending on user formatting, ideally that doesn't matter.

Sub ADDNA()

Sheets("Analysis").Select
Table1 = Worksheets("Analysis").Range("P:P")
Table2 = Worksheets("Analysis").Range("A:P")

For Each cl In Table1
        If cl.Value = "#N/A" Then
        Worksheets("Template").Select
        Worksheets("Template").Range("A3").End(xlDown).Select
        Set Target = ActiveCell
        Target.Offset(1).EntireRow.Insert shift:=xlUp
        Worksheets("Template").Range("A3").End(xlDown) = Application.WorksheetFunction.VLookup(cl, Table2, 16, 0)
        End If
Next cl

End Sub
cletus
  • 3
  • 3
  • `#N/A` is not a text value, but an error value. You can use `WorksheetFunction.IsNA` to test if a cell is an `#N/A` error. – BigBen Nov 04 '21 at 16:02
  • No, the #N/As are text in this case. – cletus Nov 04 '21 at 16:06
  • What then is the specific issue you're facing? – BigBen Nov 04 '21 at 16:06
  • I'm currently getting a 424 Object Required error for the If Statement, any ideas on that? – cletus Nov 04 '21 at 16:42
  • Add `Set` in front of `Table1 = ...` and `Table2 =`. Also, you probably should [find the last row first](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba). Otherwise you're looping all the way to the end of the worksheet. – BigBen Nov 04 '21 at 16:44
  • Also add `Option Explicit` to the top of the module, and start declaring all variables. `Dim Table1 as Range, Table2 As Range, cl As Range`. – BigBen Nov 04 '21 at 16:45
  • I used Option Explicit for tables, cl, Target, as range. I'm confused by your comment on finding the last row first, are you referring to in the table ranges or in the loop? I'm now getting a type mismatch error with cl. – cletus Nov 04 '21 at 18:44
  • `Worksheets("Analysis").Range("P:P")` - this is more than one million cells. You probably don't want to loop over that many cells. – BigBen Nov 04 '21 at 18:47
  • Okay so it is now: `Set Table1 = Worksheets("Analysis").Range("P" & LastRow) Set Table2 = Worksheets("Analysis").Range("A:P" & LastRow)` The Table2 Set reference is getting a run time error? – cletus Nov 04 '21 at 19:10
  • `Range("P1:P" & LastRow)`... `Range("A1:P" & LastRow)`. You need a starting row too, probably for both. – BigBen Nov 04 '21 at 19:24
  • That was it, it needed a starting row but just for the "A1:P" reference. Now it's running smoothly up to `If cl.Value = "#N/A" Then` and it's getting a runtime error from a type mismatch. My values in that column are text and numerical and the cells are formatted general. There's no formula references in the cells just text. – cletus Nov 04 '21 at 19:29
  • If the `#N/A`s are the result of a formula, even if you paste values, they're still error values, not text. You can use `=ISNA(...)` in the worksheet to confirm. – BigBen Nov 04 '21 at 19:32
  • Thanks that fixed it, now all of my module is working except the last print, `Worksheets("Template").Range("A3").End(xlDown) = Application.WorksheetFunction.VLookup(cl, Table2, 16, 0)` but I know that that VLookup is wrong, it's trying to reference cl, which is column P w the N/A values, when actually I want to reference and vlookup the data from column A which corresponds to the N/As in column P. Any ideas how to fix, maybe some sort of Index Match instead of VLookup? But I am unsure. – cletus Nov 04 '21 at 21:33
  • Try using `Worksheets("Analysis").Range("A" & cl.Row)` instead of `cl`. – BigBen Nov 05 '21 at 12:25
  • Thanks that worked! Now everything is working as intended except it's not looping through the for loop, it's only doing the first iteration. My sample data should have 4 pastes at the bottom, it's only pasting once. For reference: `For Each cl in Table 1 If IsError(cl) Then Worksheets("Template").Select Worksheets("Template").Range("A3").End(xlDown).Select Set Target = ActiveCell Target.Offset(EntireRow.Insert shift:=xlUp Worksheets("Template").Range("A3").End(xlDown).Offset(1,0) = Worksheets("Analysis").Range("A" & cl.Row) End If Next cl` – cletus Nov 05 '21 at 13:46
  • My guess is that you're somehow overwriting... use F8 to step through. – BigBen Nov 05 '21 at 14:01
  • Ah it's not an overwrite error it's an error that my range is only 1 cell. I have `Set Table1 = Worksheets("Analysis").Range("P" & LastRow)` which is setting the range as the single cell last row in column P. Do you have an idea of how to tweak this range so that it's inclusive, starting at P1 and ending at LastRow? – cletus Nov 05 '21 at 17:02
  • `Set Table1 = Worksheets("Analysis").Range("P1:P" & LastRow)`. – BigBen Nov 05 '21 at 17:02

1 Answers1

0

Solution below. Thanks for the help!

Sub Add_NAs()
Sheets("Analysis").Select
With Sheets("Analysis")
    LastRow = .Range("P" & .Rows.Count).End(xlUp).Row
End With
    
Set Table1 = Worksheets("Analysis").Range("P1:P" & LastRow)
Set Table2 = Worksheets("Analysis").Range("A1:P" & LastRow)

For Each cl In Table1
        If IsError(cl) Then
        Worksheets("Template").Select
        Worksheets("Template").Range("A3").End(xlDown).Select
        Set Target = ActiveCell
        Target.Offset().EntireRow.Insert shift:=xlUp
        Worksheets("Template").Range("A3").End(xlDown).Offset(1, 0) = Worksheets("Analysis").Range("A" & cl.Row)
        End If
Next cl

End Sub
cletus
  • 3
  • 3
  • Side note: the `#N/A` values are not actually text. They're error values. You don't need VBA to demonstrate that: use `ISNA` or `ISERROR` formulas to test. If they're truly text, then `ISNA` and `ISERROR` return `FALSE`. Otherwise, they are error values. – BigBen Nov 05 '21 at 19:05