using a vb variable in an excel formula
I have a VB macro variable that I want to reference in an Excel formul
(faulty code below gives me a #NAME? error) . There has to be an eas
way to do this but I haven't found it yet. Thanks in advance.
Dim ok As String
Dim no As String
String1 = "ok"
String2 = "no"
ActiveCell.FormulaR1C1 = "23"
ActiveCell.FormulaR1C1 = "45"
ActiveCell.FormulaR1C1 = "=IF(R[-1]C>20, String1, String2)"
Message posted from http://w...How to highlight cell with value?
I'd like to bold any cell with a value greater than 0. The value is
changed manually and will sometimes be changed manually back to 0.
How is this done?
Take a look at Conditional Formatting in XL Help.
In article <firstname.lastname@example.org>,
email@example.com (Brett) wrote:
> I'd like to bold any cell with a value greater than 0. The value is
> changed manually and will sometimes be changed manually back to 0.
> How is this done?
Use Conditional Formatting, under Format. Then set the condition ...Cell Protection #4
Having problem protecting cells in shared folder on shared
drive. Need to protect a group of cells that are for
viewing only, but leave adjacent cells unprotected.
Being a shared worksheet I believe is the problem, there
are limitations. Any suggestions.
Thanks to all.
I think you'll have to do all your setup (disabling protection/locking &
unlocking cells/reenabling protection) against a workbook that isn't shared.
But I'm not sure if that addresses what your problem is?????
> Having problem protecting cells in shared folder on shared
> dr...Please help me decipher the result of COUNT.
Following data from A1:A7
=COUNT(A1:A7,2) returns 4.
I don't understand where the four occurrences of 2 come from.
22.24 = 3 occurrences
12/8/2008 = 2 occurrences
total of 5??
I know I am missing something. Please tell me.
Is there a tool that I can use to trace? I tried the formula auditing
toolbar, no luck because there is no error and trace precedents is not
have a look in the help index for COUNT
firstname.lastname@example.org...If statement result appearing in different colors
Is there a way to write an IF statement where the text will change color
depending on the result? eg
=if(x=0,"RED-colored text","BLACK-colored text") where 'RED-colored text'
would appear in RED, etc
Take a look at Format|Conditional formatting.
> Is there a way to write an IF statement where the text will change color
> depending on the result? eg
> =if(x=0,"RED-colored text","BLACK-colored text") where 'RED-colored text'
> would appear in RED, etc
> Thank ...Conditional Formatting using result of Cell Formulas
I'm trying to make a conditional format when the result of a formula
results in a value being entered in a cell.
I'm using Excel Xp and my formula is as follows.
I've tried using formulas like >0, <>"", <>BLANK etc. but they all
seem to fail. All cells are formatted, even entirely blank cells, as
if the condition has been met.
BTW, is this formula better strategically than the above formula.
If ...VBA Formula Problem
I am using the code below and get an error with the formula statement. I
need help. TIA
For Each c In Range("LName")
c.Formula = Upper(Left("D2", Find(",", "D2", 1) - 1))
the formula needs to be a string. D2 should not be in quotes either:
c.Formula = "=Upper(Left(D2, Find("","", D2, 1) - 1))"
Note that this will set the entire range to read from D2. If you
want it instead to read from column...Simple cell prog. with IF...ELSE
Hello XL Gurus!
I need to warn you - I'm a perfect newbie! :confused:
Well, this is what i would like to do in an Excel sheet:
I have made some kind of table, and in some of the columns I need t
make some calculations *depending * on some inputs in some of the othe
For instance, a price calculation:
I need to calculate some averages, to sum some cells...(that ain't
problem), but then I need the calculation to add 15% in case that th
value one of my cell is (for example) VAT. Let me ilustrate naivly:
G2 = (if C2='VAT' then (A1*B1)+((A1*B1)*0.15)else(A1*B1))
...new line within a cell
In Excel 2002 or 2007, I need to enter several lines into a single cell.
What I mean is I need the capability to tell Excel to enter the next
line of text as a new line within that cell. Is that possible?
Obviously Enter does not work because it accepts the entry and goes to
the next cell.
Type some text in a cell
Alt + Enter
Type some more text in same cell
Alt + Enter
Type a third line in the cell
Enter to leave cell.
Gord Dibben MS Excel MVP
On Fri, 22 Oct 2010 11:27:11 -0400, "Jeff@nospam.invalid" <Jeff@nospam.invalid>
&g...charting missing data (empty cells bug)
I'm trying to chart data with some missing points. I read posts on this forum on this topic and checked with my settings but they seem to be OK. Still, my empty cells are plotted as zeros. Only one (!) cell out of 5 is not plotted(and I failed to figure out how that one was different from the rest).
Here are the current settings:
- Excel version is French.
- Cells are not hidden, but protected.
- They are customized at "empty cell" format (;;;)
- Empty cells are set to be plotted as gaps.
Your help would be very much appreciated.
EggHeadCafe - Software Developer Portal...Display Formula Problem
I have search Microsoft's Knowledge Base, but could not find an answer.
Hopefully, someone here can solve my problem.
I am using Excel 2002. I have several project managers maintaining budgets
in Excel workbooks. I am creating a summary Excel workbook. To get the data
out of the workbooks, I start with entering an equal sign, click on the cell
in one of the other workbooks, and press enter. The value from the source
workbook is displayed correctly.
Since I want to capture several columns and rows around this first cell, I
want to copy formula in the first cell into near cells, I mu...TEXT IN CELL, but not too wide
I wish to type a paragraph into my cell.
Instead of having it appear several inches wide on the spreadsheet, I wish
it to wrap, say, into 4 or 5 lines, each line just a few inches wide.
How is this done please??
Type a first line, then Alt-Enter, another line, Alt-Enter, etc.
(remove nothere from the email address if mailing direct)
"FLKULCHAR" <FLKULCHAR@discussions.microsoft.com> wrote in message
> I wish to type a paragraph into my cell.
> Instead of having it appear...Blank cells not to compute
I have cells that say if value is equal a certain points will be given if a
tie 2 points are given by comparing the score in 2 cells. the problem the
games have not been played and is already showing a tie so everybody is
gotten their 2 points for the tie and 5 point for getting the score correct
blank = blank.
How do i do so the cell when they are blank do not compute
"victorio" <email@example.com> wrote in message
I am trying to use an event macro referenced in another thread,
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Cancel = True 'Eliminate Edit status due to doubleclick
(credit to: http://www.mvps.org/dmcritchie/excel/insrtrow.htm)
but am getting a debug message on the following line:
I need clari...IIF formula
I have a formula in a query which works fine until I tried to test it by
deleting values in the form. Instead of nothing showing up, like how the
formula should work, it showed ",X"
Other Fills: IIf([Other]=0,Null,[Other] & "X" & [Type]) & IIf([Other
2]=0,Null,"," & [Other 2] & "X" & [Type 2])
[Other] = 2
[Type] = 4
[Other 2] = 3
[Type 2] = 5
Should look like 2X4,3X5
When [Other 2] and [Type 2] values are deleted it should look like 2X4
instead of 2X4,X
The default value for the [Other] and [Other 2]...How to setup formula to collect ongoing information
I have setup a questionnaire which will continuously have an answer section
added to it. I am setting up a formula to count the yes and no answers. How
would I set a formula up to count all the ongoing answers? Example would be
=COUNTA(B6:G6) but then I add another two column of answers and need the
formula to now read =COUNTA (B6:I6). Do I need to keep adding a new formula?
easiest way is using 'dynamic ranges'
In column M, I record the results of appointmens (Sale, No sale, Cancelled
Everytime I input a result I'd like me Result summary to update
automatically. I do it manually at the moment but it takes time and its easy
o make an error.
I've looked at SUMIF and thought I was close bu couldn't get it to work. I
thought COUNT would do the trick but it ignores text.
You had the right idea...
will give you the number of "No Sale" entries in the range M1:M100.
Adjust this for your use.
Thanks heaps, that ...formula query #3
I have a group of 52 "weekly" spreadsheets , each one is named after this
week's start date in a prcise format e.g. 130305.xls. I want to create a
cell within each weekly sheet which simply contains this weeks date.
Obviously I could manually enter this data but I would prefer it if Excel
could automatically access the filename of this spreadsheet and embed it
accordingly in the cell. Can anyone tell me if this is possible?
Chip Pearson has formulas for returning the Sheet Name or the File Name:
Wayne K...Protecting formulas #2
How can i protect a formula from either being edited or
If it's a single formula:
Select all cells on the sheet by clicking the button at the
intersection of Row and Column heads
Format > Cells > Protection
Remove the check from "Locked"
Select the cell(s) to be protected
Format > Cells > Protection
Tools > Protection > Protect Sheet
Give and confirm a password (optional)
If there's a possibility of sheet deletion to be taken into account,
you must also use:
Tools > Protection > Protect Workb...sql result different to excel result
Just wondering why I am having such problems rounding figures, when I round
the same figure on an Excel Sheet, I end up with a different end result to
what appears in sql.
The Excel Looks Like This.
Net Vat Total Allocated Result
900.00 157.50 1721.38 1721.38 1057.50
150.00 26.25 1721.38 1721.38 176.25
The Result field is a calculated field and is rounded to 2 decimal places,
the calculation is in brackets next to the title name.
The Table in SQL has the same structure but the net, vat,...How to paste Conditional Formatting to other cells but using different data cell
I've made a spreadsheet that has a column for days of testing and
another column that calculates the actual days it took for testing.
Each row has a different day requirment of when testing should be
complete. How can I format the column of "actual days of testing" to
color the cell in red with bold type when the value of this cell is
greater than the cell of "days of testing"? I tried conditional
formating, but this only works for one row, when I tried to paste the
format to other cells it used the data source from the first row. It
would be too time consuming to indiv...Count results from query with no results
I have continous form with an unbound text box [keyword] and a button
[search]. There is also an unbound textbox [numberofresults] that counts the
number of records returned. If the search produces no results, the
[numberofresults] needs to read 0, instead it is just blank. What is the
proper way to show zero results as 0 and not blank?
It depends on how you are doing your search? Do you have a function? Are
you just filtering the recordset?
email address is invalid
Please reply to newsgroup only.
"Ryan Tisserand" wrote:
> I have continous form with an un...Formula to return numerical, text result?
I need to create a formula that based upon three possible factors, wil
return one of two calculations or a text message.
If cell B="Good", the formula in cell C returns "Text"
If cell B="Better", the formula in cell C returns cell A*.3
If cell B="Best", the formula in cell C returns cell A*.5
Thanks in advance for any tips
burgeon's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2023
View this thread: http://www.excelforum.com/showthread.php...why cut and insert cells only works randomly?
It seems that cutting and inserting cells in the spreadsheet, errors every
other time and locks the excell spread sheet...
You'll have to be more specific, but you might find the answers to such problems
as inserting rows, using OFFSET with formulas. And you will probably find why
extending formulas and does not work for you. -- if any of those are the problem.
I don't know what you mean by locking the sheet, have you turned on sheet protection
or have merged cells in your copy..
David McRitchie, Microsoft...Updating cells using input boxes
My problem is that i am using Input boxes to update a spreadsheet. I
have two coloums i want to update for two diffent type of information.
I am currently trying to use
MyInput = InputBox("Enter Students Name")
Range("Sheet1!N20").End(xlDown).Offset(1, 0).Value = MyInput
MyInput = InputBox("Enter Fee")
Range("Sheet1!O20").End(xlDown).Offset(1, 0).Value = MyInput
But this seems to update the first colum ok and move down updating as
required, but it just replaces the value in the second coloum and
doesn't move ...