Solved: select and copy cells through VBA

[MrTinkertrai]

Hi all,

I've got the following I want to achieve:

In cells A2:A41 i've got a list of 40 entries
In cells G2:G41 I have a VLOOKUP formula.
Some cells in G2:G41 give a #N/B when the VLOOKUP formula is applied.

I would like to make a macro which automatically copies the contents of the cells in A2:A41, whenever that particular cell in col G gives an #N/B.

Example:
Let's say in cells G23, G30, G35 and G38 give an # N/B.
I would like to automatically copy the contents of cell A23, A30, A35 and A38 copied starting in cell A44.

This one has kept me busy for a few days now, but I can't figure it out how to do it.
I hope I've made clear what I like to get done and I hope someone can help me out with this one.

Thanks in advance

Greetz,
Mike

[Bartek]

Hi,

...I would like to automatically copy the contents of cell A23, A30, A35 and A38 copied starting in cell A44.

Try something like this:

Sub XFill()
Dim Xi1 As Integer, Xi2 As Integer
Xi2 = 44
For Xi1 = 2 To 41
 If IsError(Cells(Xi1, 7).Value) Then
  Range("A" & Xi1).Copy Range("A" & Xi2)
  Xi2 = Xi2 + 1
 End If
Next Xi1
End Sub

[IrishCharm]

Hi,

Try the below - its pretty simple. basically what it does is if the vlookup fails then return cell A. i just posted the vlookup to look at cell A for convenience sake.

You can also run the formula for, say, if the vlookup = "#N/B" then return cell A else do the vlookup.

To get the below format for formulas you need to record a new macro and record the formulae this way.

Sarah

[VBA]Range("G2").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(VLOOKUP(RC[-1],R2C1:R41C1,1,0)),RC[-6],VLOOKUP(RC[-1],R2C1:R41C1,1,0))"
'' IF THERE IS AN ERROR IN WHATEVER YOU VLOOUKP THEN RETURN CELL A ELSE RETURN THE VLOOKUP.

ActiveCell.Copy
Do Until ActiveCell.Offset(0, -6) = ""
Selection.PasteSpecial Paste:=xlPasteFormulas
ActiveCell.Offset(1, 0).Select
Loop
ActiveSheet.Calculate
Range("a1").Select[/VBA]

[MrTinkertrai]

Hello Bartek and KinkyKuddles,

Thanx for your quick replies.
I'm at work now, but I will try your suggestions tonight when i'm at home.
I'll let you know if it worked out allright.
Thanx so far

Edit: Yeahhhh, it worked
Barteks solution did the trick.
I keep KinkyKuddles solution for future reference
Both thanks again for your contribution