Concatenate with Offset

[Dani_LobP]

Hello all,

I am trying to use concatenation formula with VBA and need to use offset in order to complete it, but doesn't seem to work.

The point is:I have 2 lists of names in 2 columns, which i need to add some stuff.

What i need to do is select a column A item, and macro will insert 29 rows below (so we have total 30 rows for that cell value)
then macro will copy both values all the way down, so we end up with 30 rows with same values both columns.
What i do next is in C1, add a number i get from a range. As you can assume, this range is 1-30.

My problem comes now, because in D1 i want to concatenate B1 and C1. And in E1 concatenate A1 and C1.

I tried concatenate with offset -2 and -1 but wouldn't work.

Need it to make it with active cells and offset because not always are fix cells. so it will always depend where i click down the list*.
*Actually eventually will make it with a loop since i will need whole list sorted that way, but that comes later...

At the end I'm concatenating in F1 a Fix cell with a name + E1.

Not sure if makes sense or explained it correctly.

So should look:
A B
Milk Cow
Apple Tree
Fish Sea

(for example :P)

So if i select Milk cell and use macro, it will do:


Milk Cow 1 Cow1 Milk1 FarmMilk1
Milk Cow 2 Cow2 Milk2 FarmMilk2
and so on... until
Milk Cow 30 Cow30 Milk30 FarmMilk30

This names are just examples so it can be understood not based on reality :P

Hope someone can help!

Thanks in advance.

 

[mohadin]

Hi
Just as a start is this what you are aiming

Sub test()
    With ActiveCell
        .Offset(1).Resize(29).EntireRow.Insert
        .Offset(, 2).FormulaR1C1 = "1"
        .Offset(, 3).Resize(30).Select
        .Offset(, 2).Resize(30).DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=1
        .Resize(30, 2).Select
        .Resize(30, 2).FillDown
        For i = 0 To 30
        .Offset(i, 3) = .Offset(i, 1) & .Offset(i, 2) & " " & .Offset(i, 0) & .Offset(i, 2)
        Next
    End With
End Sub

 

[Dani_LobP]

Thanks Mohadin, modified some small detail but really helps!
Some other thing, you know how could now apply that as a loop so it do same thing row on every item of the list?
So right now is doing it only on the active cell when i select, but i'd like to know how to make it so when i select cell, then it do that, and then automatically keeps going to all items below the active cell.
Hope it makes sense.
Thanks in advance!

 

[mohadin]

Hi
May be?

Sub test()
    Application.ScreenUpdating = False
    lr = Cells(Rows.Count, 1).End(xlUp).Row
    For j = lr To 1 Step -1
        Cells(j, 1).Activate
        With ActiveCell
            .Offset(1).Resize(29).EntireRow.Insert
            .Offset(, 2).FormulaR1C1 = "1"
            .Offset(, 3).Resize(30).Select
            .Offset(, 2).Resize(30).DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=1
            .Resize(30, 2).Select
            .Resize(30, 2).FillDown
            For i = 0 To 30
                .Offset(i, 4) = .Offset(i, 0) & " " & .Offset(i, 1) & " " & .Offset(i, 2)
                Cells(j, 1).Activate
                .Offset(i, 5) = .Offset(i, 1) & .Offset(i, 2) & " " & .Offset(i, 0) & .Offset(i, 2)
                Cells(j, 1).Activate
                .Offset(i, 6) = "Farm" & .Offset(i, 0) & .Offset(i, 2)
            Next
        End With
    Next
    Application.ScreenUpdating = True
End Sub

 

[mohadin]

Sub test()
    Application.ScreenUpdating = False
    lr = Cells(Rows.Count, 1).End(xlUp).Row
    For j = lr To 1 Step -1
        Cells(j, 1).Activate
        With ActiveCell
            .Offset(1).Resize(29).EntireRow.Insert
            .Offset(, 2).FormulaR1C1 = "1"
            .Offset(, 3).Resize(30).Select
            .Offset(, 2).Resize(30).DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=1
            .Resize(30, 2).Select
            .Resize(30, 2).FillDown
            For i = 0 To 30
            Cells(j, 1).Activate
                .Offset(i, 4) = .Offset(i, 0) & " " & .Offset(i, 1) & " " & .Offset(i, 2)
                .Offset(i, 5) = .Offset(i, 1) & .Offset(i, 2) & " " & .Offset(i, 0) & .Offset(i, 2)
                .Offset(i, 6) = "Farm" & .Offset(i, 0) & .Offset(i, 2)
            Next
        End With
    Next
    Application.ScreenUpdating = True
End Sub

 

[Dani_LobP]

Thanks! works great. tweaked it a bit to fit my macro but helps a lot!

 

[mohadin]

You are very well come & thank you for the feedback
Be happy