Microsoft Excel - How to set formula in a particular cell only - excel 2007

Asked By Ajain A K P on 22-Aug-11 03:20 AM
Hi,


I want to assign one Max formula in this particular cell (M3)

I tried with this formula MAX(H:H), it is working fine and result is coming well and I recored into macro,

the code I think, ActiveCell.FormulaR1C1 = "=MAX(C[-5])" but whenever I tried to run macro, this result is 

displaying some other cell, may be what the cell I selected before running the macro that cell will be 

containg the result but I want display this result only on M3, what is the macro code for that ? somebody please help me...


Thanks in advanve.
Venkat K replied to Ajain A K P on 22-Aug-11 03:23 AM
Use absolute reference of a cell if you don't want to change by reference:
You need to use $ sign before the cell number to make it as absolute:
MAX($H$1:$H$10)

Thanks
Jitendra Faye replied to Ajain A K P on 22-Aug-11 03:24 AM
You can use cell function-

Excel’s CELL function is one of a group of “Information Functions” that can be used to find out information about a specific cell, worksheet or workbook.
 


The CELL function's job is to give out information about a cell such as its formatting, the type of data it contains, and whether or not the cell is locked or protected.


The syntax for the CELL function is:


= CELL (info_type , reference )

Hope this will help you.

Pichart Y. replied to Ajain A K P on 22-Aug-11 03:42 AM
Hi Ajain A K P,

Your assumption is correct, with this code ActiveCell.FormulaR1C1 = "=MAX(C[-5])" you will get the max of the 5th column in front of your activecell, if you activecell is in cell F1 then the result will be max of column A but it your activecell is E1 then it will error, for there is not the 5th column in front of column E.

To solve your problem, just add this line "range("M3").select" before your code

so here it is....

------------------------------------
Sub macro1()
range("M3").select
ActiveCell.FormulaR1C1 = "=MAX(C[-5])"
end sub
-------------------------------------

Pichart Y.
TSN ... replied to Ajain A K P on 22-Aug-11 04:33 AM
hi..

Refere the link below..

http://office.microsoft.com/en-us/excel-help/apply-data-validation-to-cells-HP010072600.aspx
Anoop S replied to Ajain A K P on 22-Aug-11 05:08 AM
'Select the cell on which you want to put formula
Cells(myrow, myCol).Select

or try by using application.worksheetfunction
Radhika roy replied to Ajain A K P on 22-Aug-11 09:38 AM

Using Excel 2007's CELL Function you can complete this task-

Note: For help with this example, see the image to the right.

  1. Type a number, such as " 23 ", into cell D1. 

  2. Click on cell E1 - the location where the results will be displayed.

  3. Click on the Formulas tab.

  4. Choose More Functions > Information from the ribbon to open the function drop down list.

  5. Click on CELL in the list to bring up that function's dialog box. 

  6. Enter the word "type" on the Info_type line of the dialog box

  7. Click on the Reference line in the dialog box.

  8. Click on cell D1 in the spreadsheet to enter the cell reference into the dialog box.

  9. Click OK.

  10. The letter " v " should appear in cell E1 to indicate that the data in the cell is a value.

  11. The complete function = CELL ( " type " , D1 ) appears in the formula bar above the worksheet when you click on cell E1.

Note:

  • If cell D1 was empty, the letter " b " would appear in cell E1 to indicate that the cell was blank.
  • If cell D1 contained a word of text, the letter " l " would appear in cell E1 to indicate that the cell contained a label.
  • If cell D1 contained a date, the letter " v " would appear in cell E1 to indicate that the cell contained a value - dates are often considered to be values in Excel.

Follow this link-
http://spreadsheets.about.com/od/excelfunctions/qt/20071226_cell_f.htm
Hope this will help you.