How to concatenate data into the same cell based from an array or in different cells that are already stored?

Question

How do i put multiple values from different cells (eg: values that lies below header x rel and y rel previously obtained by my program) and put them in the same cell which lies the same row as device d?(not manually select in excel or selectively using coding). What my current code does is to locate the x and y values of reliability fails then stored them in array(not sure is it correct or not) but after that how to concatenate them in the same cell shown in "After"?

Public Sub FindAndConvertforreliabilityfails()
Dim i As Integer
Dim j As Integer
Dim lastRow     As Long
Dim myRng       As Range
Dim mycell      As Range
Dim MyColl      As Collection
Dim myIterator  As Variant
Set MyColl = New Collection
Dim xpos As integr, ypos As Integer

MyColl.Add "x rel"
MyColl.Add "y rel"
Dim LastCol As Integer
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
For i = 1 To LastCol
For j = 1 To lastRow

For Each myIterator In MyColl
Do While Sheets(1).Cells(1, j).Value <> ""
If Sheets(1).Cells(1, i).Value = myIterator Then
xpos(j) = Sheets(1).Cells(Rows.Count, 6).End(xlUp).Offset(1, 0)
Else
ypos(j) = Sheets(1).Cells(Rows.Count, 7).End(xlUp).Offset(1, 0)
End If
Loop
Next
Next

' how to continue from here for the concatenate portion?

End Sub

Before

After

Currently

Naveen Kumar · Accepted Answer · 2020-02-21 10:21:32Z

1

Try this. This will give you the expected output.

Private Sub Test()
Dim output As Variant
Dim outputrow As Integer
output = ""
outputrow = 0
For i = 2 To 5 'change 5 to lastrow of F&G Column.
    If Cells(i, "B").Value = 0 Then
        output = output & "(" & Cells(i, "F").Value & "," & Cells(i, "G").Value & "),"
    Else
        Cells(i, "E") = Left(output, Len(output) - 1)
        output = "(" & Cells(i, "F").Value & "," & Cells(i, "G").Value & "),"
    End If
Next i
Cells(i, "E") = Left(output, Len(output) - 1)
End Sub

edited Feb 21, 2020 at 10:21

answered Feb 21, 2020 at 9:37

Naveen Kumar

1,9961 gold badge9 silver badges11 bronze badges

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

13 Comments

cena Over a year ago

hi dude, do i try this together with my code or just yours?

cena Over a year ago

ah thanks alot but right now the code isnt at the same row as deviced(at row5,col5) but is at device e(row 6,col 5), so how do i fix that?

cena Over a year ago

and lets say if the data vary like for eg the last row number i <> 4, can i use i = 2 to lastrow where lastrow= sheets(1) .Cells(.Rows.Count, 1).End(xlUp).Row ?

Naveen Kumar Over a year ago

It would be better to use last row of F and G column. lastrow=Columns("F:G").Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row

Naveen Kumar Over a year ago

And about your first question:Can we relate the position of output with lastrow of F column or is it dependent on some other column?

|

Collectives™ on Stack Overflow

How to concatenate data into the same cell based from an array or in different cells that are already stored?

1 Answer 1

13 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

13 Comments

Your Answer

Sign up or log in

Post as a guest

Related