VBA Split each cell value in specific range and add returned values to 1D array

Question

I have a code written that makes an array from a specific range in my worksheet

Dim LastColumn As Long
LastColumn = Cells(Cells.Find("Parameters", lookat:=xlWhole).Row, Columns.Count).End(xlToLeft).Column

Dim PackageDepthMax 
PackageDepthMax = ThisWorkbook.Worksheets("Machine Specification").Range(Cells(Cells.Find("Package Depth(mm)").Row, 3), Cells(Cells.Find("Package Depth(mm)").Row, LastColumn)).Value

Which takes all the values from my range which could look like:

What I am trying to do now is to have my "PackageDepthMax" consist of only the second integers in the value aka (100 , 110 , 120) instead of (1-100 , 1-110 ,1-120).

My attempt didn't do what I expect so I'm kinda lost

Dim LastColumn As Long
LastColumn = Cells(Cells.Find("Parameters", lookat:=xlWhole).Row, Columns.Count).End(xlToLeft).Column

Dim PackageDepthMax
PackageDepthMax = Split(ThisWorkbook.Worksheets("Machine Specification").Range(Cells(Cells.Find("Package Depth(mm)").Row, 3), Cells(Cells.Find("Package Depth(mm)").Row, LastColumn)).Value, " - ")(1)

What's the mistake I'm making and how do I achieve the goal I'm looking for?

NoobVB · Accepted Answer · 2022-02-01 15:12:59Z

1

Keep in mind, excel will not allow split multiple columns (range of columns) at the same time.

what about replacing unwanted symbols with "" ... For example:

yourRange.Replace What:=" ", Replacement:="" to remove any gaps which may popout in wrong amounts

yourRange.Replace What:="1-", Replacement:="" removing leading "1-" from the beginning of the value

or simply yourRange.Replace "1 - ", ""

edited Feb 1, 2022 at 15:12

answered Feb 1, 2022 at 15:02

NoobVB

1,0337 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA Split each cell value in specific range and add returned values to 1D array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related