I have a concatenate based on offset array code that I'm using to combine data.
Public Function concatPlusIfs(rng As Range, sep As String, lgCritOffset1 As Long, lgCritOffset2 As Long, varCrit1 As Variant, lgCritOffset3 As Long, lgCritOffset4 As Long, varCrit2 As Variant, Optional noDup As Boolean = False, Optional skipEmpty As Boolean = False) As String
Dim CL As Range, strTemp As String
If noDup Then 'remove duplicates, use collection to avoid them
Dim newRow As New Collection
On Error Resume Next
For Each CL In rng.Cells
If skipEmpty = False Or Len(Trim(CL.Text)) > 0 Then
If CL.Offset(lgCritOffset1, lgCritOffset2) = varCrit1 And CL.Offset(lgCritOffset3, lgCritOffset4) = varCrit2 Then newRow.Add CL.Text, CL.Text
End If
Next
For i = 0 To newRow.Count
strTemp = strTemp & newRow(i) & sep
Next
Else
For Each CL In rng.Cells
If skipEmpty = False Or Len(Trim(CL.Text)) > 0 Then
If CL.Offset(lgCritOffset1, lgCritOffset2) = varCrit1 And CL.Offset(lgCritOffset3, lgCritOffset4) = varCrit2 Then strTemp = strTemp & CL.Text & sep
End If
Next
End If
concatPlusIfs = Left(strTemp, Len(strTemp) - Len(sep))
End Function
The code works great. It's not mine, but I tweaked someone else's code. The problem is that it will sometimes return a small amount of text and other times a large amount of text. I need the rows to autofit height. Before I started using the new concatPlusIfs formula, I used a code on the worksheet to autofit row height, but it cause a weird problem with the above code and only the above code and I can't find any mention of this type of problem. It works fine with all other arrays or non array formulas that I'm using. Basically what happens is that for a fraction of a second I can see the correct output in the cell and then I get #value!. I have no idea what's going on. I've tried autofit rows as a macro instead and it had the same effect. If I manually autofit the row everything is fine, but that's not a viable option.
Does anyone understand what would cause a problem like this? Or How can I fix it?
I'm not using any merged rows anywhere on the sheet.
Here are a few of the autofit strategies I've tried. One as a macro:
Sub AutoFit()
Worksheets("Sheet1").Range("A2:A" & Rows.Count).Rows.AutoFit
End Sub
Also as a code on the sheet,
Private Sub Worksheet_Change(ByVal Target As Range)
Target.EntireRow.AutoFit
End Sub
And,
Private Sub Worksheet_Calculate()
Application.EnableEvents = False
Me.Rows.AutoFit
'or be specific
Me.Rows("1:33").AutoFit
Application.EnableEvents = True
End Sub
Thank you for any help with this.
You most likely get #VALUE! error when your formula tries to convert the ### from the .Text property to value. That is why you should use .Value2 or .Value instead.