VBA Selecting Specific Cells Based Off Cell Value

jherup

New Member
Joined
Jun 2, 2022
Messages
2
Office Version
  1. 365
Platform
  1. Windows
I am trying to create a macro that looks through a column of data, and if the value of a cell is #N/A, select the cell that is in the adjacent column. It should keep going down the column until it reaches an empty cell. I believe the most efficient way to do this is to have the macro call a recursive function (I could be very wrong in thinking this I don't have a whole lot of experience with VBA this complex), so that is the route I started to take. However, I keep getting Run-Time error '91': Object variable or With block variable not set. This error occurs on the line with the Union. Again, I am not used to anything this complex so feel free to tell me I'm doing this terribly wrong.
Code:
Function multipleSelect(column As Integer, row As Integer, ws As Object, WorksheetFunction) As CellFormat
    If WorksheetFunction.IsNA(Cells(row, column)) Then
        Union(ws.Cells(row, column-1)), multipleSelect = (multipleSelect(column, row+1, ws, WorksheetFunction))).Select
    ElseIf ws.Cells(row, column).Value <> "" Then
        Call multipleSelect(column, row + 1, ws, WorksheetFunction
    End If
End Function
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
SOLVED: This line of code does exactly what I was trying to accomplish.

VBA Code:
ws.Range("X:X").SpecialCells(xlCellTypeFormulas, xlErrors).Offset(0,-1).Select
 
Upvote 0

Forum statistics

Threads
1,223,237
Messages
6,170,928
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top