VBA Code check

[Alisya]

Hi does any one know why this could does not work. I am not getting any errors, it does nothing when i run. I am trying to get the code to look in Col B for N/A, if found then let the user know why msgbox

Sub Test()
    Dim LR1 As Long
    
    LR1 = Range("C" & Rows.count).End(xlUp).Row
    
    If LR1 < 5 Then LR1 = 5
    If UCase$(Trim$(Sheets(1).Range("B" & LR1).Value)) = "N/A" Then
            MsgBox "There is a new a/c"
    End If

End Sub

 

[jmthompson]

Your code is looking specifically in the last used row in column B, I think you're looking for:

Sub Test()
    Dim LR1 As Long
    Dim c As Range
    LR1 = Range("C" & Rows.Count).End(xlUp).Row
    
    If LR1 < 5 Then LR1 = 5
    For Each c In Sheets(1).Range("B5:B" & LR1)
    If UCase(Trim(c.Value)) = "N/A" Then
            MsgBox "There is a new a/c"
    End If
    Next c

End Sub

 

[Alisya]

Hi, if i have multiple N/A i have to press OK multiple times, is there a way to only limit this to only once?

Your code is looking specifically in the last used row in column B, I think you're looking for:

Sub Test()
    Dim LR1 As Long
    Dim c As Range
    LR1 = Range("C" & Rows.Count).End(xlUp).Row
 
    If LR1 < 5 Then LR1 = 5
    For Each c In Sheets(1).Range("B5:B" & LR1)
    If UCase(Trim(c.Value)) = "N/A" Then
            MsgBox "There is a new a/c"
    End If
    Next c
 
End Sub

 

[Alisya]

Anyone? How do i limit the msgbox to only pop up once rather than multiple times?

 

[Alisya]

*bump*

 

[Alisya]

*bump*

 

[Leith Ross]

Hello Alisya,

This will exit the loop when a match is made. The message will then appear only once.

Sub Test()
    Dim LR1 As Long
    Dim c As Range
    LR1 = Range("C" & Rows.Count).End(xlUp).Row
 
    If LR1 < 5 Then LR1 = 5
    For Each c In Sheets(1).Range("B5:B" & LR1)
    If UCase(Trim(c.Value)) = "N/A" Then
            MsgBox "There is a new a/c"
            Exit For
    End If
    Next c
 
End Sub

Sincerely,
Leith Ross

 

[Michael M]

Try, untested

Sub Test2()
    Dim LR1 As Long, n As Integer
    Dim c As Range
    LR1 = Range("C" & Rows.Count).End(xlUp).Row
    If LR1 < 5 Then LR1 = 5
    n = 0
    For Each c In Sheets(1).Range("B5:B" & LR1)
    If UCase(Trim(c.Value)) = "N/A" Then n = n + 1
    Next c
If n > 0 Then MsgBox "No of new a/c's is " & n
End Sub

 

[Alisya]

Ok that works. Now if i want to Exit Sub if an N/A is found then i tried doing the below but the code failed. What have i done wrong?

Sub test3()
Dim LR1 As Long, n As Integer
LR1 = Sheets("Securities").Range("C" & Rows.Count).End(xlUp).Row
    If UCase(Trim(Sheets("Securities").Range("B5:B" & LR1).Value)) = "N/A" Then
Exit Sub
End If
Else
'Continue with code
End Sub

Try, untested

Sub Test2()
    Dim LR1 As Long, n As Integer
    Dim c As Range
    LR1 = Range("C" & Rows.Count).End(xlUp).Row
    If LR1 < 5 Then LR1 = 5
    n = 0
    For Each c In Sheets(1).Range("B5:B" & LR1)
    If UCase(Trim(c.Value)) = "N/A" Then n = n + 1
    Next c
If n > 0 Then MsgBox "No of new a/c's is " & n
End Sub

 

[Sektor]

This?

[COLOR="Blue"]Sub[/COLOR] Test()
    
    [COLOR="Blue"]Dim[/COLOR] LR1 [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Long[/COLOR]
    
    LR1 = Range("C" & Rows.Count).End(xlUp).Row
    
    [COLOR="Blue"]If[/COLOR] LR1 < 5 [COLOR="Blue"]Then[/COLOR] LR1 = 5
    
    [COLOR="Blue"]If[/COLOR] IsError(Sheets(1).Range("B" & LR1)) [COLOR="Blue"]Then[/COLOR]
            MsgBox "There is a new a/c"
    [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]If[/COLOR]

[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]