Solved: VBA to search then copy found data

[simora]

I have a userform on sheet 1
I want to search a range on sheet 1 Range N2:Z2
for the value in textbox 1 on the Userform, then copy down that column from the found cell where the textbox.value is found to the last used cell in that colum and put the contents of that range on sheet 2 C2

Appreciate any vba code or suggestons

[GTO]

Greetings Simora,

Please note that I specified "Sheet3" just for the example. You mention that you have a userform "on" sheet 1. I think you just meant that "Sheet1" is where you are looking for the matched value.

This example assumes that the TextBox is named "txtFind" and that the CommandButton to execute the find is named "cmdFind".

Hope this helps,

Mark

Option Explicit

Private Sub cmdFind_Click()
Dim _
rngSearch As Range, _
rngFound As Range, _
intCol As Integer

'// First, specify the sheet that the values are on where we're going to look for //
'// a matching value. //
With Worksheets("Sheet3")

'// Then set a reference to the range of cells (on that specific sheet). //
Set rngSearch = .Range("N2:Z2")

'// Now if we find something, this will set a reference to the cell (a range //
'// object) that we found it in. Else, rngFound will be nothing. //
Set rngFound = rngSearch.Find(What:=txtFind, After:=.Range("Z2"), _
LookIn:=xlValues, LookAt:=xlWhole)

'// Now we'll test both to make sure we found a match, and that the user didn't //
'// leave the textbox empty (as of course any blank cell would match a blank //
'// textbox). //
If Not rngFound Is Nothing _
And Not Me.txtFind = vbNullString Then

'// Then we will reset this range to include all the cells below that have //
'// values. //
Set rngFound = .Range(.Cells(rngFound.Row, rngFound.Column), _
.Cells(.Cells(Rows.Count, rngFound.Column).End(xlUp).Row, _
rngFound.Column))

'// Then copy to the range you specified. //
rngFound.Copy Worksheets("Sheet2").Range("C2")
End If
End With

Unload Me

End Sub

[simora]

Thanks Mark:
I got it to work. One tiny problem. Somehow, it didn't like this little section. Specifically


If Not rngFound Is Nothing _
And Not Me.txtFind = vbNullString Then


It somehow had a problem with Me.Textfind

Thanks a million.

[GTO]

Thanks Mark:
I got it to work. One tiny problem. Somehow, it didn't like this little section. Specifically


If Not rngFound Is Nothing _
And Not Me.txtFind = vbNullString Then


It somehow had a problem with Me.Textfind

Thanks a million.

Please ensure you have the text box named "txtFind" or update the code to reflect the name of your textbox.

If this doesn't fix it, I do not believe the Me keyword should cause any issue, as it just qualifies the control with the form it belongs to. Still, you could drop "Me." off and see if anything changes.

If still not, post an example workbook with the userform as-is.

Mark