搜索整个Excell列以获取不总是存在的值，如果不存在，则创建一个新值(Search Entire Excell Column for a value that is not always present, if not present create a new value)

我一直试图找到一些帮助，阅读我正在为更大的项目编写的代码。 我正在寻找一个excel列中的字符串。 该字符串可能并不总是出现在列中，在这种情况下，我需要能够创建一个新的字符串。

我创建了一个小宏来测试它，目前我在A列中有1:20的数字。代码正在搜索“37”，并且不应该能够找到它。 如果找到该值，则显示一个msg框以确认其已被找到，或者如果找不到它，则显示一个msg框以表示未找到该信息。 如果我正在搜索“2”，则代码完美无缺。 当值不存在且“LastPLocation”没有值时，我遇到了问题。最后，我需要创建一个变量并为其赋值，例如。 一个字符串，表示“不存在”或数字“0”

变量名称取自主项目，因此您可以更改它们。

Sub test() Dim LastPLocation As String Dim NewLastPLocation as String LastPLocation = Range("A:A").Find(what:="37", after:=Range("A1"), searchdirection:=xlPrevious).Row If LastPLocation Is Nothing Then MsgBox ("No Last P") NewLastPLocation = 0 Else MsgBox (LastPLocation) NewLastPLocation = LastPLocation + 1 MsgBox (NewLastPLocation) End If End Sub

感谢您提供的任何帮助。

i have been trying to find some help reading a spinet of code i am writing for a larger project. I am looking to identify a string in a column of excel. This string may not always be present in the column, in that case i need to be able to create a new string.

I have created a small macro to test this, currently I have the number 1:20 in column A. The code is searching for "37", and shouldn't be able to find it. if the value is found display a msg box to confirm its been found or if it isn't found displays a msg box to say its not been found. If I was searching for "2" the Code works perfectly. I run into issues when the values is not present and "LastPLocation" has no value Eventually I will need to create a variable and assign it a value eg. a string which says "Not present" or a number "0"

The variables names are taken from the main project, so you can change them.

Sub test() Dim LastPLocation As String Dim NewLastPLocation as String LastPLocation = Range("A:A").Find(what:="37", after:=Range("A1"), searchdirection:=xlPrevious).Row If LastPLocation Is Nothing Then MsgBox ("No Last P") NewLastPLocation = 0 Else MsgBox (LastPLocation) NewLastPLocation = LastPLocation + 1 MsgBox (NewLastPLocation) End If End Sub

Thank you for any help that you are able to give.

最满意答案

不要将lastPLocation设置为String ，而是使用Range ：

Sub test() Dim LastPLocation As Range Dim NewLastPLocation As String Set LastPLocation = Range("A:A").Find(what:="37", after:=Range("A1"), searchdirection:=xlPrevious) If LastPLocation Is Nothing Then MsgBox ("No Last P") ' Do things here when the value is NOT found NewLastPLocation = 0 Else ' Do things here when the value IS found MsgBox (LastPLocation.Row) NewLastPLocation = LastPLocation.Row + 1 End If MsgBox (NewLastPLocation) End Sub

Instead of setting the lastPLocation as a String, use Range:

Sub test() Dim LastPLocation As Range Dim NewLastPLocation As String Set LastPLocation = Range("A:A").Find(what:="37", after:=Range("A1"), searchdirection:=xlPrevious) If LastPLocation Is Nothing Then MsgBox ("No Last P") ' Do things here when the value is NOT found NewLastPLocation = 0 Else ' Do things here when the value IS found MsgBox (LastPLocation.Row) NewLastPLocation = LastPLocation.Row + 1 End If MsgBox (NewLastPLocation) End Sub

更多推荐