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

D

Dark0Prince

Active Member
Joined
Feb 17, 2016
Messages
433
Code: 
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?
 
Sort by date Sort by votes
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.
 
Upvote 0

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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?
 
Upvote 0
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.

Code: 
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"
 
Last edited:
Upvote 0
I tweaked it to the following to get rid of quotation marks in case they weren't present in both cells

Code: 
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"
 
Last edited:
Upvote 0
Or even try this as it'll show each entry on your list that's being checked

Code: 
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"
 
Upvote 0
When I tried to incorporate your code it started from this..
Code: 
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.
Code: 
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.
 
Upvote 0
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?"
Code: 
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


 
Last edited:
Upvote 0
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.
 
Upvote 0
Dark0Prince said:
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.
Click to expand...

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.
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Question
Workbook formulas copied with Sheet references in new Workbook
Replies
0
Views
27
GK039
G
L
  • Locked
  • Question
Help Cleaning Up Code
Replies
2
Views
368
RoryA
RoryA
U
  • Question
VBA to look for an open file
Replies
1
Views
529
NateSC
NateSC
M
  • Question
I need help with this code to modify the data into a table using Userform for VBA Excel (I want to do the range unlimited instead to do C7:C80000
Replies
5
Views
484
Dan_W
D
T
  • Question
VBA code to export specific variably named tabs to new workbook?
Replies
2
Views
447
thebigejr
T

Forum statistics

Threads
1,216,073
Messages
6,128,637
Members
449,461
Latest member
kokoanutt

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top