Hi all
Hope someone can help me out a little bit
Here's some background info:
My excel file for reference:
https://www.dropbox.com/s/d2v4up2txuvm1fz/1 - Analyseresultatskema Farveplan VBA TEST 3.xlsm?dl=0
The sheet "Analyseresultatskema" is used to visually evaluate test sample results.
Column G contains test sample ID and the following columns H:AH contain testresults for individual substances.
I have written a macro to color the cells in range (H:AH) green / yellow / red, based on criterias for each substance in another sheet "Grænseværdier til formatering" contained in the file.
With some help from a friendly forum member in here, I was also able to summarize a colorcode for the G-column.
(Thank you, LiveToExcel!)
Now, on to my problem:
What I'm having a bit of trouble with, is the column AI in the "Analyseresultater" sheet.
I'm using the column to concatenate results for all substances in a single cell roughly as folows:
<tbody>
</tbody>
So far I've been using a formula in excel - something along the lines of:
=CONCATENATE(IF(IS.EMPTY(AC2);"";"Substance 1: " & AC2 & " mg/kg" & CHR(10)); IF(IS.EMPTY(AD2) .... and so on.
As you can see, the formula skips blank cells and I manually define a range of columns (which substances) that I want to be concatenated by going through them 1 by 1, since I basically only want certain substances to be concatenated.
The columns (substances) that I want to be concatenated are a static range. For instance, I'm skipping columns S:Z in my full concatenate formula in the sheet.
The above approach works well to a certain point, but the problem is that due to a big number og substances in the sheet, the number of arguments in the cell starts to exceed the limit of possible arguments in a cell (I assume).
So after a certain number of arguments (concatenated substances), excel stops showing the concatenated string and just displays "######" in the cell.
So my question is: Can this concatenation be done in <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">vba</acronym> to avoid the argument limitations in a cell?
Any help or pointers in the right direction would be greatly apreciated!
Hope someone can help me out a little bit
Here's some background info:
My excel file for reference:
https://www.dropbox.com/s/d2v4up2txuvm1fz/1 - Analyseresultatskema Farveplan VBA TEST 3.xlsm?dl=0
The sheet "Analyseresultatskema" is used to visually evaluate test sample results.
Column G contains test sample ID and the following columns H:AH contain testresults for individual substances.
I have written a macro to color the cells in range (H:AH) green / yellow / red, based on criterias for each substance in another sheet "Grænseværdier til formatering" contained in the file.
With some help from a friendly forum member in here, I was also able to summarize a colorcode for the G-column.
(Thank you, LiveToExcel!)
Now, on to my problem:
What I'm having a bit of trouble with, is the column AI in the "Analyseresultater" sheet.
I'm using the column to concatenate results for all substances in a single cell roughly as folows:
Column AC | Column AD | Column AE | Column AF | Column AG | Column AH | Column AI |
Substance 1 | Substance 2 | Substance 3 | Substance 4 | Substance 5 | Substance 6 | Concatenated result |
10 | 20 | 30 | 0,5 | Detected | Not Detected | Substance 1: 10 mg/kg Substance 2: 20 mg/kg Substance 3: 30 mg/kg Substance 5: Detected Substance 6: Not detected |
500 | 10 | 1 | Substance 1: 500 mg/kg Substance 3: 10 mg/kg | |||
Not detected | Detected | Substance 5: Not detected Substance 6: Detected |
<tbody>
</tbody>
So far I've been using a formula in excel - something along the lines of:
=CONCATENATE(IF(IS.EMPTY(AC2);"";"Substance 1: " & AC2 & " mg/kg" & CHR(10)); IF(IS.EMPTY(AD2) .... and so on.
As you can see, the formula skips blank cells and I manually define a range of columns (which substances) that I want to be concatenated by going through them 1 by 1, since I basically only want certain substances to be concatenated.
The columns (substances) that I want to be concatenated are a static range. For instance, I'm skipping columns S:Z in my full concatenate formula in the sheet.
The above approach works well to a certain point, but the problem is that due to a big number og substances in the sheet, the number of arguments in the cell starts to exceed the limit of possible arguments in a cell (I assume).
So after a certain number of arguments (concatenated substances), excel stops showing the concatenated string and just displays "######" in the cell.
So my question is: Can this concatenation be done in <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">vba</acronym> to avoid the argument limitations in a cell?
Any help or pointers in the right direction would be greatly apreciated!