Teach Yourself Excel Lesson Enter A Formula - Enter A Formula

Hi

I am looking for a way to enable fast data entry whereby a single character (between 0 and 9) is entered into a cell and the cursor then moves to the adjacent cell without having to hit Enter or Tab. I looked at Data Forms but those require a tab to move to the next field. I tried Worksheet Change Event but it still requires me to hit Enter after typing in the single character into the current cell to get to the adjacent cell.

Each record has about 30 cells line up horizontally and each cell contains a single character between 0 and 9. There are a thousand records in one worksheet that require similar data entry hence having the capability I just described will make life a lot easier for the data entry folks.

Any help or suggestion is much appreciated.

Phil

If I have a value of $10 and add 10% by dividing by 0.90, I get $11.11 which
I would like to round up to the nearest $0.5, i.e. $11.15.

Does anybody know the formula and can you use it on a result which is also a
formula?

Thanks,

Lee

I'm trying to write code that enters a formula into a single cell, which I will then use to fill down through a range.

Can you tell me what's wrong with this code?


With Worksheets("NEW")
.Range("new.range.LONG_OPEN_PX").Value = ""
.Range("new.range.LONG_CLOSE_PX").Value = ""
.Range(7, Range("new.range.LONG_LAST_PX").Column).Select
.Range(7, Range("new.range.LONG_LAST_PX").Column).Formula = "=IF($C7='','',BDP($C7,'LAST PRICE'))"
End With

Thanks!
DAn

Hi,

I have a formula as below:
=ceiling(B10,value(right(B10),3)),1000)

I am trying to create a VBA code which will copy this formula in to range of cells.

Range("A"& Rowno).forumla ="""=CEILING(B""" &"rowno" & """,VALUE(RIGHT(B""" & "rowno" & """,3)),1000)"""

I am getting the output "=CEILING(B"rownoVALUE(RIGHT(Browno",3)),1000)" in excel.

Can anybody helpme on this?

I am entering and IF expression with more than 7 nested functions. Is there
another way to enter formula which would work?

=IF(AG6<6,AF6,IF(AG6=6,AF6-1,IF(AG6=7,AF6-2,IF(AG6=8,AF6-3,IF(AG6=9,AF6-4,IF(AG6=10,AF6-5,IF(AG6=11,AF6-6IF(AG6=12,AF6-7,IF(AG6=13,AF6-8,IF(AG6=14,AF6-9,IF(AG6>14,0))))))))))))

I have office for student and teacher Edition 2003. When I try to enter a
formula into my spreadsheet/ with existing numbers inthe cells the formula
either covers up my existing entry or changes the numbers. Why? What am I
doing wrong?

Hi everyone,

I wonder if someone could help me please?

Part of the procedure I am writing needs a formula to be entered in some cells. The formula I want in the cells is:

Code:

Cells(3, 4).FormulaR1C1 = "=rc[1,-1]-("0:0:01")"

The cell I am pointing to is a time and I want to subtract one second from that time. This is all fine and works fine when I type it manually in. Likewise I have no problems entering formulas but in this instance (and many others) the formula contains " which VBA recognises as part of the programming, not part of the formula. This also happens with :, is there a way around this problem that anyone knows of?

Many thanks.

Hello. Does anybody know how to assign a Parameter Formula to a cell using VB?

For various reasons it's far easier for me to use VB to enter this formula into certain cells...please don't ask me to explain

This is the VB code that I'm using.

Private Sub CommandButton1_Click()
Worksheets("Sheet1").Range("A1").Formula = _
Chr(123) & "=IF(SUM((C2:C8=C2)*(D2:D8=D2))=SUM((C2:C8=C2)*(D2:D8=D2)*(N2:N8=" & Chr(34) & _
"Complete" & Chr(34) & "))," & Chr(34) & "Qualified" & Chr(34) & "," & _
Chr(34) & "Unqualified" & Chr(34) & ")" & Chr(125)
End Sub

The formula looks like this in excell - {=IF(SUM((C2:C8=C2)*(D2:D8=D2))=SUM((C2:C8=C2)*(D2:D8=D2)*(N2:N8="Complete")),"Qualified","Unqualified")}

When I click the button the code (in bold above) becomes the cells value, not the formula.

Maybe I neet to put in the Chr() ascii code for CTRL-SHIFT-ENTER perhaps.

Can anybody point me in the right direction?

Cheerily Muchly
clarence

Good afternoon!
I am trying to include the following code in a macro I am building. VBE throws a syntax error when I include the quotes around "00000 - South Jersy Newspapers" but if I don't include the quotes, Excel changes it to 0 - South Jersy Newspapers and my SUMIF is wrong.

Any ideas?

Code:

Cells(lastRowWFO + 2, "D").Formula = "=SUMIF(B50:B" & lastRowWFO & ", "00000 - South Jersy Newspapers", D50:D" & lastRowWFO & ")"

Hi -

New to the forum and excel. I'm trying to write a formula for cell F29. The criteria is:

IF cell B3 has a number greater than or equal to 5

Then

multiply D7 by 10%

If not - enter zero

I though the formula would be IF(B3>4)=SUM(D7*.10) but that doesn't seem to be working, and I've been unable to find any solution in my searches for the last 2 hours. Thanks for any help

Troy