vba or pseudo

  peug417 21:30 28 Mar 2004

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 2004

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 2004

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 2004

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

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

  peug417 22:31 28 Mar 2004

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


  VoG II 22:35 28 Mar 2004

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

  peug417 22:41 28 Mar 2004

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 2004


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.

Elsewhere on IDG sites

AMD Radeon Adrenalin release date, new features, compatible graphics cards

Inside the iMac Pro - Apple's most powerful Mac yet

iMac Pro release date, UK price & specs

Comment nettoyer Windows et optimiser son PC gratuitement ?