Skip a line of code if a number is contained in new workbook

[Dark0Prince]

Sub VOPSRELATIVE()
'
' VOPSRELATIVE Macro
'
' Keyboard Shortcut: Ctrl+Shift+R
'
    Number = ActiveSheet.Cells(14, 1)
    lastrow = 13
    For Each c In Range(Cells(14, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 1))
        If Number <> c Then Exit For
        lastrow = lastrow + 1
    Next
    If lastrow = 13 Then Exit Sub
    Range("A1:M" & lastrow).Copy
    Workbooks.Add
    ActiveSheet.Paste
    Workbooks("DISP FORM - BLANK.xlsm").Sheets(2).Rows("14:" & lastrow).EntireRow.Delete
    Range("A2").Activate
    Columns("A:L").EntireColumn.AutoFit
    Columns("A:A").Select
    Range("A2").Activate
    Selection.ColumnWidth = 10.5
    Range("F14").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("A7").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveWorkbook.Password = "SECRET"
    Columns(6).EntireColumn.Delete
    Range("A7:K7").Select
    Selection.Copy
End Sub

This code creates a new workbook and pw protects it no matter what client number is contained in the new workbook. I think the best way to do this is to add a sheet in excel that lists all the client numbers that I don't want to be password protected. Then if they exist on the new workbook I want it to skip the line " ActiveWorkbook.Password = "SECRET" ". How can I add this IF statement?

 

[Dark0Prince]

Re: HOW to skip a line of code if a number is contained in new workbook

No, The sheet I'm trying to protect has only one client number In A14 as the code suggests, and this one needs to be checked against a master list.

 

[MrTeeny]

The ClientNumber2SearchFor should be set as your A14 value, the "ClientListToSearch should contain your list of client numbers separated by a comma. Was the A14 showing correctly as ClientNumber2SearchFor when you had the message boxes running?

 

[MrTeeny]

Let's try it slightly differently so you can see if it's stepping thru your variables , we can tweak this code later to check it's looking at each item in your client list if needed. The number you're searching need to be in the same format as the list you're searching. So if you have you clients listed as "2000" with quotation marks you also need the number you're searching enclosed with quotation marks. They are not needed and we can strip them out if it makes life easier if your data doesn't match at the moment. Your client list of numbers should be separated with commas.

Dim ClientNumber2SearchFor As StringDim ClientListToSearch As Variant
Dim Found As Boolean




ClientNumber2SearchFor = Range("A14").Value ' this is the value to search for, fully reference it
ClientListToSearch = Split(Range("A2").Value, ",") ' this is the list of client number, fully reference them
Found = False


MsgBox ClientNumber2SearchFor & " is being checked against the master list"

For Each clientNumber In ClientListToSearch

If Trim(ClientNumber2SearchFor) = Trim(clientList) And Trim(ClientNumber2SearchFor) <> "" Then
Found = True
Exit For
End If

Next clientNumber

If Found = False Then MsgBox "not found" ' we can change this to protect the sheet later
If Found = True Then MsgBox "found"

 

[MrTeeny]

I tweaked it to the following to get rid of quotation marks in case they weren't present in both cells

Dim ClientNumber2SearchFor As String
Dim ClientListToSearch As Variant
Dim Found As Boolean


ClientNumber2SearchFor = Range("A14").Value ' this is the value to search for, fully reference it
ClientListToSearch = Split(Range("A2").Value, ",") ' this is the list of client number, fully reference them
Found = False


MsgBox Replace(ClientNumber2SearchFor, Chr(34), "") & " is being checked against the master list"


For Each clientNumber In ClientListToSearch

If Trim(Replace(ClientNumber2SearchFor, Chr(34), "")) = Trim(Replace(clientNumber, Chr(34), "")) And Trim(Replace(clientNumber, Chr(34), "")) <> "" Then
Found = True
Exit For
End If


Next clientNumber


If Found = False Then MsgBox "not found" ' we can change this to protect the sheet later
If Found = True Then MsgBox "found"

 

[MrTeeny]

Or even try this as it'll show each entry on your list that's being checked

Dim ClientNumber2SearchFor As String
Dim ClientListToSearch As Variant
Dim Found As Boolean

ClientNumber2SearchFor = Range("A14").Value ' this is the value to search for, fully reference it
ClientListToSearch = Split(Range("A2").Value, ",") ' this is the list of client number, fully reference them
Found = False


For Each clientNumber In ClientListToSearch

answer = MsgBox("Checking " & Replace(ClientNumber2SearchFor, Chr(34), "") & " against " & Replace(clientNumber, Chr(34), "") & ". Carry on checking?", vbYesNo + vbQuestion, "Empty Sheet")
If answer = vbNo Then Exit For

If Trim(Replace(ClientNumber2SearchFor, Chr(34), "")) = Trim(Replace(clientNumber, Chr(34), "")) And Trim(Replace(clientNumber, Chr(34), "")) <> "" Then
Found = True
Exit For
End If

Next clientNumber

If Found = False Then MsgBox "not found" ' we can change this to protect the sheet later
If Found = True Then MsgBox "found"

 

[Dark0Prince]

When I tried to incorporate your code it started from this..

Sub VOPSRELATIVE()
'
' VOPSRELATIVE Macro
'
' Keyboard Shortcut: Ctrl+Shift+R
'
Dim ClientNumber2SearchFor As String
Dim ClientListToSearch As String
Dim TestPosition As Integer
ClientNumber2SearchFor = Workbooks("DISPFORM - BLANK.xlsm").Sheets("ShareFile").Range("A1").Value ' fully reference these addresses
   MsgBox "ClientNumber2SearchFor value is " & ClientNumber2SearchFor
    Number = ActiveSheet.Cells(14, 1)
    lastrow = 13
    For Each c In Range(Cells(14, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 1))
        If Number <> c Then Exit For
        lastrow = lastrow + 1
    Next
    If lastrow = 13 Then Exit Sub
    Range("A1:M" & lastrow).Copy
    Workbooks.Add
    ActiveSheet.Paste
    Range("A2").Activate
    Columns("A:L").EntireColumn.AutoFit
    Columns("A:A").Select
    Range("A2").Activate
    Selection.ColumnWidth = 10.5
    Range("F14").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("A7").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
ClientListToSearch = Range("A14").Value   ' fully reference these addresses
MsgBox "ClientListToSearch value is " & ClientListToSearch
TestPosition = InStr(1, ClientListToSearch, ClientNumber2SearchFor)

If TestPosition = 0 Then
    ActiveWorkbook.Password = "SECRET"
Else
    ' Do nothing
End If
    Columns(6).EntireColumn.Delete
    Range("A7:K7").Select
    Selection.Copy
    Workbooks("DISPFORM - BLANK.xlsm").Sheets(1).Rows("14:" & lastrow).EntireRow.Delete 'change the sheet number for each DISPO
End Sub

to this.. (I get not found every time with Spacing or a Comma) It shows that it is searching the correct list and against the correct client number and Continues to say not found. Unless I only have 1 client number in the list.

Sub PROG_05565()
'
' PROG_05565 Macro
'

'
    Range("A13").Select
    Application.CutCopyMode = False
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;C:\abq\PROG_05565.TXT", _
        Destination:=Range("$A$13"))
        .Name = "PROG_5565"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlFixedWidth
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(2, 1, 1, 1, 1, 1, 1)
        .TextFileFixedColumnWidths = Array(36, 8, 9, 11, 9, 17)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Range("F:F,E:E,C:C").Select
    Range("C1").Activate
    Selection.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"
    Columns("D:D").Select
    Selection.NumberFormat = "m/d/yyyy"
    Columns("A:G").Select
    Range("G1").Activate
    Columns("A:G").EntireColumn.AutoFit
    Range("A12").Select
End Sub
Sub VOPS2()
'
' VOPS2 Macro
'
' Keyboard Shortcut: Ctrl+Shift+T
'
    Range("A1:L14").Select
    Selection.Copy
    Workbooks.Add
    ActiveSheet.Paste
    Columns("A:L").Select
    Range("A2").Activate
    Columns("A:L").EntireColumn.AutoFit
    Columns("A:A").Select
    Range("A2").Activate
    Selection.ColumnWidth = 10.5
    Range("F14").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("A7").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveWorkbook.Password = "LOBOS"
    Columns(6).EntireColumn.Delete
           Range("A7:K7").Select
    Selection.Copy
End Sub

Sub VOPSRELATIVE()
'
' VOPSRELATIVE Macro
'
' Keyboard Shortcut: Ctrl+Shift+R
'
Dim ClientNumber2SearchFor As String
Dim ClientListToSearch As Variant
Dim Found As Boolean

ClientNumber2SearchFor = Workbooks("DISPFORM - BLANK.xlsm").Sheets("ShareFile").Range("A1").Value ' fully reference these addresses
   MsgBox "ClientNumber2SearchFor value is " & ClientNumber2SearchFor
   Found = False
   
    Number = ActiveSheet.Cells(14, 1)
    lastrow = 13
    For Each c In Range(Cells(14, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 1))
        If Number <> c Then Exit For
        lastrow = lastrow + 1
    Next
    If lastrow = 13 Then Exit Sub
    Range("A1:M" & lastrow).Copy
    Workbooks.Add
    ActiveSheet.Paste
    Range("A2").Activate
    Columns("A:L").EntireColumn.AutoFit
    Columns("A:A").Select
    Range("A2").Activate
    Selection.ColumnWidth = 10.5
    Range("F14").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("A7").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
ClientListToSearch = Split(Range("A14").Value, ",")   ' fully reference these addresses
Found = False
For Each clientNumber In ClientListToSearch
answer = MsgBox("Checking " & Replace(ClientNumber2SearchFor, Chr(34), "") & " against " & Replace(clientNumber, Chr(34), "") & ". Carry on checking?", vbYesNo + vbQuestion, "Empty Sheet")
If answer = vbNo Then Exit For
If Trim(Replace(ClientNumber2SearchFor, Chr(34), "")) = Trim(Replace(clientNumber, Chr(34), "")) And Trim(Replace(clientNumber, Chr(34), "")) <> "" Then
Found = True
Exit For
End If
Next clientNumber

If Found = False Then MsgBox "not found" ' we can change this to protect the sheet later
If Found = True Then MsgBox "found"


    ActiveWorkbook.Password = "SECRET"
    Columns(6).EntireColumn.Delete
    Range("A7:K7").Select
    Selection.Copy
    Workbooks("DISPFORM - BLANK.xlsm").Sheets(1).Rows("14:" & lastrow).EntireRow.Delete 'change the sheet number for each DISPO
End Sub

Are you using this code on my sample sheet that I linked? Because we may not get anywhere if you don't see what I see, I'm thinking.

 

[MrTeeny]

I'm just using the addresses you've given, here's the code from your opening post amended using the addresses you've given

The client number on the new sheet is apparently Workbooks("DISPFORM - BLANK.xlsm").Sheets("ShareFile").Range("A1").Value
I asked you to fully reference the address of the client list, i.e. the list of numbers you wish to check the client number in Workbooks("DISPFORM - BLANK.xlsm").Sheets("ShareFile").Range("A1").Value against.

On the sheet you've entered the client list as Range("A14").Value as it's not fully referenced this value will be taken from the active sheet, it would make sense to fully reference this cell so the client list is being picked up.

The code below will pick up the value in Workbooks("DISPFORM - BLANK.xlsm").Sheets("ShareFile").Range("A1").Value and then turn the list of data in Range("A14").Value in your currently active sheet into an array of data by splitting it at each comma. The code will also output the client number being checked against each item in the client comma separated list. You should be getting lots of msgboxes stepping thru the data , are they actually showing data being picked up? The messages should be something like the following if your cells have been referenced correctly , it should show the client number ie 6015 checked one by one against whatever's in the client list

"Checking 6015 against 6222. Carry on checking?"

Sub VOPSRELATIVE()
'
' VOPSRELATIVE Macro
'
' Keyboard Shortcut: Ctrl+Shift+R
'
Dim ClientNumber2SearchFor As String
Dim ClientListToSearch As Variant
Dim Found As Boolean


ClientNumber2SearchFor = Workbooks("DISPFORM - BLANK.xlsm").Sheets("ShareFile").Range("A1").Value ' this is the value to search for, fully reference it
ClientListToSearch = Split(Range("A14").Value, ",")
If Range("A14").Value ="" Then MsgBox "You do not have any data in your client list A14"
Found = False
'
'

    Number = ActiveSheet.Cells(14, 1)
    lastrow = 13
    For Each c In Range(Cells(14, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 1))
        If Number <> c Then Exit For
        lastrow = lastrow + 1
    Next
    If lastrow = 13 Then Exit Sub
    Range("A1:M" & lastrow).Copy
    Workbooks.Add
    ActiveSheet.Paste
    Workbooks("DISP FORM - BLANK.xlsm").Sheets(2).Rows("14:" & lastrow).EntireRow.Delete
    Range("A2").Activate
    Columns("A:L").EntireColumn.AutoFit
    Columns("A:A").Select
    Range("A2").Activate
    Selection.ColumnWidth = 10.5
    Range("F14").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("A7").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False


'
'
For Each clientNumber In ClientListToSearch

answer = MsgBox("Checking " & Replace(ClientNumber2SearchFor, Chr(34), "") & " against " & Replace(clientNumber, Chr(34), "") & ". Carry on checking?", vbYesNo + vbQuestion, "Empty Sheet")
If answer = vbNo Then Exit For

If Trim(Replace(ClientNumber2SearchFor, Chr(34), "")) = Trim(Replace(clientNumber, Chr(34), "")) And Trim(Replace(clientNumber, Chr(34), "")) <> "" Then
Found = True
Exit For
End If

Next clientNumber

If Found = False Then MsgBox "not found" ' we can change this to protect the sheet later
If Found = True Then MsgBox "found"
'

    ActiveWorkbook.Password = "SECRET"
    Columns(6).EntireColumn.Delete
    Range("A7:K7").Select
    Selection.Copy
End Sub

 

[Dark0Prince]

Ok, I see the confusion. (also sorry for posting extra code in last post didn't notice that.)
ClientNumber2SearchFor (This should be A14 from the new workbook opened)it can't be fully referenced because I need to create hundreds of these and don't know what the name will be when it's created.
ClientListToSearch (This is the list of numbers on DISPFORM - BLANK.xlsm)sheet ShareFile.

The reason this isn't working is because when I go to search for the number the workbook hasn't been created how can i fix it so that the it's searching for the client number in the newly created workbook.

 

[MrTeeny]

The reason this isn't working is because when I go to search for the number the workbook hasn't been created how can i fix it so that the it's searching for the client number in the newly created workbook.

If you don't have the client number how can you decide whether to protect the sheet or not at that point? YOu must be creating your client reference at some point and should include the protection routine to run whenever you have that reference or when it's being written to the sheet.

 

[Dark0Prince]

I'm guesing that this is what is happening but when I used the msg boxes it showed me both client numbers and they matched but it was comparing the new sheet to the DISP form not the DISPform to the A14 on the new sheet.