Excel macro - search/find function

[Peterzh193]

I am designing a “basic†database in work and having trouble with some macros

The database is a record of production. Information is added at pre and post
production. Adding the pre-production information I have sorted. A form is
filled in, submit button hit and a new row is created with the information in.
Along this row there are gaps where post production information is entered,
but trying to get this information to paste into the correct row I cant do.

I have a form to fill in the information (one each for pre and post
production) which I then want adding to the database against the reference
number. Converting the information from the input form into the database I
can do for pre production, but getting the post-production data entered into
the database along the correct row I don’t know how to get there.

I believe I want a search function that takes the database reference number
(on the post-production form) and finds this in column A. once this ref
number is found, the cell is active and I can to paste the information from
the post production form into the gaps on the correct row

Anyone have a clue?

 

[Dave Peterson]

Dim DestCell as range
dim DestRow as long
with worksheets("databasesheetnamehere")
with .columns(1)
set destcell = .Cells.Find(what:=me.textbox99.text, _
after:=.Cells(.cells.count), _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlnext, _
MatchCase:=False)
end with

if destcell is nothing then
'it wasn't found
set destcell = .cells(.rows.count,"A").end(xlup).offset(1,0)
end if

destrow = destcell.row

.cells(destrow,"B").value = me.textbox2.text
.cells(destrow,"C").value = me......

end if

==============
Untested, uncompiled. Watch for typos.

 

[Joel]

I'm not sure why you are using Excel rather than Access. When you are
refereing to a database is in excel?

To find the row number for the part use FIND
Quant = 275
PartNum = "123456"
set c = column("A").find(what:=PartNum, lookin:=xlvalues, lookat:=xlwhole)
if c is nothing then
msgbox("Cannot find : " & PartNum)
else
RowNum = c.Row
Range("C" & RowNum) = "Red"
Range("D" & RowNum) = Quant
end if

 

[Don Guillett]

A good way to learn is to record a macro while doing it manually. Then,
clean it up.

 

[Peterzh193]

thanks this helps alot, how can i change this:
Range("C" & RowNum) = "Red"
Range("D" & RowNum) = Quant

to paste details from cells in a seperate worksheet? (ie post-production?)

Pete

I'm not sure why you are using Excel rather than Access. When you are
refereing to a database is in excel?

To find the row number for the part use FIND
Quant = 275
PartNum = "123456"
set c = column("A").find(what:=PartNum, lookin:=xlvalues, lookat:=xlwhole)
if c is nothing then
msgbox("Cannot find : " & PartNum)
else
RowNum = c.Row
Range("C" & RowNum) = "Red"
Range("D" & RowNum) = Quant
end if

I am designing a “basic†database in work and having trouble with some macros

[quoted text clipped - 16 lines]

Anyone have a clue?

 

[Peterzh193]

how do i record a "find"?

A good way to learn is to record a macro while doing it manually. Then,
clean it up.

I am designing a “basic†database in work and having trouble with some
macros

[quoted text clipped - 24 lines]

Anyone have a clue?

 

[Don Guillett]

on the toolbar menu
select your range>EDIT>find>>>>>

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

how do i record a "find"?

A good way to learn is to record a macro while doing it manually. Then,
clean it up.

I am designing a “basic†database in work and having trouble with some
macros

[quoted text clipped - 24 lines]

Anyone have a clue?

 

[Peterzh193]

ah right! ok...i am using Excel 2007 where do i find this???

on the toolbar menu
select your range>EDIT>find>>>>>

how do i record a "find"?

[quoted text clipped - 6 lines]

 

[Peterzh193]

ok i have found it, but struggle to get it reference the cell

ah right! ok...i am using Excel 2007 where do i find this???

on the toolbar menu
select your range>EDIT>find>>>>>

[quoted text clipped - 4 lines]

 

[Patrick Molloy]

HOME tab, EDITING group

ah right! ok...i am using Excel 2007 where do i find this???

on the toolbar menu
select your range>EDIT>find>>>>>

how do i record a "find"?

[quoted text clipped - 6 lines]

Anyone have a clue?

 

[Patrick Molloy]

Sub demoFIND()

Dim cell As Range
Dim addr As String
Dim what As String
what = "ABC"

Set cell = Cells.Find(what)

If Not cell Is Nothing Then
addr = cell.Address
Do
cell.Interior.Color = vbRed
Set cell = Cells.FindNext(cell)
Loop While cell.Address <> addr
End If

End Sub

' NOTE that FIND is circular, so once its found all the cells it starts
again....so we save the first address and check until FIND loops back and
starts again

code was probably originally from HELP in Excel 2000.

Is this helpful?

ok i have found it, but struggle to get it reference the cell

ah right! ok...i am using Excel 2007 where do i find this???

on the toolbar menu
select your range>EDIT>find>>>>>

[quoted text clipped - 4 lines]

Anyone have a clue?