PDA

View Full Version : combine cells with VBA



Mergh06
09-25-2011, 02:59 AM
Hello guys,
I have problems with combining cells.
I attached the excel file.

I need a code that can combine text-cells in every combination as you can see in the attached file. I need to be able to add more raw data (the yellow filled color).

Any suggestions?

Thank you
Mergh

Bob Phillips
09-25-2011, 03:16 AM
Where do the numbers come from?

Mergh06
09-25-2011, 03:17 AM
dosnt matter, It is just an example

Bob Phillips
09-25-2011, 03:24 AM
It does, if you want an answer.

Mergh06
09-25-2011, 03:31 AM
ah oki:) fair

It is information from my company about my employes and what they prefer to work with. The preferences can be mixed up, which im trying to do. I use a model from a Human Resouce Management book and I do not have many skills in VBA:/ long time ago..

But with data mining and the model I can make a portfolio of labor in the most efficient way

Mergh06
09-25-2011, 04:08 AM
can it be done so it highlight the 40-70 values, in the same sheet? (sheet1)
Maybe make a orange filled color ?

Mergh06
09-25-2011, 04:37 AM
noone can help?

Bob Phillips
09-25-2011, 05:05 AM
I don't mean the original numbers, I mean the numbers under the results. Why 78 under aa, why 65, etc.?

Mergh06
09-25-2011, 05:08 AM
ah, It should be the sum of a,b,c,d.. I did just make some random numbers to setup the example.

Bob Phillips
09-25-2011, 05:39 AM
That doesn't explain the many.

Can you make a realistic example, at least consistent in source and results?

Mergh06
09-25-2011, 05:45 AM
its a way to sum up the income. If they buy some things, they cannot buy other things... Maybe they buy gift instead of house you know... Then i have a portfolio of what the guys prefer... That make sence since if they are happy then maybe work harder

You can read about it in Human Resource Management: Linking Strategy to Practice. John Wiiley & Sons

Mergh06
09-25-2011, 05:48 AM
Do you think you can help with the VBA problem?

Bob Phillips
09-25-2011, 06:42 AM
its a way to sum up the income. If they buy some things, they cannot buy other things... Maybe they buy gift instead of house you know... Then i have a portfolio of what the guys prefer... That make sence since if they are happy then maybe work harder

You can read about it in Human Resource Management: Linking Strategy to Practice. John Wiiley & Sons

Do you know, when someone asks for help on a topic, and it is not so clear, it seems reasonable to me for them to explain it better. I certainly don't expect to be told I can read up about it in a book.

I'm outta here ... bye!

Mergh06
09-25-2011, 06:47 AM
but its nothing about the numbers... only the literally...

Mergh06
09-25-2011, 06:49 AM
you can see in the excel file that aa ab ac ad, ba bb bc bd and so on... i need a vba program that can do that..

mikerickson
09-25-2011, 06:43 PM
Does that HR book explain that if someone asks for something that is needed to complete a task, then they probably need that thing.

Yes, it is possible to make combinations of the column headers in the yellow area. (Do you mean pairs as in the example, or combinations which would include abc?)

In order to give more help than "Yes, it is possible" we would have to have a realistic lay-out with self-consitant results.

By the way, are you going to be adding columns to the yellow area?

Mergh06
09-25-2011, 09:22 PM
:) no the book dont. hehe.

I attached the document with possible real values. They are not my values cause I dont have all information yet... When I get within some days (alot more info) I can send you if your interrested...

yeah, i need to add alot more colums to the left.. In the yellow area (the yellow area can be alot bigger)

Still hope you can help with the combining vba problem...

thanks you
Mergh

mikerickson
09-25-2011, 10:11 PM
This solution uses names. Select a cell in column K and define these names

Name: countOfTypes RefersTo: =MATCH("",Sheet1!$2:$2&"",0)-1
Name: firstIndex RefersTo: =INT((COLUMNS(!$A:A)-1)/countOfTypes)+1
Name: secondIndex RefersTo:=MOD(COLUMNS(!$A:A)-1,countOfTypes)+1

Then put the formula
=INDEX($A2:$J2,1,firstIndex) & " " & INDEX($A2:$J2,1,secondIndex)
in K2 and drag right for the headers

An almost identical formula
=INDEX($A3:$J3,1,firstIndex)+ INDEX($A3:$J3,1,secondIndex)
put in K3 and dragged down and right will return the sums.

Mergh06
09-26-2011, 03:29 AM
Thanks alot, but is it possible with VBA? It would be a little nicer... :) It also have to be possible to add more rows..

mikerickson
09-26-2011, 07:08 AM
To add rows, just drag the formula down extra rows. You could define the range as a List/Table (nomenclature depends on Excel version) and Excel will automatically put the formula in for you.

If you want VBA, the easiest would be to practice setting up that scheme with Names and formulas. Add the final step of copying what you want and pasting special/Values. Then you can do it again with the Macro Recorder on and you have your macro. If you go this route, you should copy the formulas down beyond your data.

Mergh06
09-26-2011, 07:25 AM
hmm dont understand exactly.. cause the VBA should take care of more columns and rows in the future... I dont think it can that now?

mikerickson
09-26-2011, 08:07 AM
The formulas will adjust if you insert columns in the yellow region.
Try it and report back any problems. Excel formulas are very powerful and better than VBA, if they can do the task.
This shuffling of cell contents and simple calculation is well within their capabilities.

In general, a VBA solution is inferior to a worksheet formula solution.

Mergh06
09-27-2011, 04:42 AM
hmm you cannot write it for me?:D

I cannot get it to work :S

mikerickson
09-27-2011, 07:11 AM
In the attached, if you run SetUp macro, it will set up the names and formulas that you need.
If you add new rows, it will adjust.
If you add new columns, it will adjust to that when a new row is added, or you can manualy re-run SetUp.

BTW, the Chart is not robust against cell deletion, it needs to be fixed.

Mergh06
09-27-2011, 11:27 PM
wont work :/ you cannot make it so it just do it automatic too ? would be alot easier..

make the data on sheet 1 and the resultat is just sheet 2.... if this could be done with the simples code ever, it would be sooo great...

mikerickson
09-28-2011, 06:34 AM
What do you mean by "won't work".
What happens when you run the macro in the attachment?

Mergh06
09-28-2011, 06:42 AM
It do not work...
Nothing happens :S... right now i have this: {see the attachment (Sheet "Kurser" is input and sheet Data is output [only look at the text])}

Sub envy()

Dim Hans
Dim AntalJette
Dim Find
Dim k
Dim kk
Dim kkk

k = 0
kk = 0
kkk = 0

With ActiveWorkbook.Worksheets("Kurser")
Set Hans = Range(("B2"), Range("B2").End(xlToRight)) 'Designer et range der definere en ende mod højre (ankercelle a2)
End With


With ActiveWorkbook.Worksheets("Kurser").Range("B2")
AntalJette = Range(.Offset(0, 1), .Offset(0, 1).End(xlToRight)).Columns.Count 'Tæller antallet af labels
End With

With ActiveWorkbook.Worksheets("Data")
.Cells.Clear 'Sletter Data


For k = 0 To AntalJette
For kk = 0 To AntalJette
Set Find = Hans.Offset(0, k)
Set Find2 = Hans.Offset(0, kk)
.Cells(2, kkk + kk + k) = Find & Find2
kk = kk + 1
Next
k = k + 1
kkk = kkk + 1
Next
End Sub

mikerickson
09-28-2011, 07:54 AM
The Application.EnableEvents lines need to be added to these routines.
Sub setHeaderFormulas(rangeToSet As Range)
Application.EnableEvents = False
With rangeToSet
.FormulaR1C1 = "=INDEX(DataRow,1,FirstIndex)&CHAR(5)&"" ""&INDEX(DataRow,1,SecondIndex)"
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=(MOD(firstindex,2)=1)"
.FormatConditions(1).Interior.ColorIndex = 44
End With
Application.EnableEvents = True
End Sub

Sub setDataFormulas(rangeToSet As Range)
Application.EnableEvents = False
rangeToSet.FormulaR1C1 = "=INDEX(DataRow,1,FirstIndex) + INDEX(DataRow,1,SecondIndex)"
Application.EnableEvents = True
End Sub

Mergh06
09-28-2011, 07:58 AM
To my lines?
what do they do ? I dont fell my program works yet

mikerickson
09-28-2011, 10:09 AM
The changes described in post #28 should be made to the code in the attachment to post #24

Mergh06
09-29-2011, 02:17 AM
You cannot get my vision to work ? then i would be happy :)
Anyway, thank you
Mergh