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

[georgedixon]

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 AsLong)AsString
    Dim vArr
    vArr= Split(Cells(1, lngCol).Address(True,False),"$")
    Col_Letter= vArr(0)
EndFunction

and my code:

PrivateSub Clear_Click()
    Dim LastRowH AsInteger
    Dim ClearContent AsBoolean
    Dim ws As Worksheet
    Dim testrange As Range
    Dim Cell1 As Range
    Dim Celln As Range
    ClearContent=False
    ForEach ws In ActiveWorkbook.Worksheets
        'FINDS RANGE
        For i =1To30
            For j =1To30
                If ws.Range(Col_Letter(CLng(i))& j).Value ="Total"Then
                    Cell1= ws.Range(Col_Letter(CLng(i +1))& j)
                EndIf
                If ws.Range(Col_Letter(CLng(i))& j).Value ="Area"Then
                    Celln= ws.Range(Col_Letter(CLng(i +1))& j -1)
                EndIf
            Next
        Next
        '...<more code here>...
        If ClearContent =TrueThen
            '...<more code here>...
            ws.Range(Cell1 &":"& Celln).ClearContents
        EndIf
    Next ws
EndSub

With the above, I get a type mismatch error. I have also tried another method, by instead replacing the loops with the following, but still get the type mismatch:

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

Following from this, I attempted to scrap the loop and I wrote the following, in place of the loop:

Set Cell = Range("A1:C30").Find("Area", LookIn:=xlValues, lookat:=xlWhole)
Set Cell2 = Range("A1:C30").Find("Total", LookIn:=xlValues, lookat:=xlWhole)








If Cell2 Is Nothing Then
    GoTo NothingFound
Else
    If Cell Is Nothing Then
        Set Cell = Range("A1:C30").Find("Flat", LookIn:=xlValues, lookat:=xlWhole)
    End If


Cell.Offset(0, 1).Value.ClearContents


End If

The above, successfully allows me to clear the content of a single cell, however as soon as I put that '.offset' in there, it stopped working. Also I cannot figure out how to clear the range between Cell and Cell2, which was the intended function of the code.

If anyone could have a look at any of the three methods and suggest potential fixes, that'd be great because I am really lost at the moment. Thanks in advance.

[Paul_Hossler]

An example would help -- attach a workbook with the cells to be cleared marked

Also, if A2 = "Area" and C7 = Total, do you want to clear A2:C7 or B2:C8 or what?

[georgedixon]

An example would help -- attach a workbook with the cells to be cleared marked

Also, if A2 = "Area" and C7 = Total, do you want to clear A2:C7 or B2:C8 or what?

Area and total will always be in the same column, but if A2="Area" and A7="Total", then I want to celar B2:B6, I will make a test workbook now

[georgedixon]

Clear Contents- Test Book.xls
I have made the test book. Each page (Apart from the summary) has a grid, of similar layout (sometimes different size though), Either "Area" or "Flat" will be at the top of each grid, and "Total" always at the bottom. I'm having trouble understanding how I can manipulate these Cell 'objects'

[jolivanes]

In each Column, is "Total" always first and "Area" further down or is it mixed?
Does every Column have a "Total" and a "Area"?

Forget it, should have pushed F5 first.

[p45cal]

Two attachments.
1. vbaExpress63613Clear Contents-Exercise.xls
This contains a macro which you're intended to step through one line at a time with F8 on the keyboard, while viewing what's happening on the single sheet in the file.
It uses .Offset, .Resize and .Cells
The arguments you supply to these three statements all follow the form:
(row,column)
This should help you understand how to tweak the next file:

2. vbaExpress63613Clear Contents- Test Book.xls
which is a copy of your file but:
I've added a blank sheet to demonstrate what can happen when two suitable cells are not found.
There are two versions of essentially the same macro (ClearContents2), one commented out which actually does some clearing of cells, without any selection of sheets or cells.
The other is designed for you to step through with F8 on the keyboard. This one will run when you click your big button, but come to a stop early on due to a Stop instruction, letting you F8 through it.
It does select sheets and ranges but clears no data.

The idea is to comment-out one macro or the other after you've tweaked them to work as you want them to.
I've removed the borders around your data as it wasn't very clear what cells were selected when they were there.
I'm surprised the SUM formula at the bottom includes the Area/Flat cell at the top.

[Paul_Hossler]

To keep it simple and maintainable

Option Explicit


Private Sub Clear_Click()
    Dim ws As Worksheet
    Dim rowArea As Long, rowTotal As Long, rowFlat As Long
    Dim col As Long
    
    For Each ws In ActiveWorkbook.Worksheets
        For col = 1 To 30
        
            rowArea = 0
            rowTotal = 0
            rowFlat = 0
            
            On Error Resume Next
            rowArea = Application.WorksheetFunction.Match("Area", ws.Columns(col), 0)
            rowTotal = Application.WorksheetFunction.Match("Total", ws.Columns(col), 0)
            rowFlat = Application.WorksheetFunction.Match("Flat", ws.Columns(col), 0)
            On Error GoTo 0
            
            If rowTotal = 0 Then GoTo NextCol
            If rowArea = 0 And rowFlat = 0 Then GoTo NextCol
                
            If rowArea > 0 Then
                ws.Cells(rowArea, col + 1).Resize(rowTotal - rowArea, 1).ClearContents
                Exit For
            Else
                ws.Cells(rowFlat, col + 1).Resize(rowTotal - rowFlat, 1).ClearContents
                Exit For
            End If
NextCol:
        Next col
    Next
End Sub

[jolivanes]

And maybe another possibility that stores the sheet names and addresses first.

Option Explicit
Option Compare Text
Sub AAAAA()
Dim a(), i As Long, e As Range, ee As String, j As Long
ReDim a(1 To Sheets.Count - 1, 1 To 2)
    For i = 1 To ActiveWorkbook.Sheets.Count - 1
        With Sheets(i + 1)
            Set e = .UsedRange.Find("Area", , , 1)
                If e Is Nothing Then
                    Set e = .UsedRange.Find("Flat", , , 1)
                End If
                    ee = .UsedRange.Find("Total", , , 1).Offset(-1, 1).Address(0, 0)
                a(i, 1) = Sheets(i + 1).Name
            a(i, 2) = Sheets(i + 1).UsedRange.Find(e, , , 1).Offset(, 1).Address(0, 0) & ":" & ee
        End With
    Next i
    For j = LBound(a) To UBound(a)
        Sheets(a(j, 1)).Range(a(j, 2)).ClearContents
    Next j
End Sub

[jolivanes]

If you don't need the addresses, in your attachment you don't, maybe this works also as long as the cells above the "Area" and above the "Area" to the right are empty.

Sub AAAAA_2()
Dim i As Long, a As Range
    For i = 2 To Sheets.Count
    On Error GoTo Okay
        Set a = Sheets(i).UsedRange.Find("Total", , , 1).Offset(-1, 1)
        Sheets(i).Range(a.Address(0, 0), Sheets(i).Range(a.Address(0, 0)).End(xlUp)).ClearContents
Okay:
    Next i
End Sub

[georgedixon]

To keep it simple and maintainable

Option Explicit


Private Sub Clear_Click()
    Dim ws As Worksheet
    Dim rowArea As Long, rowTotal As Long, rowFlat As Long
    Dim col As Long
    
    For Each ws In ActiveWorkbook.Worksheets
        For col = 1 To 30
        
            rowArea = 0
            rowTotal = 0
            rowFlat = 0
            
            On Error Resume Next
            rowArea = Application.WorksheetFunction.Match("Area", ws.Columns(col), 0)
            rowTotal = Application.WorksheetFunction.Match("Total", ws.Columns(col), 0)
            rowFlat = Application.WorksheetFunction.Match("Flat", ws.Columns(col), 0)
            On Error GoTo 0
            
            If rowTotal = 0 Then GoTo NextCol
            If rowArea = 0 And rowFlat = 0 Then GoTo NextCol
                
            If rowArea > 0 Then
                ws.Cells(rowArea, col + 1).Resize(rowTotal - rowArea, 1).ClearContents
                Exit For
            Else
                ws.Cells(rowFlat, col + 1).Resize(rowTotal - rowFlat, 1).ClearContents
                Exit For
            End If
NextCol:
        Next col
    Next
End Sub

Thank you so much, that code works perfectly on the test book. There is a slightly strange problem, I have transferred the code to my workbook and it works for the "Area" regions, but not for the "Flat" Regions. I have checked the code, and it definitely should still work, but it wasn't working still. I had messed around with my workbook to see what It could be and had no luck, so I decided to copy and paste the content from my workbook to the test workbook and strangely, the original code continues to work for the initial sheets, but not the copy and pasted one. But the only thing that is different is the formatting so I am not sure why it is affecting it. I have attached my updated test workbook so you can see what I mean, Thanks again

Clear Contents- Test Book.xls

[georgedixon]

Thank you to everyone else for your help also

[p45cal]

Cell J26 has an extra space in it.

[georgedixon]

oh haha, thank you