How to create "Array" that looks at column values?

dpnab

New Member
Joined
Apr 12, 2022
Messages
35
Office Version
  1. 365
Platform
  1. Windows
I currently use the following VBA formula to print out a separate sheet based on each "ABC" value in the "array".

Sub PrintAll()
Dim i As Integer
Dim VList As Variant

VList = Array("ABC123","ABC456","ABC789")

For i = LBound(VList) To UBound(VList)
Range("B1") = VList(i)
ActiveSheet.PrintOut
Next
End Sub

I have a list of items in Sheet "Products" and in Column A starting from A2, I have all these product numbers. Instead of manually entering them into the macro (ABC123, ABC456, ABC789, etc etc), I want it to just use all the values that in in Column A.

This will change on a daily basis, so I'd like it to search in Column A and stop populating when a cell is blank.

What can I add to do this?

Thanks!
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Forgot to add, the Range("B1") = VList(i) is referencing a sheet called "Template"
 
Upvote 0
Hi & welcome to MrExcel.
How about
VBA Code:
Sub PrintAll()
Dim i As Integer
Dim VList As Variant

With Sheets("Products")
   VList = .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Value
End With

For i = LBound(VList) To UBound(VList)
Range("B1") = VList(i, 1)
ActiveSheet.PrintOut
Next
End Sub
 
Upvote 0
That worked perfectly!

If the Range in A2 is a table and I filter the table, will it still pick up all the values or jsut what is filtered?

I don't need a formula to do it based on a filter, just need to know if it will or not.
 
Upvote 0
It will pick out all the values, not just the visible ones.
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
Hi & welcome to MrExcel.
How about
VBA Code:
Sub PrintAll()
Dim i As Integer
Dim VList As Variant

With Sheets("Products")
   VList = .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Value
End With

For i = LBound(VList) To UBound(VList)
Range("B1") = VList(i, 1)
ActiveSheet.PrintOut
Next
End Sub
I tried this again and for some reason it's not longer working?

It highlights the following line of code:
For i = LBound(VList) To UBound(VList)

And gives the following error:

Run-time error '13':
Type mismatch
 
Upvote 0
Do you have any values below A2 on the Products sheet?
 
Upvote 0
Do you have any values below A2 on the Products sheet?
No. I added a few more and it printed. This is a dynamic range (query through ODBC) so it only shows "current orders" so there may only be a single line. How can I update the code to allow for that?
 
Upvote 0

Forum statistics

Threads
1,213,559
Messages
6,114,302
Members
448,564
Latest member
ED38

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