VBA VLookUp...Help needed

[Sprucy]

Hi All,

I've read about 60 post and still no luck!

I need to run a VLookUp via VBA.


Currently using this:
Note:Cell T10 and D10 are in Worksheet "WebDrop"
and A2-K10000 are in "Airports...

T10=VLOOKUP(D10,Airports!$A$2:$K$10000,8,FALSE)

Which I copied from T10.... T1500, my range varies daily between 600-1200 rows and my array table might increase one day...so I want to capture a type of 65536.End(xlUp) formula


Now, in VBA I want something like this...
Do use it, it's just a free flow example..
I've tried about 10 codes plus many modification, I'm losing it!

--------------------
Sub helpPlease()

Sheets("WebDrop").Selects ' need this line, do to previous macros...

With T10:T65536.End(xlUp) ' need my results in these cells

Formula."=vlookup(WebDrop!D10:D65536.End(xlUp),Airports'!A2:K10000,8,False)"
'want to change A2:K10000 to K65536.End(xlUp) if possible..

End With
End Sub
-------------------------

Any other approach are welcomed...

Cheers,
Sprucy

 

[Norie]

Sprucy

What do you actually want to do?

I'm afraid your post (and title) aren't particularly clear.

Do you want to use VBA to put that formula in a range?

T10=VLOOKUP(D10,Airports!$A$2:$K$10000,8,FALSE) 
Dim ws As Worksheet
Dim LastRow As Long

Set ws = Worksheets("WebDrop")

Set LastRow = ws.Range("D65536").End(xlUp).Row

ws.Range("T10:T" & LastRow) = "T10=VLOOKUP(D10,Airports!$A$2:$K$10000,8,0)"

 

[bk]

I didn't follow every word

of your post, but as I took it, you know how to write the VLOOKUP and have VBA put it in as a formula where you want it. Your only trouble was finding the end of the lookup range and then, inserting that into the formula. Let me know if this isn't correct.

When I've done similar stuff, I make the last cell into a variable prior to putting the formula in. And then, concatenating this variable into my formula.

Such as:

'select an appopriate cell first
myEndCell = Selection.End(xlDown).Select
..... .Formula = "=VLOOKUP(."=vlookup(WebDrop!D10:" & myEndCell & ",Airports'!A2:K10000,8,False)"

Is that the type of answer you were needing? And, of course, if you for some reason only need the row, you can insert a

myEnd Row = Selection.Row

in above the formula and then modify the formula assignment so that the correct column is inside the quotes after the colon and your myEndRow variable in instead of the entire cell as in using myEndCell.

HTH

 

[Sprucy]

Hope this makes more sense...

OK, perhaps my worst explanation ever, sorry.

The code below worked OK, but I no longer need to Remote Lookup, I've moved the Airports sheets into the same Workbook as WebDrod.

Hope this is clearer.
-----------------------------

Public Sub PHRLookup()

' DO A LOOKUP ON STRING VALUE "D10:D"
MsgBox RemoteVlookUp("WebDrop!D10:D" & Range("D65536").End(xlUp).Row)

End Sub

Public Function RemoteVlookUp(SrcValue) As String

Dim FullPath As String
Dim WBname As String
Dim ShName As String
Dim SourceRng As String
Dim ReturnColNum As Integer

'CONFIGURE VLOOKUP HERE !!!
FullPath = "F:\Macros\"
WBname = "Airport_PHR.xls"
ShName = "Airports"
SourceRng = "R2C1:R10000C10" 'Row1 Column 1 to Row 10000 Column 10
ReturnColNum = 8 ' column to get value from


RemoteVlookUp = ExecuteExcel4Macro("VLOOKUP(""" & SrcValue & _
""",'" & FullPath & _
"[" & WBname & "]" & _
ShName & "'!" _
& SourceRng & "," _
& ReturnColNum & ",FALSE)")

End Function
---------------
Sprucy

 

[Norie]

Sprucy

I'm afraid I still don't understand what you mean.

Perhaps you could explain in words?

Did you see/try the code I posted?

 

[bk]

I don't know what that is

I've never heard of 'Remote VLookup' and didn't see it in Help.

So, I may not be following you. The VLOOKUP formula (and coding it) works identically whether the data range is in the same or different workbook, same or different worksheet. Granted, you have to do a little additonal specification when it's in a different sheet or workbook, but it's no different than any other reference/link in that regard.

So, I don't know if I'm of help or will be or not. Just throwing this out there, to put a VLOOKUP formula (or any formula) into a cell, you do something like this:

'example to put VLOOKUP formula in cell A1 of the activated sheet, using your formula
myEndCell = Selection.End(xlDown).Select 
ActiveSheet.Range("a1").Formula = "=VLOOKUP(WebDrop!D10:" & myEndCell & ",Airports'!A2:K10000,8,False)"

If your Active Sheet is 'Web Drop' or 'Airports' then you can just omit their names, the single quotes on either side of their names and the exclamation point that follows their names.

Not sure if I'm helping you at all or not. I'm trying, though. Take care.

 

[bk]

Wait a minute, stop the presses

your VLOOKUP formula has a range for the VLOOKUP's lookup value?

I'm not sure how or if this fits into your problem or my confusion.

I was so focused on the other stuff that I didn't catch this earlier, even when I was pasting it.

 

[Norie]

bk

Your VLOOKUP formula is incorrect and as far as I can see would cause an Application defined... error..

You seem to be trying to use a range for the first argument.

The first argument in a VLOOKUP is the (single) value to be looked up.

By the way the RemoteVlookup is the function that Sprucy is in the code posted. I think it might be to lookup in a closed workbook.

 

[Sprucy]

Don't give up on me...

Hi Norie & bk,

Norie you are right about the "closed" workbook. That code was a draft, but it does work with a bit of tweaking.

Norie,

About your first code, it didn't work for me, I guess I failed to complete it properly.

I'll try to simplify this:
Sheets : Alpha & Beta

Alpha:
Column A is blank,
Column B: B1=VLOOKUP(A1,Beta!$A$1:$B$10,2,FALSE) ** from B1 to B100 **
B2=............(B2,Beta!$A$1:$B$10,2,FALSE)
Note: Current results in B1 to B10 show N/A#, because A1 to A10 are blank.

I would run a web query/macro, data fill A1 to A10, instantly B1 to B10 would return the correct value from the lookup table in Beta.
NOTE: Rows vary daily, from Row 10 to 33 ???

Beta:
A B
1 I
2 II
3 III
4 IV
5 V... all the way to 10 X

By using a macro, column B in Alpha would be blank, the Vlookup would populate the cells with the Roman # once the macro would run.

Because I have about 10 macros that rely on the "Call" code, I need to code to look like:
--------------------
Sub NoriesMagicLookUp()

worksheets("Alpha").select

'insert the VlookUp formula in B1 down to last active row based on data found in A1:A65536.End.(xlup)

end sub
------------

Thanks a million for your time in this matter..

 

[Norie]

Are you doing this to get a formula for each line of data returned by a web query?

If you are you don't need code.

There is an option to fill down adjacent formulas to the data returned.

How did you try my code?