Setting range for the VALUES obtained from formula

[Thulasiram]

Hello all,

Given below is a line that gives me constant error.
Set rng1 = rng.SpecialCells(xlConstants)
where rng
is Set rng = c(1, 2).Resize(1, 255)
Error was: No cells were found.

Later, I found that , though the cells had numbers, the numbers in the
cell were the resultant of forumla. I guess that is the reason for the
error. for example if E6 = 1; it is a resultant of E6 =
MMULT('AAA'!B2:BV74,'BBB'!B2:BV74); where AAA and BBB are sheet names.
All the cells in the range=>rng are governed by formula

I tried Set rng1 = rng.SpecialCells(xlConstants, xlTextValues) also.
Still the error persists. How to tweak the line such that the value
alone is read and NOT the formula governing it.

Any help in this issue will be greatly appreciated.

Thanks,
Thulasiram

 

[Thulasiram]

Okay!

Set rng1 = rng.SpecialCells(xlCellTypeAllFormatConditions) solves the
problem!

http://msdn2.microsoft.com/en-us/library/aa213567(office.11).aspx#

For more info on specialcells, refer the link given above.. It is
great..

 

[Thulasiram]

Hello all,

If cell.Value < 0 Or cell.Value > 0 Then
sh.Cells(1, cell.Column).Interior.ColorIndex = 3
End if

My previous case and the solution I found out are given below. In those
cases they were ranges. So, speciallcells worked. But, in this case, it
is cells.. Though the cell's value is numerical, it is governed by the
formula, so the condition in the loop is not executed.. Any idea how to
rectify it?

Thanks,
Thulasiram



_______________________________________________________________
Thulasiram wrote:
Okay!

Set rng1 = rng.SpecialCells(xlCellTypeAllFormatConditions) solves the
problem!

http://msdn2.microsoft.com/en-us/library/aa213567(office.11).aspx#

For more info on specialcells, refer the link given above.. It is
great..

___________________________________________________________
Thulasiram wrote:
Hello all,

Given below is a line that gives me constant error.
Set rng1 = rng.SpecialCells(xlConstants)
where
rng
is Set rng = c(1, 2).Resize(1, 255)
Error was: No cells were found.

Later, I found that , though the cells had numbers, the numbers in
the
cell were the resultant of forumla. I guess that is the reason for
the
error. for example if E6 = 1; it is a resultant of E6 =
MMULT('AAA'!B2:BV74,'BBB'!B2:BV74); where AAA and BBB are sheet
names.
All the cells in the range=rng are governed by formula

I tried Set rng1 = rng.SpecialCells(xlConstants, xlTextValues) also.
Still the error persists. How to tweak the line such that the value
alone is read and NOT the formula governing it.

Any help in this issue will be greatly appreciated.

Thanks,
Thulasiram

 

[Tom Ogilvy]

xlCellTypeAllFormatConditions doesn't make any sense if you say they are
produced by formulas then it should be


Set rng1 = rng.SpecialCells(xlFormulas)

or

Set rng1 = rng.SpecialCells(xlFormulas, xlNumbers)

or

Set rng1 = rng.SpecialCells(xlFormulas, xlTextValues)

--------------------

If cell.Value < 0 Or cell.Value > 0 Then
sh.Cells(1, cell.Column).Interior.ColorIndex = 3
End if

what are you trying to test for?

If cell.Value <> 0 then
sh.Cells(1, cell.Column).Interior.ColorIndex = 3
End if

perhaps. The code does not check or care how the value was produced
(constant or formula).

 

[Thulasiram]

Dear Tom,

I implemented Set rng1 = rng.SpecialCells(xlFormulas, xlNumbers) and
this solves all the questions in this thread..

I deeply appreciate your help.

Thanks,
Thulasiram