使用用户定义的范围作为单元格解析的输入 [英] Use User-defined range as input for cell parsing

问题描述

我正在Excel 2010中编写一个宏，以便删除列的多个单元格中的换行符。这个单元格需要由用户选择。遵循以前的 post 我能够创建一个InputBox来让用户选择范围，但现在我无法处理选择中的数据。

我以前的代码没有选择范围用正则表达式解析整个列，以在单元格中的字符串中查找模式，并更改其内容。
我这样做了一个对于我到Rows.Count 这样的代码块：

 对于i = 1 To Rows.Count 
 
如果不是IsEmpty（Cells（i，5）.Value）然后
 varString = Sheets（ActiveSheet.Name） .Cells（i，5）.Text 
 Sheets（ActiveSheet.Name）.Cells（i，5）.Value = objRegExp.Replace（varString，$ 1）
 End If 
 Next i 
  

现在我要替换静态列，所以我只能处理用户范围。
为了达到这个目的，我尝试过：

  Set selection = Application.InputBox（Prompt：= _ 
请选择一个范围来应用删除中断线程序，_ 
标题：=删除换行符，类型：= 8）
 
如果选择为Nothing Then 
退出子
结束如果
 
设置RowsNumber = selection.CurrentRegion  - >这行给我一个错误：需要对象
 Set RowsNumber = RowsNumber.Rows.Count 
 
 For i = 1 To RowsNumber 
如果不是IsEmpty（Cells（i，5 ）.Value）然后
 varString = Sheets（ActiveSheet.Name）.Cells（i，5）.Text 
表（ActiveSheet.Name）.Cells（i，5）.Value = objRegExp.Replace（ varString，$ 1）'用同一行中的正则表达式替换pattern 
 End If 
 Next i 
  

如何访问InputBox返回的范围内的单元格？

我也尝试更改 RowsNumber 与 selection.Rows.Count 但是这样，虽然它没有给出错误，但是当我运行调试器时，使用的单元格中有空白字符串。我认为这是因为我尝试访问行= 5，当范围可以更少，即3如果用户只是选择3个单元格。

我尝试了一个为每个下一个循环，但再次，我不知道如何使用选择范围访问单元格。

解决方案

您可以使用 For Each 循环遍历范围的单元格。

以下是您的代码修改。我将变量名称 Selection 更改为 rng ，因为 Selection 是Excel库内置函数，应该避免这个名字。

  Sub x（）
 Dim rng作为Excel.Range 
 Dim cell As Excel.Range 
 
 
设置rng = Application.InputBox（提示：= _ 
请选择一个范围来应用删除破解线程序，_ 
标题：=删除换行符，类型：= 8）
 
如果rng不是，然后
退出Sub 
结束If 
 
对于每个单元格在rng.Cells 
如果不是IsEmpty（cell.Value）然后
 varString = cell.Text 
 cell.Value = objRegExp.Replace（varString ，$ 1）'在同一行中用正则表达式替换模式
 End If 
下一个单元格
 
 End Sub 
  / pre> 
I'm writing a macro in Excel 2010 in order to remove line breaks in multiple cells of a column. This cells need to be selected by the user. Following this previous post I was able to create an InputBox to let the user select the range but now, I am unable to process the data within the selection.

My previous code without the selection range parsed an entire column with a regexp to find a pattern in the string within the cells and change its contents.
I did this with a For i To Rows.Count block of code like this:
For i = 1 To Rows.Count

    If Not IsEmpty(Cells(i, 5).Value) Then
       varString = Sheets(ActiveSheet.Name).Cells(i, 5).Text
       Sheets(ActiveSheet.Name).Cells(i,5).Value=objRegExp.Replace(varString, "$1 ")
    End If
Next i
Now I want to replace the static column so I can process only the user range.
In order to achieve that I tried this: 
Set selection = Application.InputBox(Prompt:= _
            "Please select a range to apply the remove break lines procedure.", _
                Title:="Remove Line Breaks", Type:=8)

If selection Is Nothing Then
    Exit Sub
End If

Set RowsNumber = selection.CurrentRegion -> This line gives me an error: "Object required"
Set RowsNumber = RowsNumber.Rows.Count

For i = 1 To RowsNumber
    If Not IsEmpty(Cells(i, 5).Value) Then 
       varString = Sheets(ActiveSheet.Name).Cells(i, 5).Text 
       Sheets(ActiveSheet.Name).Cells(i, 5).Value = objRegExp.Replace(varString, "$1 ") 'Replace pattern found with regular expression in the same line
    End If
Next i
How can I access the cells in the range returned by the InputBox?

I also tried changing RowsNumber with selection.Rows.Count but that way, although it doesn't gives an error, the cells used have blank string within them when I run the debugger. I think this is because I try to access row = 5 when the range could be less, i.e 3 if user just selects 3 cells.

I tried a For Each Next loop but then again, I know not how to access the cells withing the selection range.
解决方案
You can iterate through the cells of a range by using For Each loop.

Below is your code modified. I have changed the name of variable Selection to rng, because Selection is Excel library built-in function and this name should be avoided.
Sub x()
    Dim rng As Excel.Range
    Dim cell As Excel.Range


    Set rng = Application.InputBox(Prompt:= _
                "Please select a range to apply the remove break lines procedure.", _
                    Title:="Remove Line Breaks", Type:=8)

    If rng Is Nothing Then
        Exit Sub
    End If

    For Each cell In rng.Cells
        If Not IsEmpty(cell.Value) Then
           varString = cell.Text
           cell.Value = objRegExp.Replace(varString, "$1 ") 'Replace pattern found with regular expression in the same line
        End If
    Next cell

End Sub


                        
这篇关于使用用户定义的范围作为单元格解析的输入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！