Problem with variable VLOOKUP value

[Millerj64]

First off thanks for the help

I'm trying to use a variable for the VLOOKUP value. The below code works however it adds parenthesis around the VLOOKUP value, the result is it's not being recognized as a cell location. I have tried changing the variable types but have not had any luck.
This is an example of what gets put into the cell
"=VLOOKUP('a4','prod a'!$A$1:$C$800,2,FALSE)"

Need help in removing the parenthesis around the lookup value

Below is the code,
Dim i As Integer
Dim name As String

'User will select column to update via input box

Select Case ActiveCell.Value

Case "PROD A"
ActiveCell.Offset(1, 0).range("a1").Select

FinalRow = Cells(ROWs.count, 1).End(xlUp).row
For i = 2 To FinalRow

' to check that i changes (checked out and then commented out)
' MsgBox i

' add i to column A to get cell location for vlookup
name = "a" & i
' to check that cell location is correct (checked and commented out)
' MsgBox name

' two methods of naming vlookup value both result in parenthesis being added
ActiveCell.FormulaR1C1 = "=VLOOKUP(" & name & " ,'prod a'!R1C1:R800C3,2,FALSE)"
' ActiveCell.FormulaR1C1 = "=VLOOKUP(" & "a" & i & ",'prod a'!R1C1:R800C3,2,FALSE)"

ActiveCell.Offset(1, 0).range("A1").Select
Next i

' Case "PROD 20" Same code as above
' Case "PROD 21" ""
' Case "PROD 21" ""
' Case "PROD 21" ""

End Select

Thanks again

 

[BarryL]

instead of a for loop use for each as in

Dim rng As Range
 Dim name As String

 'User will select column to update via input box

 Select Case ActiveCell.Value

 Case "PROD A"
 ActiveCell.Offset(1, 0).Range("a1").Select

 FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
 For Each rng In Range("A2:A" & FinalRow)


 ActiveCell.FormulaR1C1 = "=VLOOKUP(" & rng.Value & " ,'prod a'!R1C1:R800C3,2,FALSE)"
 ' ActiveCell.FormulaR1C1 = "=VLOOKUP(" & "a" & i & ",'prod a'!R1C1:R800C3,2,FALSE)"

 ActiveCell.Offset(1, 0).Range("A1").Select
 Next rng

 ' Case "PROD 20" Same code as above
 ' Case "PROD 21" ""
 ' Case "PROD 21" ""
 ' Case "PROD 21" ""

 End Select

 

[Millerj64]

BarryL

Thanks for the quick turn around

I used your suggestion but now get a "Error 1004 - Application-defined or object defined error"

The for each works great, the vlookup doesn't like the rng.value

I'm using office 2010, could that be the problem?

 

[BarryL]

I don't think so

ActiveCell.Value = WorksheetFunction.VLookup(rng.Value, Range("A2:B" & FinalRow), 2, 0)

change the range as needed.

 

[Peter_SSs]

There are a few things in your code that look a little odd to me, but at least you shouldn't need to cycle through the rows one at a time.
Try something like this in a copy of your workbook.

Dim finalrow As Long

'User will select column to update via input box

finalrow = Cells(Rows.Count, 1).End(xlUp).Row
Range(ActiveCell.Offset(1), Cells(finalrow, ActiveCell.Column)).FormulaR1C1 = "=VLOOKUP(RC1,'" & ActiveCell.Value & "'!R1C1:R800C3,2,FALSE)"

 

[Millerj64]

Once again MrExcel SMEs assist the VBA challenged

I got it to work both ways

Thanks for all the help and appreciate your willingness to share your knowledge

Have a great weekend

 

[Peter_SSs]

You are very welcome. Thanks for the follow-up.