find row to use
 

I have a large excel data base, with an input sheet and storage sheet
each row in storgae has a unique number(at the moment i can only add then
next number)

I want to be able to user enter a number and pull the row out and put into
the entry page for editing.

Then to save over the previous data for that number with new edited data.

Thanks

find row to use
 

Hi

I think this will help you:

Sub FindNumber()
Dim Answer As Long
Dim TargetCol As String

TargetCol = "A"
Answer = InputBox("Enter number to find")

Set f = Columns(TargetCol).Find(what:=Answer)
If Not f Is Nothing Then
TargetRow = f.Row
Else
msg = MsgBox("Number " & Answer & " was not found")
Exit Sub
End If
End Sub

Regards,
Per

"Miree" skrev i meddelelsen
...
I have a large excel data base, with an input sheet and storage sheet
each row in storgae has a unique number(at the moment i can only add then
next number)

I want to be able to user enter a number and pull the row out and put into
the entry page for editing.

Then to save over the previous data for that number with new edited data.

Thanks

find row to use
 

Miree,

I assume your record number is in Column A sheet2 and you want to pull
records to Sheet1 to edit and then put then back. These 2 routines should do
that. Right click sheet1 tab, view code and paste this 2 subs in. I would run
them from a button on sheet 1

Sub PullItOut()
Dim MyRange As Range
Dim FoundIt As Boolean
FoundIt = False
Do
Response = InputBox("Enter record number to edit", vbOKCancel)
If Response = "" Then Exit Sub
If Not IsNumeric(Response) Or Response = 0 Then
MsgBox ("You must enter a number")
Else
Exit Do
End If
Loop
Response = Val(Response)
lastrow = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row
Set MyRange = Sheets("Sheet2").Range("A1:A" & lastrow)
For Each c In MyRange
If c.Value = Response Then
c.EntireRow.Copy
FoundIt = True
Exit For
End If
Next
If FoundIt Then
Range("A2").PasteSpecial
Else
MsgBox ("Record " & Response & " Not found")
End If
End Sub

Sub PutItBack()
Dim MyRange As Range
Dim FoundIt As Boolean
FoundIt = False
Record = Range("A2").Value
Rows(2).EntireRow.Copy
lastrow = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row
Set MyRange = Sheets("Sheet2").Range("A1:A" & lastrow)
For Each c In MyRange
If c.Value = Record Then
c.PasteSpecial
FoundIt = True
Exit For
End If
Next
If FoundIt Then
MsgBox ("Record replaced")
Rows(2).EntireRow.ClearContents

Else
MsgBox ("No corresponding record forund")
End If
End Sub

Mike

"Miree" wrote:

I have a large excel data base, with an input sheet and storage sheet
each row in storgae has a unique number(at the moment i can only add then
next number)

I want to be able to user enter a number and pull the row out and put into
the entry page for editing.

Then to save over the previous data for that number with new edited data.

Thanks

find row to use
 

I wouldn't ask the user to type a number. I'd ask them to click a
cell/row/range.

dim myCell as range
dim myRow as long
set mycell = nothing
on error resume next
set mycell = application.inputbox(Prompt:="Select the row to process",type:=8)
on error goto 0

if mycell is nothing then
'user hit cancel, what should happen?
'exit sub '????
else
myrow = mycell.cells(1).row
...rest of code here
end if



Miree wrote:

I have a large excel data base, with an input sheet and storage sheet
each row in storgae has a unique number(at the moment i can only add then
next number)

I want to be able to user enter a number and pull the row out and put into
the entry page for editing.

Then to save over the previous data for that number with new edited data.

Thanks

--

Dave Peterson

find row to use
 

Thanks this worked really well.

"Mike H" wrote:

Miree,

I assume your record number is in Column A sheet2 and you want to pull
records to Sheet1 to edit and then put then back. These 2 routines should do
that. Right click sheet1 tab, view code and paste this 2 subs in. I would run
them from a button on sheet 1

Sub PullItOut()
Dim MyRange As Range
Dim FoundIt As Boolean
FoundIt = False
Do
Response = InputBox("Enter record number to edit", vbOKCancel)
If Response = "" Then Exit Sub
If Not IsNumeric(Response) Or Response = 0 Then
MsgBox ("You must enter a number")
Else
Exit Do
End If
Loop
Response = Val(Response)
lastrow = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row
Set MyRange = Sheets("Sheet2").Range("A1:A" & lastrow)
For Each c In MyRange
If c.Value = Response Then
c.EntireRow.Copy
FoundIt = True
Exit For
End If
Next
If FoundIt Then
Range("A2").PasteSpecial
Else
MsgBox ("Record " & Response & " Not found")
End If
End Sub

Sub PutItBack()
Dim MyRange As Range
Dim FoundIt As Boolean
FoundIt = False
Record = Range("A2").Value
Rows(2).EntireRow.Copy
lastrow = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row
Set MyRange = Sheets("Sheet2").Range("A1:A" & lastrow)
For Each c In MyRange
If c.Value = Record Then
c.PasteSpecial
FoundIt = True
Exit For
End If
Next
If FoundIt Then
MsgBox ("Record replaced")
Rows(2).EntireRow.ClearContents

Else
MsgBox ("No corresponding record forund")
End If
End Sub

Mike

"Miree" wrote:

I have a large excel data base, with an input sheet and storage sheet
each row in storgae has a unique number(at the moment i can only add then
next number)

I want to be able to user enter a number and pull the row out and put into
the entry page for editing.

Then to save over the previous data for that number with new edited data.

Thanks