Is there a way to modify the Find section of my code (below) so that it will
look for the value starting from the bottom of the range.
There may be more than one occurrence of the InvNum and I want it to find
the last occurrence.
(Maybe there's an alternative if modifying the Find code doesn't do it?)

Dim InvName
Dim InvDate
Dim InvNum
Dim InvAmount
'The values in these ranges are determined by the Invoice that's opened.
Set InvName = Range("D13")
Set InvDate = Range("I4")
Set InvNum = Range("I2")
Set InvAmount = Range("J48")

Windows("Records (CURRENT YEAR) Modifying.xls").Activate
With Sheets("Tax Invoice Records").Range("B21:B1000")
.Find(InvNum, LookIn:=xlValues).Select
ActiveCell.Offset(0, 1) = InvDate
ActiveCell.Offset(0, 2) = InvName
ActiveCell.Offset(0, 3) = InvAmount
End With

Rob

Rob,

To search in reverse you could use a for next loop:

For x = 1000 To 21 Step -1
Cells(x, 2).Select '< Starts at B1000
ActiveCell.Offset(0, 1) = InvDate
ActiveCell.Offset(0, 2) = InvName
ActiveCell.Offset(0, 3) = InvAmount
Next

This starts in B1000 and each pass rounf the loop x reduces by 1.

Mike

"RobN" wrote:

Is there a way to modify the Find section of my code (below) so that it will
look for the value starting from the bottom of the range.
There may be more than one occurrence of the InvNum and I want it to find
the last occurrence.
(Maybe there's an alternative if modifying the Find code doesn't do it?)

Dim InvName
Dim InvDate
Dim InvNum
Dim InvAmount
'The values in these ranges are determined by the Invoice that's opened.
Set InvName = Range("D13")
Set InvDate = Range("I4")
Set InvNum = Range("I2")
Set InvAmount = Range("J48")

Windows("Records (CURRENT YEAR) Modifying.xls").Activate
With Sheets("Tax Invoice Records").Range("B21:B1000")
.Find(InvNum, LookIn:=xlValues).Select
ActiveCell.Offset(0, 1) = InvDate
ActiveCell.Offset(0, 2) = InvName
ActiveCell.Offset(0, 3) = InvAmount
End With

Rob

Thanks Mike, but I'm a bit of a dunce with loops.

I can't quite figure out how this loop is going to activate the cell that
matches the InvNum variable, so that it can use the ActiveCell.Offsets to
paste the appropriate values.

In summary, I need the code to find a Cell in column B that matches the
InvNum variable.
Then, when found, it needs to put the values from the other variables into
the adjacent cells in that row as determined by the Offset rules. (And it
needs to find relevant cell going upwards - or at least the last incidence
of that InvNum variable value.)

Rob

"Mike H" wrote in message
...
Rob,

To search in reverse you could use a for next loop:

For x = 1000 To 21 Step -1
Cells(x, 2).Select '< Starts at B1000
ActiveCell.Offset(0, 1) = InvDate
ActiveCell.Offset(0, 2) = InvName
ActiveCell.Offset(0, 3) = InvAmount
Next

This starts in B1000 and each pass rounf the loop x reduces by 1.

Mike

"RobN" wrote:

Is there a way to modify the Find section of my code (below) so that it
will
look for the value starting from the bottom of the range.
There may be more than one occurrence of the InvNum and I want it to find
the last occurrence.
(Maybe there's an alternative if modifying the Find code doesn't do it?)

Dim InvName
Dim InvDate
Dim InvNum
Dim InvAmount
'The values in these ranges are determined by the Invoice that's opened.
Set InvName = Range("D13")
Set InvDate = Range("I4")
Set InvNum = Range("I2")
Set InvAmount = Range("J48")

Windows("Records (CURRENT YEAR) Modifying.xls").Activate
With Sheets("Tax Invoice Records").Range("B21:B1000")
.Find(InvNum, LookIn:=xlValues).Select
ActiveCell.Offset(0, 1) = InvDate
ActiveCell.Offset(0, 2) = InvName
ActiveCell.Offset(0, 3) = InvAmount
End With

Rob

First, some comments.

Instead of activating the window and depending on the correct worksheet being
the active worksheet, you can work on that worksheet directly. And I wouldn't
go through the windows collection. If the user window|new window, you may not
find a window with that name. I'd go through the workbooks collection. It's
safer.

And it's always better to specify all the parameters in the .find statement. If
you don't, then excel will use the settings used by the last .find. And that
can by by the user or by any code that the user ran.

And you can pass a parm to the .find statement to look upward (xlprevious). And
if you start at the top and look up, you're looking for the last value in the
range.

This may help you:

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim FoundCell As Range
Dim InvNum As String 'long????
Dim InvDate As Date
Dim InvName As String
Dim InvAmount As Double

'change this for your worksheet name.
Set wks = Workbooks("Records (CURRENT YEAR) Modifying.xls") _
.Worksheets("sheet9999")

'testdata
InvNum = "asdf0"
InvDate = Date
InvName = "hi there"
InvAmount = 9999.99

With wks
'.Select 'you don't need this here
With .Range("b21:b1000")
Set FoundCell = .Cells.Find(what:=InvNum, _
after:=.Cells(1), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlPrevious, _
MatchCase:=False)
If FoundCell Is Nothing Then
MsgBox "not found"
Else
FoundCell.Offset(0, 1).Value = InvDate
FoundCell.Offset(0, 2).Value = InvName
FoundCell.Offset(0, 3).Value = InvAmount
End If
End With
End With

End Sub

And if I wanted to find the first in that range, I'd start at the bottom and
look for the next (xlnext).

Set FoundCell = .Cells.Find(what:=InvNum, _
after:=.Cells(.cells.count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)


RobN wrote:

Is there a way to modify the Find section of my code (below) so that it will
look for the value starting from the bottom of the range.
There may be more than one occurrence of the InvNum and I want it to find
the last occurrence.
(Maybe there's an alternative if modifying the Find code doesn't do it?)

Dim InvName
Dim InvDate
Dim InvNum
Dim InvAmount
'The values in these ranges are determined by the Invoice that's opened.
Set InvName = Range("D13")
Set InvDate = Range("I4")
Set InvNum = Range("I2")
Set InvAmount = Range("J48")

Windows("Records (CURRENT YEAR) Modifying.xls").Activate
With Sheets("Tax Invoice Records").Range("B21:B1000")
.Find(InvNum, LookIn:=xlValues).Select
ActiveCell.Offset(0, 1) = InvDate
ActiveCell.Offset(0, 2) = InvName
ActiveCell.Offset(0, 3) = InvAmount
End With

Rob

--

Dave Peterson

Thanks Dave,

I appreciate your comments. Very helpful, thanks. (I never thought about the
possible problems if all parameters weren't specified. I thought I was
being smart by deleting what I thought wasn't necessary.)

Your code works great and I should be able to fit it in with the rest of my
code.

Rob

"Dave Peterson" wrote in message
...
First, some comments.

Instead of activating the window and depending on the correct worksheet
being
the active worksheet, you can work on that worksheet directly. And I
wouldn't
go through the windows collection. If the user window|new window, you may
not
find a window with that name. I'd go through the workbooks collection.
It's
safer.

And it's always better to specify all the parameters in the .find
statement. If
you don't, then excel will use the settings used by the last .find. And
that
can by by the user or by any code that the user ran.

And you can pass a parm to the .find statement to look upward
(xlprevious). And
if you start at the top and look up, you're looking for the last value in
the
range.

This may help you:

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim FoundCell As Range
Dim InvNum As String 'long????
Dim InvDate As Date
Dim InvName As String
Dim InvAmount As Double

'change this for your worksheet name.
Set wks = Workbooks("Records (CURRENT YEAR) Modifying.xls") _
.Worksheets("sheet9999")

'testdata
InvNum = "asdf0"
InvDate = Date
InvName = "hi there"
InvAmount = 9999.99

With wks
'.Select 'you don't need this here
With .Range("b21:b1000")
Set FoundCell = .Cells.Find(what:=InvNum, _
after:=.Cells(1), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlPrevious, _
MatchCase:=False)
If FoundCell Is Nothing Then
MsgBox "not found"
Else
FoundCell.Offset(0, 1).Value = InvDate
FoundCell.Offset(0, 2).Value = InvName
FoundCell.Offset(0, 3).Value = InvAmount
End If
End With
End With

End Sub

And if I wanted to find the first in that range, I'd start at the bottom
and
look for the next (xlnext).

Set FoundCell = .Cells.Find(what:=InvNum, _
after:=.Cells(.cells.count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)


RobN wrote:

Is there a way to modify the Find section of my code (below) so that it
will
look for the value starting from the bottom of the range.
There may be more than one occurrence of the InvNum and I want it to find
the last occurrence.
(Maybe there's an alternative if modifying the Find code doesn't do it?)

Dim InvName
Dim InvDate
Dim InvNum
Dim InvAmount
'The values in these ranges are determined by the Invoice that's opened.
Set InvName = Range("D13")
Set InvDate = Range("I4")
Set InvNum = Range("I2")
Set InvAmount = Range("J48")

Windows("Records (CURRENT YEAR) Modifying.xls").Activate
With Sheets("Tax Invoice Records").Range("B21:B1000")
.Find(InvNum, LookIn:=xlValues).Select
ActiveCell.Offset(0, 1) = InvDate
ActiveCell.Offset(0, 2) = InvName
ActiveCell.Offset(0, 3) = InvAmount
End With

Rob

--

Dave Peterson