Using VBA to search a Range for a Value, and have it return the column of the value as a Variable

Question

I am new to VBA, and I am starting to do some basic coding.

My current end goal is to allow a cell in a table to be zeroed out based on the inputs of two data validated cells.

I currently have a sheet where I have managed to successfully implement what I am trying to do, but the code is lengthy and I am sure it can be streamlined by using the Find function. I tried to implement Find for a few hours before implementing this workaround.

Users select the current Forecast Month through a validated list in cell B4. Then they can choose to "zero out" the desired cell by selecting yes/no in cell B5. Months are laid out from B7:M7. My original goal was to have VBA take the selected month from B4, find that month in B7:M7, return that column, and then using the column data go to row 15 and zero out that cell. However, I could not find the appropriate way to code this into the find function without causing a compile error.

Below is the current code that I believe can be streamlined:

Private Sub Worksheet_Change_B(ByVal Target As Range)
If Target.Address(False, False) = "B5" Then
    If Range("B5").Value = "Yes" Then
        If Range("B4").Value = "Oct" Then
            Range("B15").Value = "0"
        ElseIf Range("B4").Value = "Nov" Then
            Range("C15").Value = "0"
        ElseIf Range("B4").Value = "Dec" Then
            Range("D15").Value = "0"
        ElseIf Range("B4").Value = "Jan" Then
            Range("E15").Value = "0"
        ElseIf Range("B4").Value = "Feb" Then
            Range("F15").Value = "0"
        ElseIf Range("B4").Value = "Mar" Then
            Range("G15").Value = "0"
        ElseIf Range("B4").Value = "Apr" Then
            Range("H15").Value = "0"
        ElseIf Range("B4").Value = "May" Then
            Range("I15").Value = "0"
        ElseIf Range("B4").Value = "Jun" Then
            Range("J15").Value = "0"
        ElseIf Range("B4").Value = "Jul" Then
            Range("K15").Value = "0"
        ElseIf Range("B4").Value = "Aug" Then
            Range("L15").Value = "0"
        ElseIf Range("B4").Value = "Sep" Then
            Range("M15").Value = "0"
        End If
    ElseIf Range("B5").Value = "No" Then
        If Range("B4").Value = "Oct" Then
            Range("B15").Value = Range("B35").Value
        ElseIf Range("B4").Value = "Nov" Then
            Range("C15").Value = Range("C35").Value
        ElseIf Range("B4").Value = "Dec" Then
            Range("D15").Value = Range("D35").Value
        ElseIf Range("B4").Value = "Jan" Then
            Range("E15").Value = Range("E35").Value
        ElseIf Range("B4").Value = "Feb" Then
            Range("F15").Value = Range("F35").Value
        ElseIf Range("B4").Value = "Mar" Then
            Range("G15").Value = Range("G35").Value
        ElseIf Range("B4").Value = "Apr" Then
            Range("H15").Value = Range("H35").Value
        ElseIf Range("B4").Value = "May" Then
            Range("I15").Value = Range("I35").Value
        ElseIf Range("B4").Value = "Jun" Then
            Range("J15").Value = Range("J35").Value
        ElseIf Range("B4").Value = "Jul" Then
            Range("K15").Value = Range("K35").Value
        ElseIf Range("B4").Value = "Aug" Then
            Range("L15").Value = Range("L35").Value
        ElseIf Range("B4").Value = "Sep" Then
            Range("M15").Value = Range("M35").Value
        End If
    End If
End If

End Sub

What I was originally thinking with the Find Function went like this, but I could not get it to compile properly.

Sub Test()

Dim rFind As Range

If Target.Address(False, False) = "B5" Then
    If Range("B5").Value = "Yes" Then 'If the user wants to zero out the cell on row 15
        With Range("B7:M7") 'Range of Months
            Set rFind = .Find(What:=("B4"), LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
                Range("rFind.Column,15").Value = "0" 'Set Value of cell in row 15 of referenced column to zero
        End With
    If Range("B5").Value = "No" Then 'If the user doesn't want to zero out cell on row 15
        With Range("B7:M7") 'Range of Months
            Set rFind = .Find(What:=("B4"), LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
                Range("rFind.Column,15").Value = Range("rFind.Column,35").Value 'Pulls in Previous Value
        End With
    End If
End Sub

Am I getting on track? How can I reference the month column in the cell address I would like to change?

score 3 · Answer 1 · edited Jun 20 '20 at 09:12

3

The syntax is wrong in these statements:

Range("rFind.Column,15").Value = 0

Range("rFind.Column,15").Value = Range("rFind.Column,35").Value

should be:

Cells(15, rFind.Column).Value = 0

Cells(15, rFind.Column).Value = Cells(35, rFind.Column).Value

Another syntax error here:

What:=("B4"), ...

should be

What:=Range("B4").Value, ...

For completeness, here's how you can simplify your first version:

Private Sub Worksheet_Change_B(ByVal Target As Range)

    ' we're only interested when cell B5 changes
    If Target.address <> "$B$5" Then Exit Sub

    ' First we will get the month's number from its name. We use the Month
    ' method of VBA but it needs a date. so we append a 1 to the month (i.e. Sep 1)
    Dim col As Long
    col = Month(Range("B4").value & " 1")

    ' Getting the column. Since First cell is in column 2 (B) we start at column 2
    ' Month 10 (Oct) comes first so we count from that using modulo 
    col = 2 + ((col + 2) Mod 12) ' since you start with Oct. at column B

    ' Finally we set the value at row 15 according to cell B5 (Target)
    ' We can shorten the If statement using IIF(condition, trueValue, falseValue)
    Cells(15, col).value = IIf(Target.value = "Yes", 0, Cells(35, col).value)

End Sub

edited Jun 20 '20 at 09:12

Community

1
1

answered Mar 13 '17 at 20:28

A.S.H

29,101
5
23
50

I can't edit at the moment, but there's some erroneous backticks being displayed - Cells(15, rFind.Column).Value = 0` – Adam Vincent Mar 13 '17 at 21:19
Thank you for the help! I never thought about going about it in that respect. Could you explain a little bit about what is happening when you Dim col As Long and then the following? Just trying to follow along. – D. Horowitz Mar 14 '17 at 17:41
@D.Horowitz sure, I will add more comments. – A.S.H Mar 14 '17 at 18:00
@D.Horowitz done, I hope the comments in code make it clear enough :) – A.S.H Mar 14 '17 at 18:11
I see! That makes a lot of sense. The IIF statement is a great way of condensing Ifs as well. – D. Horowitz Mar 14 '17 at 18:30

score 2 · Answer 2 · edited May 23 '17 at 12:17

A couple of Select Case statements and some rudimentary maths make quick work of all of your conditions. Once a pattern is found, some date functions achieve the ordinal month which is adjusted to match your offset.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address(False, False) = "B5" Then
        On Error GoTo Safe_exit
        Application.EnableEvents = False
        If LCase(Range("B5").Value2) = "yes" Then
            Select Case LCase(Range("B4").Value2)
                Case "jan", "feb", "mar", "apr", "jun ", "jul", "aug", "sep", "oct", "nov", "dec"
                    Cells(15, Month(DateSerial(2017, Month(DateValue("1-" & Range("B4").Value2)) + 3, 1)) + 1) = 0
                Case Else
                    'do nothing
            End Select
        ElseIf LCase(Range("B5").Value2) = "no" Then
            Select Case LCase(Range("B4").Value2)
                Case "jan", "feb", "mar", "apr", "jun ", "jul", "aug", "sep", "oct", "nov", "dec"
                    Cells(15, Month(DateSerial(2017, Month(DateValue("1-" & Range("B4").Value2)) + 3, 1)) + 1) = _
                      Cells(35, Month(DateSerial(2017, Month(DateValue("1-" & Range("B4").Value2)) + 3, 1)) + 1).Value
                Case Else
                    'do nothing
            End Select
        End If
    End If
Safe_exit:
    Application.EnableEvents = True
End Sub

Don't worry about qualifying the parent worksheet. You are in a private sub on the worksheet's code sheet. Any unqualified range or cells reference is assumed to belong to the code sheet's worksheet (see Is the . in .Range necessary when defined by .Cells? for more information).

Thank you for the help! I didn't know of this ability in VBA, and its definitely going to help me on some other elements of the project. — D. Horowitz, Mar 14 '17 at 17:41

score 0 · Answer 3 · answered Mar 13 '17 at 21:17

I'm not too sure about your month sequence. You could try:

Private Sub Worksheet_Change_B(ByVal Target As Range)
    If Target.address <> "$B$5" Then Exit Sub
    With Range("B7:M7").Find(What:=Range("B4").Value, lookin:=xlValues, lookAt:=xlWhole)
        Cells(15, .Column).value = IIf(Target.value = "Yes", 0, Cells(35, .Column).value)
    End With
End Sub

Using VBA to search a Range for a Value, and have it return the column of the value as a Variable

3 Answers3