Find data only specified Range ??

[bdsii]

I have searched high and low and it seems the code I am using should work to find data only a specified range for a value but it is not working as I thought it would. :dunno

I created a sample of what I am working on for demonstration purposes. I am attaching the sample for review.

The purpose of this is to enter completed Transaction Info into a Userform and the Userform will enter text and a date into a spreadsheet.

I want to set the Range for searching because there could be cases where the Transaction Number may conflict with data elsewhere in the spreadsheet. Once the data has been found, the code should enter "YES" into the Completed column and the completed date into the column next to it. If the Transaction Number is not found it exits the sub.

I am unable to get the Find to limit itself to only the range I specified (A1 to the total rows in the data). I am also getting an error when it checks to verify the data entered into the userform matches the Customer Number and the Account Number data on the spreadsheet. This helps prevent errors.

I am not certain where the End With statement should go which may be causing some of my problems.

Use Transaction number 77777 to edit as a test because that number is no where else on the spreadsheet. Then use Transaction Number 88888 or 99999 because those Transaction numbers are also Customer Numbers and Account Numbers. This tests to verify the search is only in Column A.

I am hoping this will also help other newbies in the future.

Thoughts and advice would be appreciated ! :yes

[Simon Lloyd]

I've not looked at your example yet or the code but to restrict find to a range: ActiveSheet.Range("A1:A" & ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row).Find(What:=....etcor to a column
ActiveSheet.Columns(1).Find(What:=.....etc

[bdsii]

This is the code to specify the range I used. I think it should work but it doesn't ????



Dim totalrows As Long
totalrows = 0
totalrows = ActiveSheet.UsedRange.Rows.Count


With Sheets("ThisOne").Range("A1:A" & totalrows)

Set transloc = Cells.Find(What:=Trans, After:=Range("A1"), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

[lucas]

is Trans a variable?

[bdsii]

Yes....Trans is a variable that is used on the userform. It is the Transaction Number. Did I use it correctly ?

[bdsii]

anyone have an idea ?

[lucas]

I was just looking at this. I'm not sure if it will help but try my change to your userform.

Load the userform and start typing 7's into the transaction textbox(trans).

See if this will help determine if the number is already there......

It's early so I may have missed the objective...

[GTO]

Hi Steve,

Hope plenty of wood cut and all are warm/toasty :-)

Greetings bdsii,

Had a better/more complete answer, but something keeps timing out and its late. Just reference #3:
With Sheets("ThisOne").Range("A1:A" & totalrows)
Set transloc = .Find(What:=Trans, After:=Sheets("ThisOne").Range("A1"), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)

...You want to drop the unqualified Cells in order to get the .Find referenced to the range delineated in the With line. Also note that the After:= arg should be qualified.

A good day to all,

Mark

[bdsii]

Lucas - My response to you timed out. Yep, it does show that the Transaction is already there. That code seems to be pretty handy and I think I am gonna keep that code around to use later in other apps :-) It does provide a workaround. I just couldn't figure out how to limit the search or Find range.

GTO provided the code I needed to limit the search by removing the Cells portion of the .Find statement. Thanks GTO ! Your info about the After statement needing to be qualified has me stumped. I used your code which calls the sheet name and it worked and I then took out the sheet name portion from the After statement and it worked as well. I didn't realize it required the sheet name since it would only search the sheet called earlier, right ?

After changing the code as GTO's noted, I am still getting an error when it checks to verify the data entered into the userform matches the Customer Number and the Account Number data on the spreadsheet. This helps prevent errors. It will now find the correct entry in Column A but for some reason it thinks that "77777" entered into the userform is different than the "77777" entered into the spreadsheet and is triggering the code to react as if they do not match.

Any ideas on that one ?

Thanks so much guys !

[bdsii]

Any ideas on the second part of this problem that is not resolved ? I cannot figure that part out.

thanks!