CODEWITHSUNDEEP
vbaexcel
alex2002
alex2002

Reputation: 161

IF range doesn't return the value needed

I am facing one problem and I don't understand how it can be fixed. It is a simple code: if range("A1:A100") = "Product" then range("B1:B100") should have automatically 1 (this range is empty). So for example: If anyone writes in A1 = Product then B1 should automatically have 1. Else, if Product is deleted from A1, then 1 should be deleted from B1. My code is below:

Thanks in advance!

Sub product(o As Long)

If Cells(o, "A") = "Product" And Cells(o, "B") = "" Then
    Cells(o, "B") = "1"
ElseIf Cells(o, "A") = "" And Cells(o, "B") = "1" Then
    Cells(o, "B") = ""

End If

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)


Dim cell As Range
Dim cell1 As Range
Set cell = Intersect(Range("b1:b100"), Target)
If Not cell Is Nothing Then
    For Each cell1 In cell.Rows
        product cell1.Row
    Next cell1
End If


End Sub

Upvotes: 2

Views: 113

Answers (2)

user4039065
user4039065

Reputation:

In the worksheet's code sheet,

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Columns(1), Target) Is Nothing Then
        On Error GoTo safe_exit
        Application.EnableEvents = False
        Dim trgt As Range
        For Each trgt In Intersect(Columns(1), Target, Target.Parent.UsedRange)
            Select Case LCase(trgt.Value2)
                Case "product"
                    trgt.Offset(0, 1) = 1
                Case ""
                    trgt.Offset(0, 1) = vbnullstring
                Case Else
                    'do nothing
            End Select
        Next trgt
    End If
safe_exit:
    Application.EnableEvents = True

End Sub

Upvotes: 1

Vityata
Vityata

Reputation: 43585

Actually just a small fix is needed:

Set cell = Intersect(Range("A1:B100"), Target)

instead of b1:b100. You can even consider Range("A:B"), if you want to leave it active for the whole column.

Upvotes: 1

Related Questions