Logo 
Search:

MS Office Answers

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds
  Question Asked By: Jeff Cunningham   on Sep 20 In MS Office Category.

  
Question Answered By: Abraham Lopez   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

Share: 

 

This Question has 17 more answer(s). View Complete Question Thread

 
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: