-----Original Message-----
Mostly just personal preferences--but some not.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim RngAbov As Range
Dim iRow As Variant
With Target
If .Cells.Count > 1 Then Exit Sub
If Intersect(.Cells, Me.Range("a2:a65536")) Is Nothing Then Exit Sub
Set RngAbov = Me.Range("a1", .Offset(-1, 0))
iRow = Application.Match(.Value, RngAbov, 0)
If IsError(iRow) Then
'no previous match
Else
Application.EnableEvents = False
.Offset(0, 1).Value = RngAbov(iRow, 2)
.Offset(0, 2).Value = RngAbov(iRow, 3)
.Offset(1, 0).Select
Application.EnableEvents = True
End If
End With
End Sub
I like to use the Intersect() to determine if I'm in a range--rather than
checking the column (and row!). I think it's easier for most people to
change--especially in cases with multiple areas like:
if intersect(.cells,me.range("a:a,c:d,g1:h9"))...
If you use application.match, you'll get an error back. If you use
application.worksheetfunction, you'll get a runtime error that you have to trap
for (why you used "on error").
But since you already knew if you found a match, you wouldn't need to use the
application.countif() to check again.
Using your way:
irow = 0
On Error Resume Next
irow = Application.WorksheetFunction. _
Match(.Value, rngabov, 0)
On Error GoTo 0
if irow <> 0 then
'do the work
else
'don't do anything
end if
And just an option to show how you can index into that array (rngabov) and even
move over a column or 2.
But if you're changing something (or selecting something), you do want to
..enableevents = false to stop your code from firing again.
And I like to use upper/lower case in my variables. Then when I type in lower
case, if I see that my variable stays lower case, I know I made a typo. If I
see it change, I know that I typed it correctly (or at least matched one of the
declared variables!).
And you probably don't want to use Activecell. Target is safer.
And I've been putting me.range(...) in my code. A few months ago, I tried to
help someone who had lots of .selects and .activates in his event code.
One of the Range() referred to a different sheet and screwed things up
mightily--Instead of fixing it correctly, I weaselled and just put "Me." in
front of every range that I needed.
Me is shorthand that represents the object that holds the code--in this case,
the worksheet owning the code.
And your code would be easily changed to point at a table on another worksheet
(kind of buiding an on the fly =vlookup()).
You'd just have to change:
Set RngAbov = Me.Range("a1", .Offset(-1, 0))
to something like:
Set RngAbov = Me.Parent.Worksheets("sheet2").Range ("a:a")
Jason said:
Assuming col. A is "item", col. B is "descrip.", and col.
C. is "price", you could use:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngabov As Range
Dim irow As Long
With Target
If .Count > 1 Or .Column > 1 Then Exit Sub
Set rngabov = Range("A1:A" & ActiveCell.Row - 1)
On Error Resume Next
irow = Application.WorksheetFunction. _
Match(.Value, rngabov, 0)
On Error GoTo 0
If Application.WorksheetFunction. _
CountIf(rngabov, .Value) > 0 Then
.Offset(0, 1).Value = Cells(irow, "B").Value
.Offset(0, 2).Value = Cells(irow, "C").Value
.Offset(1, 0).Select
End If
End With
End Sub
---
Right-click the worksheet tab, View Code, and enter in
the code above. Enter values in col. A and tab over as if
you were going to fill in columns B and C. If the item is
already in the list above, column B and C will fill in.
<constructive criticism of the code above welcome>
HTH
Jason
Atlanta, GA
-----Original Message-----
Here is my issue, any help would be greatly appreciated:
I am creating an excel sheet whereby I list an item, its
description,
and its price
[AB124] [stapler] [29.95]
I am continuously entering the same items, but I still
have to manually
enter the description and price in the adjacent cells.
Is there anyway I can do it so that everytime I type an
item name
[AB124] it automatically puts the description and price
in the adjacent
cells?
Once again, any help would be greatly appreciate. please
let me know if
my question is unclear. Thanks!
--
JPA888
------------------------------------------------------ ---
---------------
JPA888's Profile:
http://www.excelforum.com/member.php?
action=getinfo&userid=15880
View this thread:
http://www.excelforum.com/showthread.php? threadid=273649
.
--
Dave Peterson
[email protected]
.
