Macro to read text in cell and write in adjacent cell

[shre0047]

Hi All,
I'm trying to come up with a macro where for a specific column, depending on what each cell value is, the adjacent value is provide a certain value.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Header 1[/TD]
[TD]Header 2[/TD]
[TD]Header 3[/TD]
[TD]Header 4[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD][/TD]
[TD]050[/TD]
[TD]Ignore[/TD]
[/TR]
[TR]
[TD]Jill[/TD]
[TD][/TD]
[TD]100[/TD]
[TD]Don't Ignore[/TD]
[/TR]
[TR]
[TD]Jack[/TD]
[TD][/TD]
[TD][/TD]
[TD]Talk to[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD][/TD]
[TD]050[/TD]
[TD]Ignore[/TD]
[/TR]
</tbody>[/TABLE]
For example, in cell D2, I have the value set as "Ignore." The macro would look at this cell, determine it is value of "Ignore" then write in cell C2, 050.
Additionally, in cell D4, the value is set as "Talk to." So the value of C4 will be blank (if there was a previous value, it would be removed)

 

[philwojo]

=IF(D2="Ignore","050",IF(D2="Don't Ignore","100",""))

Assuming that you don't have any other values other than those 3 in column-D.

Put that in C2 and copy down.

 

[shre0047]

=IF(D2="Ignore","050",IF(D2="Don't Ignore","100",""))

Assuming that you don't have any other values other than those 3 in column-D.

Put that in C2 and copy down.

End users are going to be able to alter column C to different values (more than 3), so I was hoping to avoid using formulas.

 

[daverunt]

Hi,

here's the basic code to make a start.

Sub Action_add()

 Set rng = Range(Range("D2"), Range("D" & Rows.Count).End(xlUp))
  
   For Each cell In rng
    
    Select Case cell.Value
    
    Case "Ignore"
    
    cell.Offset(0, -1).Value = 50
    
    Case "Don't Ignore"
    
    cell.Offset(0, -1).Value = 100
    
    Case "Talk to"
    
    cell.Offset(0, -1).Value = ""
    
    Case Else
    
    End Select
   
Next
End Sub

 

[shre0047]

Thank you! This was a great start to the macro. I tweaked a few things to make it work for me.

Hi,

here's the basic code to make a start.

Sub Action_add()

 Set rng = Range(Range("D2"), Range("D" & Rows.Count).End(xlUp))
  
   For Each cell In rng
    
    Select Case cell.Value
    
    Case "Ignore"
    
    cell.Offset(0, -1).Value = 50
    
    Case "Don't Ignore"
    
    cell.Offset(0, -1).Value = 100
    
    Case "Talk to"
    
    cell.Offset(0, -1).Value = ""
    
    Case Else
    
    End Select
   
Next
End Sub

 

[shre0047]

I removed the line item regarding set 'Talk to' cell values as "" because if the cell was already entered by an end user, I don't want to lose that information.

[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD]Header 1[/TD]
[TD]Header 2[/TD]
[TD]Header 3[/TD]
[TD]Header 4[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD][/TD]
[TD]050[/TD]
[TD]Ignore[/TD]
[/TR]
[TR]
[TD]Jill[/TD]
[TD][/TD]
[TD]100[/TD]
[TD]Don't Ignore[/TD]
[/TR]
[TR]
[TD]Jack[/TD]
[TD][/TD]
[TD][/TD]
[TD]Talk to[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD][/TD]
[TD]050[/TD]
[TD]Ignore[/TD]
[/TR]
</tbody>[/TABLE]

What aspect do I need to include so that if D2 eventually changes from Ignore to Talk to, the 050 changes to blank?

 

[daverunt]

Hi,

It was explained that the entries in column 4 would determine the values in column 3 and that if 'Talk to' was encountered the adjacent cell would have any value removed.
So the macro employed just goes down column 4 and the content of column 4 determines what is in column 3.

So now you keep the user entered value against the Talk to and you want to change 050 to blank if it changes in future from Ignore to Talk to.

Can you potentially have an 050 with Ignore and an 050 with Talk to at any point in time? If not the code below may work but I suspect it may become more convoluted.
My other question is why, if you are editing the Ignore to Talk to, you can't manually clear the cell right next to the one you are editing?

    Case "Talk to"
    
    If Cell.Offset(0, -1).Value = "050" Then
       Cell.Offset(0, -1).Value = ""
       Else
       End If
    
    Case Else