Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > creating a formula in textfield
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default creating a formula in textfield
Hi, I could use some help on this error I'm receiving. I have trie
everything I can think of. I am using a formula that takes the sum o
2 fields and combines them. Sometimes the 2 fields will not contai
any information, so I don't want it to put anything in the 3rd field.
How do I do this?

This is my code so far:
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
ActiveCell.Offset(1, 0).Range("A1").Select

Example : If A1 & B1 contain data, then C1 =SUM(A1:B1), but if A1 or B
does not contain data, then C1 needs to be left blank.

Thanks for the help

--
Message posted from http://www.ExcelForum.com

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 70
Default creating a formula in textfield
Hi,

This would appear to do what you want:

Sub A()
Dim r As Range

Set r = ActiveCell

If IsEmpty(r.Value) Or IsEmpty(r.Offset(0, 1).Value) Then
r.Offset(0, 2).Clear
Else
r.Offset(0, 2).Formula = "=SUM(RC[-2]:RC[-1])"
End If
End Sub

HTH

Peter Beach

"tiptop " wrote in message
...
Hi, I could use some help on this error I'm receiving. I have tried
everything I can think of. I am using a formula that takes the sum of
2 fields and combines them. Sometimes the 2 fields will not contain
any information, so I don't want it to put anything in the 3rd field.
How do I do this?

This is my code so far:
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
ActiveCell.Offset(1, 0).Range("A1").Select

Example : If A1 & B1 contain data, then C1 =SUM(A1:B1), but if A1 or B1
does not contain data, then C1 needs to be left blank.

Thanks for the help.


---
Message posted from http://www.ExcelForum.com/



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default creating a formula in textfield
Ok, I see how this works, but I cant figure out how to integrate i
into my code. Do I leave that as a standalone sub, or do I integrat
it into my existing one? And if so, how? Do I just call it up afte
each set?

The number set's start in O3 & P3, and the sum would be in Q3, and s
on. It lasts for aprox 23 rows, but not all the rows are going t
contain data.

Sub ALLstart()
ActiveWindow.WindowState = xlMinimized
ActiveWindow.WindowState = xlMaximized
ActiveCell.Columns("Q:Q").EntireColumn.Select
Selection.Insert Shift:=xlToRight
ActiveCell.Offset(4, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "Combined Answers"
With ActiveCell.Characters(Start:=1, Length:=17).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 7
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Bold = False
End With
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "Part 1"
With ActiveCell.Characters(Start:=1, Length:=8).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 7
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.Bold = False
End With
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
ActiveCell.Offset(1, 0).Range("A1").Select
Range("E5,e6,q5,q6,r5,r6,w5,w6,y5,y6,aa5,aa6").Sel ect
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Range("A1").Select
End Sub
Sub A()
Dim r As Range

Set r = ActiveCell

If IsEmpty(r.Value) Or IsEmpty(r.Offset(0, 1).Value) Then
r.Offset(0, 2).Clear
Else
r.Offset(0, 2).Formula = "=SUM(RC[-2]:RC[-1])"
End If
End Su

--
Message posted from http://www.ExcelForum.com

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 94
Default creating a formula in textfield
Perhaps you are killing a gnat with a hand gernade? Would it suffice
to just put a formula in the cell that returns the correct value?
=IF(AND(A1="",B1=""),"",A1+B1)
or
=IF(OR(A1="",B1=""),"",A1+B1)

"Peter Beach" wrote in message
...
Hi,

This would appear to do what you want:

Sub A()
Dim r As Range

Set r = ActiveCell

If IsEmpty(r.Value) Or IsEmpty(r.Offset(0, 1).Value) Then
r.Offset(0, 2).Clear
Else
r.Offset(0, 2).Formula = "=SUM(RC[-2]:RC[-1])"
End If
End Sub

HTH

Peter Beach

"tiptop " wrote in message
...
Hi, I could use some help on this error I'm receiving. I have tried
everything I can think of. I am using a formula that takes the sum of
2 fields and combines them. Sometimes the 2 fields will not contain
any information, so I don't want it to put anything in the 3rd field.
How do I do this?

This is my code so far:
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
ActiveCell.Offset(1, 0).Range("A1").Select

Example : If A1 & B1 contain data, then C1 =SUM(A1:B1), but if A1 or B1
does not contain data, then C1 needs to be left blank.

Thanks for the help.


---
Message posted from http://www.ExcelForum.com/





Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 70
Default creating a formula in textfield
Hi,

I'm having a bit of difficulty working out what your routine is trying to
accomplish. Why for example do you first minimise, and then immediately
maximise the active window?

Perhaps you have been using the Macro recorder? There are a few problems
with the code generated by the recorder :-( In VBA you virtually never need
to .Select anything.

Without knowing exactly what you're trying to achieve, I would try something
like (this is untested code - apologies for any typos or silly errors):

Sub ALLstart()
dim R as Range
dim i as Long

ActiveWindow.WindowState = xlMaximized
Range(q:q).Insert Shift :=xlToRight
Range("U1").Value = "Combined Answers"
Range("U1").Font.Size = 7
Range("V1").Value = "Part1"
Range("V1").Font.Size = 7

set r = Range("O3")
for i = 1 To 23 ' Or whatever the appropriate test is?
If IsEmpty(r.Value) Or IsEmpty(r.Offset(0, 1).Value) Then
r.Offset(0, 2).Clear
Else
r.Offset(0, 2).Formula = "=SUM(RC[-2]:RC[-1])"
End If
Set r = r.Offset(1,0)
Next i
End Sub

Regards,

Peter Beach

"tiptop " wrote in message
...
Ok, I see how this works, but I cant figure out how to integrate it
into my code. Do I leave that as a standalone sub, or do I integrate
it into my existing one? And if so, how? Do I just call it up after
each set?

The number set's start in O3 & P3, and the sum would be in Q3, and so
on. It lasts for aprox 23 rows, but not all the rows are going to
contain data.

Sub ALLstart()
ActiveWindow.WindowState = xlMinimized
ActiveWindow.WindowState = xlMaximized
ActiveCell.Columns("Q:Q").EntireColumn.Select
Selection.Insert Shift:=xlToRight
ActiveCell.Offset(4, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "Combined Answers"
With ActiveCell.Characters(Start:=1, Length:=17).Font
Name = "Arial"
FontStyle = "Regular"
Size = 7
Strikethrough = False
Superscript = False
Subscript = False
OutlineFont = False
Shadow = False
Underline = xlUnderlineStyleNone
ColorIndex = xlAutomatic
Bold = False
End With
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "Part 1"
With ActiveCell.Characters(Start:=1, Length:=8).Font
Name = "Arial"
FontStyle = "Regular"
Size = 7
Strikethrough = False
Superscript = False
Subscript = False
OutlineFont = False
Shadow = False
Underline = xlUnderlineStyleNone
ColorIndex = xlAutomatic
Bold = False
End With
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])"
ActiveCell.Offset(1, 0).Range("A1").Select
Range("E5,e6,q5,q6,r5,r6,w5,w6,y5,y6,aa5,aa6").Sel ect
With Selection.Interior
ColorIndex = 6
Pattern = xlSolid
End With
Range("A1").Select
End Sub
Sub A()
Dim r As Range

Set r = ActiveCell

If IsEmpty(r.Value) Or IsEmpty(r.Offset(0, 1).Value) Then
r.Offset(0, 2).Clear
Else
r.Offset(0, 2).Formula = "=SUM(RC[-2]:RC[-1])"
End If
End Sub


---
Message posted from http://www.ExcelForum.com/



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Using Excel as a database and textfield C Tate[_2_] Excel Discussion (Misc queries) 13 March 12th 08 01:13 PM
Creating a formula JMP Excel Worksheet Functions 2 December 8th 06 12:42 PM
HELP creating formula! dmeals Excel Worksheet Functions 0 January 2nd 05 11:39 PM
commandbar with own textfield for datetime input ?? Frank Huebener Excel Programming 2 December 21st 03 08:33 PM
formatting a TextField as DD-MMM-YY format Tom Ogilvy Excel Programming 1 September 14th 03 06:00 PM


All times are GMT +1. The time now is 11:14 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement