A bit of Syntax help please...

  • Thread starter Thread starter ste mac
  • Start date Start date
S

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
 
Hi Ste Mac,

It would be helpful if you were to explain, in words, what your code is
intended to achieve.
 
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.
 
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
 
Sorry for the late reply.

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

cheers

ste
 
Back
Top