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

 

[Alisya]

Sektor what about my query above in #9

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]

 

[Sektor]

There's no need for Exit Sub because Else part will never be reached if there's error in cell.

 

[Michael M]

Hi Sektor
I don't think the N/A means error....I think the OP is trying to identify a N/A....New Account

 

[Michael M]

Alisya

I'm really hoping this is an additional question to the original (solved) post.
try, untested

Sub test3()
Dim LR1 As Long, n As Integer, r As Long
LR1 = Sheets("Securities").Range("C" & Rows.Count).End(xlUp).Row
    For r = Range("B" & LR1) To 5 Step -1
        If UCase(Trim(Sheets("Securities").Rows(r))) = "N/A" Then Exit Sub
    Next r
End Sub

 

[Michael M]

Oh dear....see what happens when you can't test something....I just read what I posted and it won't work.
try this

Sub test3()
Dim LR1 As Long, n As Integer, r As Long
LR1 = Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp).Row
    For r = Range("B" & LR1) To 5 Step -1
        If UCase(Trim(Range("B" & r))) = "N/A" Then Exit Sub
    Next r
End Sub

 

[Michael M]

One last attempt at this before I go nuts and turn to the drink

Sub test3()
Dim LR1 As Long, n As Integer, r As Long
LR1 = Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp).Row
    For r = LR1 To 5 Step -1
        If UCase(Trim(Range("B" & r))) = "N/A" Then Exit Sub
    Next r
End Sub

 

[jmthompson]

Try

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"
            GoTo EndIt
    End If
    Next c
'Your code here
EndIt:
End Sub

 

[Alisya]

If i was calling this macro in another Macro, then if there is a new a/c would the exit Sub work befoire it goes to 'Some Other code?

Sub MyTest

'My code

ExitRoutine:
Call Test
'Some other code

Exit Sub

End Sub

Try

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"
            GoTo EndIt
    End If
    Next c
'Your code here
EndIt:
End Sub

 

[Michael M]

Aliysa
This thread seems to be a constantly changing need.
Might be simpler if you explain to us what you actually want the code to do completely, and then we can actually create the script from start to finish

 

[Alisya]

Michael, the code itself is fine, i am trying to make the overall code bulletproof incase of errors so if i was calling the macro into another macro then i want to make sure that when it finds N/A the macro does not keep continuing