A bit of Syntax help please...
 

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

A bit of Syntax help please...
 

Hi Ste Mac,

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


---
Regards,
Norman



"ste mac" wrote in message
oups.com...
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

A bit of Syntax help please...
 

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" wrote:

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

A bit of Syntax help please...
 

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

JLatham (removethis) wrote:
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" wrote:

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

A bit of Syntax help please...
 

Sorry for the late reply.

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

cheers

ste



JLatham (removethis) wrote:
Well it's tough to be absolute unless we know the range that the value in
columns I and J may be -
But here's how to save the values
minValue = Sheets("Main").Range("I" & Rows.Count).End(xlUp).value
maxValue = Sheets("Main").Range("J" & Rows.Count).End(xlUp).value

now the problem is getting last data to be between them? Since we don't
know how much variance there may be between minValue and maxValue, then it's
hard to guess what to do with this. But here is one attempt:

lastdata = minValue + 1
If lastData maxValue Then
lastdata = minValue
End If

That sets lastdata to 1 greater than minValue (from col I) to begin with,
then tests to see if that is greater than maxValue (from col J). If it is
greater, then minValue = maxValue, and so we reset it back to = minValue
which is also =maxValue

Another approach would be similar to what I proposed befo finding the
median value of the two:
lastdata = (minValue + maxValue)/2

Remember that once you get last data and try to .Find it, you only get
success if there is an exact match found, otherwise the .Find returns an
error. Your myfound will be null if no match found.

"ste mac" wrote:

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

JLatham (removethis) wrote:
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" wrote:

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