georgedixon
09-09-2018, 10:09 AM
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.
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.