get value in a cell based on another cell-CodePudding

am trying to get "values" in cells based on another cells "text" with multiple ranges. if I put "ok" in "A1" then I get the value "66" in "B1" automatically likewise I want to put values one by one in "A2", "A3", "A4"... and to get values in B2, B3 B4...

here I have a code for a single cell.

    If Not Application.Intersect(Target, range("A1")) Is Nothing Then
        If Val(Target.Value) = ok Then
            range("B1") = "66"
        End If
    End If
End Sub

CodePudding user response：

I assume that you want the results to populate in the adjacent B column based on what they select in column A.

you could adapt your code to say:

Dim xRow As Long

xRow = ActiveCell.Row

If Not Application.Intersect(Target, Range("A" & xRow)) Is Nothing Then
   If Target.Value = "ok" Then
       Range("B" & xRow) = "66"
   End If
End If

CodePudding user response：

This solution allows you to process both single and batch data entry into adjacent and non-adjacent cells of the specified column, forming the target value in other cells specified by the offset

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Rng As Range, cl As Range
    Set Rng = Application.Intersect(Target, Me.Columns("A"))
    If Not Rng Is Nothing Then
        For Each cl In Rng
            If cl = "ok" Then cl.Offset(, 1) = "66"
        Next
    End If
End Sub

Result: