Macro to retrieve data

[Nahuster]

Hello all,
This is my first post and I'm writing it because I couldn't find what I'm looking for anywhere else.
I need this:

In sheet 1 (which I called TEMPLATE) cell C3 I put a ticket number eg (T007) and in sheet 8 (TICKET_TRACK) all the tickets are being recorded.
The record function is working fine, what I now need is to be able to retrieve data. I created another button that is supposed to do that, but the only thing I was able to get is that it takes me to the TICKET_TRACK worksheet to where that particular ticket number is located.

I need that every time I put a ticket number in C3 on this worksheet, the macro looks up the ticket number in worksheet (TICKET_TRACK) and fills out the data in cells C3,C4,C5,C6,C7, corresponding to the name of the person, the ID, the phone number, etc.

This is what I could get till now, and it's what looks up the ticket number but I don't know how to retrieve the data:

Private Sub CommandButton9_Click()
Dim FindString As String
Dim Rng As Range
FindString = Sheets("TEMPLATE").Range("C2").Value
If Trim(FindString) <> "" Then
With Sheets("TICKET_TRACK").Range("A:A")
Set Rng = .Find(What:=FindString, _
After:=.Cells(1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False)
If Not Rng Is Nothing Then
Application.Goto Rng, True
Else
MsgBox "Nothing found"
End If
End With
End If
End Sub


Can someone out there help me please? =O

Also, it'd be cool if I could do this without having to click on a button...

Thanks in advance

 

[JLGWhiz]

fills out the data in cells C3,C4,C5,C6,C7, corresponding to the name of the person, the ID, the phone number, etc.

Can we just make up the source for this data or do you have something specific in mind, like which cells of the template this data can be found in and which is in which cell?

 

[Nahuster]

Can we just make up the source for this data or do you have something specific in mind, like which cells of the template this data can be found in and which is in which cell?

Hello YES,
C3 in TEMPLATE corresponds to column A in TICKET_TRACK
C4 in TEMPLATE corresponds to column B in TICKET_TRACK
C5 = C
C6 = D
C7 = E
...

Sorry I didn't put that on the post!

 

[JLGWhiz]

Private Sub CommandButton9_Click()
Dim FindStr As Range
Set FindStr = Sheets("TICKET_TRACK"),Range("A:A").Find(Sheets("Template").Range("C3").Value, , xlValues, xlWhole)
	If Not FindStr Is Nothing Then
		With Sheets("Template")
			.Range("C4") = FindStr.Offset(, 1).Value
			.Range("C5") = FindStr.Offset(, 2).Value
			.Range("C6") = Findstr.Offset(, 3).Value
			.range("C7") = FindStr.Offset(, 4).Value
		End with
	Else
		MsgBox "Input Value not found"
	End If
End Sub

Untested.

 

[Nahuster]

Private Sub CommandButton9_Click()
Dim FindStr As Range
Set FindStr = Sheets("TICKET_TRACK"),Range("A:A").Find(Sheets("Template").Range("C3").Value, , xlValues, xlWhole)
	If Not FindStr Is Nothing Then
		With Sheets("Template")
			.Range("C4") = FindStr.Offset(, 1).Value
			.Range("C5") = FindStr.Offset(, 2).Value
			.Range("C6") = Findstr.Offset(, 3).Value
			.range("C7") = FindStr.Offset(, 4).Value
		End with
	Else
		MsgBox "Input Value not found"
	End If
End Sub

Untested.

Wow, it's much shorter than I thought...
Just one thing its... I get a syntax error in this string
Set FindStr = Sheets("TICKET_TRACK"),Range("A:A").Find(Sheets("Template").Range("C2").Value, , xlValues, xlWhole)

For the life of me I can't find what it is.

Again, I don't know if I wrote it down correctly so this is how it goes:

I put a number in C2 in worksheet TEMPLATE and that corresponds to column A in worksheet TICKET_TRACK
SO the button should
1st Find the corresponding number on that column ("A") in worksheet TICKET_TRACK
2nd say the ticket was t007 and was found in A7 in worksheet TICKET_TRACK so after finding that entry copy B7, C7, D7 and E7 from worksheet TICKET_TRACK to cells C3, C4, C5 and C6 respectively in worksheet TEMPLATE.

Again, thanks a bunch for answering my question

 

[Nahuster]

Sorry, my bad... there was a coma instead of a dot and that was the whole issue.
It's working perfectly.
THANK YOU SO MUCH!

 

[JLGWhiz]

Yep, typos can be troublesome.
Regards, JLG