I have no Idea how to do this in Macro.. or if it can even be done easily

[Ray via OfficeKB.com]

Thanks for your help guys. This is what I am working with. The problem is:

Start With:
{=COUNT(IF(($C$11:$C$12862>0)*(C$11:C$12862>0),0))}

Step 1:
Make the formula in the row below the initial one increment the column
referenced in the first part of the formula (change $C$11:$C$12862 to $D$11:
$D$12862), but leave the reference in the second part the same. I can do
this by hand, but am looking for a way to program it to eliminate the manual
part. IE:

Drag it down one row to make the formula:
{=COUNT(IF(($D$11:$D$12862>0)*(C$11:C$12862>0),0))}

Step 2:
I understand this and I have it working in both regular Excel and with an
autofill command in VBA, but there might be a reason to increment this as
well to make Step 1 run more smoothly, I just don't know enough about it.

Drag that (From Step 1) to the right one column to make the formula:
{=COUNT(IF(($D$11:$D$12862>0)*(D$11$12862>0),0))}

I want to find a way to automate the change in Step 1 either through marcos
autofill or through draging it down.

Thanks again folks!!!!!

 

[Bernie Deitrick]

Ray,

Select the cells (below the cell with the formula) that you want to autofill - do not select the
cell with the formula - and run the macro below.

Just as a disclaimer, the macro will increment the column letter of the first range in the formula,
which must be an absolute (uses $) multi-row single column range. Otherwise, bad things will happen
;-).

HTH,
Bernie
MS Excel MVP


Sub TryNow()
Dim myStr As String
Dim myCol1 As String
Dim myCol2 As String
Dim myCell As Range
For Each myCell In Selection
myStr = myCell(0, 1).Formula
myCol1 = ColLet(myStr)
myCol2 = ColLet(Range(myCol1 & "1")(1, 2).Address)
myStr = Replace(myStr, "$" & myCol1, "$" & myCol2, 1, 2)
If myCell(0, 1).HasArray Then
myCell.FormulaArray = myStr
Else
myCell.Formula = myStr
End If
Next myCell
End Sub

Function ColLet(FormStr As String) As String
Dim mySplit As Variant
mySplit = Split(FormStr, "$")
ColLet = mySplit(LBound(mySplit) + 1)
End Function

 

[Raymond P via OfficeKB.com]

thanks a bunch bernie

Ray,

Select the cells (below the cell with the formula) that you want to autofill - do not select the
cell with the formula - and run the macro below.

Just as a disclaimer, the macro will increment the column letter of the first range in the formula,
which must be an absolute (uses $) multi-row single column range. Otherwise, bad things will happen
;-).

HTH,
Bernie
MS Excel MVP

Sub TryNow()
Dim myStr As String
Dim myCol1 As String
Dim myCol2 As String
Dim myCell As Range
For Each myCell In Selection
myStr = myCell(0, 1).Formula
myCol1 = ColLet(myStr)
myCol2 = ColLet(Range(myCol1 & "1")(1, 2).Address)
myStr = Replace(myStr, "$" & myCol1, "$" & myCol2, 1, 2)
If myCell(0, 1).HasArray Then
myCell.FormulaArray = myStr
Else
myCell.Formula = myStr
End If
Next myCell
End Sub

Function ColLet(FormStr As String) As String
Dim mySplit As Variant
mySplit = Split(FormStr, "$")
ColLet = mySplit(LBound(mySplit) + 1)
End Function

Thanks for your help guys. This is what I am working with. The problem is:

[quoted text clipped - 23 lines]

Thanks again folks!!!!!