Code for Transferring Needed

charllie

Well-known Member
Joined
Apr 6, 2005
Messages
971
Hi Folks,

I would be really grateful if someone could help me with a code please.

I want to do the following when commandbutton1 is used in a userform1.

  • Take the text in userform1 textbox1 and
    Find a match for it in Column A Worksheet1
    When a match is found then copy the text in userfrom1 textbox2 and
    Then go along the matched row in Worksheet1 until you find a blank cell
    Then place the text from textbox2 into the blank cell
    Next do the same for textbox3.
I hope that explains myself well enough for someone to help me.

Thanks
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,696
Platform
  1. Windows
Hello,

How about

Code:
Private Sub CommandButton1_Click()
MY_TEXT_1 = TextBox1.Text
MY_TEXT_2 = TextBox2.Text
MY_TEXT_3 = TextBox3.Text
UserForm1.Hide
For MY_ROWS = 1 To Range("A65536").End(xlUp).Row
    If Range("A"&MY_ROWS).Value = MY_TEXT_1 Then
        Range("IV" & MY_ROWS).End(xlToLeft).Offset(0, 1).Value = MY_TEXT_1
        Range("IV" & MY_ROWS).End(xlToLeft).Offset(0, 1).Value = MY_TEXT_2
        Range("IV" & MY_ROWS).End(xlToLeft).Offset(0, 1).Value = MY_TEXT_3
        Exit Sub
    End If
Next MY_ROWS
End Sub
 

charllie

Well-known Member
Joined
Apr 6, 2005
Messages
971
Hi onlyadrafter

Thanks for your reply.

I posted your code as is but nothing happened. It appeared to work (got no error message). However i couldn't find the the text anywhere.

Any ideas?
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Hi try
Code:
Private Sub CommandButton1_Click()
Dim r As Range
If Len(Me.TextBox1)=0 Then Exit Sub
With Sheets("YourSheetNmae")       '<- change here
   Set r = .Columns("a").Find(Me.TextBox1.Text)
   If Not r Is Nothing Then
      With .Cells(r.Row,Columns.Count).End(xlToLeft)
         .Offset(,1).Value = Me.TextBox2.Text
         .Offset(,2).Value = Me.TextBox3.Text
      End With
   Else
      MsgBox "Not Found"
   End If
End With
End Sub
 

charllie

Well-known Member
Joined
Apr 6, 2005
Messages
971
Hi Jindon,

Long time no see. Hope you are well.

Thanks for the code, it works brilliantly.

Thanks to both for your time and help, i appreciate it.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,634
Messages
5,523,996
Members
409,555
Latest member
TIPSAREA

This Week's Hot Topics

Top