Insert Values Based on another Column

[kamalm]

Hi I need help to create a macro that can add values at Column J based on Column B.

For an example, if the values at Column B is between 1 until 6, the Column J should produce "min. material level". If the values at Column B between 7 until 12, the Column J should have "Material flow" and if the values Column B between 13 until 18, the Column J should display "No Comp. Air".

thanks!

 

[cliftonvilla]

You can do this as an Excel nested IF formula which would update automatically as the values in Column B change, or use a simple macro such as:

Sub test()
Dim colB As Range
Dim colJ As Range
Set colB = Range("B1:B200")
Set colJ = Range("J1:J200")
For ii = 1 To 200
If colB.Cells(ii) >= 1 And colB.Cells(ii) <= 6 Then
colJ.Cells(ii) = "min.material level"
ElseIf colB.Cells(ii) >= 7 And colB.Cells(ii) <= 12 Then
colJ.Cells(ii) = "Material flow"
ElseIf colB.Cells(ii) >= 13 And colB.Cells(ii) <= 18 Then
colJ.Cells(ii) = "No Comp.Air"
End If
Next ii

End Sub

You can run the macro manually with a button or automatically within VBA e.g. on an event.

If you have many values best to lift the values off the spreadsheet within the macro using 'variant' instead of 'range' variables, would be much faster.

 

[kamalm]

Hi, thanks for responding. But what if I need to change the values 1 until 6 become A-01, A-02, A-03, A-04, A-05, A-06 at column B. It does display same result at Column J which is "min.material level". how does the code looks like ?

 

[Takae]

Please past below code in Sheet module.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B:B")) Is Nothing Then
        Exit Sub
    Else
        Select Case Right(Target.Value, 2) * 1
        Case Is <= 6
            cells(Target.Row, 10).Value = "min. material level"
        Case Is <= 12
            cells(Target.Row, 10).Value = "Material flow"
        Case Is <= 18
            cells(Target.Row, 10).Value = "No Comp. Air"
        End Select
    End If
End Sub

 

[cliftonvilla]

You can still use conditions with strings provided the string range is contiguous e.g.

Sub test()
Dim colB As Range
Dim colJ As Range
Set colB = Range("B1:B200")
Set colJ = Range("J1:J200")
For ii = 1 To 200
If colB.Cells(ii) >= "A-01" And colB.Cells(ii) <= "A-06" Then
colJ.Cells(ii) = "min.material level"
ElseIf colB.Cells(ii) >= "A-07" And colB.Cells(ii) <= "A-12" Then
colJ.Cells(ii) = "Material flow"
ElseIf colB.Cells(ii) >= "A-13" And colB.Cells(ii) <= "A-18" Then
colJ.Cells(ii) = "No Comp.Air"
End If
Next ii

End Sub


If not contiguous you can use a lookup table to assign the results e.g. set up a small table containing the A-01 strings, each with an associated result for Column J.

 

[kamalm]

If not contiguous you can use a lookup table to assign the results e.g. set up a small table containing the A-01 strings, each with an associated result for Column J.

Thank you! I'll try soon . Thanks mate