|
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 |
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/ |
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 |
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/ |
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/ |
| All times are GMT +1. The time now is 08:32 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com