Results 1 to 10 of 10

Thread: For loop through array
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jun 2019
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default For loop through array

    I'm trying to replicate what this code does...

    Code:
    Sub Workbook_Open()
    
    
    'unprotect sheet, start with all cells locked
    ActiveSheet.Unprotect
    ActiveSheet.UsedRange.Locked = True
    
    'define variables
    Dim InspNo As String
    Dim DateRecd As String
    Dim QtyRecd As String
    Dim PurchNo As String
    Dim SampSize As String
    Dim Vendor As String
    Dim VendNo As String
    Dim PartNo As String
    Dim Rev As String
    Dim Descr As String
    
    
    'prompts when workbook is opened
    InspNo = InputBox("What is the inspection report number?", "Inspection Report Number")
    DateRecd = InputBox("What date was part received?", "Date Received")
    QtyRecd = InputBox("How many of this part were received?", "Quantity Received")
    PurchNo = InputBox("What is the purchase order number?", "Purchase Order Number")
    SampSize = InputBox("How many parts are being inspected?", "Sample Size")
    Vendor = InputBox("What is the vendor name?", "Vendor Name")
    VendNo = InputBox("What is the vendor code number?", "Vendor Code Number")
    PartNo = InputBox("What is the part number?", "Part Number")
    Rev = InputBox("What is the current revision level?", "Revision Level")
    Descr = InputBox("What is the part description?", "Description")
    
    
    'place values from prompts onto worksheet
    Range("O2").Value = InspNo
    Range("C3").Value = DateRecd
    Range("O3").Value = QtyRecd
    Range("C4").Value = PurchNo
    Range("O4").Value = SampSize
    Range("C5").Value = Vendor
    Range("O5").Value = VendNo
    Range("C6").Value = PartNo
    Range("H6").Value = Rev
    Range("K6").Value = Descr
    
    
    'unlock blank fields in heading
    'If IsEmpty(ActiveSheet.Range("O2")) Then ActiveSheet.Range("O2").Locked = False
    'If IsEmpty(ActiveSheet.Range("C3")) Then ActiveSheet.Range("C3").Locked = False
    'If IsEmpty(ActiveSheet.Range("O3")) Then ActiveSheet.Range("O3").Locked = False
    'If IsEmpty(ActiveSheet.Range("C4")) Then ActiveSheet.Range("C4").Locked = False
    'If IsEmpty(ActiveSheet.Range("O4")) Then ActiveSheet.Range("O4").Locked = False
    'If IsEmpty(ActiveSheet.Range("C5")) Then ActiveSheet.Range("C5").Locked = False
    'If IsEmpty(ActiveSheet.Range("O5")) Then ActiveSheet.Range("O5").Locked = False
    'If IsEmpty(ActiveSheet.Range("C6")) Then ActiveSheet.Range("O2").Locked = False
    'If IsEmpty(ActiveSheet.Range("H6")) Then ActiveSheet.Range("H6").Locked = False
    'If IsEmpty(ActiveSheet.Range("K6")) Then ActiveSheet.Range("K6").Locked = False
    
    'unlock measurement columns
    Range("D9:M26").Locked = False
    
    
    'protect sheet to prevent edits anywhere but unlocked cells
    ActiveSheet.Protect
    
    
    End Sub
    ...by creating two arrays, one for variables and one for the cells their values will be placed in, then having a For loop iterate through the arrays rather than writing the same code for each variable. This is the code I tried to accomplish that with:

    Code:
    'Dim Vars As Variant
    Vars = Array("InspNo", "DateRecd", "QtyRecd", "PurchNo", "SampSize", "Vendor", "VendNo", "PartNo", "Rev", "Descr")
    Dim Cells As Variant
    Cells = Array("O2", "C3", "O3", "C4", "O4", "C5", "O5", "C6", "H6", "K6")
    
    For i = LBound(Vars) To UBound(Vars)
        Dim Vars(i) As String
    Next i
    but I keep running into errors no matter what tweaks I try. I want to apologize in advance for not having kept track of the changes I've made so far, but I'll make sure to do that from now on!

    Any help is very much appreciated.

  2. #2
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,251
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: For loop through array

    You cannot reference variables by their names stored as text in String variable. If you created an array where each index number was specifically for a given purpose (what you are trying to use the variable's name for), you could iterate that array and accomplish what your code is attempting. However, there is a simpler approach at your disposal. Since you are not testing the user's response to the InputBox question, and assuming you are content to keep assuming the user will only input valid answers, you could simply assign the output to the cell directly. For example, where you have these two (separated) lines of code...

    Vendor = InputBox("What is the vendor name?", "Vendor Name")
    ....
    Range("C5").Value = Vendor

    You could do away with the Vendor variable and make this single line assignment to replace the above two lines of code...

    Range("C5").Value = InputBox("What is the vendor name?", "Vendor Name")
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  3. #3
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,331
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    14 Thread(s)

    Default Re: For loop through array

    To capture many data, it is best to capture from a userform.

    You can check the excel default data form
    Put the following code in a module:

    Code:
    Sub Open_DataForm()
        ActiveSheet.ShowDataForm
    End Sub
    Your active sheet must have headers.

    check the following video
    https://www.youtube.com/watch?v=JwU1xj7mPOc

    ------------------

    Or you can also create your own userform

    Regards Dante Amor

  4. #4
    New Member
    Join Date
    Jun 2019
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: For loop through array

    Quote Originally Posted by Rick Rothstein View Post
    Vendor = InputBox("What is the vendor name?", "Vendor Name")
    ....
    Range("C5").Value = Vendor

    You could do away with the Vendor variable and make this single line assignment to replace the above two lines of code...

    Range("C5").Value = InputBox("What is the vendor name?", "Vendor Name")
    Thanks for the suggestion! That does make the code much more concise. To make sure the inputs are valid, I just used an "If" statement that opens a new InputBox if the entry isn't valid.

    Dante, I'm just starting to learn about user forms, but I'll keep them in mind in the future.

  5. #5
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,331
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    14 Thread(s)

    Default Re: For loop through array

    Quote Originally Posted by samerickson89 View Post
    Dante, I'm just starting to learn about user forms, but I'll keep them in mind in the future.
    When you try, let me know if you have questions.
    Regards Dante Amor

  6. #6
    New Member
    Join Date
    Jun 2019
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: For loop through array

    Quote Originally Posted by Rick Rothstein View Post
    You could do away with the Vendor variable and make this single line assignment to replace the above two lines of code...

    Range("C5").Value = InputBox("What is the vendor name?", "Vendor Name")
    I'm noticing an issue with this method. If I have to add or delete any columns or rows, I have to go through and change every reference to the cell number. Is there a way to do that quickly, or would I be better off going back to variables?

  7. #7
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,251
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: For loop through array

    Quote Originally Posted by samerickson89 View Post
    I'm noticing an issue with this method. If I have to add or delete any columns or rows, I have to go through and change every reference to the cell number. Is there a way to do that quickly, or would I be better off going back to variables?
    I made my suggestion based on the code you posted and that code did not hint at your wanting to add or delete columns. Given that, I am not sure when or how you intend to insert or delete columns but wonder how storing vendor names in variables is going to help (probably depends on the code ideas you plan on implementing which you did not yet share with us).
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  8. #8
    New Member
    Join Date
    Jun 2019
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: For loop through array

    Quote Originally Posted by Rick Rothstein View Post
    I made my suggestion based on the code you posted and that code did not hint at your wanting to add or delete columns. Given that, I am not sure when or how you intend to insert or delete columns but wonder how storing vendor names in variables is going to help (probably depends on the code ideas you plan on implementing which you did not yet share with us).
    Yeah sorry about that, I didn't realize I would be changing the layout of the worksheet when I first posted this question. I'll try to give the simplest example I can. I originally had "part number" placed in cell C6, then a separate macro which prints a PDF of the sheet used the contents of cell C6 in the output file name. After adding a column, part number was now in D6, and I had to go in and manually change "C6" to "D6" in the other macro. I was thinking that if I have it as a variable instead, I would be able to call that variable in the other macro instead of referring to a specific cell, so it would still work when rows and columns change.

  9. #9
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,251
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: For loop through array

    Quote Originally Posted by samerickson89 View Post
    Yeah sorry about that, I didn't realize I would be changing the layout of the worksheet when I first posted this question. I'll try to give the simplest example I can. I originally had "part number" placed in cell C6, then a separate macro which prints a PDF of the sheet used the contents of cell C6 in the output file name. After adding a column, part number was now in D6, and I had to go in and manually change "C6" to "D6" in the other macro. I was thinking that if I have it as a variable instead, I would be able to call that variable in the other macro instead of referring to a specific cell, so it would still work when rows and columns change.
    If you give a cell or range a Defined Name and then use that Defined Name in your code (as a quoted string within the Range object), it will update automatically as columns or rows are added/deleted.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  10. #10
    New Member
    Join Date
    Jun 2019
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: For loop through array

    Quote Originally Posted by Rick Rothstein View Post
    If you give a cell or range a Defined Name and then use that Defined Name in your code (as a quoted string within the Range object), it will update automatically as columns or rows are added/deleted.
    Ok I'll give that a try. Thanks!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •