Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Find Value then Replace something else

  Asked By: Jeff    Date: Sep 20    Category: MS Office    Views: 859
  

I am working on a spreedsheet that will keep track of repairs and
purchase orders based on the type of equipment. I currently have it
setup that you enter in the PO number, Outside tracking number, and
select the type of equipment this is for. It will then insert that
information on the appropriate page on the next blank line.

Then I have a command button to "receive in" the equipment when it
comes back from repairs. This is where I run into a problem.
The command button opens up a user form where the user puts in the PO
they are receiving in and the date. I then want to have it search the
3 worksheets to find the PO number and put the date they entered into
the cell that's over from it.

I hope I'm not asking for too much here. I've managed to "wing" my way
through it this far and it works great until this point.

Share: 

 

18 Answers Found

 
Answer #1    Answered By: Saiqa Mian     Answered On: Sep 20

The following code should help. It moves from sheet to sheet, based  on
what you enter  into the array, until it finds the value you are looking
for.

Dim i As Integer
Dim a As Integer
Dim k As Integer
Dim aSheet() As Variant

aSheet = Array("Sheet1", "Sheet2")

For k = 0 To UBound(aSheet())
Sheets(aSheet(k)).Select
Range("A1").Select
i = Cells(Rows.Count, "A").End(xlUp).Row
For a = 0 To i
If ActiveCell.Offset(a, 0).Value = "PONUMBER" Then
ActiveCell.Offset(a, 1).Value = "Date"
Exit Sub
End If
Next a
Next k

 
Answer #2    Answered By: Fareess Khan     Answered On: Sep 20

This is sooo close to working. It finds the correct page, finds
correct the row, but... it puts the date  in the first empty column
in the row - which is "H" and my received date is currently in
column "E"..the PO that it is searching for is in Column A.

 
Answer #3    Answered By: Luann Schmidt     Answered On: Sep 20

The "OffSet" property identifies the row and column location of your
spreadsheet by number, depending of course on the current active cell.
i.e. ActiveCell.Offset(0,1) means current active selected row plus one
column to the right. Change the second number  in the OffSet condition to
match the number of columns over from column A you want the date  to be
placed. Since you said H is your column, then it should be set to 7. ---
ActiveCell.Offset(0,7).value = "Date".

 
Answer #4    Answered By: Garritt Bakker     Answered On: Sep 20

I have had that happen before and the way I fixed mine was that where
ever my activecell was located I counted columns from there. Example:
If my active cell  was C1 and I wanted text to go in E1 I had to choose
column number  2 instead of 5. hope that helps.

 
Answer #5    Answered By: Cameron Smith     Answered On: Sep 20

Which bit are you having problems with?

Post your code and explain where the difficulty lies.

 
Answer #6    Answered By: Akina Suzuki     Answered On: Sep 20

I am using this:
Dim i As Integer
Dim a As Integer
Dim k As Integer
Dim aSheet() As Variant

aSheet = Array("Telxon Repair History", "3870 Repair History", _
"SF51 Repair History")

For k = 0 To UBound(aSheet())
Sheets(aSheet(k)).Select
Range("A1").Select
i = Cells(Rows.Count, "A").End(xlUp).Row
For a = 0 To i
If ActiveCell.Offset(a, 0).Value = recpo.Text Then
ActiveCell.Offset(a, 1).Value = recdate.Text
Exit Sub
End If
Next a
Next k


My recpo (The PO number  I am looking for) is in Column A, which it
finds on the right page. If I have the offset column at "1" it will
put the recdate in column H. If I change it to 4, it puts it in
column J.

 
Answer #7    Answered By: Abraham Lopez     Answered On: Sep 20

The Offset method (absolute address) is not very reliable especially
if you have to move things around like inserting rows or columns. I
would use range names instead.

Here are both examples.

1. the offset method: You must remember to change the Offset arg's
if you add/delete cells

Sub testOffset()
Dim rngA As Range
Dim k As Integer
Dim aSheet() As Variant

aSheet = Array("Telxon Repair History", "3870 Repair History", _
"SF51 Repair History")
For k = 0 To UBound(aSheet())
Set rngA = Sheets(aSheet(k)).Cells.Find(What:=recpo.text, _
SearchDirection:=xlPrevious,
SearchOrder:=xlByRows)
If Not rngA Is Nothing Then
Cells(rngA.Row, [e:e].Column).Value = recdate.text
Exit Sub
End If
Next k
End Sub

2. The Range Name method: You don't have to worry about where the
column is it will always find  the right cell  to put  the data in.

You must name your "Received Date" header so that you could always
find it with code. In this case, I just let Excel named it
as "Received_Date".

Sub testRangeName()
Dim rngA As Range
Dim k As Integer
Dim aSheet() As Variant

aSheet = Array("Telxon Repair History", "3870 Repair History", _
"SF51 Repair History")
For k = 0 To UBound(aSheet())
Set rngA = Sheets(aSheet(k)).Cells.Find(What:=recpo.text, _
SearchDirection:=xlPrevious,
SearchOrder:=xlByRows)
If Not rngA Is Nothing Then
Cells(rngA.Row, [Received_Date].Column).Value =
recdate.text
Exit Sub
End If
Next k
End Sub

 
Answer #8    Answered By: Eula Armstrong     Answered On: Sep 20

I am not having any luck at all with these.
With the first one, it will just go to the next open space in column
E, but only on the current page, not according to the PO number  I am
searching for. With the second option I put  in a header just like
you have, and it still tells me an object is required.

 
Answer #9    Answered By: Samuel Evans     Answered On: Sep 20

I could be totally off whack here so please shout at me if I
am... it sounds as though you want to be totally certain the value goes
where you want it.

Do you have any unique column headers? It may be possible to scan along the
headers to look for a column to put  your data in. That's assuming noone is
going to change the header text... :-)

I've had a lot of success with scanning down rows and across columns and
setting a certain cell  to a range so I can enter  stuff there.

I's a bit "simplistic" I know but it only fails on the odd case where people
insist on changing those headers!!

 
Answer #10    Answered By: Fergus Jones     Answered On: Sep 20

sounds like you may have the right 'relative address' but not the right sheet.
When the 'hit' or 'search result' is found, you need to add the spec to identify
the sheet name or index; in the example looks like you are setting up Sheet
Indexes by the k value. If so, try this:

Sheets(k).Cells(rngA.Row, [e:e].Column).Value = recdate.text

see if that get you what you need.

 
Answer #11    Answered By: Dashiell Jones     Answered On: Sep 20

I tend to not "select/activate" a sheet if I don't have to to optimze
the codes. In this case, the code works  fine during the search
operation, but fails because the sheet that it needs to plop the rec'd
date on is not the activesheet, therefore it's dropped on the wrong
sheet (activesheet).

Like AJ said you have to specify which sheet to fill the date  on...
as "Sheets(k).Cells(rngA.Row, [e:e].Column).Value = recdate.text"

Here's the new code with the correct sheet explicitly defined:

Sub testRangeName()
Dim rngA As Range
Dim k As Integer
Dim aSheet() As Variant
Dim strTest As String

aSheet = Array("Telxon Repair History", "3870 Repair History", _
"SF51 Repair History")

For k = 0 To UBound(aSheet())
Set rngA = Sheets(aSheet(k)).Cells.Find(What:=recpo.text, _
SearchDirection:=xlPrevious, SearchOrder:=xlByRows)
If Not rngA Is Nothing Then
Sheets(aSheet(k)).Cells(rngA.Row,
[Received_Date].Column).Value = recdate.text
Exit Sub
End If
Next k
End Sub

 
Answer #12    Answered By: Emily Campbell     Answered On: Sep 20

Using this (i took out the .offset from the last one) from Craig and
now I can get it to find  the PO i am looking for, AND find the
column I want to put  it in, but it puts in the received PO on the
row above it. So if the PO I am looking for is in column A on row
13, I will put this in the correct column of E, but in row 12. I am
so close there has to be one small thing that is being overlooked
with this.:

Dim i As Integer
Dim a As Integer
Dim k As Integer
Dim aSheet() As Variant

aSheet = Array("Telxon Repair History", "3870 Repair History", _
"SF51 Repair History")

For k = 0 To UBound(aSheet())
Sheets(aSheet(k)).Select
Range("A1").Select
i = Cells(Rows.Count, "A").End(xlUp).Row
For a = 0 To i
If ActiveCell.Offset(a, 0).Value = recpo.Text Then
ActiveCell(a, 5).Value = recdate.Text
Exit Sub
End If
Next a
Next k

 
Answer #13    Answered By: Brooke Robertson     Answered On: Sep 20

adjust the offset:

ActiveCell(a+1, 5).Value = recdate.Text

 
Answer #14    Answered By: Trina King     Answered On: Sep 20

Should

ActiveCell(a, 5)

Be

ActiveCell.offset(a, 5)

 
Answer #15    Answered By: Baden Smith     Answered On: Sep 20

The answer has all ready been given - ActiveCell (A + 1, 5).Value. Your
loop statement is starting at zero, so even though you begin at row 1
the "a" variable is at 0. When your loop reaches 12 you are physically
at row 13, but the variable indicates 12. By adding one to the variable
you will be in the correct row.

 
Answer #16    Answered By: Zeke Thompson     Answered On: Sep 20

It appears I've run  into a small road block. Using the A + 1,
I can find  the row, find the column and the date  goes in perfect.
Like a dream. But now I have hopefully a simple problem  to fix..

Each PO number  could be entered  in multiple times (all in column A)
based on the number of scanners being sent in for repair.

This will find the first reference for the PO and change the date
the way that it should, but as I said if this PO is on the sheet
more than once, it will not find/change any of those.

Is it possible to have it look for any more references of that PO
number and change those as well?

 
Answer #17    Answered By: Amir Hashmi     Answered On: Sep 20

Simple;
Loop through the whole range of PO cells, don't exit the loop structure when
there is a 'hit' or 'match'.

 
Answer #18    Answered By: Jarryd Williams     Answered On: Sep 20

Look up help from within the VBE. Search for "FindPrevious". This will
show you how to accomplish what you want.

 
Didn't find what you were looking for? Find more on Find Value then Replace something else Or get search suggestion and latest updates.




Tagged: