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

[Sayre]

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!

 

[jindon]

try

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]

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]

Hummm not usre why

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]

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]

Ok try this one, and if you get an error, your excel is something wrong

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]

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]

OOps
can you change to

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

 

[Sayre]

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]

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.