|
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 |
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 |
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 |
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 |
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 |
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 |
| All times are GMT +1. The time now is 04:21 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com