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]

22850
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 :)

22853

[georgedixon]

Thank you to everyone else for your help also :)

[p45cal]

Cell J26 has an extra space in it.

[georgedixon]

oh haha, thank you :)