Auto filling in adjacent cells

  • Thread starter Thread starter JPA888
  • Start date Start date
J

JPA888

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 manuall
enter the description and price in the adjacent cells.
Is there anyway I can do it so that everytime I type an item nam
[AB124] it automatically puts the description and price in the adjacen
cells?
Once again, any help would be greatly appreciate. please let me know i
my question is unclear. Thanks
 
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
 
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

.
Click to expand...
Click to expand...
 
Thanks Dave! Your feedback on my child-like VBA solutions
are invaluable and much appreciated. Have a good weekend!
Jason
-----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,
Click to expand...
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
Click to expand...
an
item name
[AB124] it automatically puts the description and
Click to expand...
price
in the adjacent
cells?
Once again, any help would be greatly appreciate.
Click to expand...
please
let me know if
my question is unclear. Thanks!
Click to expand...
---
---------------
JPA888's Profile:
Click to expand...
http://www.excelforum.com/member.php?
action=getinfo&userid=15880
View this thread:
Click to expand...
http://www.excelforum.com/showthread.php? threadid=273649
.
Click to expand...
Click to expand...

--

Dave Peterson
(e-mail address removed)
.
Click to expand...
 
Someday, you'll dump those complex formulas for, er, complex UDF's!



Jason said:
Thanks Dave! Your feedback on my child-like VBA solutions
are invaluable and much appreciated. Have a good weekend!
Jason
-----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

.
Click to expand...

--

Dave Peterson
(e-mail address removed)
.
Click to expand...
Click to expand...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Auto fill adjacent cells 3
fill in date into adjacent cell automatically 2
Problem with Pick List associated cell 2
Auto fill Adjacent cells 7
making a drop-down list with two separate columns 2
Import data, fill adjacent formulas 1
Pick List Problem 7
Adding current dates to adjacent cells automatically. 2

Back
Top