MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Search then go to particular cell

  Asked By: Donna    Date: Oct 08    Category: MS Office    Views: 914

I would like to thank the moderators for
approving my membership. My name is Bud from the Philippines and
frequently use my favorite program Excel97. Although I have no
programming knowledge, I am trying hard to improve my skills in
using Excel by beginning to learn using VBA.

Normally I would like to get a feel for an egroup before introducing
myself but in this case, Im fairly stuck on a problem and badly need
help since its driving me nuts already. Ive been trying to find
solutions over the net but have found none to my simple problem.

Here's what I would like to do:

I have created an invoice form connected to a datalist for data
retrieval of past recorded invoices via a unique record number field
with range name InvoiceNo.

The pseudo code for this macro is:

1. Read the inputed InvoiceNo then match it in the datalist.
Finally highlight (bring the cursor) to the start of that record.

2. Copy every field of that record back into the invoice form.

Ive done the macro code for the second part (by using the macro
recorder to record relative coordinates to copy and paste) but its
the first part (the search and goto cell) that has baffled me for
several days already. Could someone give me a clue on what VBA
commands will allow me to search and retrieve the proper record's
cell address.

A clue to the proper direction would be greatly appreciated. Thanks
in advance for any replies.



10 Answers Found

Answer #1    Answered By: Eula Armstrong     Answered On: Oct 08

I sometimes use the code  below to look for a cell. It's based arounf the VBA
"Find" which is well documented in the help.
It returns a "range", which you can then do things with including going to
it and getting it's address.

You'll see that I've included a second "parameter" for the function. If the
parameter isn't given then it will default to the first found, Otherwise it
will loop around finding the Nth occurence of the search  string.

If the search string isn't found  at all then it gives a message... "Not

You could call this from a sub.... Something like...

Sub subFindAndGetAddress()

Dim rlFound as range

Set rlFound = fncLookForCell("Search String")
MsgBox rlFound.Address

End Sub

This is an example but should give  you something to bite on.

Answer #2    Answered By: Samuel Evans     Answered On: Oct 08

Thank you for the reply. The code  works well! I wont pretend I
understand half of the code but it works auto magically!

I would like to improve  the search  though and limit the range  to a
particular range name called "InvoiceNo" which represents cells
B4:B43 (for example).

Ive tried to change the line

Set rlFromRange = Range("a1")

to any of the following but only get a runtime error

Set rlFromRange = Range("InvoiceNo")
Set rlFromRange = Range(InvoiceNo)
Set rlFromRange = Range(B4:B43)

Any clue on how I can limit the search range to just a particular
range name rather than the whole worksheet?

Answer #3    Answered By: Fergus Jones     Answered On: Oct 08

Check out the help on FIND in the visual basic editor. The easiest way to do
this is to click on the word find... No need to select the whole word... and
press F1.

You could also look at http://www.cpearson.com/excel/RangeFind.htm ... Chip
Pearsons site. It's a wonderful excel  VBA reference!

The help will tell you that Find is to look for stuff within a range.

To search  within a range  use something like...

Set rlFRange = Range("invoiceno")
Set rlFound = rlFRange.Find( _
What:="TTT", _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _

Note that I've...
1 Set the range to look in at the top
2 Am NOT using the After parameter
3 Looking for "TTT". This should be replaced by your variable

Answer #4    Answered By: Dashiell Jones     Answered On: Oct 08

Thank you for pointing me to the right
direction. Time for me to do my homework and do some research ... 8-

Answer #5    Answered By: Emily Campbell     Answered On: Oct 08

Another thing that might be relevant is
finding some record  number or word in one list and moving
information from that record to another sheet.
For example, if you have a record with other information
connected to it on the same row
(e.g. "Record#" "Date" "Address" "Phone" "Item" each in columns),
you can find that same record number  on another sheet and move the
information in column 5 ("E" column, "Item") to the other page.
This sub allows you to find the selected record on the other
page, even if it's not on the same row as the first one (e.g. it
might find it on row 36 while the first sheet had it on row 10), and
moves the coresponding "Item" value to the 5th column on the
matching record on the other page.

Sub findmatchandmove()

Dim actVal
'<----the variable of the active record to be found
Dim newVal
'<---the variable of the value that needs to be moved
Dim fc
'<---variable for the found cell  that matches
Dim fndRrow
'<---variable for the row that matches
actVal = ActiveCell.Value
'<--- actVal is located in the active cell
newVal = ActiveCell(Offset, 5).Value
'<--- location of newVal, column 5
Set fc = Worksheets("Sheet2").Columns("A").Find(what:=actVal)
'<--finds it on the other sheet
fndRrow = fc.Row
'<--identifies the row it's on in the list
Sheets("Sheet2").Cells(fndRrow, 5).Value = newVal
'<---puts the value in column 5 of the newly found  row
MsgBox "Item information is now on Sheet2 on line = " & fndRrow
End Sub

Answer #6    Answered By: Brooke Robertson     Answered On: Oct 08

Nice suggestion but somewhat too advance for my level
and/or my simple  needs for now.

Answer #7    Answered By: Trina King     Answered On: Oct 08

Could someone point or convert the line below
written in XLM macro  language


to its equivalent in VBA?

Answer #8    Answered By: Baden Smith     Answered On: Oct 08

Maybe something like:

Sub findforms()
Dim cell  As Range
For Each cell In ActiveSheet.UsedRange.SpecialCells(xlFormulas)
If InStr(cell.Formula, "(") Then
MsgBox cell.Address
End If
End Sub

then substitute what you are looking for in place of "("?

Worth a try. I use that form  to replace cells with formulas
with the values.

Answer #9    Answered By: Zeke Thompson     Answered On: Oct 08

Thanks for your example. Ive also thought of doing my own
loop but did not want to re-invent the wheel if there was already a
subroutine/function already built in VBA.

Answer #10    Answered By: Amir Hashmi     Answered On: Oct 08

Finally got my first VBA code  to work properly.

Didn't find what you were looking for? Find more on Search then go to particular cell Or get search suggestion and latest updates.