Cleaning up the syntax
 

Hi,

I have 2 lines of code Which works perfectly fine

Cells(1, 1) = "=VLookup(" & """" & sheeetname & """" & " , m7:n" & opi & ",
2, False)"
Worksheets("" & Cells(1, 1) & "").Select

Can I somehow reduce them to one line of code by not using cells(1,1) as an
intermediary.

I tried the following variations

a) Worksheets("" & Application.VLookup("""" & sheeetname & """", "m7:n" &
opi, 2, False) & "").Select

b) Worksheets(Application.VLookup("""" & sheeetname & """", "m7:n" & opi,
2, False)).Select

c) Worksheets(Application.VLookup(""" & sheeetname & """, "m7:n" & opi, 2,
False)).Select

But getting Run-time error 13, type mismatch.

Please guide me.

Thanks a lot,
Hari
India

Cleaning up the syntax
 

Hi,

If possible please also tell me the logic of constructing such syntaxes.

I tend to use lot of intermediary cells because of syntax problems
especially where the apostrophe creates problems .

Thanks a lot,
Hari
India

Cleaning up the syntax
 

Somebody might prove me wrong, but I don't think you can combine these 2
lines into one, but the question
is do you really need to select the cell. Probably not.
It would simplify matters a lot if you gave the lookup range a name:

Cells(1).Formula = "=VLOOKUP(""opi"", LookupTable, 2, FALSE)"

Another option is to do the lookup in VBA and have no formula in the cell,
but just the value:

Cells(2).Value = WorksheetFunction.VLookup("opi", Range("LookupTable"),
2, False)


RBS


"Hari Prasadh" wrote in message
...
Hi,

I have 2 lines of code Which works perfectly fine

Cells(1, 1) = "=VLookup(" & """" & sheeetname & """" & " , m7:n" & opi &
", 2, False)"
Worksheets("" & Cells(1, 1) & "").Select

Can I somehow reduce them to one line of code by not using cells(1,1) as
an intermediary.

I tried the following variations

a) Worksheets("" & Application.VLookup("""" & sheeetname & """", "m7:n" &
opi, 2, False) & "").Select

b) Worksheets(Application.VLookup("""" & sheeetname & """", "m7:n" & opi,
2, False)).Select

c) Worksheets(Application.VLookup(""" & sheeetname & """, "m7:n" & opi,
2, False)).Select

But getting Run-time error 13, type mismatch.

Please guide me.

Thanks a lot,
Hari
India

Cleaning up the syntax
 

RBS

As I understand it from Hari's code, he's not trying to select the cell, but
use the value returned from the VLOOKUP to return a sheet name which he then
wants to select. (Activate)

I think it might be a little clearer though if we could see a little more of
the code, like what type are worksheeet or opi

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"RB Smissaert" wrote in message
...
Somebody might prove me wrong, but I don't think you can combine these 2
lines into one, but the question
is do you really need to select the cell. Probably not.
It would simplify matters a lot if you gave the lookup range a name:

Cells(1).Formula = "=VLOOKUP(""opi"", LookupTable, 2, FALSE)"

Another option is to do the lookup in VBA and have no formula in the cell,
but just the value:

Cells(2).Value = WorksheetFunction.VLookup("opi", Range("LookupTable"),
2, False)


RBS


"Hari Prasadh" wrote in message
...
Hi,

I have 2 lines of code Which works perfectly fine

Cells(1, 1) = "=VLookup(" & """" & sheeetname & """" & " , m7:n" & opi &
", 2, False)"
Worksheets("" & Cells(1, 1) & "").Select

Can I somehow reduce them to one line of code by not using cells(1,1) as
an intermediary.

I tried the following variations

a) Worksheets("" & Application.VLookup("""" & sheeetname & """", "m7:n" &
opi, 2, False) & "").Select

b) Worksheets(Application.VLookup("""" & sheeetname & """", "m7:n" &
opi, 2, False)).Select

c) Worksheets(Application.VLookup(""" & sheeetname & """, "m7:n" & opi,
2, False)).Select

But getting Run-time error 13, type mismatch.

Please guide me.

Thanks a lot,
Hari
India

Cleaning up the syntax
 

Hi Nick and RBS,

Thnx a lot for your posts.

Please find some details .

Sub readingarrayofuniquewords(sheeetname As String)
Dim opi As Integer

Workbooks(s).Activate
Worksheets("Input Sheet").Activate
Range("m65536").Select
Selection.End(xlUp).Select
opi = Selection.Row

Cells(1, 1) = "=VLookup(" & """" & sheeetname & """" & " , m7:n" & opi & ",
2, False)"
Worksheets("" & Cells(1, 1) & "").Select

....
....
....
...

End Sub

Basically "Input Sheet" contains a table (2 columns and many rows). 1st
column contains a list of -- sheeetname As String -- while the second column
contains names of some of the Worksheets within -- Workbooks(s).Activate --

Presently to look up a Worksheet corresponding to a Sheeetname, Im storing
the value of vlookup within cells(1,1) and then Im trying to activate the
worksheet whose name is in Cells
(1,1).


Thanks a lot,
Hari
India

Cleaning up the syntax
 

Hari,

As it stands you I don't believe that you can combine that code. The reason
for this is that sheetname variable must contain a sheetname and cell
reference, something like Sheet1!A1. When you co0mbine the lines, as you
have deduced you need to use the VLOOKUP function, not just build it, so you
n eed to pass a valid range as the argument, and sheetname is not a valid
range.

If you have 2 variables, say sheetname with the sheetname (Sheet1), and
rangename with the cell address (A1), you can use

Worksheets(Application.VLookup(Worksheets(sheetnam e).Range("A1"),
Range("M7:N" & opi), 2, False)).Select


or if you have a range ob ject pointing at A1 on Sheet1, you can use

Worksheets(Application.VLookup(myRng,Range("M7:N" & opi), 2, False)).Select


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Hari Prasadh" wrote in message
...
Hi,

I have 2 lines of code Which works perfectly fine

Cells(1, 1) = "=VLookup(" & """" & sheeetname & """" & " , m7:n" & opi &
",
2, False)"
Worksheets("" & Cells(1, 1) & "").Select

Can I somehow reduce them to one line of code by not using cells(1,1) as
an
intermediary.

I tried the following variations

a) Worksheets("" & Application.VLookup("""" & sheeetname & """", "m7:n" &
opi, 2, False) & "").Select

b) Worksheets(Application.VLookup("""" & sheeetname & """", "m7:n" & opi,
2, False)).Select

c) Worksheets(Application.VLookup(""" & sheeetname & """, "m7:n" & opi,
2,
False)).Select

But getting Run-time error 13, type mismatch.

Please guide me.

Thanks a lot,
Hari
India

Cleaning up the syntax
 

Hari

First you should have defined opi as a Long, as integer can only hold
numbers up to 32767 and as there are 65536 rows in Excel you should use a
variable capable of holding the maximum.

You also have no need in most cases to select anything in Excel. So you can
cut out all of you Select...Selection pairs. This code...

Workbooks(s).Activate
Worksheets("Input Sheet").Activate
Range("m65536").Select
Selection.End(xlUp).Select
opi = Selection.Row

Could become...

Dim opi as Long
opi = Workbooks(s).Worksheets("Input Sheet").Range("M65536").End(xlUp).Row

(In case it wraps, the line opi..... is on one line, no selection, no
activation, etc)

Still thinking of a way of doing what you want in the rest

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"Hari Prasadh" wrote in message
...
Hi Nick and RBS,

Thnx a lot for your posts.

Please find some details .

Sub readingarrayofuniquewords(sheeetname As String)
Dim opi As Integer

Workbooks(s).Activate
Worksheets("Input Sheet").Activate
Range("m65536").Select
Selection.End(xlUp).Select
opi = Selection.Row

Cells(1, 1) = "=VLookup(" & """" & sheeetname & """" & " , m7:n" & opi &
", 2, False)"
Worksheets("" & Cells(1, 1) & "").Select

...
...
...
..

End Sub

Basically "Input Sheet" contains a table (2 columns and many rows). 1st
column contains a list of -- sheeetname As String -- while the second
column contains names of some of the Worksheets within --
Workbooks(s).Activate --

Presently to look up a Worksheet corresponding to a Sheeetname, Im storing
the value of vlookup within cells(1,1) and then Im trying to activate the
worksheet whose name is in Cells
(1,1).


Thanks a lot,
Hari
India

Cleaning up the syntax
 

Nick,

Yes, I had not read the post properly.
This one line of code would I think do what is needed:

Sheets(WorksheetFunction.VLookup("LookupValue", _
Range("LookupTable"), _
2, _
False)).Activate

RBS


"Nick Hodge" wrote in message
...
RBS

As I understand it from Hari's code, he's not trying to select the cell,
but use the value returned from the VLOOKUP to return a sheet name which
he then wants to select. (Activate)

I think it might be a little clearer though if we could see a little more
of the code, like what type are worksheeet or opi

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"RB Smissaert" wrote in message
...
Somebody might prove me wrong, but I don't think you can combine these 2
lines into one, but the question
is do you really need to select the cell. Probably not.
It would simplify matters a lot if you gave the lookup range a name:

Cells(1).Formula = "=VLOOKUP(""opi"", LookupTable, 2, FALSE)"

Another option is to do the lookup in VBA and have no formula in the
cell, but just the value:

Cells(2).Value = WorksheetFunction.VLookup("opi",
Range("LookupTable"), 2, False)


RBS


"Hari Prasadh" wrote in message
...
Hi,

I have 2 lines of code Which works perfectly fine

Cells(1, 1) = "=VLookup(" & """" & sheeetname & """" & " , m7:n" & opi &
", 2, False)"
Worksheets("" & Cells(1, 1) & "").Select

Can I somehow reduce them to one line of code by not using cells(1,1) as
an intermediary.

I tried the following variations

a) Worksheets("" & Application.VLookup("""" & sheeetname & """", "m7:n"
& opi, 2, False) & "").Select

b) Worksheets(Application.VLookup("""" & sheeetname & """", "m7:n" &
opi, 2, False)).Select

c) Worksheets(Application.VLookup(""" & sheeetname & """, "m7:n" & opi,
2, False)).Select

But getting Run-time error 13, type mismatch.

Please guide me.

Thanks a lot,
Hari
India

Cleaning up the syntax
 

RBS

Yes checks out for me. In this instance I generally use Range.Find rather
than worksheet functions as I find it easier to trap the error when it can't
be found, but hey...more than one way to skin a cat ;-)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"RB Smissaert" wrote in message
...
Nick,

Yes, I had not read the post properly.
This one line of code would I think do what is needed:

Sheets(WorksheetFunction.VLookup("LookupValue", _
Range("LookupTable"), _
2, _
False)).Activate

RBS


"Nick Hodge" wrote in message
...
RBS

As I understand it from Hari's code, he's not trying to select the cell,
but use the value returned from the VLOOKUP to return a sheet name which
he then wants to select. (Activate)

I think it might be a little clearer though if we could see a little more
of the code, like what type are worksheeet or opi

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"RB Smissaert" wrote in message
...
Somebody might prove me wrong, but I don't think you can combine these 2
lines into one, but the question
is do you really need to select the cell. Probably not.
It would simplify matters a lot if you gave the lookup range a name:

Cells(1).Formula = "=VLOOKUP(""opi"", LookupTable, 2, FALSE)"

Another option is to do the lookup in VBA and have no formula in the
cell, but just the value:

Cells(2).Value = WorksheetFunction.VLookup("opi",
Range("LookupTable"), 2, False)


RBS


"Hari Prasadh" wrote in message
...
Hi,

I have 2 lines of code Which works perfectly fine

Cells(1, 1) = "=VLookup(" & """" & sheeetname & """" & " , m7:n" & opi
& ", 2, False)"
Worksheets("" & Cells(1, 1) & "").Select

Can I somehow reduce them to one line of code by not using cells(1,1)
as an intermediary.

I tried the following variations

a) Worksheets("" & Application.VLookup("""" & sheeetname & """", "m7:n"
& opi, 2, False) & "").Select

b) Worksheets(Application.VLookup("""" & sheeetname & """", "m7:n" &
opi, 2, False)).Select

c) Worksheets(Application.VLookup(""" & sheeetname & """, "m7:n" &
opi, 2, False)).Select

But getting Run-time error 13, type mismatch.

Please guide me.

Thanks a lot,
Hari
India

Cleaning up the syntax
 

Hi RBS,

Thnx a lot for your kind help. I substituted the required arguments and it
works now.

Worksheets(WorksheetFunction.VLookup(sheeetname, Range("m7:n" & opi), 2,
False)).Select

Thanks a lot,
Hari
India

"RB Smissaert" wrote in message
...
Nick,

Yes, I had not read the post properly.
This one line of code would I think do what is needed:

Sheets(WorksheetFunction.VLookup("LookupValue", _
Range("LookupTable"), _
2, _
False)).Activate

RBS


"Nick Hodge" wrote in message
...
RBS

As I understand it from Hari's code, he's not trying to select the cell,
but use the value returned from the VLOOKUP to return a sheet name which
he then wants to select. (Activate)

I think it might be a little clearer though if we could see a little more
of the code, like what type are worksheeet or opi

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"RB Smissaert" wrote in message
...
Somebody might prove me wrong, but I don't think you can combine these 2
lines into one, but the question
is do you really need to select the cell. Probably not.
It would simplify matters a lot if you gave the lookup range a name:

Cells(1).Formula = "=VLOOKUP(""opi"", LookupTable, 2, FALSE)"

Another option is to do the lookup in VBA and have no formula in the
cell, but just the value:

Cells(2).Value = WorksheetFunction.VLookup("opi",
Range("LookupTable"), 2, False)


RBS


"Hari Prasadh" wrote in message
...
Hi,

I have 2 lines of code Which works perfectly fine

Cells(1, 1) = "=VLookup(" & """" & sheeetname & """" & " , m7:n" & opi
& ", 2, False)"
Worksheets("" & Cells(1, 1) & "").Select

Can I somehow reduce them to one line of code by not using cells(1,1)
as an intermediary.

I tried the following variations

a) Worksheets("" & Application.VLookup("""" & sheeetname & """", "m7:n"
& opi, 2, False) & "").Select

b) Worksheets(Application.VLookup("""" & sheeetname & """", "m7:n" &
opi, 2, False)).Select

c) Worksheets(Application.VLookup(""" & sheeetname & """, "m7:n" &
opi, 2, False)).Select

But getting Run-time error 13, type mismatch.

Please guide me.

Thanks a lot,
Hari
India