vba or pseudo

  peug417 21:30 28 Mar 04

Hello Folks
This is one for the Vogs and Whisperers amongst you.
I have a Excel speadsheet that links to another
with the formulae in the cell H3 is as follows:
=IF(Monday!G3=0,"",VLOOKUP(Monday!G3,data!E2:F4400,2,FALSE))... The contents of the range e2:f4400 are product codes and descriptions. What i need to do is when an new code is entered into g3 and H3 returns #N/A is to open a Msg box in which the new codes and products can be entered into the form data! I have my codes for the Msg box but cant get them to open when cell H3 contains #N/A.

Thanks in anticipation

  VoG II 21:51 28 Mar 04

Rigt click the sheet tab and select View Code.

Then add something along the lines of

Private Sub Worksheet_Change(ByVal target As Excel.Range)

If target.Address(False, False) = "H3" Then

If (IsError(Range("H3").Value)) Then

MsgBox ("Error in H3")

End If

End If

End Sub

  peug417 22:00 28 Mar 04

Have tried this to no avail.
new code in G3 returns #N/A in H3
No message box for me to install new products.


  VoG II 22:22 28 Mar 04

If target.Address(False, False) = "G3" Then

If (IsError(Range("H3").Value)) Then

  peug417 22:31 28 Mar 04

Would it make it clearer if i was to mail you the workbook? Virus Protected of course


  VoG II 22:35 28 Mar 04

Mail it to Whisperer. No offence but I do not have the time. Really I don't.

  peug417 22:41 28 Mar 04

My mate Whisperer is not online tonight, otherwise I would have bypassed the forum, Thanks for your contributions it is appreciated, I will have to catch him tommorow regards.

  peug417 22:19 03 Apr 04


By all means send me a copy of the workbook and if I succeed we will post a composite response to the forum

Place the first piece of code in the worksheets code area and the remainder in a module, it should do what you require.

For others following the thread, column 7 (G) contains a product code and column 8 (H) the product description. The code is entered (it could be manual or a data validated list) and the vlookup formula in the adjacent H cell provides the description.

It was also a requirement that if a new code was added then it would automatically update the main lookup table which was in a dynamic named range called 'Stock'.

If there are follow-up queries then please post as a separate thread.


Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Column = 7 then Call CheckForProduct(Target)

End Sub


Option Explicit

Public Code As String

Public NextRow As Integer


Sub CheckForProduct(Target As Excel.Range)

Cells(Target.Row, Target.Column + 1).Select

If IsError(ActiveCell.Value) Then

Code = Target.Value

Call NewProduct(Code)

End If

End Sub


Sub NewProduct(Newcode As String)

Dim Description As String


NextRow = Cells(Rows.Count, 5).End(xlUp).Row + 1

ActiveSheet.Unprotect Password:="****" (insert the password here if applicable)

Application.ScreenUpdating = False

Description = InputBox("Please enter the Description of Goods . . ", "Product Description")

Range("E" & NextRow).Value = Newcode

Range("F" & NextRow).Value = Description

Application.Goto Reference:="Stock"

Selection.sort Key1:=Range("E2"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=False,Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

End Sub

That is infuriating as every entry was on a separate line with a gap between

The first code should be as follows


Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Column = 7 then Call CheckForProduct(Target)

End Sub

This thread is now locked and can not be replied to.

Nintendo Switch (Nintendo NX) release date, price, specs and preview trailer: Codename NX console…

1995-2015: How technology has changed the world in 20 years

8 things designers (and brands) need to know about the modern woman

How to speed up a slow Mac: 19 great tips to make an iMac, MacBook or Mac mini run faster | Speed…