Format cells according to the Concatenated number of cells.

[S.H.A.D.O.]

Good afternoon,

I have a macro that outputs number data into columns "A:G", just as a General format.

Now there could be data in all of the columns, or 3 of the columns, or 4 of the columns etc

I have a line of code that concatenates these columns...

    Range("I1:I" & Range("A" & Rows.Count).End(xlUp).Row).Formula = "=A1&B1&C1&D1&E1&F1&G1"

...which works perfectly.

At the moment, further down in the Macro I have a line of code...

    Range("I1:I" & Range("A" & Rows.Count).End(xlUp).Row).NumberFormat = "000"

...that formats the Concatenated cells according to the number of columns that has data. In this case the code above would format it as 3, being "000", meaning there are 3 columns with data in. This has to be changed manually within the code each time according to the number of columns used.

My question is, is there a way that I can amend the first line of code I have posted to accomodate this as the data is output from the Macro please. So for 4 columns it automatically formats the Concatenated cells as "0000", for 5 columns it automatically formats the Concatenated cells as "00000" etc So basically it is only formated according to the number of columns used and not for columns that have no data.

Thanks in advance.

 

[S.H.A.D.O.]

Hi again,

I have tried a variation of...

TRIM(SUBSTITUTE(SUBSTITUTE(TRIM(

SUBSTITUTE(SUBSTITUTE(TRIM(

SUBSTITUTE(TRIM(CONCATENATE(

CONCATENATE(TEXT(

...but to no avail.

Thanks in advance.

 

[S.H.A.D.O.]

I have tried...

=IF(A1="","",A1&IF(B1="","",B1&IF(C1="","",C1&IF(D1="","",D1&IF(E1="","",E1&IF(F1="","",F1&IF(G1="","",G1)))))))

...but how can I get it to only format cells that are not blank and be as long as the number of columns used.

Thanks in advance.

 

[S.H.A.D.O.]

Hi,

I have sort of got this working with...

Range("I1:I" & Range("A" & Rows.Count).End(xlUp).Row).Formula = _
    "=IF(A1="""","""",TEXT(A1,""0"")&IF(B1="""","""",TEXT(B1,""0"")&IF(C1="""","""",TEXT(C1,""0"")&IF(D1="""","""",TEXT(D1,""0"")&IF(E1="""","""",TEXT(E1,""0"")&IF(F1="""","""",TEXT(F1,""0"")&IF(G1="""","""",TEXT(G1,""0"")))))))"

...but if...

A1=1
B1=0
C1=0
D1=0
E1=0
F1=6
G1= BLANK

...it gives the answer 000016 instead of 100006.

Any help will be appreciated.

Thanks in advance.

 

[Rick Rothstein]

Let's go back to your first post... I don't understand what the problem is? Can you show us values in some cells on Row 1 where the concatenation formula does not work the way you want (without needing to format the results the way you are trying to do with the "000" part)? Also remember to show us how you want it to look.

 

[S.H.A.D.O.]

I have amended the formula to...

=IF(A1="","",TEXT(A1,"0"))&IF(B1="","",TEXT(B1,"0"))&IF(C1="","",TEXT(C1,"0"))&IF(D1="","",TEXT(D1,"0"))&IF(E1="","",TEXT(E1,"0"))&IF(F1="","",TEXT(F1,"0"))&IF(G1="","",TEXT(G1,"0"))

...which works in the WorkSheet but doesn't work when I apply it in the code. That is also with doubling up on the quotes in the code.

Hi Rick,

If I have...

A1=1
B1=0
C1=0
D1=0
E1=0
F1=6
G1= BLANK

...I want it to show in cell I1 the figure 100006.

Thanks in advance.

 

[mole999]

not enough speech marks

    ActiveCell.FormulaR1C1 = "=IF(R[-1]C[-1]="""","""",TEXT(R[-1]C[-1],""0""))&IF(R[-1]C="""","""",TEXT(R[-1]C,""0""))&IF(R[-1]C[1]="""","""",TEXT(R[-1]C[1],""0""))&IF(R[-1]C[2]="""","""",TEXT(R[-1]C[2],""0""))&IF(R[-1]C[3]="""","""",TEXT(R[-1]C[3],""0""))&IF(R[-1]C[4]="""","""",TEXT(R[-1]C[4],""0""))&IF(R[-1]C[5]="""","""",TEXT(R[-1]C[5],""0""))"

 

[Rick Rothstein]

Hi Rick,

If I have...

A1=1
B1=0
C1=0
D1=0
E1=0
F1=6
G1= BLANK

...I want it to show in cell I1 the figure 100006.

I understand that... and that is what the formula you posted in Message #1 outputs for those values... without needing to format anything. My question to you was for you to show me values in A1:G1 where the output from the formula in Message #1 outputted the wrong value and for you to tell us what the right value should be.

 

[mole999]

could you have a double digit in one or more than one cell

 

[S.H.A.D.O.]

Hi Rick,

I have discovered something.

The concatenated output via the code in message #1 does actually cater for whatever columns have numbers in, regardless of the number of columns with numbers in, and in the correct order, and if there is no number in a cell then it just ignores, it which is perfect.

I have found the problem to be that I have a line of code...

    Range("I1:K" & Range("A" & Rows.Count).End(xlUp).Row).Value = _
            Range("I1:K" & Range("A" & Rows.Count).End(xlUp).Row).Value

...that runs and obviously converts the columns to values as opposed to formulas.

When this above code runs, it drops any leading zero's, so A1=0, B1=0, C1=6 becomes just 6 in cell I1.
I want it to still keep the leading zero's so it should show the answer to be 006. I tried to get round this previously by using the line of code...

Range("I1:I" & Range("A" & Rows.Count).End(xlUp).Row).NumberFormat = "000"

...but this only works if there are only 3 columns with data in, I want it to cater for any columns with data in.

I hope this makes more sense.

Hi mole999,

All digits will be single digits in any of the columns.

Thank you both in advance.