CODEWITHSUNDEEP
excelvba
CDay
CDay

Reputation: 111

Find a Text Value, Offset, Enter Formula based on OffSet Values

I have another VBA question please.

  1. I have a Table in Excel, I want to find the text: "All Other" that will always be in Column B, but may not be in the same Row #.

  2. After I find "All Other" cell, I want to enter a Sum formula in the next column over (0,1).

  3. The formula would Sum the unknown range starting with 3 rows down from the Activecell to the end of the data.

I'm getting an error: Invalid or unqualified reference.

PrintScreen:

enter image description here

I currently have:

Dim ws As Worksheet
Dim aOther As Range
Dim DataLastRow As Range

Set ws = ActiveSheet

Set DataLastRow = ws.Cells.Range(ws.Rows.Count, 1).End(xlUp).Rows

    Set aOther = ws.Range("B:B").Find("All Other", LookIn:=xlValues, lookat:=xlWhole)
    ActiveCell.Offset(0, 1).Formula = "=SUM(" & .Offset(3, 0) & DataLastRow & ")"

Upvotes: 0

Views: 264

Answers (1)

Scott Craner
Scott Craner

Reputation: 152495

Dim ws As Worksheet
Dim aOther As Range
Dim DataLastRow As Long

Set ws = ActiveSheet

DataLastRow = ws.Cells(ws.Rows.Count, 3).End(xlUp).Row

    Set aOther = ws.Range("B:B").Find("All Other", LookIn:=xlValues, lookat:=xlWhole)

    If Not aOther is Nothing Then
        aOther.Offset(0, 1).Formula = "=SUM(" & aOther.Offset(3, 1).Address & ":" & ws.Cells(DataLastRow,3).Address & ")"
    Else
        MsgBox """All Other"" not found in column."
    End If

Upvotes: 1

Related Questions