talkinggoat
New Member
- Joined
- Feb 1, 2022
- Messages
- 33
- Office Version
- 365
- Platform
- Windows
My Code:
I have to insert something into at least one of the cells, to ensure the Find methods work... they have to have something to find, or they produce an error, so I insert a single character into the first cell of the data portion of the sheet. That way, it will always have something to clear. This works fine if I manually type A or whatever, but if I let VBA enter the value, it produces the error, "Clear Method of Range Failed", even though I know it's putting A in the cell, as the value. It doesn't matter if I change it to Range(A8).Clear, ActiveSheet.Range(A8).Clear it produces the error, but if I manually type something in there, it works fine. Other methods, such as Select or Value work fine. Only Clear will produce an error.
I also checked the Locals window to ensure lrowDel and lcolDel are receiving numbers, which they are.
VBA Code:
ClearFormImportSheetsButton "Sheet1"
Sub ClearFormImportSheetsButton(Fsheet As String)
Dim sh As Worksheet
Set sh = ThisWorkbook.Sheets(Fsheet)
sh.Range("A1").Value = "A"
lrowDel = sh.Cells.Find(what:="*", _
after:=Range("a1"), _
lookat:=xlPart, _
LookIn:=xlFormulas, _
searchorder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
lcolDel = sh.Cells.Find(what:="*", _
after:=Range("A1"), _
lookat:=xlPart, _
LookIn:=xlFormulas, _
searchorder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
Debug.Print "Clearing sheet. " & Fsheet
sh.Range(Cells(8, 1), Cells(lrowDel, lcolDel)).Clear
End Sub
I have to insert something into at least one of the cells, to ensure the Find methods work... they have to have something to find, or they produce an error, so I insert a single character into the first cell of the data portion of the sheet. That way, it will always have something to clear. This works fine if I manually type A or whatever, but if I let VBA enter the value, it produces the error, "Clear Method of Range Failed", even though I know it's putting A in the cell, as the value. It doesn't matter if I change it to Range(A8).Clear, ActiveSheet.Range(A8).Clear it produces the error, but if I manually type something in there, it works fine. Other methods, such as Select or Value work fine. Only Clear will produce an error.
I also checked the Locals window to ensure lrowDel and lcolDel are receiving numbers, which they are.