Cells not updating with Worksheet_Change(ByVal Target As Excel.Range)

[LlebKcir]

Thank you everyone in advance for the help. I am still learning basic VBA so this one is a bit of a stretch...

For all cells in column A that are updated (be that manually entered, or updated via click and drag, other) I want a specific output in column B. I have the if statements working as desired when data is manually entered into column A, but any other type of manipulation including mass select and delete fail to update column B.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.Column = 1 Then
        ThisRow = Target.Row


            If Range("a" & ThisRow).Value Like "*foo1*" Or Range("a" & ThisRow).Value Like "*foo2*" Then
                Range("b" & ThisRow).Value = "OUT1"
            Else
                If Range("a" & ThisRow).Value Like "*foo3*" Or Range("a" & ThisRow).Value Like "*foo4*" Or Range("a" & ThisRow).Value Like "*foo5*" Then
                    Range("b" & ThisRow).Value = "OUT2"
            Else
                If Range("a" & ThisRow).Value Like "*foo6*" Or Range("a" & ThisRow).Value Like "*foo7*" Or Range("a" & ThisRow).Value Like "*foo8*" Or Range("a" & ThisRow).Value Like "*f0016*" Then
                    Range("b" & ThisRow).Value = "OUT3"
            Else
                If Range("a" & ThisRow).Value Like "*foo9*" Or Range("a" & ThisRow).Value Like "*foo10*" Or Range("a" & ThisRow).Value Like "*foo11*" Or Range("a" & ThisRow).Value Like "*foo17*" Then
                    Range("b" & ThisRow).Value = "OUT4"
            Else
                If Range("a" & ThisRow).Value Like "*foo12*" Then
                    Range("b" & ThisRow).Value = "OUT5"
            Else
                If Range("a" & ThisRow).Value Like "*foo13*" Or Range("a" & ThisRow).Value Like "*foo14*" Or Range("a" & ThisRow).Value Like "*foo15*" Then
                    Range("b" & ThisRow).Value = "OUT6"
            Else
                If IsEmpty(Range("a" & ThisRow).Value) Then
                    Range("b" & ThisRow).Value = ""
            End If
            End If
            End If
            End If
            End If
            End If
            End If


    End If


End Sub

so rather simple set of code, but what am i missing to enable the click/drag or double click on a cell in column A to update all of the equal number of cells in column B?

Thank you again in advance.

 

[My Aswer Is This]

Please explain in words what your trying to do.
Writing line after line of code with a lot of if statements should not be needed

So just say something like this:

If I enter this in Row 1 column 1 do this in column B Row 1

Not sure why your using

"*foo12*"


And then you have

OUT5

So if you enter a value in A5 B5 would say

OUT5
I do not understand this:

"*foo12*"

So do you mean any value that has foo12

Like: I like foo12 do you​

​

​

<strike></strike>

​

 

[LlebKcir]

Please explain in words what your trying to do.
Writing line after line of code with a lot of if statements should not be needed

So just say something like this:

If I enter this in Row 1 column 1 do this in column B Row 1

Not sure why your using

"*foo12*"


And then you have

OUT5

So if you enter a value in A5 B5 would say

OUT5
I do not understand this:

"*foo12*"

So do you mean any value that has foo12

Like: I like foo12 do you​

​

​

<strike></strike>

​

foo and OUT are just placeholders for internal values. think private data.

If column A, cell X is updated with a select value of foo, then OUTX into column B. I would like that to happen no matter how column A is populated. Currently this action only functions when column A is manually updated. Select a large range of cells in column A and delete, only the lowest row number in column B gets updated. Double click, or click and drag a cell in column A, only the lowest row level gets updated in column B.

The desired output is under any circumstance that column A is updated, so should the same number of cells be updated in column B.

sorry if my OP was not clear enough.

 

[My Aswer Is This]

Not sure still what this means:

foo and OUT are just placeholders for internal values. think private data. ​

Are you saying you cannot tell me what foo is or what OUT is because that's private data

Then why not tell me something like this if Cat is entered into A3 then I want Dog entered in B3

And then when I send you the script you can change the script how you want.

 

[LlebKcir]

Not sure still what this means:

foo and OUT are just placeholders for internal values. think private data. ​

Are you saying you cannot tell me what foo is or what OUT is because that's private data

Then why not tell me something like this if Cat is entered into A3 then I want Dog entered in B3

And then when I send you the script you can change the script how you want.

While I may not be a great coder, foo is standard for fill_in_the_blank, and OUT is just that some output.

Those portions of the code are functioning exactly as desired. What is not functioning as desired is the fact that column B only updates with column A is manually touched. standard Excel manipulations to copy/paste data do not trigger the correct action. You can not select a range of cells, right click, copy, select another cell, right click, paste. That does not trigger the proper output in column B.

Not sure how to make that any more clear.

 

[Fluff]

How about something like

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
   Dim Cl As Range
   If Target.Column = 1 Then
      For Each Cl In Intersect(Target, Range("A:A"))
         If Cl.Value Like "*foo13*" Or Cl.Value Like "*foo14*" Or Cl.Value Like "*foo15*" Then
            Cl.Offset(, 1).Value = "OUT6"
         ElseIf Cl.Value Like "*foo12*" Then
            Cl.Offset(, 1).Value = "OUT5"
         ElseIf Cl.Value Like "*foo9*" Or Cl.Value Like "*foo10*" Or Cl.Value Like "*foo11*" Or Cl.Value Like "*foo17*" Then
            Cl.Offset(, 1).Value = "OUT4"
         End If
      Next Cl
   End If
End Sub

 

[LlebKcir]

How about something like

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
   Dim Cl As Range
   If Target.Column = 1 Then
      For Each Cl In Intersect(Target, Range("A:A"))
         If Cl.Value Like "*foo13*" Or Cl.Value Like "*foo14*" Or Cl.Value Like "*foo15*" Then
            Cl.Offset(, 1).Value = "OUT6"
         ElseIf Cl.Value Like "*foo12*" Then
            Cl.Offset(, 1).Value = "OUT5"
         ElseIf Cl.Value Like "*foo9*" Or Cl.Value Like "*foo10*" Or Cl.Value Like "*foo11*" Or Cl.Value Like "*foo17*" Then
            Cl.Offset(, 1).Value = "OUT4"
         End If
      Next Cl
   End If
End Sub

Fluff, I will try that when I get back in the office tomorrow.

Thank you, but what is the difference in using the for loop combined with the ifelse statements vs just using the ifelse statements?

 

[Fluff]

The for loop is just looping through the Target area in col A.
The If statements are still there, I just rearranged it into (what IMO is) a neater way.

 

[LlebKcir]

Thank you for the explanation.

 

[LlebKcir]

Thank you that did more and found some other tips along the way. here is the final resault, but i do not understand what the roww is inside of the cell.( , ).

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim rng As Range
    Dim WorkRng As Range
    Set WorkRng = Intersect(Range("A2:A1000"), Target)
    If Not WorkRng Is Nothing Then
        ThisRow = Target.Row
        For Each rng In WorkRng
            roww = rng.Row
            If Cells(roww, "A").Value Like "*a*" Or Cells(roww, "A").Value Like "*b*" Then
                Cells(roww, "B").Value = "OUT1"
            ElseIf Cells(roww, "A").Value Like "*c*" Or Cells(roww, "A").Value Like "*d*" Or Cells(roww, "A").Value Like "*e*" Then
                    Cells(roww, "B").Value = "OUT2"
            ElseIf Cells(roww, "A").Value Like "*f*" Or Cells(roww, "A").Value Like "*g*" Or Cells(roww, "A").Value Like "*h*" Or Cells(roww, "A").Value Like "*i*" Then
                    Cells(roww, "B").Value = "OUT3"
            ElseIf Cells(roww, "A").Value Like "*j*" Or Cells(roww, "A").Value Like "*k*" Or Cells(roww, "A").Value Like "*l*" Or Cells(roww, "A").Value Like "*m*" Then
                    Cells(roww, "B").Value = "OUT4"
            ElseIf Cells(roww, "A").Value Like "*n*" Then
                    Cells(roww, "B").Value = "OUT5"
            ElseIf Cells(roww, "A").Value Like "*o*" Or Cells(roww, "A").Value Like "*p*" Or Cells(roww, "A").Value Like "*q*" Then
                    Cells(roww, "B").Value = "OUT6"
            ElseIf IsEmpty(Cells(roww, "A").Value) Then
                    Cells(roww, "B").Value = ""
            End If
        Next
    End If
End Sub

Thank you again fluffy

p.s. how do i now mark this thread solved?