Forumla

  • Thread starter Thread starter john smith
  • Start date Start date
J

john smith

Hello,

I have the following lookup formula that gets prices from the partlist sheet
and enters them in the cells when I enter a part#
The column next to each of these price columns is the extended value. The
Qty in column F * the Price.

How do I code this to happen when I enter the Qty in Column F?

Thanks in advance for any help
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:D,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)"
Application.EnableEvents = True
End If
End With
End Sub
 
Hi John
try replacing the line
.Offset(0, 1).Formula = _
"=VLOOKUP(" & .Address(False, False) & _
", partlist!C:D,2,FALSE)"

with
..Offset(0, 1).FormulaR1C1 =
"=VLOOKUP(R[0]C[-1],partlist!R1C3:R1000C3,2,FALSE)"

and adapt the other formulas accordingly


also change the line
If .Column = 4 Then
to
If .Column = 6 and .value <>"" Then
 
Back
Top