Range Problem

[jesmin]

Hi: I am selecting 2 ranges formed by union. 1 is working and other is NOT.
----Code:
Dim rng1, rng2, rngt As Variant
dim l1,l2 as variant
Set rng1 = _
Application.Union(Range("E:10:E25"), Range("G10:G25"))
rng1.FormulaR1C1 = "=RC[-3]"
Set rngt = Application.Union(Range("C11:c25"), rng1)

Set rng2 = _
Application.Union(Range("B10:B25), Range("D10:D25"))

l1 = Application.WorksheetFunction.Max(rng2)
Cells.Find(What = l1).Activate------working fine

l2 = Application.WorksheetFunction.Max(rngt)
Cells.Find(What = l2).Activate------Error: Object or With block not set

Why the 1st is working but no the 2nd one.

Thanks in advance

[Barb Reinhardt]

Could it be this line. E:10:E25 ???


Set rng1 = _
Application.Union(Range("E:10:E25"), Range("G10:G25"))


"jesmin" <[email protected]> wrote in
message news:[email protected]...
>
> Hi: I am selecting 2 ranges formed by union. 1 is working and other is
> NOT.
> ----Code:
> Dim rng1, rng2, rngt As Variant
> dim l1,l2 as variant
> Set rng1 = _
> Application.Union(Range("E:10:E25"), Range("G10:G25"))
> rng1.FormulaR1C1 = "=RC[-3]"
> Set rngt = Application.Union(Range("C11:c25"), rng1)
>
> Set rng2 = _
> Application.Union(Range("B10:B25), Range("D10:D25"))
>
> l1 = Application.WorksheetFunction.Max(rng2)
> Cells.Find(What = l1).Activate------working fine
>
> l2 = Application.WorksheetFunction.Max(rngt)
> Cells.Find(What = l2).Activate------Error: Object or With block not set
>
>
> Why the 1st is working but no the 2nd one.
>
> Thanks in advance
>
>
> --
> jesmin
> ------------------------------------------------------------------------
> jesmin's Profile:
> http://www.excelforum.com/member.php...o&userid=29540
> View this thread: http://www.excelforum.com/showthread...hreadid=492932
>

[jesmin]

Thanks. No syntex wise code is fine(I was typing actually).
Only the 2nd FIND case its not working. 1st cells.FIND working well.
2nd cells.find(what:=var).activate --this part is not working

Am I doing wrong in writing both cells.FIND similar way(Ommiting other find parameters).???
l1 = Application.WorksheetFunction.Max(rng2)
Cells.Find(What = l1).Activate------working fine

l2 = Application.WorksheetFunction.Max(rngt)
Cells.Find(What = l2).Activate------Error: Object or With block not set

[Rowan Drummond]

You would be better off cutting and pasting your code directly from the
VBE into your message. There are a number of typos in here which makes
it hard to say what the real problem is eg
Cells.Find(What = l1).Activate
should be
Cells.Find(What:= l1).Activate

Two pointers when using the find method. One it will remember the
settings from the last time it was used either manually or in a macro so
you should specify all relavant parameters.
Secondly you should cater for the fact that you may not find what you
are looking for. So your code would look something like:

Dim fndCell as range
Set fndCell = Cells.Find(What:=l2, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext _
, MatchCase:=False)
If not fndCell is nothing then
fndcell.activate
else
msgbox "Not Found"
End if

Hope this helps
Rowan

jesmin wrote:
> Thanks. No syntex wise code is fine(I was typing actually).
> Only the 2nd FIND case its not working. 1st cells.FIND working well.
> 2nd cells.find(what:=var).activate --this part is not working
>
> Am I doing wrong in writing both cells.FIND similar way(Ommiting other
> find parameters).???
> l1 = Application.WorksheetFunction.Max(rng2)
> Cells.Find(What = l1).Activate------working fine
>
> l2 = Application.WorksheetFunction.Max(rngt)
> Cells.Find(What = l2).Activate------Error: Object or With block not set
>
>

[jesmin]

Hi Rowan:The code:
Dim rng1, rng2, rngt,lv,hv,yr1,yr2 As Variant
Set rng1 = _
Application.Union(Range("E" & sr & ":" & "E" & num), Range("G" & sr & ":" & "G" & num))
rng1.FormulaR1C1 = "=RC[-3]"

Set rng2 = _
Application.Union(Range("B" & sr & ":" & "B" & num), Range("D" & sr & ":" & "D" & num))
lv = Application.WorksheetFunction.Min(rng2)
yr1 = valcal(lv, rng2)

Set rngt = Application.Union(Range("C" & sr + 1 & ":" & "C" & num), rng1)
hv = Application.WorksheetFunction.Max(rngt)--It has a value
yr2 = valcal(hv, rngt) ----not working here

------------
Function valcal(val, mrn)
Dim r, c As Integer
Dim yr As String
Dim fndCell As Range
With mrn
Set fndCell = Cells.Find(What:=val, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext _
, MatchCase:=False)
If Not fndCell Is Nothing Then
r = fndCell.Row
c = fndCell.Column
yr = Cells(r, 1).Value
yr = Right("0" & c, 2) & yr
Else
MsgBox "Not Found"
End If
Stop
End With
End Function


--result: In 2nd call, FIND not working

[Rowan Drummond]

Hi Jesmin

Worked fine for me. Are you getting an error message, unexpected
results, etc etc?

Regards
Rowan

jesmin wrote:
> Hi Rowan:The code:
> Dim rng1, rng2, rngt,lv,hv,yr1,yr2 As Variant
> Set rng1 = _
> Application.Union(Range("E" & sr & ":" & "E" & num), Range("G" & sr &
> ":" & "G" & num))
> rng1.FormulaR1C1 = "=RC[-3]"
>
> Set rng2 = _
> Application.Union(Range("B" & sr & ":" & "B" & num), Range("D" & sr &
> ":" & "D" & num))
> lv = Application.WorksheetFunction.Min(rng2)
> yr1 = valcal(lv, rng2)
>
> Set rngt = Application.Union(Range("C" & sr + 1 & ":" & "C" & num),
> rng1)
> hv = Application.WorksheetFunction.Max(rngt)--It has a value
> yr2 = valcal(hv, rngt) ----not working here
>
> ------------
> Function valcal(val, mrn)
> Dim r, c As Integer
> Dim yr As String
> Dim fndCell As Range
> With mrn
> Set fndCell = Cells.Find(What:=val, LookIn:=xlValues, LookAt:= _
> xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext _
> , MatchCase:=False)
> If Not fndCell Is Nothing Then
> r = fndCell.Row
> c = fndCell.Column
> yr = Cells(r, 1).Value
> yr = Right("0" & c, 2) & yr
> Else
> MsgBox "Not Found"
> End If
> Stop
> End With
> End Function
>
>
> --result: In 2nd call, FIND not working
>
>

[jesmin]

Hi rown:

In the 2nd call, fndcell is empty. The range selection is perfect. Its selecting the required columns. Only it canot find the value.
can you send me your code. Is it something during passing the range as parameter.

Thanks

[Rowan Drummond]

As it is your function is not passing a value back to the main
procedure. Try changing it to:

Function valcal(val, mrn)
Dim r, c As Integer
Dim yr As String
Dim fndCell As Range
With mrn
Set fndCell = .Find(What:=val, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext _
, MatchCase:=False)
If Not fndCell Is Nothing Then
r = fndCell.Row
c = fndCell.Column
yr = Cells(r, 1).Value
valcal = Right("0" & c, 2) & yr '<<changed
End If
End With
End Function

Hope this helps
Rowan

jesmin wrote:
> Hi rown:
>
> In the 2nd call, fndcell is empty. The range selection is perfect. Its
> selecting the required columns. Only it canot find the value.
>
> Thanks
>
>

[jesmin]

Hi Rown:

I found the problem. Its the data type. I declared the variable lv, hv as variant. In 2nd case, hv=1.60026386 and it failed. I put a smaller number 1.3098 and it ran fine in 2nd call. So I changed them to double. Now I get another value for hv as 1.3000687785 and this time it failed again. I will better format the data to 4 decimal first and then will calculate. Is not 1.3000687785 is a double? What is the safe data type that I can declare in case of decimals.

Thanks

[Rowan Drummond]

1.3000687785 is a double. You should try being more specific with the
way you declare your variables. If you use:
Dim rng1, rng2, rngt,lv,hv,yr1,yr2 As Double
Then yr2 will be a double while rng1, rng2, rngt etc will all be
Variants. You should use:
Dim rng1 as range
dim rng2 as range
dim lv as double
etc

or
dim rng1 as range, rng2 as range, lv as double etc

Hope this helps
Rowan

jesmin wrote:
> Hi Rown:
>
> I found the problem. Its the data type. I declared the variable lv, hv
> as variant. In 2nd case, hv=1.60026386 and it failed. I put a smaller
> number 1.3098 and it ran fine in 2nd call. So I changed them to double.
> Now I get another value for hv as 1.3000687785 and this time it failed
> again. I will better format the data to 4 decimal first and then will
> calculate. Is not 1.3000687785 is a double? What is the safe data type
> that I can declare in case of decimals.
>
> Thanks
>
>

[jesmin]

Hi Rown: Thanks for reply. I modified as you said. Now its only the cells.find() thats not working. I am using this code(cells.find()) directly not in a function.
I have to find min and max cell from col E,G. These 2 cols are filled with data by formula as shown below. The data are huge big decimal numbers.('num' is a variable to count last row of current region)
dim rng1 as range
dim hpc as double
Set rng1 = _
Application.Union(Range("E" & sr & ":" & "E" & num), Range("G" & sr & ":" & "G" & num)
rng1.FormulaR1C1 = "=(RC[-1]-RC[-3])*100/(RC[-3]))"

hpc = Application.WorksheetFunction.Max(rng1)
--upto this works fine. Following not working:
With rng1
Set fndcell = Cells.Find(What:=hpc, LookIn:=xlValues, LookAt:= xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext , MatchCase:=False)
end with
--error: min value hpc is a very big double number and find() is not working for this big number. fndcell=empty

I tried many ways.
(a)Formatted data like:rng1.numberformat="#.####". here data are being formatted but when trying to find, its not finding. In sheet, the data are still original big decimal whereas formatted data are 4 decimals.
(b)Using FIXED:
rng1.FormulaR1C1="=FIXED((RC[-1]-RC[-3])*100/(RC[-3]))),4)"
In this case min() does not work even.
hpc = Application.WorksheetFunction.Max(rng1)
--hpc=0

--Please help. I spent almost whole day nothing happened.

[Rowan Drummond]

Try something like:

Dim rng1 As Range
Dim hpc As Double
Dim hpcStr As String
Dim fndCell As Range
Set rng1 = _
Application.Union(Range("E" & sr & ":" & "E" & num) _
, Range("G" & sr & ":" & "G" & num))
rng1.FormulaR1C1 = "=(RC[-1]-RC[-3])*100/(RC[-3])"
rng1.NumberFormat = "#,##0.0000"

hpc = Application.WorksheetFunction.Max(rng1)
hpcStr = Format(hpc, "#,##0.0000")

With rng1
Set fndCell = .Find(What:=hpcStr, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase:=False)
End With

Regards
Rowan

jesmin wrote:
> Hi Rown: Thanks for reply. I modified as you said. Now its only the
> cells.find() thats not working. I am using this code(cells.find())
> directly not in a function.
> I have to find min and max cell from col E,G. These 2 cols are filled
> with data by formula as shown below. The data are huge big decimal
> numbers.('num' is a variable to count last row of current region)
> dim rng1 as range
> dim hpc as double
> Set rng1 = _
> Application.Union(Range("E" & sr & ":" & "E" & num), Range("G" & sr &
> ":" & "G" & num)
> rng1.FormulaR1C1 = "=(RC[-1]-RC[-3])*100/(RC[-3]))"
>
> hpc = Application.WorksheetFunction.Max(rng1)
> --upto this works fine. Following not working:
> With rng1
> Set fndcell = Cells.Find(What:=hpc, LookIn:=xlValues, LookAt:= xlPart,
> SearchOrder:=xlByColumns, SearchDirection:=xlNext , MatchCase:=False)
> end with
> --error: min value hpc is a very big double number and find() is not
> working for this big number. fndcell=empty
>
> I tried many ways.
> (a)Formatted data like:rng1.numberformat="#.####". here data are being
> formatted but when trying to find, its not finding. In sheet, the data
> are still original big decimal whereas formatted data are 4 decimals.
> (b)Using FIXED:
> rng1.FormulaR1C1="=FIXED((RC[-1]-RC[-3])*100/(RC[-3]))),4)"
> In this case min() does not work even.
> hpc = Application.WorksheetFunction.Max(rng1)
> --hpc=0
>
> --Please help. I spent almost whole day nothing happened.
>
>

[jesmin]

Rowan:
Thanks a thanks thanks. Its working. I am getting all my values and formatted on the report sheet. I will contact you tomorrow.

I really appreciate your contribution.

-----

[Rowan Drummond]

You're welcome. Thanks for the feedback.

jesmin wrote:
> Rowan:
> Thanks a thanks thanks. Its working. I am getting all my values and
> formatted on the report sheet. I will contact you tomorrow.
>
> I really appreciate your contribution.
>
> -----
>
>

[jesmin]

Hi Rowan:

Ref. to my last prob that you solved. Today I am having a strange prob. I am calculating min and max values in some excel col range s and finding them and locating the cells. The values are being too big double, I was formatting them as: "0.0000" and "0.0"(ex:1.2034,-1.2,0.9 etc).
Prob: I have 2 values min -5.4 and max 5.4. When using FIND() for both values it refering to the same cell(say cells(20,2) which one comes first.(search by column).
So the max & min works fine. But for -5.4 and 5.4 why its refering to same cell?
fndcell = .Find(What:=hpcStr, .....your code below.

Note: The formatted data are 1.2345,-1.3340(I used "0.0000"--Am I right, how about +-sign) and 1.3,1.0,-1.1,-0.9(I used "0.0" Am I OK).

Thanks again

[Rowan Drummond]

Try changing the search conditions to match the entire cell contents eg:

'------------------------------------------------
Dim rng1 As Range
Dim hpc As Double
Dim hpcStr As String
Dim fndCell As Range
Set rng1 = _
Application.Union(Range("E" & sr & ":" & "E" & num) _
, Range("G" & sr & ":" & "G" & num))
rng1.FormulaR1C1 = "=(RC[-1]-RC[-3])*100/(RC[-3])"
rng1.NumberFormat = "#,##0.0000"

hpc = Application.WorksheetFunction.Max(rng1)
hpcStr = Format(hpc, "#,##0.0000")

With rng1
Set fndCell = .Find(What:=hpcStr, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase:=False)
End With
'-------------------------------------------------


LookAt:=xlPart changed to LookAt:=xlWhole

Hope this helps
Rowan


jesmin wrote:
> Hi Rowan:
>
> Ref. to my last prob that you solved. Today I am having a strange
> prob. I am calculating min and max values in some excel col range s and
> finding them and locating the cells. The values are being too big
> double, I was formatting them as: "0.0000" and "0.0"(ex:1.2034,-1.2,0.9
> etc).
> Prob: I have 2 values min -5.4 and max 5.4. When using FIND() for both
> values it refering to the same cell(say cells(20,2) which one comes
> first.(search by column).
> So the max & min works fine. But for -5.4 and 5.4 why its refering to
> same cell?
> fndcell = .Find(What:=hpcStr, .....your code below.
>
> Note: The formatted data are 1.2345,-1.3340(I used "0.0000"--Am I
> right, how about +-sign) and 1.3,1.0,-1.1,-0.9(I used "0.0" Am I OK).
>
> Thanks again
>
>

[jesmin]

Hi Rowan: Thanks for the help. Now it looks finally OK. I am exhausted. What is the diff between format "0.0" and "#0.0".
My data are like 5.1566,-5.1456,0.9,-1.2 etc. Both way, I am getting right format.

Thanks again

[Rowan Drummond]

Hi Jesmin

From Excel help:
# displays only significant digits and does not display insignificant zeros.
0 (zero) displays insignificant zeros if a number has fewer digits than
there are zeros in the format

So for your data it should make no difference. If you used the format
00.0 then 4.5456 would display as 04.5. While if you used #0.0 as the
format it would display as 4.5.

I am not completely clear on what you are doing but if you are just
wanting to set the format of the max and min values in rng1 then I would
probably approach it differently. I would use conditional formatting to
do the formatting which means that if any of the values in the range
change then the formatting will also automatically change. E.G.

'--------------------------------------------
Dim rng1 As Range
Dim hpc As Double
Dim hpcStr As String
Dim fndCell As Range
Set rng1 = _
Application.Union(Range("E" & sr & ":" & "E" & num) _
, Range("G" & sr & ":" & "G" & num))

With rng1
.FormulaR1C1 = "=(RC[-1]-RC[-3])*100/(RC[-3])"
.NumberFormat = "#,##0.0000"
.Name = "TheRange"
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=RC=MIN(TheRange)"
.FormatConditions(1).Interior.ColorIndex = 36
.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=RC=MAX(TheRange)"
.FormatConditions(2).Interior.ColorIndex = 40
End With
'-----------------------------------------------------------------


Hope this helps
Rowan

jesmin wrote:
> Hi Rowan: Thanks for the help. Now it looks finally OK. I am exhausted.
> What is the diff between format "0.0" and "#0.0".
> My data are like 5.1566,-5.1456,0.9,-1.2 etc. Both way, I am getting
> right format.
>
> Thanks again
>
>

[Rowan Drummond]

Try something like:

Sub PgBreaks()
Dim fndCell As Range
Dim pb As Long
Dim firstAddress As String

With ActiveSheet.Cells
Set fndCell = .Find(what:="J/J", After:=Range("A1"), _
LookIn:=xlValues, LookAt:=xlPart, _
SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False)
If Not fndCell Is Nothing Then
firstAddress = fndCell.Address
Do
If pb = 2 Then
ActiveSheet.HPageBreaks.Add Before:=fndCell
pb = 1
Else
pb = pb + 1
End If
Set fndCell = .FindNext(fndCell)
Loop While Not fndCell Is Nothing _
And fndCell.Address <> firstAddress
End If
End With

End Sub

Hope this helps
Rowan

jesmin wrote:
> Hi Rowan:
> Thanks for everything. I will not touch the code any more as it is
> creating my report. Thanks all your help and your patience.
> Now I am trying to finalize it this way. I want to put only 2 reports
> per page. Say after 2 reports, I will give a page break at line number
> 40. I know that 2 reports will take maximum 4o lines(including heading
> etc). Each report has a word "J/J" in header line(That I can search).
> So in each page I will have 2 headers with text "J/J". I was thinking
> to code this way:
> ------------------------------------
> dim i as Integer
> dim fnd as Boolean
> dim fndrng as Range
>
> Set fndrng = activesheet.usedrange.find(what:="J/J")
>
> do while(fnd=true)
> i=i+1
> if (i=2) then
> activesheet.pagebreak.rows=40
> i=0
> end if
> set activesheet.usedrange.findNext(what:="J/J")---I dont know exact
> code.
> end loop
>
> --I know the above approach is not well enough. I would request you a
> better idea.
>
> Thanks again
>
>