[已解决]为什么后面多了个逗号？

lingyuncelia1

Function mymlookup(myword As Range, mybook As Range, mycol As Integer)
Dim mysht As Worksheet
Set mysht = ActiveSheet
maxrow = mysht.UsedRange.Rows.Count
For i = 1 To maxrow
If myword.Value Like "*" & mybook.Cells(i, 1) & "*" Then
myresult = myresult & "," & mybook.Cells(i, mycol).Value
End If
Next
mymlookup = myresult
End Function

自己写了个一对多模糊查找自定义函数，查找结果F1前面的逗号我是明白的，但不明白为什么最后也有个逗号，输出结果不是应该：

,5880205011010200258,80010000859312986

？为什么后面多了个逗号？

最佳答案

月排行榜 / 总排行榜

zjdh

2016-11-6 12:53

试试删除第4行整行。说明该行中有不可见数据。

跳转到最佳答案楼层

苏子龙

Function mymlookup(myword As Range, mybook As Range, mycol As Integer)
Dim mysht As Worksheet
Set mysht = ActiveSheet
maxrow = mysht.UsedRange.Rows.Count
For i = 1 To maxrow
If myword.Value Like "*" & mybook.Cells(i, 1) & "*" And mybook.Cells(i, 1) <> "" Then
myresult = myresult & "," & mybook.Cells(i, mycol).Value
End If
Next
mymlookup = Mid(myresult, 2)
End Function
maxrow是4，有空白单元格，另自定义函数不要这样写的好

zjdh

试试删除第4行整行。说明该行中有不可见数据。

fjmxwrs

maxrow的值运行到空行了，空单元格是会被找到的，所以最后又多了一个逗号

fjmxwrs

1. Function mymlookup(myword As Range, mybook As Range, mycol As Integer)
   
2. Dim mysht As Worksheet
   
3. Set mysht = ActiveSheet
   
4. maxrow = mysht.UsedRange.Rows.Count
   
5. For i = 1 To maxrow
   
6. If myword.Value Like "*" & mybook.Cells(i, 1) & "*" And mybook.Cells(i, 1) <> "" Then
   
7. myresult = myresult & "," & mybook.Cells(i, mycol).Value
   
8. End If
   
9. Next
   
10. mymlookup = Mid(myresult, 2, Len(myresult))
    
11. End Function

复制代码

lingyuncelia1

苏子龙 发表于 2016-11-6 12:53
Function mymlookup(myword As Range, mybook As Range, mycol As Integer)
Dim mysht As Worksheet
Set  ...

maxrow 明明是3啊，怎么会是4呢？

lingyuncelia1

zjdh 发表于 2016-11-6 12:53
试试删除第4行整行。说明该行中有不可见数据。

成功了，居然真有不可见数据，用Delete键也无法清楚，怎么会如此的？

lingyuncelia1

zjdh 发表于 2016-11-6 12:53
试试删除第4行整行。说明该行中有不可见数据。

连用LEN函数也测不出有东西在