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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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 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
ASKER
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?
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?
ASKER
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 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.
ASKER
For the macro send me please with general variables , and comments
ASKER
if you can do it as macro i could understand better ,thanks anyway
test-macro.xlsm