Vlookup from other Sheet vba

[vikas_newports]

IN VBA , I want to use vlookup from the other sheet within the same workbook but I do not want to use worksheet name (DOVE) in formula
Currently, I am using the below formula and it is working fine

 Range("C2").Formula = "=VLOOKUP(B2,DOVE!$C$14:$C$1048576,1,0)"

 

[Fluff]

What are you going to use, if not the sheet name?

 

[vikas_newports]

Fluff, i think we can use sheet number, so in my data, the dove sheet is the 2nd sheet ... how can we use it as worksheet(2) or sheets(2)

 

[Fluff]

You are far better off using the sheet name, if somebody moves the sheet then you will look at the wrong sheet.

 

[vikas_newports]

you are right but I am afraid if the user change the sheet name from Dove to TOVE then again it will be failed while the sheet number will remain same until unless someone deleted the Dove sheet and create new sheet with the name of Dove

 

[Fluff]

The sheet number is just a reference to where the sheet is in the workbook.
It may be the 2nd sheet now, but if somebody moves it to the 3rd sheet, then it will fail.

 

[Fluff]

Another thing, is that the whatever you use to identify the sheet in the code, it will need to be converted to the sheet name for the formula to work.

 

[vikas_newports]

Fluff, as per the image it is sheet2 while If I move the position of this sheet to last it will remain Sheet2

 

[vikas_newports]

I think we can use variable in formula ... also I found below but dont know how to use
below is the example code

vAns = Application.WorksheetFunction.VLookup(activecell.Offset(0,-10), Workbooks(2).Sheets(1).Range("A:A"), 1, False)

 

[Fluff]

What you said here

how can we use it as worksheet(2) or sheets(2)

Has nothing to do with the codename Sheet2

Also the formula you posted is completely different again.
However you refer to the sheet in VBA you still need to convert it to DOVE! for the formula to work in Excel.