| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Remove #VALUE! from ConCat Range Function
How do I remove the #VALUE! from my output file when running the function
below. Some cells in my input file have no data, and I need the output file to reflect this rather than displaying a #VALUE! error. Function ConCatRange(CellBlock As Range) As String Dim Cell As Range Dim sbuf As String For Each Cell In CellBlock If Len(Cell.Text) 0 Then sbuf = sbuf & Cell.Text & Chr(166) sbuf = sbuf & Cell.Text & "|" End If Next ConCatRange = Left(sbuf, Len(sbuf) - 1) End Function |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Remove #VALUE! from ConCat Range Function
I posted this in the Excel user group
See J.E's function http://www.mcgimpsey.com/excel/udfs/multicat.html -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Excel Helps" wrote in message ... How do I remove the #VALUE! from my output file when running the function below. Some cells in my input file have no data, and I need the output file to reflect this rather than displaying a #VALUE! error. Function ConCatRange(CellBlock As Range) As String Dim Cell As Range Dim sbuf As String For Each Cell In CellBlock If Len(Cell.Text) 0 Then sbuf = sbuf & Cell.Text & Chr(166) sbuf = sbuf & Cell.Text & "|" End If Next ConCatRange = Left(sbuf, Len(sbuf) - 1) End Function |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Remove #VALUE! from ConCat Range Function
Thanks Ron
But I can't fathom out what I need to remove the #VALUE! error from your link. I'm hoping that Roger may pick up this thread as he forwarded me the piece of code which works fine for my needs apart from the #VALUE! error. My limited experience with VB code is not sufficient to alter the code. "Ron de Bruin" wrote: I posted this in the Excel user group See J.E's function http://www.mcgimpsey.com/excel/udfs/multicat.html -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Excel Helps" wrote in message ... How do I remove the #VALUE! from my output file when running the function below. Some cells in my input file have no data, and I need the output file to reflect this rather than displaying a #VALUE! error. Function ConCatRange(CellBlock As Range) As String Dim Cell As Range Dim sbuf As String For Each Cell In CellBlock If Len(Cell.Text) 0 Then sbuf = sbuf & Cell.Text & Chr(166) sbuf = sbuf & Cell.Text & "|" End If Next ConCatRange = Left(sbuf, Len(sbuf) - 1) End Function |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Remove #VALUE! from ConCat Range Function
There are no errors when you use the function from J.E when there are empty cells in the range
-- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Excel Helps" wrote in message ... Thanks Ron But I can't fathom out what I need to remove the #VALUE! error from your link. I'm hoping that Roger may pick up this thread as he forwarded me the piece of code which works fine for my needs apart from the #VALUE! error. My limited experience with VB code is not sufficient to alter the code. "Ron de Bruin" wrote: I posted this in the Excel user group See J.E's function http://www.mcgimpsey.com/excel/udfs/multicat.html -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Excel Helps" wrote in message ... How do I remove the #VALUE! from my output file when running the function below. Some cells in my input file have no data, and I need the output file to reflect this rather than displaying a #VALUE! error. Function ConCatRange(CellBlock As Range) As String Dim Cell As Range Dim sbuf As String For Each Cell In CellBlock If Len(Cell.Text) 0 Then sbuf = sbuf & Cell.Text & Chr(166) sbuf = sbuf & Cell.Text & "|" End If Next ConCatRange = Left(sbuf, Len(sbuf) - 1) End Function |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Remove #VALUE! from ConCat Range Function
Sorry I not read it good
Roger posted a suggestion for you in the Excel user group -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... There are no errors when you use the function from J.E when there are empty cells in the range -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Excel Helps" wrote in message ... Thanks Ron But I can't fathom out what I need to remove the #VALUE! error from your link. I'm hoping that Roger may pick up this thread as he forwarded me the piece of code which works fine for my needs apart from the #VALUE! error. My limited experience with VB code is not sufficient to alter the code. "Ron de Bruin" wrote: I posted this in the Excel user group See J.E's function http://www.mcgimpsey.com/excel/udfs/multicat.html -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Excel Helps" wrote in message ... How do I remove the #VALUE! from my output file when running the function below. Some cells in my input file have no data, and I need the output file to reflect this rather than displaying a #VALUE! error. Function ConCatRange(CellBlock As Range) As String Dim Cell As Range Dim sbuf As String For Each Cell In CellBlock If Len(Cell.Text) 0 Then sbuf = sbuf & Cell.Text & Chr(166) sbuf = sbuf & Cell.Text & "|" End If Next ConCatRange = Left(sbuf, Len(sbuf) - 1) End Function |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Remove #VALUE! from ConCat Range Function
Thanks,
I'll take a look. Your link to your site is very interesting for other Excel Stuff! Thanks "Ron de Bruin" wrote: Sorry I not read it good Roger posted a suggestion for you in the Excel user group -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Ron de Bruin" wrote in message ... There are no errors when you use the function from J.E when there are empty cells in the range -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Excel Helps" wrote in message ... Thanks Ron But I can't fathom out what I need to remove the #VALUE! error from your link. I'm hoping that Roger may pick up this thread as he forwarded me the piece of code which works fine for my needs apart from the #VALUE! error. My limited experience with VB code is not sufficient to alter the code. "Ron de Bruin" wrote: I posted this in the Excel user group See J.E's function http://www.mcgimpsey.com/excel/udfs/multicat.html -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Excel Helps" wrote in message ... How do I remove the #VALUE! from my output file when running the function below. Some cells in my input file have no data, and I need the output file to reflect this rather than displaying a #VALUE! error. Function ConCatRange(CellBlock As Range) As String Dim Cell As Range Dim sbuf As String For Each Cell In CellBlock If Len(Cell.Text) 0 Then sbuf = sbuf & Cell.Text & Chr(166) sbuf = sbuf & Cell.Text & "|" End If Next ConCatRange = Left(sbuf, Len(sbuf) - 1) End Function |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| howto: concat (x1:x3) | New Users to Excel | |||
| Concat Macro help... | Excel Discussion (Misc queries) | |||
| Remove blank cells from a range | Excel Discussion (Misc queries) | |||
| First letter of names, mi and last name concat, How? | Excel Discussion (Misc queries) | |||
| Can I remove blanks from a range without using sort? | Excel Discussion (Misc queries) | |||
Linear Mode
