Range Problem
 

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

Range Problem
 

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


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


"jesmin" wrote in
message ...

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

Range Problem
 

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
------------------------------------------------------------------------
jesmin's Profile: http://www.excelforum.com/member.php...o&userid=29540
View this thread: http://www.excelforum.com/showthread...hreadid=492932

Range Problem
 

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

Range Problem
 

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
------------------------------------------------------------------------
jesmin's Profile: http://www.excelforum.com/member.php...o&userid=29540
View this thread: http://www.excelforum.com/showthread...hreadid=492932

Range Problem
 

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

Range Problem
 

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
------------------------------------------------------------------------
jesmin's Profile: http://www.excelforum.com/member.php...o&userid=29540
View this thread: http://www.excelforum.com/showthread...hreadid=492932

Range Problem
 

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

Range Problem
 

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
------------------------------------------------------------------------
jesmin's Profile: http://www.excelforum.com/member.php...o&userid=29540
View this thread: http://www.excelforum.com/showthread...hreadid=492932

Range Problem
 

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

Range Problem
 

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
------------------------------------------------------------------------
jesmin's Profile: http://www.excelforum.com/member.php...o&userid=29540
View this thread: http://www.excelforum.com/showthread...hreadid=492932

Range Problem
 

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.

Range Problem
 

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
------------------------------------------------------------------------
jesmin's Profile: http://www.excelforum.com/member.php...o&userid=29540
View this thread: http://www.excelforum.com/showthread...hreadid=492932

Range Problem
 

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.

-----

Range Problem
 

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
------------------------------------------------------------------------
jesmin's Profile: http://www.excelforum.com/member.php...o&userid=29540
View this thread: http://www.excelforum.com/showthread...hreadid=492932

Range Problem
 

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

Range Problem
 

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


--
jesmin
------------------------------------------------------------------------
jesmin's Profile: http://www.excelforum.com/member.php...o&userid=29540
View this thread: http://www.excelforum.com/showthread...hreadid=492932

Range Problem
 

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

Range Problem
 

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


--
jesmin
------------------------------------------------------------------------
jesmin's Profile: http://www.excelforum.com/member.php...o&userid=29540
View this thread: http://www.excelforum.com/showthread...hreadid=492932

Range Problem
 

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 Befo=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