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

dpnab

New Member
Joined
Apr 12, 2022
Messages
33
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

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

dpnab

New Member
Joined
Apr 12, 2022
Messages
33
Office Version
  1. 365
Platform
  1. Windows
Forgot to add, the Range("B1") = VList(i) is referencing a sheet called "Template"
 
Upvote 0

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
81,804
Office Version
  1. 365
Platform
  1. Windows
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

dpnab

New Member
Joined
Apr 12, 2022
Messages
33
Office Version
  1. 365
Platform
  1. Windows
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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
81,804
Office Version
  1. 365
Platform
  1. Windows
It will pick out all the values, not just the visible ones.
 
Upvote 0

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
81,804
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 
Upvote 0

dpnab

New Member
Joined
Apr 12, 2022
Messages
33
Office Version
  1. 365
Platform
  1. Windows
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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
81,804
Office Version
  1. 365
Platform
  1. Windows
Do you have any values below A2 on the Products sheet?
 
Upvote 0

dpnab

New Member
Joined
Apr 12, 2022
Messages
33
Office Version
  1. 365
Platform
  1. Windows
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,187,202
Messages
5,962,181
Members
438,592
Latest member
vincethesun

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
Top