userform check if value is already in sheet

[AnnetteTR]

Hi
I have created a userform with a lot of textboxes and comboboxes.
The userform is used to populate a worksheet with information.

When the user clicks the "Save" button all data is saved in the next empty row.
Before data is saved I want to check if the value in textbox1 is already there - and have the rownumber in return.

Something like

dim lIsInRow as Long
 
If textbox1.value is already in column B then  
   msgBox ("The value is already in row " &lIsInRow)

can anyone please tell me the syntax.

Regards Annette

 

[GTO]

Check if the value is where? Anyplace in Col B, or just in the last (lowermost) cell with data?

 

[AnnetteTR]

It is anyplace in Col B

/Annette

 

[GTO]

Hi Annette,

As by description, your code already finds the last row with data, then you could 'search' the range with either the .Find method, or, you could use MATCH() something like:

If Not IsError(Application.Match(TheValue, CurrentRangeOfExistingVals, 0)) Then
    'stop processing, warn user...
End If

 

[AnnetteTR]

Hi Gto
I tried your suggestion, but when I run the code it is never used.
Neither if the value is there nor if it is not there.

Inspired by your suggestion I tried this

     lIsInRow = Application.Match(txtRumnr.Text, rumnummer, False)
 
   MsgBox ("rummet er allerede i rumnummer " & lIsInRow)

But I get an error "Type MisMatch" in the msgbox line - so I dont know if it can be used.

Then I tried

lIsInRow = Cells.Find(What:=txtRumnr.Text, After:=[B1], _ 
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

This returns the row number when the text is in the column.
But it returns an error "Run-time error '91':
Object variable or With block variable not set"
if the text is not in the column.

So now I am a bit stuck - hope you can help me out.

Regards Annette

 

[GTO]

Hi Gto
I tried your suggestion, but when I run the code it is never used.
Neither if the value is there nor if it is not there.

Inspired by your suggestion I tried this

     lIsInRow = Application.Match(txtRumnr.Text, rumnummer, False)
 
   MsgBox ("rummet er allerede i rumnummer " & lIsInRow)

But I get an error "Type MisMatch" in the msgbox line - so I dont know if it can be used.

I did not check, but I do not believe you can use FALSE for the last arg in MATCH. I am assuming that 'rumnummer' is a Range.

Then I tried

lIsInRow = Cells.Find(What:=txtRumnr.Text, After:=[B1], _ 
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

This returns the row number when the text is in the column.
But it returns an error "Run-time error '91':
Object variable or With block variable not set"
if the text is not in the column...

IMO, unless you are absoulutely sure to succeed in finding what is sought, its best to first attempt to Set to the range (cell) returned by .Find. That way you can test to see if range Is Nothing, then you know nothing was found and can handle as appropriate.

For Application.Match, try this as a test:

In a blank/new wb, setup test:

Excel Workbook
	A	B
1		HEADER
2		Candy
3		Daisy
4		Craig
5		Prudence
6		Mark
7		Annie
8		Bill
9		Frank
10		Chester
11
Sheet1

Excel 2003

In a Standard Module:

Option Explicit
    
Sub exa()
Dim rngColB As Range
Dim strSearchTerm As String
    With Sheet1
        Set rngColB = Range(.Range("B2"), .Cells(.Rows.Count, "B").End(xlUp))
        
        strSearchTerm = InputBox("Enter data that might match...", "")
        
        If Not IsError(Application.Match(strSearchTerm, rngColB, 0)) Then
            MsgBox "Val already addded", vbInformation, vbNullString
        Else
            .Cells(.Rows.Count, 2).End(xlUp).Offset(1).Value = strSearchTerm
        End If
    End With
End Sub

Does that help?

Mark

 

[GTO]

Oops Sorry Annette, I should have mentioned that 'Sheet1' is the default CodeName of the sheet, change to suit...

BTW, a total guess, is that French?

Thank you,

Mark

 

[AnnetteTR]

Hi Mark

No it is not French it is Danish.

Yes! - Your little test works as I want it to.
Unfortunately I have to do something else now - but I will try to use it in my userform tomorrow.

Thank you
Annette

 

[GTO]

Hi Mark

No it is not French it is Danish.

I figured I was about to embarrass myself... Glad its working

 

[AnnetteTR]

Hi Mark
I have incoorporated it in my macro, and made a double condition, and it works - Great!!
Thank you for your help.
Annette