Help VBA code keeps returning Fixed cells.

[bobshah2010]

Hi there,

Please see attached code i've made to create a new "section"

Sub AddSection(Optional ByVal control As IRibbonControl)

'******************
'This macro inserts a section divider above the active cell
'******************


Dim lActiveRow As Long
Dim rngScnStart As Range
Dim rngScnEnd As Range
Dim strScnCell As String
Dim rngTargetCell As Range


'Application.ScreenUpdating = False


'Insert new row above the active cell
Application.CutCopyMode = False
ActiveCell.EntireRow.Insert xlShiftDown


'Set beginning and end cells
Set rngTargetCell = ActiveCell
lActiveRow = rngTargetCell.Row
Set rngScnStart = Cells(lActiveRow, 2)
Set rngScnEnd = Cells(lActiveRow, 16384)


'Find address for use in the formula
strScnCell = rngScnStart.Address


'Set styles and formulas for the section
Range(rngScnStart, rngScnEnd).Style = "Section (Filled)"
rngScnStart.Formula = "=FLOOR(MAX(B1:OFFSET(" & strScnCell & ",-1,0),B1),ScnInc)+ScnInc"
rngScnStart.Style = "Section Number"

When I run this macro the formula "=FLOOR(MAX(B1:OFFSET(" & strScnCell & ",-1,0),B1),ScnInc)+ScnInc" returns strScnCell is fixed - i.e with $$.

This is most definitely a silly and easy question to solve. Could someone please assist. I do not want it to return a fixed cell.

Thanks

 

[footoo]

strScnCell = rngScnStart.Address(0,0)

 

[StephenCrump]

strScnCell = rngScnStart.Address(0,0)

Agree, or perhaps just simplify:

rngScnStart.Formula = "=FLOOR(MAX(B1:OFFSET(" & strScnCell & ",-1,0),B1),ScnInc)+ScnInc"

to

rngScnStart.Formula = "=FLOOR(MAX(B1:B" & lActiveRow - 1 & "),ScnInc)+ScnInc"

Is that formula giving you the results you want, by the way? For example, if ScnInc is 0.5, any number already ending in 0.5 will be adjusted up by a further 0.5?