VBA - How to copy cell from a different worksheet with conditionals

[yorgosnc]

Hi guys, so I'm trying to copy the Text from one sheet to another with some conditions. So I have in the Column D, after the cell D7, some data organized by rows, and this data can vary. And if the cell in Column D is filled, the Column H will always have the same data as the Column D to Column "i" if they have more data.

What I need to do is, I need to copy the Text in the Column D, but if they have more then just one data filled in the Column H to Column "i" I need to put all the Text's filled. For example:

COLUMN D	COLUMN H	COLUMN I	COLUMN J	COLUMN K
P1001	P1001
P1002	P1002	P1006
P1003	P1003	P1007	P1008
P1004	P1004
P1005	P1005	P1009	P1010	P1011

<tbody>
</tbody>

In the other sheet I need to organize in this way:

Column A
A4	P1001
A5	P1002 = P1006
A6	P1003=P1007=P1008
A7	P1004
A8	P1005=P1009=P1010=P1011

<tbody>
</tbody>

What I came up so far is this:
Code:
Lr = Sheets("Folha Chave").Columns("D").Find("*", After:=.Range("D7"), SearchDirection:=xlNext, SearchOrder:=xlByRows, LookIn:=xlValues).Row

Set Rng = Sheets("Folha Chave").Range("D7:D" & Lr)
For Each cell In Rng
If cell.Value <> "" Then
Sheets("Agrupamento").Cells(4, 1) = cell.Value
End If
Next cell
The problem is, I can't figured out how to copy in sequence to my destination sheet. For instance, Cells(4,1) aka A4 is the first cell, but I want to copy the second data in the cell A5, etc..

Thanks in advance guys

 

[yorgosnc]

I improved a little bit of my code:

Lr = Sheets("Folha Chave").Columns("D").Find("*", After:=.Range("D7"), SearchDirection:=xlNext, SearchOrder:=xlByRows, LookIn:=xlValues).Row
NP = Application.WorksheetFunction.CountIf(Range("D7:D" & Lr), "<>" & "")


Set Rng = Sheets("Folha Chave").Range("D7:D" & Lr)
For Each cell In Rng
If cell.Value <> "" Then
For x = 4 To 4 + NP
Sheets("Agrupamento").Cells(x, 1) = cell.Value
Next x
End If
Next cell

But what i need is to jump cell and then jump the 'x' if you know what I mean.

 

[yorgosnc]

Any helps guys?

 

[Meggesto]

What about something like this not using VBA:

=E5&IF(F5="","","="&F5)&IF(G5="","","="&G5)&IF(H5="","","="&H5)

Using IF Statements and Concatenate.
In my example I started in E you'll start in H it looks like. But it says start with the value in H, IF there's a value in F then Concatenate it with the H value and an "=" between them if there isn't then it's blank. Build it from there to encompass the longest row of values you have and then drag it through on your other sheets.

 

[yorgosnc]

What about something like this not using VBA:

=E5&IF(F5="","","="&F5)&IF(G5="","","="&G5)&IF(H5="","","="&H5)

Using IF Statements and Concatenate.
In my example I started in E you'll start in H it looks like. But it says start with the value in H, IF there's a value in F then Concatenate it with the H value and an "=" between them if there isn't then it's blank. Build it from there to encompass the longest row of values you have and then drag it through on your other sheets.

It's a good solution, but I'm creating a macro that generates some sheets, and inside this new sheet, it's my problem, I can use your solution but in that case, I need to type the formulas, and what a need i'ts to give me the solution automatically, I'll try to use VBA to insert your formula. Cheers mate.