Hi All...
I have an EXCEL workbook with two sheets A & B
On Sheet A i have a Display Page with Various vlookups from Sheet B ...
I created a Form which has a text box '1' with a number which is taken from Sheet A (a3) & an other empty text box '2' which is also taken from sheet A (comments d5)....
What i need is a string that takes the text that is inputted in textbox2 on the form finds the number which is on textbox 1 on Sheet B and inserts the text in same row but in the a different column of sheet B.
I hope u can help me ...cos i am going nuts !!!
Forms Text Input
Chernichkin Stanislav
Here's a quick script I whipped up that will do what you want. You'll need to tweak it a bit to make it fit your needs exactly. Basically this code assumes you've got values from 1-4 in cells C10:C13 on Sheet1, and text to be copied in cells D10:D14. It finds the index number you choose, then copies the text from column D on Sheet1 into column E on Sheet2. Let me know if you have trouble making this work.
Option Explicit
Public Sub TransferTextByIndex()
On Error GoTo err_exit
Dim index As Integer, rfound As Range, sourcerow As Long, copytext As String
Do
index = CInt(InputBox("Please enter a whole number from 1-4:", , 1))
If index > 0 And index < 5 Then Exit Do
Loop
' search for number in the appropriate range of Sheet1
Set rfound = Sheet1.Range("C10:C13").Find(index)
If rfound Is Nothing Then
MsgBox "Not found."
Exit Sub
End If
' get text value from this row in Sheet1
copytext = Sheet1.Cells(rfound.Row, rfound.Column + 1)
' and paste it one column further to the right, but in the same row, on Sheet2
Sheet2.Cells(rfound.Row, rfound.Column + 2) = copytext
Exit Sub
err_exit:
' handle errors
End Sub