I would like to find a way of getting excel to interpret numbers associated
with letters and output a text string. What I want to do is then use this
text string with a conditional format to provide a horizontal colour
sequence. Example below for how the table is required to output the text
string.

I 2 4
Input E 1 2
W 3

Output desired I I E W W W E E I I I
I

In any one column there will only be one number so excel needs to scan the
left most column first and repeat in a row in separate cells the number of
letters quoted in the table for that input. Then scan the second column and
output the letter x times in the next cell along etc.

Do you know if this is possible with Excel?

This macro assumes the input table start in A1.
It continues until it finds 3 empties in a column in the table
The macro stores the output in row 4

Sub liad()
' gsnuxx
For j = 2 To Columns.Count
Set r = Range(Cells(1, j), Cells(3, j))
If Application.WorksheetFunction.CountA(r) = 0 Then
Exit Sub
End If
times = Application.WorksheetFunction.Max(r)
If Not IsEmpty(Cells(1, j)) Then simbol = "I"
If Not IsEmpty(Cells(2, j)) Then simbol = "E"
If Not IsEmpty(Cells(3, j)) Then simbol = "W"

If IsEmpty(Cells(4, 1)) Then
n = 1
Else
n = Cells(4, Columns.Count).End(xlToLeft).Column + 1
End If

For k = 1 To times
Cells(4, k + n - 1).Value = simbol
Next
Next
End Sub
--
Gary''s Student - gsnu200819


"LiAD" wrote:

I would like to find a way of getting excel to interpret numbers associated
with letters and output a text string. What I want to do is then use this
text string with a conditional format to provide a horizontal colour
sequence. Example below for how the table is required to output the text
string.

I 2 4
Input E 1 2
W 3

Output desired I I E W W W E E I I I
I

In any one column there will only be one number so excel needs to scan the
left most column first and repeat in a row in separate cells the number of
letters quoted in the table for that input. Then scan the second column and
output the letter x times in the next cell along etc.

Do you know if this is possible with Excel?

Thanks,

Unfortunately I cannot get this to run at all. I can't get any output from
it. Do i need to do something special to make this run?

Is VB the only way of doing this or is there a formula that would do this?

Thanks
LD

"Gary''s Student" wrote:

This macro assumes the input table start in A1.
It continues until it finds 3 empties in a column in the table
The macro stores the output in row 4

Sub liad()
' gsnuxx
For j = 2 To Columns.Count
Set r = Range(Cells(1, j), Cells(3, j))
If Application.WorksheetFunction.CountA(r) = 0 Then
Exit Sub
End If
times = Application.WorksheetFunction.Max(r)
If Not IsEmpty(Cells(1, j)) Then simbol = "I"
If Not IsEmpty(Cells(2, j)) Then simbol = "E"
If Not IsEmpty(Cells(3, j)) Then simbol = "W"

If IsEmpty(Cells(4, 1)) Then
n = 1
Else
n = Cells(4, Columns.Count).End(xlToLeft).Column + 1
End If

For k = 1 To times
Cells(4, k + n - 1).Value = simbol
Next
Next
End Sub
--
Gary''s Student - gsnu200819


"LiAD" wrote:

I would like to find a way of getting excel to interpret numbers associated
with letters and output a text string. What I want to do is then use this
text string with a conditional format to provide a horizontal colour
sequence. Example below for how the table is required to output the text
string.

I 2 4
Input E 1 2
W 3

Output desired I I E W W W E E I I I
I

In any one column there will only be one number so excel needs to scan the
left most column first and repeat in a row in separate cells the number of
letters quoted in the table for that input. Then scan the second column and
output the letter x times in the next cell along etc.

Do you know if this is possible with Excel?

For j = 2 To Columns.Count - for this line its saying compile error, invalid
outside procedure

I'm afraid VB is past me, so this means nothing for me. I tried doing F5 to
run and it came up with this error.

"LiAD" wrote:

Thanks,

Unfortunately I cannot get this to run at all. I can't get any output from
it. Do i need to do something special to make this run?

Is VB the only way of doing this or is there a formula that would do this?

Thanks
LD

"Gary''s Student" wrote:

This macro assumes the input table start in A1.
It continues until it finds 3 empties in a column in the table
The macro stores the output in row 4

Sub liad()
' gsnuxx
For j = 2 To Columns.Count
Set r = Range(Cells(1, j), Cells(3, j))
If Application.WorksheetFunction.CountA(r) = 0 Then
Exit Sub
End If
times = Application.WorksheetFunction.Max(r)
If Not IsEmpty(Cells(1, j)) Then simbol = "I"
If Not IsEmpty(Cells(2, j)) Then simbol = "E"
If Not IsEmpty(Cells(3, j)) Then simbol = "W"

If IsEmpty(Cells(4, 1)) Then
n = 1
Else
n = Cells(4, Columns.Count).End(xlToLeft).Column + 1
End If

For k = 1 To times
Cells(4, k + n - 1).Value = simbol
Next
Next
End Sub
--
Gary''s Student - gsnu200819


"LiAD" wrote:

I would like to find a way of getting excel to interpret numbers associated
with letters and output a text string. What I want to do is then use this
text string with a conditional format to provide a horizontal colour
sequence. Example below for how the table is required to output the text
string.

I 2 4
Input E 1 2
W 3

Output desired I I E W W W E E I I I
I

In any one column there will only be one number so excel needs to scan the
left most column first and repeat in a row in separate cells the number of
letters quoted in the table for that input. Then scan the second column and
output the letter x times in the next cell along etc.

Do you know if this is possible with Excel?

Worry not! Through persistence we will prevail. Begin by erasing the old macro

Macros are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To use the macro from the normal Excel window:

1. ALT-F8
2. Select the macro
3. Touch Run



To remove the macro:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Just make sure that your input table is located in the proper rows and
columns.
--
Gary''s Student - gsnu200819


"LiAD" wrote:

For j = 2 To Columns.Count - for this line its saying compile error, invalid
outside procedure

I'm afraid VB is past me, so this means nothing for me. I tried doing F5 to
run and it came up with this error.

"LiAD" wrote:

Thanks,

Unfortunately I cannot get this to run at all. I can't get any output from
it. Do i need to do something special to make this run?

Is VB the only way of doing this or is there a formula that would do this?

Thanks
LD

"Gary''s Student" wrote:

This macro assumes the input table start in A1.
It continues until it finds 3 empties in a column in the table
The macro stores the output in row 4

Sub liad()
' gsnuxx
For j = 2 To Columns.Count
Set r = Range(Cells(1, j), Cells(3, j))
If Application.WorksheetFunction.CountA(r) = 0 Then
Exit Sub
End If
times = Application.WorksheetFunction.Max(r)
If Not IsEmpty(Cells(1, j)) Then simbol = "I"
If Not IsEmpty(Cells(2, j)) Then simbol = "E"
If Not IsEmpty(Cells(3, j)) Then simbol = "W"

If IsEmpty(Cells(4, 1)) Then
n = 1
Else
n = Cells(4, Columns.Count).End(xlToLeft).Column + 1
End If

For k = 1 To times
Cells(4, k + n - 1).Value = simbol
Next
Next
End Sub
--
Gary''s Student - gsnu200819


"LiAD" wrote:

I would like to find a way of getting excel to interpret numbers associated
with letters and output a text string. What I want to do is then use this
text string with a conditional format to provide a horizontal colour
sequence. Example below for how the table is required to output the text
string.

I 2 4
Input E 1 2
W 3

Output desired I I E W W W E E I I I
I

In any one column there will only be one number so excel needs to scan the
left most column first and repeat in a row in separate cells the number of
letters quoted in the table for that input. Then scan the second column and
output the letter x times in the next cell along etc.

Do you know if this is possible with Excel?

Sounds simple...but unfortunately it doesn't work for me. Thats actually
what I did when i got the compile error. One thought I have a non english
computer so when i use excel i need to use ; instead of , in formulas. Could
this be a problem here?

"Gary''s Student" wrote:

Worry not! Through persistence we will prevail. Begin by erasing the old macro

Macros are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To use the macro from the normal Excel window:

1. ALT-F8
2. Select the macro
3. Touch Run



To remove the macro:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Just make sure that your input table is located in the proper rows and
columns.
--
Gary''s Student - gsnu200819


"LiAD" wrote:

For j = 2 To Columns.Count - for this line its saying compile error, invalid
outside procedure

I'm afraid VB is past me, so this means nothing for me. I tried doing F5 to
run and it came up with this error.

"LiAD" wrote:

Thanks,

Unfortunately I cannot get this to run at all. I can't get any output from
it. Do i need to do something special to make this run?

Is VB the only way of doing this or is there a formula that would do this?

Thanks
LD

"Gary''s Student" wrote:

This macro assumes the input table start in A1.
It continues until it finds 3 empties in a column in the table
The macro stores the output in row 4

Sub liad()
' gsnuxx
For j = 2 To Columns.Count
Set r = Range(Cells(1, j), Cells(3, j))
If Application.WorksheetFunction.CountA(r) = 0 Then
Exit Sub
End If
times = Application.WorksheetFunction.Max(r)
If Not IsEmpty(Cells(1, j)) Then simbol = "I"
If Not IsEmpty(Cells(2, j)) Then simbol = "E"
If Not IsEmpty(Cells(3, j)) Then simbol = "W"

If IsEmpty(Cells(4, 1)) Then
n = 1
Else
n = Cells(4, Columns.Count).End(xlToLeft).Column + 1
End If

For k = 1 To times
Cells(4, k + n - 1).Value = simbol
Next
Next
End Sub
--
Gary''s Student - gsnu200819


"LiAD" wrote:

I would like to find a way of getting excel to interpret numbers associated
with letters and output a text string. What I want to do is then use this
text string with a conditional format to provide a horizontal colour
sequence. Example below for how the table is required to output the text
string.

I 2 4
Input E 1 2
W 3

Output desired I I E W W W E E I I I
I

In any one column there will only be one number so excel needs to scan the
left most column first and repeat in a row in separate cells the number of
letters quoted in the table for that input. Then scan the second column and
output the letter x times in the next cell along etc.

Do you know if this is possible with Excel?

It might be a problem. I don't know.
--
Gary''s Student - gsnu200819


"LiAD" wrote:

Sounds simple...but unfortunately it doesn't work for me. Thats actually
what I did when i got the compile error. One thought I have a non english
computer so when i use excel i need to use ; instead of , in formulas. Could
this be a problem here?

"Gary''s Student" wrote:

Worry not! Through persistence we will prevail. Begin by erasing the old macro

Macros are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To use the macro from the normal Excel window:

1. ALT-F8
2. Select the macro
3. Touch Run



To remove the macro:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Just make sure that your input table is located in the proper rows and
columns.
--
Gary''s Student - gsnu200819


"LiAD" wrote:

For j = 2 To Columns.Count - for this line its saying compile error, invalid
outside procedure

I'm afraid VB is past me, so this means nothing for me. I tried doing F5 to
run and it came up with this error.

"LiAD" wrote:

Thanks,

Unfortunately I cannot get this to run at all. I can't get any output from
it. Do i need to do something special to make this run?

Is VB the only way of doing this or is there a formula that would do this?

Thanks
LD

"Gary''s Student" wrote:

This macro assumes the input table start in A1.
It continues until it finds 3 empties in a column in the table
The macro stores the output in row 4

Sub liad()
' gsnuxx
For j = 2 To Columns.Count
Set r = Range(Cells(1, j), Cells(3, j))
If Application.WorksheetFunction.CountA(r) = 0 Then
Exit Sub
End If
times = Application.WorksheetFunction.Max(r)
If Not IsEmpty(Cells(1, j)) Then simbol = "I"
If Not IsEmpty(Cells(2, j)) Then simbol = "E"
If Not IsEmpty(Cells(3, j)) Then simbol = "W"

If IsEmpty(Cells(4, 1)) Then
n = 1
Else
n = Cells(4, Columns.Count).End(xlToLeft).Column + 1
End If

For k = 1 To times
Cells(4, k + n - 1).Value = simbol
Next
Next
End Sub
--
Gary''s Student - gsnu200819


"LiAD" wrote:

I would like to find a way of getting excel to interpret numbers associated
with letters and output a text string. What I want to do is then use this
text string with a conditional format to provide a horizontal colour
sequence. Example below for how the table is required to output the text
string.

I 2 4
Input E 1 2
W 3

Output desired I I E W W W E E I I I
I

In any one column there will only be one number so excel needs to scan the
left most column first and repeat in a row in separate cells the number of
letters quoted in the table for that input. Then scan the second column and
output the letter x times in the next cell along etc.

Do you know if this is possible with Excel?

sorry stupid mistake on my part i was copying all the as well as the
routine.

works a lot better now!
thanks for your help

"Gary''s Student" wrote:

It might be a problem. I don't know.
--
Gary''s Student - gsnu200819


"LiAD" wrote:

Sounds simple...but unfortunately it doesn't work for me. Thats actually
what I did when i got the compile error. One thought I have a non english
computer so when i use excel i need to use ; instead of , in formulas. Could
this be a problem here?

"Gary''s Student" wrote:

Worry not! Through persistence we will prevail. Begin by erasing the old macro

Macros are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To use the macro from the normal Excel window:

1. ALT-F8
2. Select the macro
3. Touch Run



To remove the macro:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Just make sure that your input table is located in the proper rows and
columns.
--
Gary''s Student - gsnu200819


"LiAD" wrote:

For j = 2 To Columns.Count - for this line its saying compile error, invalid
outside procedure

I'm afraid VB is past me, so this means nothing for me. I tried doing F5 to
run and it came up with this error.

"LiAD" wrote:

Thanks,

Unfortunately I cannot get this to run at all. I can't get any output from
it. Do i need to do something special to make this run?

Is VB the only way of doing this or is there a formula that would do this?

Thanks
LD

"Gary''s Student" wrote:

This macro assumes the input table start in A1.
It continues until it finds 3 empties in a column in the table
The macro stores the output in row 4

Sub liad()
' gsnuxx
For j = 2 To Columns.Count
Set r = Range(Cells(1, j), Cells(3, j))
If Application.WorksheetFunction.CountA(r) = 0 Then
Exit Sub
End If
times = Application.WorksheetFunction.Max(r)
If Not IsEmpty(Cells(1, j)) Then simbol = "I"
If Not IsEmpty(Cells(2, j)) Then simbol = "E"
If Not IsEmpty(Cells(3, j)) Then simbol = "W"

If IsEmpty(Cells(4, 1)) Then
n = 1
Else
n = Cells(4, Columns.Count).End(xlToLeft).Column + 1
End If

For k = 1 To times
Cells(4, k + n - 1).Value = simbol
Next
Next
End Sub
--
Gary''s Student - gsnu200819


"LiAD" wrote:

I would like to find a way of getting excel to interpret numbers associated
with letters and output a text string. What I want to do is then use this
text string with a conditional format to provide a horizontal colour
sequence. Example below for how the table is required to output the text
string.

I 2 4
Input E 1 2
W 3

Output desired I I E W W W E E I I I
I

In any one column there will only be one number so excel needs to scan the
left most column first and repeat in a row in separate cells the number of
letters quoted in the table for that input. Then scan the second column and
output the letter x times in the next cell along etc.

Do you know if this is possible with Excel?