Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Cleaning | Excel Discussion (Misc queries) | |||
Help with cleaning up a workbook. | Excel Discussion (Misc queries) | |||
Cleaning up some VB | Excel Programming | |||
Help cleaning up some Strings | Excel Programming | |||
cleaning up a field | Excel Programming |