A bit of Syntax help please...

[ste mac]

Hi, I have the code below, but I do not know how to adjust the syntax.

How do I get 'lastdata' to be equal to, greater than and equal to,
less than two cell values?

cheers

ste

Sub findlatest()
Application.ScreenUpdating = false
Dim myrng As Range
Dim myfound As Range
Dim lastdata As Long

Sheets("Main").Select

'Problem here!
lastdata= (>= Sheets("Main").Range("I65536").End(xlUp).Value And
lastdata <= Sheets("Main").Range("J65536").End(xlUp).Value )

Set myrng = Sheets("BaseData").Columns("B:U")

Set myfound = myrng.Find(What:=lastdata, After:=myrng.Cells(1),
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlPrevious, MatchCase:=False)

Sheets("Main").Range("L65536").End(xlUp).Value = myfound.Row - 1

Application.ScreenUpdating = True
End Sub

 

[Norman Jones]

Hi Ste Mac,

It would be helpful if you were to explain, in words, what your code is
intended to achieve.

 

[Guest]

Not sure how to advise you because I'm not sure what your values in the last
row of columns I and J may look like.

But possibly something like this would work??
lastdata = (Sheets("Main").Range("I" & Rows.Count).End(xlUp).Value + _
Sheets("Main").Range("J" & Rows.Count).End(xlUp).Value) / 2

That would take the two values, add them together and divide by two to give
an average which should be a number between the two, or equal two if both
numbers happen to be the same.

P.S. Range("J" & Rows.Count) is same as Range("J65536") without being
limited to a version of Excel where 65536 is the max row count.

 

[ste mac]

Sorry guys for the poor explanation.

The code looks at data in another sheet '("BaseData")' and searches
backwards
through the range 'Sheets("BaseData").Columns("B:U")' to find
'lastdata'.

My problem is that 'lastdata' will be equal to or between...two
values...
Sheets("Main").Range("I65536").End(xlUp).Value... and
....Sheets("Main").Range("J65536").End(xlUp).Value)

The code searches backwards in the range on the sheet "BaseData"
and finds the first value in the range that is equal to/between:
Sheets("Main").Range("I65536").End(xlUp).Value &
Sheets("Main").Range("J65536").End(xlUp).Value)

If I change the code to e.g 'lastdata = 14070' (which is inbetween the
values required) then the code works fine and will search backwards and
locate the first instance.

The problem I have is that I don't know how to declare 'lastdata' as
any value equal to
or between the two values required.

once again thanks for any help...

ste

 

[ste mac]

Sorry for the late reply.

JLatham, thankyou very much, I now have something to work with, and a
good explanation.

cheers

ste