vba vlookup

AI Training: For Busy Decision Makers & Professionals Book now

Forum home » Delegate support and help forum » Microsoft VBA Training and help » VBA Vlookup

VBA Vlookup

resolvedResolved · Urgent Priority · Version 2007

Miles has attended:
Excel VBA Intro Intermediate course

VBA Vlookup

Hi

I have having a slight problem with an offset.

As part of a Case statement, I have the following code:

Select Case Target.Column
Case 1
If Target.Column = 1 Then
If Len(Target) Then
Target.Offset(, 4).Value = Date
cell.Offset(, 5).Value = WorksheetFunction.Vlookup(Target, Sheets("ITFC Returns").Range("B:C"), 2, False)
cell.Offset(, 5).Value = WorksheetFunction.Vlookup(Target, Sheets("LML Returns").Range("B:C"), 2, False)
If cell.Offset(, 5).Value = "ITFC" Then
cell.Offset(, 7).Value = Date

End If
If cell.Offset(, 5).Value = "LML" Then
cell.Offset(, 7).Value = Date
End If
If cell.Offset(, 5).Value = "" Then
cell.Offset(, 5).Value = "IN CHISWICK"
cell.Offset(, 7).Value = Date
End If
Else
cell.Offset(, 4).Value = ""
cell.Offset(, 5).Value = ""

End If
End If


My problem is that it is not actually activating the vlookup formula at all without me pressing F2 and enter in the cell in column A. I'm sure that there is an easy fix to this.

My other issue is that the VLookup is not working like a formula and not always active, ie if the lookup value is deleted from the lookup location, it does not update where the lookup result goes.

Would you be able to help me make the vlookup always active so that it is always looking for an answer?

I would greatly appreciate your assistance with the above

Many thanks

Miles



RE: VBA Vlookup

Hello Miles,

Without seeing the worksheet and code in context its difficult to solve the issue. If you could send over a sample of the code and its relevant worksheet to mm@stl-training.co.uk then I'll look at this for you

If you require further assistance, please reply to this post. Or perhaps you have another Microsoft Office question?

Have a great day.
Regards,

Mark
Microsoft Office Specialist Trainer

RE: VBA Vlookup

Hi Mark

I was wondering if you have received my workbook sent via email? I have not received a response so therefore did not know if you have received it.

Many Thanks

Miles

RE: VBA Vlookup

Hi Mark,

I am currently going through the outstanding questions on the forum and was wondering whether you have heard from Mark regarding the spreadsheet?

If not, please reply within the next five days and I can contact Mark. If we don't hear from you within the next five days, then the question will be marked as resolved automatically.

Thank you for your assistance.

Regards

Simon

Will be marked as resolved in 5 days

Notice: This is an automated message. Due to inactivity, this forum post will be marked as 'resolved' if there are no further responses in the next 5 days.

Fri 23 Nov 2012: Automatically marked as resolved.

 

Training courses

 

Training information:

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

VBA tip:

Empty The Clipboard with CutCopyMode

After each Copy/Paste operation in VBA, you should use the following line of code to empty the clipboard. This ensures that the computer memory doesn't overload:

ActiveSheet.Paste
Application.CutCopyMode = False

View all VBA hints and tips


Server loaded in 0.12 secs.