CODEWITHSUNDEEP
vbaexcel
MattE
MattE

Reputation: 1114

Cannot access Cells in a Sub from a passed in Worksheet

I'm trying to write a custom DeleteRows Sub that I can call from various points in my code, but I am running into an issue where the worksheet I am passing in doesn't seem to have any cells associated with it, and run into "Type Mismatch" errors when running it. I am sorting to get a certain value to appear at the beginning and then looping through to see how many cells it appears in and then deleting those rows.

Option Explicit

Public Sub DeleteRows(ByRef MySheet As Worksheet, RowsToDelete As Long, ColumnToUse As String, ValueToSearch As String, UseAsInt As Boolean)
    Dim MyLong As Long
    If UseAsInt Then 'We are looking for a numeric value
        MyLong = CLng(ValueToSearch)
        Do While MySheet.Cells(RowsToDelete, ColumnToUse).Value = MyLong
            RowsToDelete = RowsToDelete + 1
        Loop
    Else
        Do While MySheet.Cells(RowsToDelete, ColumnToUse).Value = ValueToSearch
            RowsToDelete = RowsToDelete + 1
        Loop
    End If
    If RowsToDelete > 2 Then 'If the row is 2 then no rows were found
        MySheet.Rows(2 & ":" & RowsToDelete - 1).Delete 'Delete the rows up to the lastRowToDelete minus 1 row(because it started at 2)
    End If
End Sub

I am calling it from another Sub:

Dim CurDay as Worksheet
Set CurDay = Sheets("Current Day")

Call DeleteRows(CurDay, 2, "L","#N/A", False)

However when I add a watch to MySheet in the DeleteRows Sub, it says there are no cells in the array and I get a Type Mismatch error. Where am I going wrong here? VBA is so frustrating at times coming from a C#/VB.Net background...

UPDATE: Found out I had to check for .Text instead of .Value or .Value2 and it works...

Upvotes: 1

Views: 61

Answers (1)

A.S.H
A.S.H

Reputation: 29332

That's not how you should compare error cells. When the cell is #N/A you cannot compare it to something else like a string or a number. You should first check if the value is an error using IsError(cel).

Alternatively you can use the .Text property, which works fine with erroneous cells, returning a "#N/A" string instead of an Error Variant.

You then have to face the issue of comparing strings to numbers. Easiest, drop the UseAsInt parameter; use always a string and compare toward the .Text property.

Public Sub DeleteRows(ByRef MySheet As Worksheet, RowsToDelete As Long, ColumnToUse As String, ValueToSearch As String)
  Do While MySheet.Cells(RowsToDelete, ColumnToUse).Text = ValueToSearch
      RowsToDelete = RowsToDelete + 1
  Loop
  If RowsToDelete > 2 Then 'If the row is 2 then no rows were found
      MySheet.Rows(2 & ":" & RowsToDelete - 1).Delete 'Delete the rows up to the lastRowToDelete minus 1 row(because it started at 2)
  End If
End Sub

Upvotes: 1

Related Questions