mcomp72
Active Member
- Joined
- Aug 14, 2016
- Messages
- 275
- Office Version
- 365
- 2019
- 2016
- 2011
- Platform
- Windows
- MacOS
I have a workbook that creates another workbook, and copies one of the sheets to it, via VBA code. Let's call this sheet "NewSheet". I place some formulas in NewSheet in a few cells. In those cells, I see the little green triangles in the top left corner, with the error "Formula omits adjacent cells". With VBA code, I would like to make the little green triangles go away.
I did some research on this board, and found a thread that I thought would help solve it.
Ignor Error: Inconsistent Calculated Column Formula using VBA
It doesn't look like it's for the same exact error, but I figured the code itself would work, and I could just change the number next to "Errors" until it worked. However, I couldn't get it to work no matter what I tried.
Note: LastRow is a variable because the last row that is affected will be different, depending on how much data is present on this sheet. In my testing, it is equal to 20.
I wasn't sure which number I needed to put in parentheses next to Errors, so that's why I tried all 9. The above code didn't work, so either the error I'm trying to ignore uses a different number, or something is wrong with the code itself. The code doesn't generate an error, but it also doesn't hide the little green triangle.
I'm on Windows 10 with Excel 365.
Any help would be appreciated!
I did some research on this board, and found a thread that I thought would help solve it.
Ignor Error: Inconsistent Calculated Column Formula using VBA
It doesn't look like it's for the same exact error, but I figured the code itself would work, and I could just change the number next to "Errors" until it worked. However, I couldn't get it to work no matter what I tried.
VBA Code:
Dim cel As Range
Dim r As Range
Set r = ThisWorkbook.Sheets("NewSheet").Range("AF9:AF" & LastRow)
For Each cel In r
cel.Errors(1).Ignore = True
cel.Errors(2).Ignore = True
cel.Errors(3).Ignore = True
cel.Errors(4).Ignore = True
cel.Errors(5).Ignore = True
cel.Errors(6).Ignore = True
cel.Errors(7).Ignore = True
cel.Errors(8).Ignore = True
cel.Errors(9).Ignore = True
Next cel
Set r = Nothing
Set cel = Nothing
Note: LastRow is a variable because the last row that is affected will be different, depending on how much data is present on this sheet. In my testing, it is equal to 20.
I wasn't sure which number I needed to put in parentheses next to Errors, so that's why I tried all 9. The above code didn't work, so either the error I'm trying to ignore uses a different number, or something is wrong with the code itself. The code doesn't generate an error, but it also doesn't hide the little green triangle.
I'm on Windows 10 with Excel 365.
Any help would be appreciated!