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/