creating a formula in textfield

  • Thread starter Thread starter tiptop
  • Start date Start date
T

tiptop

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
 
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
 
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").Select
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
 
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 said:
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 > said:
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.
Click to expand...
Click to expand...
 
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 > said:
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").Select
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
Click to expand...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Data samples 3
How to write a for loop to get the range between a ,b columns 4
"=TEXT(RC[-1],"MMM")" 4
why won't this work 2
Formula in macro causes macro to fail 2
Linking Cells Macro 3
Totals An Easier Way?? 5
changing range in recorded macro 1

Back
Top