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 before: 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
> > > >
> > > >
> >
> >
|
Similar Threads
