look up and input

[john smith]

Hello,

In my workbook I have two sheets.

One has a list has a column with part numbers and a column with a
description of the part number.

The second sheet I used the lookup wizard to create a formula that when I
enter a part # it gets the part description from the first sheet.

This all works fine.

Problem.

I want to enter a part # 9130 which is a non stocking part and the part #
and decscription is not in the parts list. Then be able to enter my own
description while still leaving the lookup formula in the cell so I can use
ctrl D to copy the formula to the next cell down.

I think I have to use the input box in VB to do this but don't know how. If
I don't have to use VB please advise.

Thanks in advance for any help

Dan

 

[Frank Kabel]

Hi Dan
you can't have both in a cell: a formula and a manual entered value
(also not with VBA). You have to add this part number to your part
information sheet.
To prevent errors or to tag such rows you may change your VLOOKUP
formula to something like
=IF(ISNA(your_vlookup_formula),"part # not found",your_vlookup_formula)

 

[JE McGimpsey]

One way:

This macro will put the formula in the adjacent cell when an entry is
made. You can then overwrite it if the value isn't found.

Put this in the worksheet code module (right-click on the worksheet tab
and choose View Code):

Private Sub Worksheet_Change(ByVal Target As Range)
With Target(1)
If .Column = 1 Then
Application.EnableEvents = False
.Offset(0, 1).Formula = _
"=VLOOKUP(" & .Address(False, False) & _
", Sheet2!A:B,2,FALSE)"
Application.EnableEvents = True
End If
End With
End Sub


Adjust your sheet/cell references as needed.

 

[john smith]

JE,

That worked great!

Thank you very much

One way:

This macro will put the formula in the adjacent cell when an entry is
made. You can then overwrite it if the value isn't found.

Put this in the worksheet code module (right-click on the worksheet tab
and choose View Code):

Private Sub Worksheet_Change(ByVal Target As Range)
With Target(1)
If .Column = 1 Then
Application.EnableEvents = False
.Offset(0, 1).Formula = _
"=VLOOKUP(" & .Address(False, False) & _
", Sheet2!A:B,2,FALSE)"
Application.EnableEvents = True
End If
End With
End Sub


Adjust your sheet/cell references as needed.

 

[john smith]

Hello,
I have modified the original code from JE to look up several datas from the
partlist and enter it in the appropriate cells.
All this works great.
I added the last line of code so that the look up data (price) will be
multiplied by the qty the user inputs.
This kind of works except I need the reference of the cells to change with
the row I am on.
F686 is the qty entered by user G686 is the price looked up
How do I make the references change with the row I am on?

Thanks again
Dan


Private Sub Worksheet_Change(ByVal Target As Range)
With Target(1)
If .Column = 4 Then
Application.EnableEvents = False
.Offset(0, 1).Formula = _
"=VLOOKUP(" & .Address(False, False) & _
", partlist!C,2,FALSE)"
.Offset(0, 3).Formula = _
"=VLOOKUP(" & .Address(False, False) & _
", partlist!C:E,3,FALSE)"
.Offset(0, 5).Formula = _
"=VLOOKUP(" & .Address(False, False) & _
", partlist!C:F,4,FALSE)"
.Offset(0, 10).Formula = _
"=VLOOKUP(" & .Address(False, False) & _
", partlist!C:H,6,FALSE)"
.Offset(0, 4).Formula = _
"=(f686*g686)"

Application.EnableEvents = True
End If
End With
End Sub

 

[Dave Peterson]

Sometimes, it's just easier using the R1C1 notation. Then you don't have to
worry about what row you're on:

.offset(0,4).formulaR1c1 = "=RC[-2]*RC[-1]"


But if you want:

.Offset(0, 4).Formula = _
"=(f" & .Row & "*g" & .Row & ")"

and you could drop the ()'s, too.

.Offset(0, 4).Formula = _
"=f" & .Row & "*g" & .Row

 

[john smith]

Dave,

Thanks the R1C1 worked for me.

Thanks for the extra option also its always nice to know different tricks

Dan

Sometimes, it's just easier using the R1C1 notation. Then you don't have to
worry about what row you're on:

.offset(0,4).formulaR1c1 = "=RC[-2]*RC[-1]"


But if you want:

.Offset(0, 4).Formula = _
"=(f" & .Row & "*g" & .Row & ")"

and you could drop the ()'s, too.

.Offset(0, 4).Formula = _
"=f" & .Row & "*g" & .Row

Hello,
I have modified the original code from JE to look up several datas from the
partlist and enter it in the appropriate cells.
All this works great.
I added the last line of code so that the look up data (price) will be
multiplied by the qty the user inputs.
This kind of works except I need the reference of the cells to change with
the row I am on.
F686 is the qty entered by user G686 is the price looked up
How do I make the references change with the row I am on?

Thanks again
Dan

Private Sub Worksheet_Change(ByVal Target As Range)
With Target(1)
If .Column = 4 Then
Application.EnableEvents = False
.Offset(0, 1).Formula = _
"=VLOOKUP(" & .Address(False, False) & _
", partlist!C,2,FALSE)"
.Offset(0, 3).Formula = _
"=VLOOKUP(" & .Address(False, False) & _
", partlist!C:E,3,FALSE)"
.Offset(0, 5).Formula = _
"=VLOOKUP(" & .Address(False, False) & _
", partlist!C:F,4,FALSE)"
.Offset(0, 10).Formula = _
"=VLOOKUP(" & .Address(False, False) & _
", partlist!C:H,6,FALSE)"
.Offset(0, 4).Formula = _
"=(f686*g686)"

Application.EnableEvents = True
End If
End With
End Sub
my
own I
can know
how.