Excel VBA - 检查单元格是否包含文本 [英] Excel VBA - Check cell whether it contains piece of text

问题描述

我想检查一段文本的单元格范围。这个文本总是在我的文档中，除了它的单元格是变量（列总是B）。所以我检查一个范围从1:75是否任何单元格包含一段文本，但似乎不起作用。

I want to check a range of cells for a certain piece of text. This text is always in my document, except it's cell is variable (column is always B). So I check a range from 1:75 whether any cells contain a piece of text, but it doesn't seem to work.

Dim FoundRange As Range
Set FoundRange = Cells.Find("5/7 binnen 4h")
Range("I" & EmptyCell + 2).Value = ... (value of cell I on same row as B)

单元我正在寻找总是包含这个文本 Onderhoud 5/7 binnen 4h 但它的位置可以有所不同，这就是为什么我只需要检查它是否包含任何它。当我找到这个单元格，我需要同一行的值。

Cell I'm looking for always contains this text Onderhoud 5/7 binnen 4h but its position can vary, that's why I just need to check whether it contains any of it. When I find that cell, I need the value I on the same row.

欢迎任何建议！

推荐答案

你能不能搜索子串？

Sheet1.Cells.Find("string to find")

将返回包含字符串的范围（或者如果字符串可以

Will return the a range containing the string (or nothing if the string can't be found.

例如

Public Sub Macro1()
Dim FoundRange As Range

Set FoundRange = Sheet1.Cells.Find("5/7 binnen 4h")

' display the cell address to the user
MsgBox FoundRange.Address


' put the found value in column i in the same row as the found text in a known location ($C$1 in this case)
Sheet1.Range("$C$1").Value = Sheet1.Cells(FoundRange.Row, 9).Value

' put the found value in four columns to the right in the same row as the found text in a known location ($C$1 in this case)
Sheet1.Range("$C$2").Value = FoundRange.Offset(0, 4).Value

End Sub

这篇关于Excel VBA - 检查单元格是否包含文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！