I start the search on a sheet named "Search". I enter the ID number that I
want to look up in cell E4.

Trying to get a macro button to work. Can't figure out how to locate an ID
number on a sheet (named "Data") and if it is found, transfer data in the row
the ID number is located to another sheet (named "Final") using the lines
below.

Sheets("Final").Range("D14").Value = Sheets("Data").Range("B*").Value
Sheets("Final").Range("D15").Value = Sheets("Data").Range("C*").Value
Sheets("Final").Range("D13").Value = Sheets("Data").Range("E*").Value
Sheets("Final").Range("H18").Value = Sheets("Data").Range("G*").Value
Sheets("Final").Range("H19").Value = Sheets("Data").Range("H*").Value
Sheets("Final").Range("H20").Value = Sheets("Data").Range("I*").Value
Sheets("Final").Range("H21").Value = Sheets("Data").Range("J*").Value
Sheets("Final").Range("H22").Value = Sheets("Data").Range("K*").Value
Sheets("Final").Range("H23").Value = Sheets("Data").Range("L*").Value

* = the row in which the ID number you searched is located.


If the ID number is not found I would like it to pop up a MsgBox saying for
example "No record".

Any help would be greatly appreciated.

Thank you!

From what you describe you would have to use the Find method.
Sub fndID()
RngSrch = Worksheets("Search").Range("E4").Value
With Sheets("Data").UsedRange
Set c = .Find(RngSrch, Lookin:=xlValues)
If Not c Is Nothing Then
RngFnd = c.Address
End If
MsgBox Range(RngFnd).Value
End Sub

But after that, I am not sure what you are trying to do.

"Ryan Hess" wrote:

I start the search on a sheet named "Search". I enter the ID number that I
want to look up in cell E4.

Trying to get a macro button to work. Can't figure out how to locate an ID
number on a sheet (named "Data") and if it is found, transfer data in the row
the ID number is located to another sheet (named "Final") using the lines
below.

Sheets("Final").Range("D14").Value = Sheets("Data").Range("B*").Value
Sheets("Final").Range("D15").Value = Sheets("Data").Range("C*").Value
Sheets("Final").Range("D13").Value = Sheets("Data").Range("E*").Value
Sheets("Final").Range("H18").Value = Sheets("Data").Range("G*").Value
Sheets("Final").Range("H19").Value = Sheets("Data").Range("H*").Value
Sheets("Final").Range("H20").Value = Sheets("Data").Range("I*").Value
Sheets("Final").Range("H21").Value = Sheets("Data").Range("J*").Value
Sheets("Final").Range("H22").Value = Sheets("Data").Range("K*").Value
Sheets("Final").Range("H23").Value = Sheets("Data").Range("L*").Value

* = the row in which the ID number you searched is located.


If the ID number is not found I would like it to pop up a MsgBox saying for
example "No record".

Any help would be greatly appreciated.

Thank you!

I'm not sure that is what I'm looking for. Perhaps I didnt explain what I'm
trying to do well enough though. Let me try and explain more.

To simplify a little I'll just use default names for the sheets rather than
what I renamed them.

So I have; Sheet1, Sheet2, Sheet3

Sheet1 -- I enter an ID number in cell E4 that I want to look up.
(A macro button is on Sheet1 to initiate the search)

Sheet2 -- Is where all my values are stored for lets say 11 different
paramaters.
Column B is a list of all the ID numbers. Columns C - L is a
list of all the
other paramaters that coincide with ID number in their
respected row.

Sheet3 -- Is a form used to print out for specific ID numbers using Sheet1
to define
which ID number is used and Sheet2 to provide the 4 data
values that are
designated to that same ID number that was chosen.

1) Enter in the desired ID number. (Sheet1 cell E4)
2) Click on the macro button. (Sheet1)
3) Sub
4) Find the ID numbered entered above on Sheet2 ColumnB
4a) If the ID number is found (recognizing the row) transfer the data
in
Columns B - L to specific locations on Sheet3 (**** see the code
in
the OP as I do not want to just copy the row and paste it over)
4b) If the ID number is not found, MsgBox "ID number not found"
5) End If End Sub





"JLGWhiz" wrote:

From what you describe you would have to use the Find method.
Sub fndID()
RngSrch = Worksheets("Search").Range("E4").Value
With Sheets("Data").UsedRange
Set c = .Find(RngSrch, Lookin:=xlValues)
If Not c Is Nothing Then
RngFnd = c.Address
End If
MsgBox Range(RngFnd).Value
End Sub

But after that, I am not sure what you are trying to do.

"Ryan Hess" wrote:

I start the search on a sheet named "Search". I enter the ID number that I
want to look up in cell E4.

Trying to get a macro button to work. Can't figure out how to locate an ID
number on a sheet (named "Data") and if it is found, transfer data in the row
the ID number is located to another sheet (named "Final") using the lines
below.

Sheets("Final").Range("D14").Value = Sheets("Data").Range("B*").Value
Sheets("Final").Range("D15").Value = Sheets("Data").Range("C*").Value
Sheets("Final").Range("D13").Value = Sheets("Data").Range("E*").Value
Sheets("Final").Range("H18").Value = Sheets("Data").Range("G*").Value
Sheets("Final").Range("H19").Value = Sheets("Data").Range("H*").Value
Sheets("Final").Range("H20").Value = Sheets("Data").Range("I*").Value
Sheets("Final").Range("H21").Value = Sheets("Data").Range("J*").Value
Sheets("Final").Range("H22").Value = Sheets("Data").Range("K*").Value
Sheets("Final").Range("H23").Value = Sheets("Data").Range("L*").Value

* = the row in which the ID number you searched is located.


If the ID number is not found I would like it to pop up a MsgBox saying for
example "No record".

Any help would be greatly appreciated.

Thank you!

I do have this code which allows me to search the ID number and then
copy/paste the row on a new sheet but like I said, I want to take only
certain cells in the row and "Copy/Paste" them to specific cells on Sheet3.

Private Sub Search1_Click()

Sheets("Search").Unprotect Password:="qwerty"
Range("B17:L10000").Select
Selection.Delete Shift:=xlToLeft
Dim sh As Worksheet
Dim sh1 As Worksheet
Dim sAddr As String, s As Variant
Dim rng As Range, rng1 As Range
Set sh1 = Worksheets("Search")
Set sh = Worksheets("Database")
s = sh1.Range("E9")
Set rng = sh.Range(sh.Range("A3"), _
sh.Cells(Rows.Count, "B")).Find(What:=s, _
After:=sh.Cells(Rows.Count, 1), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not rng Is Nothing Then
sAddr = rng.Address
Do
If rng1 Is Nothing Then
Set rng1 = rng
Else
Set rng1 = Union(rng1, rng)
End If
Set rng = sh.Range(sh.Range("B3"), _
sh.Cells(Rows.Count, 1)).FindNext(rng)
Loop Until rng.Address = sAddr
If Not rng1 Is Nothing Then
Set rng1 = Intersect(rng1.EntireRow, sh.Range("B:L"))
rng1.Copy sh1.Range("B17")
End If
End If
Sheets("Search").Select
Range("E9").Select
Selection.ClearContents
Sheets("Search").Protect Password:="qwerty"
'ActiveWorkbook.Save

End Sub

Note: I just cut paste the code as it is in my button macro. So ignore the
little extras I added to the "Search/Cut-Paste" portion.

"Ryan Hess" wrote:

I'm not sure that is what I'm looking for. Perhaps I didnt explain what I'm
trying to do well enough though. Let me try and explain more.

To simplify a little I'll just use default names for the sheets rather than
what I renamed them.

So I have; Sheet1, Sheet2, Sheet3

Sheet1 -- I enter an ID number in cell E4 that I want to look up.
(A macro button is on Sheet1 to initiate the search)

Sheet2 -- Is where all my values are stored for lets say 11 different
paramaters.
Column B is a list of all the ID numbers. Columns C - L is a
list of all the
other paramaters that coincide with ID number in their
respected row.

Sheet3 -- Is a form used to print out for specific ID numbers using Sheet1
to define
which ID number is used and Sheet2 to provide the 4 data
values that are
designated to that same ID number that was chosen.

1) Enter in the desired ID number. (Sheet1 cell E4)
2) Click on the macro button. (Sheet1)
3) Sub
4) Find the ID numbered entered above on Sheet2 ColumnB
4a) If the ID number is found (recognizing the row) transfer the data
in
Columns B - L to specific locations on Sheet3 (**** see the code
in
the OP as I do not want to just copy the row and paste it over)
4b) If the ID number is not found, MsgBox "ID number not found"
5) End If End Sub





"JLGWhiz" wrote:

From what you describe you would have to use the Find method.
Sub fndID()
RngSrch = Worksheets("Search").Range("E4").Value
With Sheets("Data").UsedRange
Set c = .Find(RngSrch, Lookin:=xlValues)
If Not c Is Nothing Then
RngFnd = c.Address
End If
MsgBox Range(RngFnd).Value
End Sub

But after that, I am not sure what you are trying to do.

"Ryan Hess" wrote:

I start the search on a sheet named "Search". I enter the ID number that I
want to look up in cell E4.

Trying to get a macro button to work. Can't figure out how to locate an ID
number on a sheet (named "Data") and if it is found, transfer data in the row
the ID number is located to another sheet (named "Final") using the lines
below.

Sheets("Final").Range("D14").Value = Sheets("Data").Range("B*").Value
Sheets("Final").Range("D15").Value = Sheets("Data").Range("C*").Value
Sheets("Final").Range("D13").Value = Sheets("Data").Range("E*").Value
Sheets("Final").Range("H18").Value = Sheets("Data").Range("G*").Value
Sheets("Final").Range("H19").Value = Sheets("Data").Range("H*").Value
Sheets("Final").Range("H20").Value = Sheets("Data").Range("I*").Value
Sheets("Final").Range("H21").Value = Sheets("Data").Range("J*").Value
Sheets("Final").Range("H22").Value = Sheets("Data").Range("K*").Value
Sheets("Final").Range("H23").Value = Sheets("Data").Range("L*").Value

* = the row in which the ID number you searched is located.


If the ID number is not found I would like it to pop up a MsgBox saying for
example "No record".

Any help would be greatly appreciated.

Thank you!