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.

 
Dim strLastRow As String
Dim rngC As Range
Dim strToFind As Variant, FirstAddress As String
'Dim strToFind As String, FirstAddress As String
Dim wSht As Worksheet
Dim rngtest As String
Application.ScreenUpdating = False

Set wSht = Worksheets("Transfer Sheet")
strToFind = Application.InputBox("Enter Your Initials")
If strToFind = False Or strToFind = "" Then Exit Sub

With ActiveSheet.Range("H2:H5000")
Set rngC = .Find(what:=strToFind, LookAt:=xlPart)
If Not rngC Is Nothing Then
FirstAddress = rngC.Address
Do
strLastRow = Worksheets("Transfer Sheet").Range("A" & Rows.Count).End(xlUp).Row + 1
rngC.EntireRow.Copy wSht.Cells(strLastRow, 1)
Set rngC = .FindNext(rngC)
Loop While Not rngC Is Nothing And rngC.Address <> FirstAddress
End If
End With

MsgBox ("All relevant data has been moved to the transfer sheet ")

End Sub

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.

 
Dim strLastRow As String
Dim rngC As Range
Dim strToFind As Variant, FirstAddress As String
'Dim strToFind As String, FirstAddress As String
Dim wSht As Worksheet
Dim rngtest As String
Application.ScreenUpdating = False

Set wSht = Worksheets("Transfer Sheet")
strToFind = Application.InputBox("Enter Your Initials")
If strToFind = False Or strToFind = "" Then Exit Sub

With ActiveSheet.Range("H2:H5000")
Set rngC = .Find(what:=strToFind, LookAt:=xlPart)
If Not rngC Is Nothing Then
FirstAddress = rngC.Address
Do
strLastRow = Worksheets("Transfer Sheet").Range("A" & Rows.Count).End(xlUp).Row + 1
rngC.EntireRow.Copy wSht.Cells(strLastRow, 1)
Set rngC = .FindNext(rngC)
Loop While Not rngC Is Nothing And rngC.Address <> FirstAddress
End If
End With

MsgBox ("All relevant data has been moved to the transfer sheet ")

End Sub

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