CODEWITHSUNDEEP
vbaexcel
Srikanth Yadake
Srikanth Yadake

Reputation: 573

Need simple generic vba macro script to change value of cell based on value of other cell

Need simple generic vba macro script to change value of cell based on value of other cell.

I have values in the range from e1:e1000. In any of these cell in the range if the value is "x" then its adjacent 2 cells in respective rows should change the value to "y"

eg: if e1 = "x" then f1 = "y" and g1 = "y"

Similarly for other cells too...

Upvotes: 1

Views: 21022

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149335

Srikanth, Reafidy has a point. Even I would prefer a formula. However if you still want a VBA code here it is.

USING FORMULA

Type this in Cell F1 and G1 and simply drag it down till F1000 and G1000

=IF(E1="X","Y","")

USING CODE

Sub Sample()
    Sheets("Sheet1").Range("F1:F1000").Formula = "=If(E1=""X"",""Y"","""")"
    Sheets("Sheet1").Range("G1:G1000").Formula = "=If(E1=""X"",""Y"","""")"
End Sub

FOLLOW UP

Sheets("Sheet1").Range("F1:F1000").Formula = "=If(D1=""Ready"",""Ready"","""")"

OR if you do not want to use the formulas altogether then use this

Sub Sample()
    For i = 1 To 1000
        With Sheets("Sheet1")
            If .Range("D" & i).Value = "Ready" Then _
            .Range("F" & i).Value = "Ready" Else .Range("F" & i).Value = ""
        End With
    Next i
End Sub

MORE FOLLOW UP

thanks for reply. I tried using the one without the formula,But even this is failing. i have put the code inside Worksheet_Change function. its throwing error with 'With Sheets("Sheet1")' line, even though Worksheet_Change is in Sheet1. know why? – Srikanth Yadake 11 mins ago

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Whoa

    If Not Intersect(Target, Range("D1:D1000")) Is Nothing Then
        Application.EnableEvents = False
        If Target.Value = "Ready" Then _
        Target.Offset(, 2).Value = "Ready" Else Target.Offset(, 2).Value = ""
    End If

LetsContinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

Upvotes: 2

Related Questions