Macro to retrieve data

Nahuster

New Member
Joined
Apr 22, 2016
Messages
33
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

Well-known Member
Joined
Feb 7, 2012
Messages
11,294
Office Version
2013
Platform
Windows
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?
 
Last edited:

Nahuster

New Member
Joined
Apr 22, 2016
Messages
33
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! :eek:
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
11,294
Office Version
2013
Platform
Windows
Code:
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.
 
Last edited:

Nahuster

New Member
Joined
Apr 22, 2016
Messages
33
Code:
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 :biggrin:
 

Nahuster

New Member
Joined
Apr 22, 2016
Messages
33
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! :biggrin:
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
11,294
Office Version
2013
Platform
Windows
Yep, typos can be troublesome.
Regards, JLG
 

Forum statistics

Threads
1,082,380
Messages
5,365,124
Members
400,824
Latest member
Themilkybarkid

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top