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

The problem still remains. I'm not having an issue referencing the sheet. I'm having an issue skipping the password when I have more then one number on my ShareFile Sheet in cell A1. I currently have the data in like this. "6055","3720","11885","20864","08296","05570","2648" Should I put it in differently?

[TABLE="width: 64"]
<colgroup><col width="64"></colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[/TR]
</tbody>[/TABLE]

 

[MrTeeny]

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

All the code does is try to see if a string contained in ClientNumber2SearchFor exists in another string ClientListToSearch .


I'd assumed the client reference was unique not a separate list of numbers. Is it always a list of numbers to be checked against another a list of mumbers? I guess the way forward depends on how many of these client numbers need to be exclude, if there aren't too many you could simply hard code the into your vba. If they swap and change you could always turn the search for list into an array and check them one by one against the list to search.

 

[Dark0Prince]

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

It should be the active sheet when it gets to that part of the code. I know the code works because if I just use one client number it works fine. Also there would be no way to reference it anyway because I'm using the VBA to continue to create new sheets so the name keeps changing to blank1 blank2 ect.

 

[MrTeeny]

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

How is the client number being generated and where is it being stored? That's the number that needs to be checked against the list. And where is the list you're actually searching being stored what cell and format?

The code is just simply searching for a string of text contained in ClientNumber2SearchFor if that contained 08296 it'd be found in the ClientListToSearch if that was "6055","3720","11885","20864","08296","05570","2648" . If anything the search is a bit too loose as it'd find things like 082 in the ClientListToSearch also, but that can easily be rectified by enclosing serial's in quotation marks so not a probale right now.

 

[Dark0Prince]

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

When the new sheet is created, A14 on the new sheet will have the client number that appears on the list on the blankDispo excel workbook.

 

[MrTeeny]

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

I think you probably need to do some error checking to make sure all the variables are being picked up correctly as the code is just a simple search. Maybe stick in some msgboxes so you can see what each variable is holding before the code moves on, should help track down where any errors are occurring, maybe your client number is being written after the code has run I've only seen the snippet. Just add the following lines after you've set your ClientNumber2SearchFor & ClientListToSearch values and you'll be able to see if they're actually getting picked up

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

MsgBox "ClientNumber2SearchFor value is " & ClientNumber2SearchFor
MsgBox "ClientListToSearch value is " & ClientListToSearch

 

[Dark0Prince]

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

So when I do what you ask I see that it is picking up my list, but like I tested earlier this code only works when I have the exact and only the exact client number in

ClientNumber2SearchFor = Workbooks("DISP FORM - BLANK.xlsm").Sheets("ShareFile").Range("A1").Value

Even if I have a space after my client number (or more then one client number) it doesn't match perfectly and then fails to increase the testposition value

 

[MrTeeny]

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

But it needs to match the client numbers exactly otherwise it'd be a bit pointless because it may match partial numbers of excluded or allowed numbers. To clear spaces from your values is easily fixed by using trim() which will clear any whitespaces at the start or end of your string. You need the values you enter and the values you're checking to be in a regular format for things to work well

ClientNumber2SearchFor = Trim(Workbooks("DISP FORM - BLANK.xlsm").Sheets("ShareFile").Range("A1").Value)

I'm not too sure what you mean by "and only the exact client number in" why would the sheet you're deciding to protect or not have more than one client number?

 

[Dark0Prince]

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

Trim() won't work because I need a list of numbers to search against, and something needs to separate each one(I tried quotes, comas, spacing, and a mix of the lot). The sheet I'm trying to protect should not be protected if it contains one of the client numbers on the list from the DISP FORM. Also what I mean by "and only the exact client number in" is this.. If I have client number 1234 with no spacing after the number 4 on the DISP FORM then it matches the new active sheet. If I have 1234 6453 in the same cell it will not work because 1234 6453 doesn't match 1234.

 

[MrTeeny]

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

So the sheet you're trying to protect can have more than one client number on it and each one of those needs to be checked against a master list? If the sheet contains any matched client number it should not be protected?