VLOOKUP in VBA returns #Name?

I have a line is a VBA script that reads:

"=IF(ISNA(VLOOKUP(RC[-24],M2M!A:L,9,FALSE)),"" "",VLOOKUP(RC[-24],M2M!A:L,9,FALSE))"

When I execute the code, this returns #Name?
If I then go to that cell, hit Edit (F2), and then hit Enter, it returns the actual value that I expect. Can anyone help me fix this in the VBA code? As it is now, the user runs the VBA, and then has to go to that cell, hit Edit, hit Save, and copy the formula to the bottom of the range. I'm stumped.

TIA,
Theresa


Hi all, i have managed to work out how to do a vlookup in VBA which helps me check some criteria before moving on with the rest of the code, at this moment in time it looks for a name and a date in a named range and if it exists bring up a MsgBox..............I'm just having one problem with it, and that is the vlookup stops at the first match it comes across, so if my named range looked like this: (a & b are the columns)
A.........B
Emma 14/7/06
Cheryl 15/7/06
Lauren16/7/06
Cheryl 14/7/06
If with my userform i look for Emma 14/7/06 the vlookup will find it no problem if i go back to my userform and choose a new name "Cheryl" 14/7/06 it will only see the first "Cheryl" it comes across which is 15/7/06 anyone know how to fix this?

Public Sub FindSlot()
Dim rng As Range
Dim w, t, s, d As Variant
Dim r As Range
Dim mycell
Dim r2

Application.EnableEvents = False
w = UserForm2.ComboBox3.Value
s = UserForm2.ComboBox2.Value
Worksheets(w).Visible = True
Worksheets(w).Select
t = UserForm2.ComboBox1.Value
d = Application.VLookup(UserForm2.ComboBox2.Text, Range("StaffHols"), (2), False)

With Worksheets(w)
Select Case t
Case Is = "Tuesday"
Set r = .Range("A4:A46")
Case Is = "Wednesday"
Set r = .Range("A49:A94")
Case Is = "Thursday"
Set r = .Range("A97:A142")
Case Is = "Friday"
Set r = .Range("A145:A190")
Case Is = "Saturday"
Set r = .Range("A193:A238")
End Select
End With
With Worksheets(w)
Select Case t
Case Is = "Tuesday"
Set r2 = .Range("A1")
Case Is = "Wednesday"
Set r2 = .Range("A49")
Case Is = "Thursday"
Set r2 = .Range("A97")
Case Is = "Friday"
Set r2 = .Range("A145")
Case Is = "Saturday"
Set r2 = .Range("A193")
End Select
End With
'On Error GoTo cls
Application.EnableEvents = False
For Each mycell In r2
If d <> "" And d = r2 Then
MsgBox "Not available " & s & " is on holiday!" & Chr(13) & "Please choose another week, day or stylist!"

Exit Sub
End If
Next
For Each mycell In r
If mycell.Text = UserForm2.ListBox1.Text Then
mycell.Select
UserForm2.Hide

Select Case s
Case Is = "Lauren"
c = 1: GoSub TestSlot
Case Is = "Emma"
c = 5: GoSub TestSlot
Case Is = "Cheryl"
c = 9: GoSub TestSlot
End Select

End If

Next mycell

Worksheets("Week Selection").Visible = True
Worksheets(w).Visible = False

cls:
Application.EnableEvents = True
Unload UserForm2

Exit Sub

TestSlot:
If mycell.Offset(0, c) <> "" And mycell.Offset(0, c + 2) <> "" Then
Msg = "Please Choose New Time, Day or Week... " & mycell.Value & " For " & s & " Is Taken!"
MsgBox Msg, vbOKOnly, "Time Slot Taken"
UserForm2.Show
ElseIf mycell.Offset(0, c) = "" Or mycell.Offset(0, c + 2) = "" Then
Answer = MsgBox(" Chosen Time Has An Empty Slot" & Chr(13) & "Click Yes to Make Booking or Click No To Exit", vbYesNo, "Make A Booking?")
If Answer = vbYes Then
Unload UserForm2
UserForm1.Show
End If
End If
Return
Set d = Nothing

End Sub

Hi all

I have searched for a solution for my problem but cant find one.

I need a way to maintain my hyperlinks when I use a vlookup in VBA. I am new at all this so please forgive my ignorance and probably bloated code. I have attached my workbook which contains the code.

Sheet1 contains data in columns A to F. Column G and H contain a hyperlink (and text)to a file which supports the data in columns A to F.

The problem is that I need to refresh the data in column A to F and this means that column G and H no longer correspond to the correct data. You can see an example of what I mean on Sheet2.

I have a vlookup which solves my problem of matching the columns for me but the Vlookup is stripping my hyperlink and just returning the file name.

Some one please help a desperate newbie. I also welcome constructive code critism or suggestions on a better way to do the whole thing!

Many thanks all.

DETERMINING ROW NUMBER FOR DATA FOUND WITH VLOOKUP IN VBA

I have a user form with a combobox (called STKCODE) that calls up a vlookup routine to search for this item in a named range on a worksheet and retrieve related data to display on the userform. All this works fine.

What I would like to know is how to easily determine the row number on the worksheet, representing the row the data is stored in, if thats at all possible.

Hope thats clear and thanks


	VB:
	
ASSEMBno = Me.STKCODE.Value 
DESC = Application.WorksheetFunction.VLookup(ASSEMBno, Range("STOCKTABLE"), 3, False) 
Me.STKDESC.Text = DESC 
 ' Code to get row number from worksheet for data found in stock table to be added here

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I want to use Match/Index or VLOOKUP in VBA to locate a cell in another worksheet and perform a calculation.

Sequence of Events:

-User will select a Component from a drop-down (Cell B5) and hit an "Overhaul Completed" button
-The button will look up the selected Component in another sheet where all components (A column) are listed with lots of data about them
-The corresponding cell for that component will be selected in the "Hours till Next Overhaul" column (C column).
-For this example, lets say that the cell selected is C5
-The formula for C5 should currently be B5-(PME!G1), where the value imported from PME! is the current engine hour value and it changes every month
- The formula should now change to B5-(PME!G1)-(PME!G1). But the second PME!G1 is going to a static number that will never change. So if the hours are at 14474 this month, the formula should be B5-(PME!G1)-14474.

This is what I have so far:
Dim rngRequested As Range
Set rngRequested = Sheets("Sheet10").Range("B5")
.VLookup(rngRequested.Value, Sheets("Sheet7").Range("A3:C21"), 3, False).Select

The select and vlookup functions are probably used incorrectly here.. So once I have that fixed, I can move onto the calculation part...

The reason why I might seem to be going a bit crazy on the automation is because I want to exploit the full capabilities of excel/vba to make the workbook idiot proof. There will be many operators using this book and our goal is to have them do a little as possible, while getting all sorts of valuable data.

I need to find a way to use VLOOKUP in VBA. Using the formula in my
spreadsheet won't work for me. Users will enter data in cell A1. If it
matches data in another list elsewhere, VLOOKUP will autofill A2 and A3.
However, if it doesn't match any other data, users must manually fill in A2
and A3. This causes a problem with the formula as the manual entries will
erase the formula.

The VLOOKUP formula I'm using is:

=VLOOKUP(A1,Sheet2!A1:C1800,2,FALSE)
and
=VLOOKUP(B2,Sheet2!A1:C1800,3,FALSE)

Can anyone offer me some helpful suggestions?

Mark

I am having a problem trying to programatically identify a column and create a VLOOKUP function there. Code snippet:
====================================================
' {ColCount is the column number of the column I want to post data into}
ColCount = ActiveCell.Value
ColCount1 = ColCount - 1
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-ColCount1],DATA!R2C1:R1000C2,2,0)"
=====================================================
The problem occurs in the last line. I have placed the cursor in the correct cell and now want to create a VLOOKUP command with a relative referance for the first term (a date in Column A or Column 1). If I manually change the code to say (in this case) -1 (as in RC[-1] ) the code runs fine. Colcount1 is initialized as an integer and shows the value of 1 in the watch window. When I run the code as shown the program hangs on the Vlookup line with a "Runtime Error 1004 - Application defined or object defined error". Is there a way to pass a variable to Vlookup in VBA?

My worksheet has the flexibility to paste a formula to the workbook, use copy/Paste Special to turn it to a value, and then bring the value back into the VBA side. (not had any luck with that here). I also tried defining a simple variable i=-1 and ran the Vlookup with RC[i] and it bombed the same way.

Thoughts?

Dave

I need to find a way to use VLOOKUP in VBA. Using the formula in my
spreadsheet won't work for me. Users will enter data in cell A1. If it
matches data in another list elsewhere, VLOOKUP will autofill A2 and A3.
However, if it doesn't match any other data, users must manually fill in A2
and A3. This causes a problem with the formula as the manual entries will
erase the formula.

The VLOOKUP formula I'm using is:

=VLOOKUP(A1,Sheet2!A1:C1800,2,FALSE)
and
=VLOOKUP(B2,Sheet2!A1:C1800,3,FALSE)

Can anyone offer me some helpful suggestions?

Mark

Hi,

I have two files Book1 and Book2.
The datas are in Book2.
In Book1 I have to use Vlookup by VBA code.

I want to use Vlookup in VBA. please find below the code given below and also attached book1 and book2.

Sub vbVlookup()
Range("b1")=Application.WorksheetFunction.VLookup("India",("c:book2.xls")worksheets("sheet1").range("a2:b5"),2,"false")
End Sub
Thanks,
RajuA

Hi,

Am trying to use a VLOOKUP in VBA on a form, I am entering data in one field so data will be displayed in another field.

They are both text boxes.

Here is the code;

Private Sub
phoneport_Change()

Me.phoneport.Value = Application.WorksheetFunction.VLookup(Me.phoneext.Value,
Sheets("LookupData").Range("A2:B23"), 2, False)

End Sub
Thanks for any help

Karl

Hello,

I am trying to use the "ThisWorkbook.Name" property for the workbook name in Vlookup

I previously used the following which worked -


	VB:
	
ActiveCell.FormulaR1C1 = _ 
"=VLOOKUP(C[-1],'[myworkbook.xls]Sheet1'!R1C2:R100C2,1,FALSE)" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I change the workbook name often so I figured instead of constantly changing the name in the vba code, why not use
ThisWorkbook.name...unfortunatley it does not work when I do the following...


	VB:
	
ActiveCell.FormulaR1C1 = _ 
"=VLOOKUP(C[-1],'[ThisWorkbook.Name]Sheet1'!R1C2:R100C2,1,FALSE)" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Any suggestions on this would be appreciated.

Thank you!

Chik wee (as Borat might say),

I'm wondering? is there any way that you can use a named range for the array in a vlookup that is done in VBA?

my named range is "Lists" and here's my code:

As ever people, thanks for looking & god bless this website or i'd be jobless!! lol

Hi guys!

I'm having some problems using the vlookup function between to sheets in a workbook. What I wanto accomplish is as vlookup function in vba that pulls the name of a company starting from cell A2 in sheet2 and uses this name to search for the corresponding company name in sheet1 column A.

After this is accomplished I want to be able insert a value from sheet1 that lies one column and three rows down from the name that was looked up above. This value will then be pasted into sheet2 column B alongside the correct company name.

Can anyone help me with this? I would be very thankfull for all the help I can get!

Cheers!

When I try and use the following Vlookup function in VBA it runs:

but it returns Error 2042???


	VB:
	
Prices(n) = Application.VLookup(theStart, _ 
Workbooks("Platts.xls").Sheets("Data").Range("A6:BG500"), 4, False) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


The full code is shown below what am I doing wrong. You can find the Vllokup about 4/5 of the way down


	VB:
	
 asian() 
     'Function Asian(ByVal asstart As Date, ByVal asend As Date, ByVal asstrike As Double, _
    ByVal aspricemove As Double, ByVal asvolmove As Double, ByVal ascp As String, _ 
    ByVal asoptdiscyn As String, ByVal ascommno As Integer, ByVal asperiod As String, _ 
    ByVal asdiscyn As String, ByVal darray As String, ByVal verthoriz As String) 
     
    Dim Dates() As Date 
    Dim Holidays As Variant 
    Dim Prices() 'As Double
    Dim n 'As Integer
     
     
    ascommno = 93 
     
     
     
    Dim MnthAdd As Integer, MnthLess As Integer 
    Dim myCommNo As Integer, OpType As Integer 
    Dim periods As Integer, i As Integer 
    Dim theStart As Date, theEnd As Date 
    Dim thepossibleend As Date, dip As Integer 
    Dim theTime As Double, theTime2 As Double 
    Dim theTimetom As Double, theTime2tom As Double 
    Dim DF As Double, Price As Double, Volatility As Double 
    Dim RA, RAtom 
    Dim Premium As Double, Premium1 As Double 
    Dim Delta As Double, deltas As Double 
    Dim Gamma As Double, Vega As Double 
    Dim theta As Double 
     
     'On Error GoTo EH
     
    myCommNo = ascommno 
     
    Workbooks("Murex Asian Options 10.xls").Activate 
    theStart = Range("U44").Value 
    asstart = theStart 
    theEnd = Range("U45").Value 
    asend = theEnd 
     
     
     '  n = holsarray(1, 0, 1)
     
     
     'With Workbooks("Setup.xls").Sheets("HOLIDAYS")
     '  Set Holidays = .Range(.Cells(3, 2 + commset(commno, 11)), .Cells(holies + 2, 2 + commset(commno, 11)))
     'End With
     
     'n = Holidays(3)
     
     
    If ascp = "C" Then OpType = 1 Else OpType = 0 
     
     ' Select Case asperiod
     
     '  Case Is = "M"
     
     'Identifies how many different months (periods) there are
    periods = Month(asend) - Month(asstart) + (Year(asend) - Year(asstart)) * 12 + 1 
     
    For i = 1 To periods 'go for all the periods apart from the last one which _
        should be calculated separately, allowing For an End _ 
        In the middle of the month start of the option 
         
        If i = 1 Then theStart = asstart Else theStart = theEnd + 1 
         
         ' end of the option.
         ' The idea is to go to the beginning of the next month and subtract one day.
         
        If Month(theStart) = 12 Then 
            MnthAdd = 1 
            MnthLess = -12 
        Else 
            MnthAdd = 0 
            MnthLess = 0 
        End If 
         
         'the idea is that on the last period we need to allow for an odd last day
         'and it needs to be able to recognise that:
         
        thepossibleend = DateValue(Str(1) + "/" + Str(Month(theStart) + 1 + MnthLess) + "/" + Str(Year(theStart) + MnthAdd))
- 1 
        If thepossibleend < asend Then theEnd = thepossibleend Else theEnd = asend 
         
         ' days in the accounting period
        dip = splits(theStart, theEnd, myCommNo) 
        Splita = dip - Splitday(theStart, theEnd, myCommNo) 
        expday = futexdates(theStart - firstd, 2, commset(ascommno, 7)) 
         
        Redim Preserve Prices(dip) 
         
        For n = 1 To Splita 
             
            If Date >= theStart + n Then 
                 
                Prices(n) = Application.VLookup(theStart, Workbooks("Platts.xls").Sheets("Data").Range("A6:BG500"), 4, True) 
                 
            Else 
                 
                Prices(n) = EnergyPrice(theStart, theStart, theStart, ascommno, "Q", "y") 
                 
            End If 
             
             
             'End If
             'Prices(n) = Application.WORKDAY(theStart, n)
             
        Next n 
         
         'Prices(n)
         
         
         
         
         
         
         
        theTime = (theStart - Date) / 365 
        theTime2 = (theEnd - Date) / 365 
        theTimetom = (theStart - (Date + 1)) / 365 
        theTime2tom = (theEnd - (Date + 1)) / 365 
         
         ' discount factor if applicable
        If asoptdiscyn = "Y" Then 
            DF = intDF(theEnd, "D", myCommNo) / 100 
        Else 
            DF = 1 
        End If 
         
    Next i 
     'End Select
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi guys,

I'm having trouble understanding how Vlookup in VBA works. I have tried numerous ways on the forums / net on handling the errors should Vlookup not find anything, but it didn't work so far...

I'm trying to find a concatenated value from workbook A in another spreadsheet in workbook B. I have no problems looking up btw workbooks (though i don't mind suggestions...my code's a mess! )

Once a value is found, i want the code to execute a code and if not found, another block of code is executed. I'm trying to use an IF ISERROR(value), amongst others, to handle this, but it didn't work.

Please advice. Below is the problematic code.

intBillingBottomPrice =
Application.VLookup(strConcatenate, Range("A2:F" & intFinalBillingRow), 6, True)
If IsError(intBillingBottomPrice) Then GoTo NewSupplierFound:

NewSupplierFound:
    ''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Value NOT found by VLookup. Error handling code here.
    '''''''''''''''''''''''''''''''''''''''''''''''''''''
    Cells(intNewCPRow, 1) = strCompanyName
    Cells(intNewCPRow, 2) = strAccountName
    Cells(intNewCPRow, 3) = strDestination
    Cells(intNewCPRow, 4) = intBottomPrice
    Cells(intNewCPRow, 5) = intLowestPrice
    Cells(intNewCPRow, 6) = strCurrency

    intNewCPRow = intNewCPRow + 1
Else
    ''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' Value found by VLookup. Continue normal execution.
    '''''''''''''''''''''''''''''''''''''''''''''''''''''
    'strBillingLocation = Application.VLookup(strConcatenate, Range("A2:F" & intFinalBillingRow), 2, True)
    If intBillingBottomPrice <> intBottomPrice Then
        CPWorkbook.Activate
        '***Look up on how to set a minimum no. of decimal points***
        ActiveSheet.Cells(x, 5).Value = intBillingBottomPrice
        ActiveSheet.Cells(x, 5).Select
            With Selection
            .Interior.Pattern = xlSolid
            .Interior.PatternColorIndex = xlAutomatic
            .Interior.ThemeColor = xlThemeColorAccent6
            .Interior.TintAndShade = 0.399975585192419
            .Interior.PatternTintAndShade = 0
            End With
        ActiveSheet.Cells(x, 6).Value = intBillingBottomPrice
        ActiveSheet.Cells(x, 6).Select
            With Selection
            .Interior.Pattern = xlSolid
            .Interior.PatternColorIndex = xlAutomatic
            .Interior.ThemeColor = xlThemeColorAccent6
            .Interior.TintAndShade = 0.399975585192419
            .Interior.PatternTintAndShade = 0
            End With
        
        'Inserting ChangeLog details
        ThisWorkbook.Activate
        ThisWorkbook.Worksheets("Changelog").Select
        intChangeLogFinalRow = Cells(Rows.Count, 1).End(xlUp).Row
        intChangeLogFinalRow = intChangeLogFinalRow + 1
        'MsgBox intChangeLogFinalRow
        Cells(intChangeLogFinalRow, 1) = strBillingLocation
        Cells(intChangeLogFinalRow, 2) = strCompanyName
        Cells(intChangeLogFinalRow, 3) = strAccountName
        Cells(intChangeLogFinalRow, 4) = strDestination
        Cells(intChangeLogFinalRow, 5) = intBottomPrice
        Cells(intChangeLogFinalRow, 6) = intLowestPrice
        Cells(intChangeLogFinalRow, 7) = intBillingBottomPrice
        Cells(intChangeLogFinalRow, 8) = strCurrency
        Cells(intChangeLogFinalRow, 9) = Now()
        Cells(intChangeLogFinalRow, 10) = ThisWorkbook.Worksheets(2).Name
                
        CPWorkbook.Activate
        
    End If
End If


Hi All,

I need a VBA CODE as same as VLOOKUP
I have two sheets (Members & Contact Details).

Based on the names listed on the Members worksheet, I need to get the contact details such as Address, Phonenumber & Email ID which are listed in sheet 2 (Contact Details). This can be done using the Vlookup in MS excel 2007, But I need the VBA Excel code to perform this action, since the list will be updated on daily basis. no idea how many lines

Hi there,
I am trying to use a match function in VBA to return a value when a number of values in a row match a certain criteria (I have tried the Find approach on the Ozgrid help pages but it is not what I am after). My problem using a match function of course is that when there is no match the code returns a type mismatch error. I have tried to circumvent the error problem using an "On error resume next" but this then delivers a match for all rows. Anyone have any thoughts about how I can get around this problem?


	VB:
	
 DataSum() 
     
    Dim strData As String 
    Dim i As Integer 
     
    i = 0 
    Sheets("Sheet1").Activate 
    Range("B4", Range("B4").End(xlDown).Offset(0, 0)).Select 
    For Each cell In Selection 
        If cell.Value = 0 Then 
            i = i + 1 
        Else 
            strData = Sheets("Sheet1").Range("$B$4").Offset(i, 0) 
            myVal1 = Application.Match("A", Range("$C$4").Offset(i, 0), 0) 
            myVal2 = Application.Match("B", Range("$F$4").Offset(i, 0), 0) 
            myVal3 = Application.Match("C", Range("$I$4").Offset(i, 0), 0) 
            myTotal = myVal1 * myVal2 * myVal3 
            If myTotal = 1 Then 
                Sheets("Sheet1").Range("K2").Offset(i, 0) = strData 
            Else 
            End If 
            i = i + 1 
        End If 
    Next cell 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi

I hope someone can help me with this. I'm running into problems when I try to change a vlookup formula in vba to reference a new file and range that the user selected. Here's some sample code:


	VB:
	
 Range 
Dim prompt As Variant 
Dim title As Variant 
prompt = "Select a range to link to for current subscription, redemption and profit/loss amounts." 
title = "Select a range to link to" 
 'Display input box
Set UserRange = Application.InputBox( _ 
prompt:=prompt, _ 
title:=title, _ 
Default:=ActiveCell.Address, _ 
Type:=8) 
 'Range selection
MsgBox UserRange.Address(external:=True) 
Range("F11").Select 
ActiveCell.Formula = "=+VLOOKUP(f$7," & UserRange & ",11,0)" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I keep getting an error message when the macro gets to the ActiveCell.Formula part of the code.

Thanks in advance for your help.

Hi

I wondered if anyone on this forum knew of how you use the VLOOKUP function in VBA. An example of using a VLOOKUP on the Excel worksheet is:

=VLOOKUP(1,LooKup_range, 2)

Can you modify this for use in VBA?

Cheers
Johno

Can someone help me simplify this?

could it be done with a vlookup function? is it possible to do that in VBA?

That would also make it easier if I ever want to make changes to the lookup table.

Range("E" & ActiveCell.Row).Select
Select Case ActiveCell.Value
Case "XXXXX"
ActiveCell.Value = "xxxxxx"
Case "XXXX"
ActiveCell.Value = "xxxxx"
Case "XXX"
ActiveCell.Value = "xxxx"
Case "XX"
ActiveCell.Value = "xxx"
Case "X"
ActiveCell.Value = "xx"
Case "xxxxx"
ActiveCell.Value = "xxxxxx"
Case "xxxx"
ActiveCell.Value = "xxxxx"
Case "xxx"
ActiveCell.Value = "xxxx"
Case "xx"
ActiveCell.Value = "xxx"
Case "x"
ActiveCell.Value = "xx"
Case "!!!!!!"
ActiveCell.Value = "xxxxx"
Case "!!!!!"
ActiveCell.Value = "xxxx"
Case "!!!!"
ActiveCell.Value = "xxx"
Case "!!!"
ActiveCell.Value = "xx"
Case "!!"
ActiveCell.Value = "x"
Case "!"
ActiveCell.Value = "x"
Case ""
ActiveCell.Value = "x"
End Select

thanks

Hi, I'm having trouble making the vlookup function work in VBA.
the values that are used are all dates, I don't know if that makes a
difference.

Worksheets("sheet1").range("a1").value = application.WorksheetFunction.
_
Vlookup(Range("c1"), range("a:a"), 2)

this gives me a run-time error '1004' Unable to get the vlookup
property of the Worksheet function class

so i tried this:
Worksheets("sheet1").range("a1").value =
application.Vlookup(Range("c1"), _
range("a:a"), 2)

but this gives me a #REF error.

What am I doing wrong?

Any help would be appreciated!

--
ayl322
------------------------------------------------------------------------
ayl322's Profile: http://www.excelforum.com/member.php...fo&userid=9846
View this thread: http://www.excelforum.com/showthread...hreadid=390609

Hello all,

I have a problem with vlookup, some of the items in the lookup array are not unique and vlookup gets confused with multiple matches,

however if I can use a combination of 1st and second columns as the criterion then it would result in a unique match.

Problem is I don't know how to acheive this in vba (but I can with the worksheet formulas).

Any advice? (attached is a sample worksheet)

I recently wrote the following code to solve another thead on this site:
Function RentSum(Test_Year As Integer, table_array As Range)

    Dim Start_Date As Long
    Dim End_Date As Long
    Dim Temp_Date As Long
    Dim Date_Range As Range
    Dim Total_Rent As Variant
    
    Set Date_Range = table_array.Resize(, 2)
    Start_Date = Application.WorksheetFunction.Min(Date_Range)
    End_Date = Application.WorksheetFunction.Max(Date_Range)
    Temp_Date = Start_Date
    
    Do While Temp_Date < End_Date
        
        If Year(Temp_Date) = Test_Year Then
        
            Total_Rent = Application.WorksheetFunction.VLookup(Temp_Date, table_array, 4, True) + Total_Rent
                    
        End If
        
        Temp_Date = DateAdd("m", 1, Temp_Date)
          
    Loop
    
    RentSum = Total_Rent
I had origianlly defined the date variables as DATE, but the Vlookup portion of the code did not work as a serial date in VBA returns as #1/1/2010# while a serial date on the spreadsheet shows as a number - 40179. I was able to get the code to work by changing the date variables to long, as shown above.

I "m a noob so my question is was my solution appropriate, or is there a more correct way to make this code work with dates defined as DATE?"

Thanks,

Tom

Hello Everyone,
I have been struggling this problem for past 2 weeks and it seems that I am not able to do this alone. I want to copy all the data in column A, B and C into K, L and M before the file is closed, meaning whenever the user tries to close the workbook then I want the contents on column A, B and C to be copied into K, L and M. So when the user comes back and opens this workbook again then I want to use vlookup using VBA to get the values for column C from column K. Pls. note Source Type is always unique and can be used in the vlookup to get the values for column C. Pls. note that I am using a Data Validation List (Drop Down Box) on column C. See attached for more details. Thanks for your help.

Hi all.

Good news, Google answers most of my questions. Bad news, not this one.

I'm trying to use vlookup in vba to look at a specific source cell.

If the month is Jan, the vlookup formula goes into column L, for Feburary it goes into column T, March goes into AB - but I always want the source column to be A.

I tried this, but it didn't work...

ActiveCell.FormulaR1C1 = "=VLOOKUP($A1,M1:Z53,2,FALSE)"

Anyone know what I need to do?

Thanks

Colin