Solved: VBA Vlookup & Auto Population

[hobbiton73]

Hi, I wonder whether someone may be able to help me please.

I'm using the attached file to record staff resource.

The problem I'm having revolves aroud the use of a VBA Vlookup and auto completion of cell text.
If you navigate to the 'Input Sheet, then,
Please select "BC", "BNC", or "OH" from the drop down menu in cell I7. Once a value has been selected, you will see that the corresponding values will be returned in J7 and K7, telling the user to either select, or enter a value.In addition to using a VB script on the 'Input' sheet, the values for column I are taken from column A on the 'Lists' sheet and the values for columns J and K are taken from column B and C on the 'Lists' sheet.

The problem I have is if the value of "E" is selected in cell I7.

The correct values of 'Enter the E Number' appears in J7 and 'Enter the name of E' in K7, but as soon as I enter text in J7, the text in K7 disappears.

It's this, that I would, if at all possible please like to stop. So if the value of "E" is selected in I7, text is manually entered in J7 I would like K7 still to say 'Enter the name of ".

I've been working on this for weeks, and writtena nd re-written my sheet trying different methods to over come this issue.

I just wondered whether someone could possibly look at this please and offers oem guidance on how I may overcome this problem.

Many thanks and kind regards

[Trebor76]

Hi there,

If you enter text in J7 that doesn't exist in B2:B24 of the "Lists" tab, an error value of 2042 (i.e.#N/A) is assigned being assigned to the "res" variable. When this happens, the code has been instructed to return a null i.e. "" to the active row in column K which is why the existing text is being "cleared" (it's actually being replaced with a null).

If the text does exist, the corresponding entry from C2:C24 from the "Lists" tab is returned to the active row in column K.

HTH

Robert

[hobbiton73]

Hi @Trebor786, thank you for taking the time to reply to my post and for the guidance.

I've been working on this throughout today and I've managed to get this to work by using the follwoing:

If Target.Column = 11 Then
If Target.Value = "" And Target.Offset(, -2).Value = "E" Then
Target.Value = "Enter the name of the E"
End If
End If

Many thanks and kind regards