Short IsError/If statement question

[hitbid]

Hi
I figured out how to use the IsError function this morning to help me find errors in the vlookup.
So now what I am hoping to do is find all of the cells NEXT to the error,(using a simple offset), which in this case are stock tickers, and then list them out in a new range/area off to the side.

I am just having trouble figuring out how to nest another If statement inside the one already built to then paste the offset value to another place.
Recently new at this, so 9x out of 10 i've been able to solution by looking and testing, but still not great with looping and nesting if's.

Any help is appreciated.

Sub Test2()
Dim Cell As Range
Dim CellRange As Range
Set CellRange = Range("C1:C4")
For Each Cell In CellRange

   If IsError(Cell) Then
    Cell.Offset(0, -1).Copy
    End If
    
    Next Cell

End Sub

The Excel sheet looks something like:
Col B Col C
AAPL $100
ABT $ 50
CAT #N/A
DE $40

I need to capture CAT since it has no price and paste it in Column F.

 

[hitbid]

OH. See, I just had to keep working on it to find the answer. Reviewed some other posts, found it. So last question - it looks like the "Destination:=" option replaces the Paste function? I've been recording and pasting the Paste Special values when I do things. This is WAY cleaner. Does the destination let you define if you want to place it as a value/with format/etc?

Sub Test2()
Dim Cell As Range
Dim CellRange As Range
Set CellRange = Range("C1:C7")
For Each Cell In CellRange
   If IsError(Cell) Then
    Cell.Offset(0, -1).Copy Destination:=Cell.Offset(0, 3)
    End If
 Next Cell

End Sub

 

[MARK858]

Does the destination let you define if you want to place it as a value

No you can't use it to place it as values. You can drop the "Destination:=" though

Cell.Offset(0, -1).Copy Cell.Offset(0, 3)

does the same, if you want it as values try

Cell.Offset(0, 3).Value = Cell.Offset(0, -1).Value

 

[hitbid]

Wow, excellent. Even cleaner. Thank you.

So how could I type this out if I did need to paste values or something?

 

[MARK858]

See my edit to my last post.

 

[hitbid]

Thanks again Mark858.

So I am trying to place this information on a new sheet. I got this far. But when I paste the tickers that are problematic onto Sheet2, it keeps pasting them into Column B, while I clearly have Range A1 marked as the area. I haven't offset the column by 1, so kinda puzzled.

Sub test4()
Dim OneRange As Range
Dim TwoRange As Range
Dim i As Integer
Dim LastRw As Long
Dim LastRwb As Long
Set OneRange = Sheets("Sheet1").Range("C1:C7")
Set TwoRange = Sheets("Sheet2").Range("A1")
Sheets("Sheet1").Activate
LastRw = OneRange.Cells(Rows.count, 1).End(xlUp).Row
LastRwb = TwoRange.Cells(Rows.count, "A").End(xlUp).Row + 1

    For i = 1 To LastRw
        If IsError(OneRange.Cells(i, 1)) Then
            Rows(i).Copy Destination:=TwoRange.Cells(LastRwb, "A")
            LastRwb = LastRwb + 1
        End If
    Next

End Sub

 

[hitbid]

Didn't notice an edit option. The routine works, but it takes the errors from OneRange and places them into the TwoRange sheet in column B. I am just not understanding how to target the correct column. I changed a few things, but keeps going to B.

 

[MARK858]

Try (untested)

Sub test4()
    Dim OneRange As Range
    Dim TwoRange As worksheet
    Dim i As Long
    Dim LastRw As Long
    Dim LastRwb As Range
    Set OneRange = Sheets("Sheet1").Range("C1:C7")
    Set TwoRange = Sheets("Sheet2")
    'Sheets("Sheet1").Activate
    LastRw = OneRange.Cells(Rows.Count, 1).End(xlUp).Row
    LastRwb = TwoRange.Cells(Rows.Count, "A").End(xlUp)

    For i = 1 To LastRw
        If IsError(OneRange.Cells(i, 1)) Then
            Rows(i).Copy LastRwb.Offset(1)
        End If
    Next

End Sub

 

[MARK858]

Btw, I just tested your code as posted in post #6 and it put the data in Column A not B. What do you have in Column A of Sheet1?

 

[hitbid]

Good Question. I just had the sample data up, was just running it based on what I saw. You're right, if I move the tickers to A and the price vlookup to B, change the range, it works.
Your code kept telling me object variable or With block variable not set for lastrwb. I don't know.
Moving the data worked.