Link to home
Start Free TrialLog in
Avatar of tonyantony
tonyantony

asked on

i need a macro to (merge) concatenate a number(example-5cells of a column[example at the 1st column]) and do it that every 4 columns?

i need a macro to (merge) concatenate a number Variable X (example-4cells of a column) ,
Variable Y  [example at the 1st column] and do it that every Variable Z=X+N(rows) {example every 4-rows}
i am sending  an excel
test-macro.xlsm
SOLUTION
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of tonyantony
tonyantony

ASKER

there is an error that prevents the formula to be executed ,maybe i am do it wrong
if you can do it as macro i could understand better ,thanks anyway
test-macro.xlsm
Looks like you entered it as text, delete the apostrophe at the start of the formula in B2 and C2 and then copy down.

Corrected file attached.
test-macro-1.xlsm
Corrected code

Sub macro()

myCnt = Range("A" & Cells.Rows.Count).End(xlUp).Row - 1
For Idx = 1 To myCnt / 4 + 1 Step 1
    Range("E" & Idx + 1) = _
    [B2] & Range("A" & 1 + ((Idx - 1) * 4) + 1) & [B3] & _
    [B2] & Range("A" & 1 + ((Idx - 1) * 4) + 2) & [B3] & _
    [B2] & Range("A" & 1 + ((Idx - 1) * 4) + 3) & [B3] & _
    [B2] & Range("A" & 1 + ((Idx - 1) * 4) + 4) & [B3]
    Range("E" & Idx + 1).Value = Replace(Range("E" & Idx + 1).Value, [B3]& [B2], "")
Next
End Sub

Open in new window

Thanks for the right answers both, tested  ok
can i ask something else
except of merge(concatenate) the cells can we do transpose from column to rows (not to merge) without separation chars?
For both solutions can you make comments and add general variables that so i can change it easy for another use? , THANKS ANYWAY FOR QUICK RESPONSE
For the transpose question, I suggest you raise a separate question.

For the  variables question, changing the cells that I referred to will change the result, ie changing the 4 in E8 to 5 will change the result to 5 entries per result on the 5th row of each block.
For the macro send me please with general variables , and comments