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

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

Start With:
{=COUNT(IF(($C$11:$C$128620)*(C$11:C$128620),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$128620)*(C$11:C$128620),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$128620)*(D$11:D$128620),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!!!!!


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200509/1

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

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


"Ray via OfficeKB.com" wrote in message ...
Thanks for your help guys. This is what I am working with. The problem is:

Start With:
{=COUNT(IF(($C$11:$C$128620)*(C$11:C$128620),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$128620)*(C$11:C$128620),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$128620)*(D$11:D$128620),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!!!!!


--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200509/1

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

thanks a bunch bernie

Bernie Deitrick wrote:
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!!!!!


--
Message posted via http://www.officekb.com