For loop through array

[samerickson89]

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

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:

'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.

 

[Rick Rothstein]

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")

 

[DanteAmor]

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:

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

 

[samerickson89]

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.

 

[DanteAmor]

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.

 

[samerickson89]

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?

 

[Rick Rothstein]

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).

 

[samerickson89]

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.

 

[Rick Rothstein]

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.

 

[samerickson89]

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!