Posts by snooks679

Re: Formula using ISBLANK is not returning intended result

Just realized it didn't take the highlite. this is what I changed and it worked.

If(And($AC3="N/A",$AC3="N/A"),"Compliant"

to

If(And($AC3="N/A",$AD3="N/A"),"Compliant"

Re: Formula using ISBLANK is not returning intended result

I figured it out---> Changing the cell highlited in red below to AD3 from AC3 solved it.

= If($AR3="No","Not Applicable",  If( And(Or($AC3="N/A",  $AC3="Yes" ), Or($AD3="N/A",$AD3="Yes"), Not($AE3="[URL]http://[/URL]"),Not(LEN($AE3)=0),Not($AE3="Blank")), "Compliant",  If(And($AC3="N/A",[COLOR=#b22222][B]$AD3[/B][/COLOR]="N/A"),"Compliant",  If(Or(ISBLANK($AD3), $AD3="Blank",ISBLANK($AC3), $AC3="Blank"),"Incomplete",  If(And(Or($AC3="N/A",$AC3="Yes"), Or($AD3="N/A",$AD3="Yes"), Or($AE3="[URL]http://[/URL]",$AE3="Blank",LEN($AE3)=0)), "Incomplete", "Not Compliant"  )))))

I have this formula and this first part works just fine. I'm including it here as a reference.

The valid values for cell AG3 are Yes, No or N/A.
If AG3 is Yes and AH3 has a hyperlink then it is Compliant and no hyperlink or blank or word "blank"
in cell AH3 is Incomplete. Note. http:// is default in cell AH3 but users are supposed to put a complete link.
If AG3 is N/A then it is Compliant and no need for hyperlink in AH3.
If AG3 is No then it is Not Compliant and no hyperlink required in AH3
If AG3 is blank then Incomplete
If AG3 has the word "blank" then Incomplete

[FONT=Calibri]= IF($AR3="No","Not Applicable",IF( OR(AND($AG3="Yes",NOT($AH3="[URL]http://[/URL]"),NOT($AH3="Blank"),NOT( ISBLANK($AH3))),$AG3="N/A"),"Compliant",IF($AG3="No","Not Compliant",IF( OR(ISBLANK($AG3),$AG3="Blank", ISBLANK($AH3),$AH3="Blank", $AH3="[URL]http://[/URL]"),"Incomplete", "Not Compliant"))))
[/FONT]
]

My question is, I now want this to look at AC3, AD3 and AE3. Same or similar logic as above;

If AC3 & AD3 are Yes and AE3 has a hyperlink then it is Compliant and no hyperlink or blank or word "blank"
in cell AE3 is Incomplete. Note. http:// is default in cell AE3 but users are supposed to put a complete link.
If AC3 & AD3 are N/A then it is Compliant and no need for hyperlink in AE3. If either AC3 or AD3 are Yes and N/A or N/A and Yes and a hyperlink is included in AE3 it is Compliant. If AE3 is blank or has the word "blank" then Incomplete
If AC3 &/or AD3 are No then it is Not Compliant and no hyperlink required in AE3. If one has a Yes and another a No still Not Compliant, regardless of what is in AE3
If AC3 &/or AD3 is blank then Incomplete
If AC3 &/or AD3 has the word "blank" then Incomplete

= IF($AR3="No","Not Applicable",
IF( AND(OR($AC3="N/A",  $AC3="Yes" ), OR($AD3="N/A",$AD3="Yes"), NOT($AE3="[URL]http://[/URL]"),NOT(LEN($AE3)=0),NOT($AE3="Blank")), "Compliant",
IF(AND($AC3="N/A",$AC3="N/A"),"Compliant",
IF(OR(ISBLANK($AD3), $AD3="Blank",ISBLANK($AC3), $AC3="Blank"),"Incomplete",
IF(AND(OR($AC3="N/A",$AC3="Yes"), OR($AD3="N/A",$AD3="Yes"), OR($AE3="[URL]http://[/URL]",$AE3="Blank",LEN($AE3)=0)), "Incomplete", "Not Compliant"
)))))

So far the other scenarios work but two are not which are;
If AC3 is N/A and AD3 is Yes and AE3 is blank or has the work "blank" it is showing Compliant instead of Incomplete. The second one is,
If AC3 is N/A and AD3 is No and regardless of what's in AE3 should be Not Compliant but it is showing Compliant.
How can I fix that and also, is there a much simpler, intuitive way to write this formula?.

See attachment - Cell AT3 is the formula am looking at. Cells referenced above highlited in yellow.

Re: VBA runtime error '6': Overflow

It works now! I had missed a couple lines that had:

Dim row as integer, I changed all to:

Dim row as long and seems to work fine now.

Holger thank you for your assistance.

Re: VBA runtime error '6': Overflow

Quote from HaHoBe;661030

Hi, snooks679,

instead of using Integer try using Long:

Code

Dim row As Long
Dim LastRow As Long
'...
Private Function SetLastRow(WorksheetName As String) As Long

Ciao,
Holger

HoIger,

This doesn't seem to work. My original code works for fewer rows but it fails when it has 800 rows.

[h=1]VBA runtime error '6': overflow [code here][/h]I have 2 worksheets giving me the same error. This is part of the code. What do I need to change here in order to get rid of this error? Thanks in advance!

code 1:

Dim row As Integer
Dim LastRow As Integer
LastRow = Sheets("worksheetname").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row


Range(Cells(3, 1), Cells(LastRow, 41)).ClearFormats
Range(Cells(3, 1), Cells(LastRow, 41)).Locked = False


If LastRow < Cells(LastRow, 1).End(xlDown).row Then
    
    Range(Cells(LastRow + 1, 1), Cells(LastRow, 1).End(xlDown)).Locked = True
    Rows(CStr(LastRow + 1) & ":" & CStr(Cells(LastRow, 1).End(xlDown).row)).Hidden = True


End If

Code 2:

Private Sub SetValidationAndFormatting()


    LastRow = SetLastRow("worksheetname")


    With Sheets("worksheetname")
    .Range(Cells(3, 1), Cells(LastRow_DRC, 7)).Locked = True
    
    If LastRow_DRC < .Cells(LastRow_DRC, 1).End(xlDown).row Then
    
        .Range(.Cells(LastRow_DRC + 1, 1), .Cells(LastRow_DRC, 1).End(xlDown)).Locked = True
        .Rows(CStr(LastRow_DRC + 1) & ":" & CStr(.Cells(LastRow_DRC, 1).End(xlDown).row)).Hidden = True


    End If

Private Function SetLastRow(WorksheetName As String) As Integer
    SetLastRow = Sheets("Data Role Coverage").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).row
End Function

MODERATOR EDIT

Welcome to OZ Grid
Please use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window.

I’ve added them this time but please comply in the future