Can't add values to an array

[sharky12345]

I'm trying to add values from a row to an array and just can't figure it out.

What I can't work out is how to add the values from column H onward to an array. It's probably very straightforward but I've tried all manner of solutions and just can't get it to work. The latest thing I have tried is producing an array but it has extra blank values added too.

This is what I've been playing with, (don't make fun at how rough and crude it is, I'm doing my best to tinker and learn!).

LastRow = ThisWorkbook.Worksheets("COPYRESULTS").Range("A65536").End(xlUp).Row

For Each Cell In ThisWorkbook.Worksheets("COPYRESULTS").Range("A2:A" & LastRow)

r = Cell.Row

ColNo = Cells(r, Columns.Count).End(xlToLeft).Column
ColLe = Col_Letter(Cells(r, Columns.Count).End(xlToLeft).Column)

Dim myary(1 To 7) As String
Dim var As Integer
Dim myrang As Range, myC As Range
var = 1
Set myrang = Range("H3:" & ColLe & "3")
For Each myC In myrang
myary(var) = "• " & myC.Value
var = var + 1
Next myC
For var = LBound(myary) To UBound(myary)
Debug.Print myary(var)
Next var

Next Cell

Any advice is appreciated!

 

[kevin9999]

I'm not 100% clear on what range exactly you want stored in your array, but please give this a try & see if it comes close to what you are looking for:

Option Explicit
Sub FillArray_1()
    Dim ws As Worksheet
    Set ws = Worksheets("COPYRESULTS")
    Dim LRow As Long, LCol As Long
    LRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
    LCol = ws.Cells.Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column
    Dim myary, n As Long, i As Long, c As Range
    n = ws.Range(ws.Cells(3, 8), ws.Cells(LRow, LCol)).Cells.Count  '<-- cells 3,8 = H3 your starting point?
    ReDim myary(1 To n) As Variant
    
    i = 1
    For Each c In ws.Range(ws.Cells(3, 8), ws.Cells(LRow, LCol))
        If c <> "" Then
            myary(i) = "• " & c.Value
            i = i + 1
        End If
    Next c
    ReDim Preserve myary(1 To i - 1)
    
    For i = LBound(myary) To UBound(myary)
        Debug.Print myary(i)
    Next i
End Sub

 

[sharky12345]

Thanks very much, that's certainly progress!

Can it be tweaked please so that it adds each value (for each row) into 1 string with each value on a new line?

I want to store the result in column G of that row.

Thanks, appreciate your help!

 

[kevin9999]

Can it be tweaked please so that it adds each value (for each row) into 1 string with each value on a new line?

I want to store the result in column G of that row.

I'm struggling to visualise this a bit - could you provide a before-and-after sample so I can get my head around it? Preferably using the XL2BB add in.

 

[sharky12345]

Yeah sure - I'm on a train at the moment and don't have my laptop so I'll reply later - thanks!

 

[sharky12345]

I've managed to borrow a colleagues laptop!

So this is the before;

All Gold Checks.xlsm
	G	H	I	J
1	Summary	1	2	3
2		Delay calling	Warning not added
3		No log	No stamp	Documented clear
COPYRESULTS

and this is the after;

All Gold Checks.xlsm
	G	H	I	J
1	Summary	1	2	3
2	• Delay calling •Warning not added	Delay calling	Warning not added
3	• No log • No stamp • Documented clear	No log	No stamp	Documented clear
COPYRESULTS

I have to say, I find arrays so confusing to understand so I'm grateful for your help!

 

[kevin9999]

Please try this on a copy of your workbook:

Option Explicit
Sub FillArray_2()
    Dim ws As Worksheet
    Set ws = Worksheets("COPYRESULTS")
    Dim LRow As Long, LCol As Long
    LRow = ws.Cells.Find("*", , xlFormulas, , xlByRows, xlPrevious).Row
    LCol = ws.Cells.Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column
    Dim myary, outary, i As Long, j As Long, s As String
   
    myary = ws.Range(ws.Cells(2, 8), ws.Cells(LRow, LCol))  '<-- *** assumes you're starting in H2 (2,8)
    ReDim outary(1 To UBound(myary, 1), 1 To 1)
    For i = 1 To UBound(myary, 1)
        For j = 1 To UBound(myary, 2)
            If myary(i, j) <> "" Then s = s & "• " & myary(i, j) & vbCrLf
        Next j
        s = Left(s, Len(s) - 1)
        outary(i, 1) = s: s = ""
    Next i
        ws.Range("G2").Resize(UBound(outary, 1), 1).Value = outary
        ws.Range("G2:G" & LRow).EntireRow.AutoFit
End Sub

Before:

sharky.xlsm
	G	H	I	J
1	Summary	1	2	3
2		Delay calling	Warning not added
3		No log	No stamp	Documented clear
COPYRESULTS

After:

sharky.xlsm
	G	H	I	J
1	Summary	1	2	3
2	• Delay calling • Warning not added	Delay calling	Warning not added
3	• No log • No stamp • Documented clear	No log	No stamp	Documented clear
4
COPYRESULTS

 

[sharky12345]

Thanks!

I'm getting a run-time error 5, invalid procedure call or argument on this line;

s = Left(s, Len(s) - 1)

 

[kevin9999]

OK, as you can see, I don't get that when I run the code on the data sample you supplied. Is it possible to share your file via Google Drive, Dropbox or similar file sharing platform? That way, we'll both be looking at the same data.

 

[sharky12345]

Yeah sure, I'll need to sanitise it first but certainly......I'll come back to you