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

"=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

- Vlookup in VBA will only find first match???
- Hyperlinks and vlookup in VBA
- Determine Row Number For Data Found With Vlookup In Vba
- Match/Index or VLOOKUP in VBA?
- VLOOKUP in VBA code
- Using variable in VLOOKUP in VBA code
- Using VLOOKUP in VBA code
- How to do Vlookup in VBA calling the values in closed file.
- VLOOKUP in VBA - Text Boxes
- Vlookup in VBA with ThisWorkbook.Name
- Named range in VBA Vlookup?
- Using vLookup in Vba
- VLookup in VBA
- How to handle errors (error 1004 i think...) in VBA Vlookup?
- VLOOKUP in VBA Excel
- Match Function In Vba Returning Error
- Change vlookup in vba with user selected range
- VLOOKUP in VBA
- I'm sure there's an easier way to do this. vlookup in VBA?
- VLookup in VBA giving error message
- Multiple criteria vlookup in vba userform
- Serial dates in VBA vs Serial dates in Excel
- Using vlookup in vba
- Vlookup using absolute cell references in VBA

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

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.

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 hereIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

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.

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

====================================================

' {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

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 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 SubThanks,

RajuA

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 SubThanks for any help

Karl

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

I previously used the following which worked -

VB:I change the workbook name often so I figured instead of constantly changing the name in the vba code, why not useActiveCell.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

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!

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

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!

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 SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

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

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

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 SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

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:I keep getting an error message when the macro gets to the ActiveCell.Formula part of the code.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

Thanks in advance for your help.

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

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

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

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)

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_RentI 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

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.

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