For loop through array

samerickson89

New Member
Joined
Jun 13, 2019
Messages
38
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.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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")
 
Upvote 0
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

Picture-2-Update-Delete-Box.png
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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).
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,957
Members
448,535
Latest member
alrossman

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