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?

 

[MrTeeny]

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

Could use a simple find statement searching your range of client numbers A1:A100 to replace the current line ActiveWorkbook.Password = "SECRET"

Set rgFound = Range("A1:A100").find("client number")


If rgFound Is Nothing Then
    ActiveWorkbook.Password = "SECRET"
Else
    ' Do nothing 
End If

 

[Dark0Prince]

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

Worked like a charm! Thanks.

 

[Dark0Prince]

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

I'm running into a problem, I'm not sure how to separate each client number I tried just a space or a comma, but it ignores both.

 

[MrTeeny]

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

How are you storing the client numbers on the sheet and the one to be checked and the likely format they're in?

It's not a big problem if they're all lumped together in one cell, rather than a range like A1:A100, as you have other options like Instr to search for it in the single cell ,if it's unlikely to be easily repeated, or even just split the cell into an array and loop thru it.

To search for a string (i.e. client number) within a string (a list of client numbers comma separated)

Dim ClientNumber2SearchFor As String = Range("A1").value 
Dim ClientListToSearch As String = Range("A2").value 
Dim TestPosition As Integer 


TestPosition = InStr(1, ClientListToSearch, ClientNumber2SearchFor)

If the client number in A1 was found in A2 then TestPosition would be higher than 0 so you'd just do a simple IF

If TestPosition =0  Then
    ActiveWorkbook.Password = "SECRET"
Else
    ' Do nothing 
End If

 

[Dark0Prince]

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

Dim ClientNumber2SearchFor As String = Range("A14").value 
Dim ClientListToSearch As String = Range("A2").value

I'm not sure why I'm getting errors on this, but the list of numbers I need to search from are in workbook "DISP FORM - BLANK.xlsm". I'm searching on the new active workbook that is created.

 

[MrTeeny]

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

As far as I can tell your code is running from the DISP FORM - BLANK.xlsm sheet. If the value isn't on the active sheet you just need to fully reference any address instead of just Range("A14").value put the full address Worksheets("Sheet1").Range("A14").value,Worksheets("Sheet2").Range("A2").value etc if the value isn't on the active sheet even if it is it's probably wise to reference it anyway.

What errors were you getting?

Sorry the code should have read

Dim ClientNumber2SearchFor As String 
Dim ClientListToSearch As String 
Dim TestPosition As Integer 


ClientNumber2SearchFor= Range("A14").value  ' fully reference these addresses
ClientListToSearch= Range("A2").value    ' fully reference these addresses


TestPosition = InStr(1, ClientListToSearch, ClientNumber2SearchFor)


If TestPosition =0  Then
    ActiveWorkbook.Password = "SECRET"
Else
    ' Do nothing 
End If

 

[Dark0Prince]

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

The code isn't doing anything at all for me. Maybe I didn't put it in the right spots can you take a look?
Here is a copy of what the workbook looks like. https://1drv.ms/x/s!Ap0t6Fmj7XZeg3UwryuzOsbbNhMg

Sub VOPSRELATIVE()
'
' VOPSRELATIVE Macro
'
' Keyboard Shortcut: Ctrl+Shift+R
'
Dim ClientNumber2SearchFor As String
Dim ClientListToSearch As String
Dim TestPosition As Integer
TestPosition = InStr(1, ClientListToSearch, 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
ClientNumber2SearchFor = Range("A14").Value ' fully reference these addresses
ClientListToSearch = Range("A14").Value   ' fully reference these addresses
If TestPosition = 0 Then
    ActiveWorkbook.Password = "SECRET"
Else
    ' Do nothing
End If
    Columns(6).EntireColumn.Delete
    Range("A7:K7").Select
    Selection.Copy
    Workbooks("DISP FORM - BLANK.xlsm").Sheets(2).Rows("14:" & lastrow).EntireRow.Delete
End Sub

 

[Dark0Prince]

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

The last thing to get to work is to get it to work with more then 1 client number in A1

Sub VOPSRELATIVE()
'
' VOPSRELATIVE Macro
'
' Keyboard Shortcut: Ctrl+Shift+R
'
Dim ClientNumber2SearchFor As String
Dim ClientListToSearch As String
Dim TestPosition As Integer
ClientNumber2SearchFor = Worksheets("Sheet1").Range("A1").Value ' fully reference these addresses
    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
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(2).Rows("14:" & lastrow).EntireRow.Delete
End Sub

 

[MrTeeny]

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

If the client list of numbers is on the workbook you're using you just need to reference the cell address so excel can find the data , if you just put RANGE(“A1”).Value it would just look in A1 on the active worksheet so we need to put the full address. In the address you posted up there was a space between . and Sheets.

If DISP FORM - BLANK.xlsm is not the open workbook we'd need to grab that from the closed workbook , dump it on the sheet in a temporary cell so we can search then delete it.

ClientNumber2SearchFor = Workbooks("DISP FORM - BLANK.xlsm").Sheets("ShareFile").Range("A1").Value ' fully reference these addresses

ClientListToSearch= Range("A2").value would also need to be fully referenced if it's not on the active sheet