Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting range for the VALUES obtained from formula
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Setting range for the VALUES obtained from formula
Okay!
Set rng1 = rng.SpecialCells(xlCellTypeAllFormatConditions) solves the problem! http://msdn2.microsoft.com/en-us/lib...fice.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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Seperating value of a cell from its governing formula
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/lib...fice.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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Seperating value of a cell from its governing formula
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). -- Regards, Tom Ogilvy "Thulasiram" wrote in message ups.com... 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/lib...fice.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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Seperating value of a cell from its governing formula
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 Tom Ogilvy wrote: 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). -- Regards, Tom Ogilvy "Thulasiram" wrote in message ups.com... 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/lib...fice.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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using cell reference in a formula instead of the obtained value | Excel Worksheet Functions | |||
freezing data in a cell which was obtained using a formula | Excel Programming | |||
Need formula help setting a range for calculations | Excel Worksheet Functions | |||
Setting dynamic range in a formula | Excel Worksheet Functions |