macro - type in value, fill other cells w/same value if....

Sayre

Board Regular
Joined
Apr 12, 2005
Messages
180
Col D has list of order #'s, starting in row 9. Col M is for COMMENTS about each order. Order #'s can appear in Col D more than once.

What I am looking for is a macro that will automatically detect if I have entered a new COMMENT in Col M, recognise the order # in adjacent Col D, and put my new comment in other cells in Col M if the SAME ORDER # is referenced in adjacent Col D.

Example:

worksheet starts with:
Col D Col M
1234 x
123 xyz
1234 zyx
4321 zyx
321 y
1234 yz

If I type the letter "Q" into Col M in a row adjacent to order # 1234, the result should be:
Col D Col M
1234 Q
123 xyz
1234 Q
4321 zyx
321 y
1234 Q

I think I would need to put this macro into a Worksheet Change event but beyond that I do not know what I need. UDF? Macro?

Hope the above makes sense, thanks for help in advance!
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
try
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim a, b, i As Long, LastR As Long, myOrd, myValue
With Target
     If .Row < 9 Or .Column <> 13 Then Exit Sub
     If .Count > 1 Then
          With Application
               .EnableEvents = False
               .Undo
               .EnableEvents = True
          End With
          MsgBox "Can't change multiple cells at a time"
          Exit Sub
     End If
     myOrd = .Offset(,-9).Value
     myValue = .Value
End With
LastR = Range("d" & Rows.Count).End(xlUp)
a = Range("d9:d" & LastR).Value
b = Range("m9:m" & LastR).Value
For i = 1 To UBound(a,1)
     If a(i,1) = myOrd Then b(i,1) = myValue
Next
With Application
     .EnableEvents = False
     Range("m9").Resize(UBound(b,1)).Value = b
     .EnableEvents = True
End With
End Sub
 

Sayre

Board Regular
Joined
Apr 12, 2005
Messages
180
Thanks! But it may need some tweaking, or I am not doing it something right.

After pasting your code into the worksheet code section, I entered the word "comment" into cell M9. The macro did activate when I exited the cell. However it gives me error message of "Run-time error '424': Object Required

Its getting stuck on this part of the code:
"If .Row < 9 Or .Column <> 13 Then"

Any ideas?
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Hummm not usre why
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim a, b, i As Long, LastR As Long, myOrd, myValue
With Target
     If .Column <> 13 Then Exit Sub
     If .Row < 9 Then Exit Sub
     If .Count > 1 Then
          With Application
               .EnableEvents = False
               .Undo
               .EnableEvents = True
          End With
          MsgBox "Can't change multiple cells at a time"
          Exit Sub
     End If
     myOrd = .Offset(,-9).Value
     myValue = .Value
End With
LastR = Range("d" & Rows.Count).End(xlUp)
a = Range("d9:d" & LastR).Value
b = Range("m9:m" & LastR).Value
For i = 1 To UBound(a,1)
     If a(i,1) = myOrd Then b(i,1) = myValue
Next
With Application
     .EnableEvents = False
     Range("m9").Resize(UBound(b,1)).Value = b
     .EnableEvents = True
End With
End Sub
 

Sayre

Board Regular
Joined
Apr 12, 2005
Messages
180
Still not working.

I tried removing 'Exit Sub' lines from the code and ran again. Got the same error on the very next line, "If .Count > 1 Then".

Ugh...
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Ok try this one, and if you get an error, your excel is something wrong
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim a, b, i As Long, LastR As Long, myOrd, myValue
If Target.Column <> 13 Then Exit Sub
If Target.Row < 9 Then Exit Sub
If Target.Count > 1 Then
     With Application
          .EnableEvents = False
          .Undo
          .EnableEvents = True
     End With
     MsgBox "Can't change multiple cells at a time"
     Exit Sub
End If
myOrd = Target.Offset(,-9).Value
myValue = Target.Value
LastR = Range("d" & Rows.Count).End(xlUp).Row
a = Range("d9:d" & LastR).Value
b = Range("m9:m" & LastR).Value
For i = 1 To UBound(a,1)
     If a(i,1) = myOrd Then b(i,1) = myValue
Next
With Application
     .EnableEvents = False
     Range("m9").Resize(UBound(b,1)).Value = b
     .EnableEvents = True
End With
End Sub
 

Sayre

Board Regular
Joined
Apr 12, 2005
Messages
180
OK, I got past that error but it is now stuck on another part of the code. I am getting 'Type Mismatch error' on this line:

"LastR = Range("d" & Rows.Count).End(xlUp)"

FYI about the earlier errors, I had pasted your code into a regular macro module and then referenced that macro in the worksheet_change event.
When I put your raw code straight into the sheet module, the earlier error did not pop up but this new one did. Not sure what to make of that?
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
OOps
can you change to

LastR = Range("d" & Rows.Count).End(xlUp).Row
 

Sayre

Board Regular
Joined
Apr 12, 2005
Messages
180
That did it! Working like a charm now, thanks so much!

I am curious as to why this same code does not work when I put in to a regular macro and then reference that macro in the worksheet change module. Why does it give me 'Object Required' error? That seems bizarre to me but I'm sure there is a good reason.

If anyone has an idea why I would love to know, thanks!
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
That did it! Working like a charm now, thanks so much!

I am curious as to why this same code does not work when I put in to a regular macro and then reference that macro in the worksheet change module. Why does it give me 'Object Required' error? That seems bizarre to me but I'm sure there is a good reason.

If anyone has an idea why I would love to know, thanks!

That's should be the good resone.

You coudn't pass "Target" to the sub in a stadard module, and that's why it aksed an "Object".
No wonder.
 

Forum statistics

Threads
1,181,410
Messages
5,929,774
Members
436,688
Latest member
sunnyBNH013

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top