Loop to find cell, then using that cell reference to clear a range

Question

I am trying to write some code, that will search the first 30 columns and rows for the words Total and Area. I am looking to store the locations of these words in a variable and then use these variables to clear a range relative to them, this then loops across all worksheets.

I have tried to use a number to letter converter that I found online to store the column number, and I think this is where my problem is coming in.

Here is the code I found online:

Function Col_Letter(lngCol As Long) As String
    Dim vArr
    vArr = Split(Cells(1, lngCol).Address(True, False), "$")
    Col_Letter = vArr(0)
End Function

and my code:

Private Sub Clear_Click()
    Dim LastRowH As Integer
    Dim ClearContent As Boolean
    Dim ws As Worksheet
    Dim testrange As Range
    Dim Cell1 As Range
    Dim Celln As Range
    ClearContent = False
    For Each ws In ActiveWorkbook.Worksheets
        'FINDS RANGE
        For i = 1 To 30
            For j = 1 To 30
                If ActiveWorkbook.Sheets(ws).Range(Col_Letter(CLng(i)) & j).Value = "Total" Then
                    Cell1 = ws.Range(Col_Letter(CLng(i + 1)) & j)
                End If
                If ActiveWorkbook.Sheets(ws).Range(Col_Letter(CLng(i)) & j).Value = "Area" Then
                    Celln = ws.Range(Col_Letter(CLng(i + 1)) & j - 1)
                End If
            Next
        Next
        '...<more code here>...
        If ClearContent = True Then
            '...<more code here>...
            ws.Range(Cell1 & ":" & Celln).ClearContents
        End If
    Next ws
End Sub

When I run the code, I get the error message:

Run-time error '13': Type Mismatch

I have tried a couple of other methods but cannot get it to work.

Any help is appreciated, Thanks in advance :)

UPDATE I have tried replacing the for loops in the code to use the "Cells" function, as follows:

For i = 1 To 30
            For j = 1 To 30
            If Sheets(ws).Cells(j, i).Value = "Total" Then
                    Set Cell1 = ws.Cells(j - 1, i + 1)
                End If
                If Sheets(ws).Cells(j, i).Value = "Area" Then
                    Set Celln = ws.Cells(j, i + 1)
                End If
            Next
        Next

But I am still receiving the Type Mismatch

There are several ways to convert a column number to letter. Here's [one of my answers](https://stackoverflow.com/a/49577009/8112776) on a post with 31 other answers. (I think this is the easiest way, but I'm biased! :-) — ashleedawg, Sep 08 '18 at 13:08
You already have **i** and **j**, so you can use `Cells(j,i)` rather than `Range(Col_Letter(CLng(i)) & j)` — Gary's Student, Sep 08 '18 at 13:32
I tried using the Cells(j,i) method, but when it wasn't letting me assign it to a variable — George, Sep 08 '18 at 13:54
Also @ashleedawg I have had a look at your suggestion on the other thread but am unsure how I would go about applying this to my own code — George, Sep 08 '18 at 14:07
It looks like the main problem is you are mismatching range objects and string variables. You define `Cell1` and `Celln` as Range objects, but then later try to use them as String variables. Decide which way you want to go with, and if you want to use them as Range variables, remember to use the `Set` keyword when assigning them, and then just reference them directly, like `Range(Cell1, Celln).ClearContents` — tigeravatar, Sep 08 '18 at 15:07
Also, if you decide to use them as String variables, then the only change to your code would be to `Dim Cell1 As String` (same with `Celln`) and then also to use the `.Address` property when assigning their values: `Cell1 = ws.Range(Col_Letter(CLng(i + 1)) & j).Address` (and same with `Celln`) or alternately: `Cell1 = Col_Letter(CLng(i + 1)) & j` — tigeravatar, Sep 08 '18 at 15:10
I have attempted to change this, but I am still getting Type Mismatch — George, Sep 08 '18 at 18:09

score 0 · Answer 1 · answered Sep 08 '18 at 15:22

Your Type Mismatch is due ActiveWorkbook.Sheets(ws).Range ws is a worksheet, not an index or name. ws.range will scan the ranges of that worksheet. Few other modifications have been made see comments.

Function Col_Letter(lngCol As Long) As String
    Dim vArr
    vArr = Split(Cells(1, lngCol).Address(True, False), "$")
    Col_Letter = vArr(0)
End Function

Sub test()
    Dim LastRowH As Integer
    Dim ClearContent As Boolean
    Dim ws As Worksheet
    Dim testrange As Range
    Dim Cell1 As Range
    Dim Celln As Range
    ClearContent = False
    For Each ws In ActiveWorkbook.Worksheets
        'FINDS RANGE
        For i = 1 To 30
            For j = 1 To 30
                If ws.Range(Col_Letter(CLng(i)) & j).Value = "Total" Then
                    Set Cell1 = ws.Range(Col_Letter(CLng(i + 1)) & j)  ' Set This
                End If
                If ws.Range(Col_Letter(CLng(i)) & j).Value = "Area" Then
                    Set Celln = ws.Range(Col_Letter(CLng(i + 1)) & j - 1)  ' Set This
                End If
            Next
        Next
        '...<more code here>...
        'ClearContent = True    ' Me Testing
        If ClearContent = True Then
            '...<more code here>...
            Cell1.ClearContents
            Celln.ClearContents

            'ws.Range(Cell1 & ":" & Celln).ClearContents  ' don't think this will work properly
        End If
    Next ws
End Sub

I have added your changes to my code, but it seems I am still getting the Type mismatch error with this line of code highligthed: 'If ws.Range(Col_Letter(CLng(i)) & j).Value = "Total" Then' — George, Sep 08 '18 at 15:47

Loop to find cell, then using that cell reference to clear a range

1 Answers1