Using TextBox data from user form to find cell in column

[dannyh]

Hi All

I have a simple loop looking for a date in a column based on whatever is typed in the Text Box in my user form, I know the date is there but it just keeps looping past it. I have tried testing putting in a simple number “5” and searching for that and again it fails. Is there any other tests I can do ?

My loop code

Rng = UserForm3.TextBox1
Range(“A1”).select
Do Until ActiveCell.Value = Rng
ActiveCell.Offset(1,0)
Loop

Thanks Dan

 

[My Aswer Is This]

I see nothing in your code which says what to do if it finds the value

 

[dannyh]

I see nothing in your code which says what to do if it finds the value

Sorry was typing on phone, I would like to paste a set range that never changes from an additional sheet in the workbook, call it (“DATA”) (“G5:O5”)

 

[dannyh]

Sorry was typing on phone, I would like to paste a set range that never changes from an additional sheet in the workbook, call it (“DATA”) (“G5:O5”)

Offset(0,1) to found cell

 

[My Aswer Is This]

Sorry still do not understand
You want to search for value entered in TextBox1 and do what when found.
And do you want to find this value in column A of active sheet
You want to paste the Range Sheets("Data").Range("G5:O5")

 

[JackDanIce]

Try:

Sub Macro1()

On Error Resume Next
[A:A].Find(UserForm3.TextBox1, , xlFormulas, xlPart).Offset(1).Resize(, 9).Value = Sheets("Data").[G5:O5].Value
On Error GoTo 0

End Sub

 

[My Aswer Is This]

I see your getting help now. I will move on to another question

 

[dannyh]

Sorry still do not understand
You want to search for value entered in TextBox1 and do what when found.
And do you want to find this value in column A of active sheet
You want to paste the Range Sheets("Data").Range("G5:O5")

Sorry, i'll try again,

Search for value in TextBox1 in Col A on active sheet
Once the value is found I would like to past the range G5:O5 on Sheet "DATA" into the cells directly next to the found value, eg if the found value is in A7 then paste range from DATA sheet into B7:K7

 

[dannyh]

Try:

Sub Macro1()

On Error Resume Next
[A:A].Find(UserForm3.TextBox1, , xlFormulas, xlPart).Offset(1).Resize(, 9).Value = Sheets("Data").[G5:O5].Value
On Error GoTo 0

End Sub

Hi Jack

I tried your solution still no luck, the code cycles through but doesn't find the date that is there, I have tried changing the word to text and it does find that but it inserts the copied data below the cell not to the side of it so I just need to change the offset.

 

[JackDanIce]

Hi @dannyh

For side of it, try:

Sub Macro1()

On Error Resume Next
[A:A].Find(UserForm3.TextBox1, , xlFormulas, xlPart).Offset(,1).Resize(, 9).Value = Sheets("Data").[G5:O5].Value
On Error GoTo 0

End Sub

Unsure about not finding date, could be the format of values in column A are not set to date or the value being searched for isn't recognised as a date.

As a guess, I've commented out the error surpressions and forced the search to be a date, try:

Sub Macro1()

'On Error Resume Next
With UserForm3
    [A:A].Find(DateSerial(Year(.TextBox1), Month(.TextBox1), Day(.TextBox1)), , xlFormulas, xlPart).Offset(, 1).Resize(, 9).Value = Sheets("Data").[G5:O5].Value
End With
'On Error GoTo 0

End Sub