Exact values needed in InputBox?

[deeppurple247]

I have a problem with the code shown bellow. When I click a command button an InputBox pops up asking me to enter a set of initials. When I enter the initials and click OK all of the rows with the initials in column 'H' are transfered to another worksheet. However if I was searching for say the initials 'AP' and I only entered 'A' and clicked OK, it will still transfer all the corresponding data with an 'A' in column 'H'.

In other words I want the data that is entered into the inputbox to be exact or nothing gets transfered.

Please Login or Register  to view this content.

Any ideas on this?

Alex

[Bryan Hessey]

I have a problem with the code shown bellow. When I click a command button an InputBox pops up asking me to enter a set of initials. When I enter the initials and click OK all of the rows with the initials in column 'H' are transfered to another worksheet. However if I was searching for say the initials 'AP' and I only entered 'A' and clicked OK, it will still transfer all the corresponding data with an 'A' in column 'H'.

In other words I want the data that is entered into the inputbox to be exact or nothing gets transfered.

Please Login or Register  to view this content.

Any ideas on this?

Alex

Hi,

try xlWhole in place of xlPart in the LookAt

hth
---

[MSP77079]

Two ideas:
1. change
Set rngC = .Find(what:=strToFind, LookAt:=xlPart)
to
Set rngC = .Find(what:=strToFind, LookAt:=xlWhole)

2. iterate over each cell in the range. It won't take as long as you might think. VB can READ from Excel about 50,000 times faster than it can WRITE to Excel. In other words, in the time it takes to change the value in a single cell, it can "check" the contents of 50,000 cells.

[MSP77079]

Bryan, you are just one step ahead of me this evening.

[deeppurple247]

Hi,

That's brilliant, thanks. The last thing is that if I put in a set of initials that don't exist or just one initial the message ("All relevant data has been moved to the transfer sheet") still pops up.

I would prefer that it only pops up if the exact initials are entered.

Thanks

Alex

[Bryan Hessey]

Hi,

That's brilliant, thanks. The last thing is that if I put in a set of initials that don't exist or just one initial the message ("All relevant data has been moved to the transfer sheet") still pops up.

I would prefer that it only pops up if the exact initials are entered.

Thanks

Alex

Hi,

probably easiest is to Dim a counter and set that to zero, then for each row written +1 to the counter.

At the msgbox, if counter = zero then 'nothing found',
else if =1 'single find'
else current 'all relevant'

hth
---

[deeppurple247]

Hi,

Thanks, but I'm pretty dense about all this code stuff. How would I apply that to my code exactly?

Alex

[Bryan Hessey]

Hi,

Thanks, but I'm pretty dense about all this code stuff. How would I apply that to my code exactly?

Alex

put a

Dim myCount as long
myCount = 0

at the top near your other dim statements


Where you write the lines

rngC.EntireRow.Copy wSht.Cells(strLastRow, 1)

add a line

myCount = myCount +1

then before the msgbox

If myCount = 0 then
msgbox "Nothing found"
else
your current msgbox
End If

-----------------

[deeppurple247]

Hi,

That's Great. Thanks for your help

Alex

[Bryan Hessey]

Hi,

That's Great. Thanks for your help

Alex

np - good to see it helped, and thanks for your response.

---