Macro to change columns - monthly question

[Bob]

I've got the following 4 formulas in an excel wroksheet:

=SUM(E7/Sheet1!$B$3)*52 E7 = Feb $B$3 = 4

=SUM(D7+E7)/Sheet1!$C$3*52 D7+E7 = Jan + Feb $C$3 = 8

=SUM(D7+E7+F7)/Sheet1!$D$3*52 D7+E7+F7 = Jan+Feb+Mar $D$3 = 13

=SUM(P7/8)*52 P7 = YTD 8=number of accounting weeks thru Feb and 52 =
total weeks in a year.

I would like to create a macro in that all I would need to do is for example
change the E7 to an F7 and D7+E7 to E7+F7 in the macro instead of changing
them in the worksheet and copying and pasting.


Thanks.

 

[Bob Phillips]

Try this

Public Sub MoveFormulae()
Dim i As Long

With ActiveSheet

For i = 1 To 4
.Cells(i, "K").FormulaR1C1 = MoveIt(.Cells(i, "K").FormulaR1C1)
Next i
End With
End Sub

Private Function MoveIt(Formula As String)
Dim mpFormula As String
Dim mpNew As String
Dim mpStart As Long
Dim mpColumn As Long
Dim i As Long

mpFormula = Formula
mpStart = 1
For i = 1 To Len(mpFormula)

If Mid$(mpFormula, i, 1) = "C" And _
Mid$(mpFormula, i + 1, 1) = "[" Then

mpColumn = Mid$(mpFormula, i + 2, InStr(i + 3, mpFormula, "]") -
i - 2)
mpColumn = mpColumn + 1
mpNew = mpNew & Mid$(mpFormula, mpStart, i - mpStart + 2) &
mpColumn & "]"
i = InStr(i + 3, mpFormula, "]")
mpStart = i + 1
End If
Next i
mpNew = mpNew & Mid$(mpFormula, mpStart, i - mpStart)
MoveIt = mpNew
End Function



--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Bob]

Thanks Bob - Would I have to edit this macro monthly in order to get my new
columns? I'm not well versed in constructing macros and was wondering how new
columns would be captured.

Thanks.
--
Bob

Try this

Public Sub MoveFormulae()
Dim i As Long

With ActiveSheet

For i = 1 To 4
.Cells(i, "K").FormulaR1C1 = MoveIt(.Cells(i, "K").FormulaR1C1)
Next i
End With
End Sub

Private Function MoveIt(Formula As String)
Dim mpFormula As String
Dim mpNew As String
Dim mpStart As Long
Dim mpColumn As Long
Dim i As Long

mpFormula = Formula
mpStart = 1
For i = 1 To Len(mpFormula)

If Mid$(mpFormula, i, 1) = "C" And _
Mid$(mpFormula, i + 1, 1) = "[" Then

mpColumn = Mid$(mpFormula, i + 2, InStr(i + 3, mpFormula, "]") -
i - 2)
mpColumn = mpColumn + 1
mpNew = mpNew & Mid$(mpFormula, mpStart, i - mpStart + 2) &
mpColumn & "]"
i = InStr(i + 3, mpFormula, "]")
mpStart = i + 1
End If
Next i
mpNew = mpNew & Mid$(mpFormula, mpStart, i - mpStart)
MoveIt = mpNew
End Function



--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

I've got the following 4 formulas in an excel wroksheet:

=SUM(E7/Sheet1!$B$3)*52 E7 = Feb $B$3 = 4

=SUM(D7+E7)/Sheet1!$C$3*52 D7+E7 = Jan + Feb $C$3 = 8

=SUM(D7+E7+F7)/Sheet1!$D$3*52 D7+E7+F7 = Jan+Feb+Mar $D$3 = 13

=SUM(P7/8)*52 P7 = YTD 8=number of accounting weeks thru Feb and 52 =
total weeks in a year.

I would like to create a macro in that all I would need to do is for
example
change the E7 to an F7 and D7+E7 to E7+F7 in the macro instead of
changing
them in the worksheet and copying and pasting.


Thanks.

 

[Bob Phillips]

Only the first part, the part that calls the generic function

Public Sub MoveFormulae()
Dim i As Long

With ActiveSheet

For i = 1 To 4
.Cells(i, "K").FormulaR1C1 = MoveIt(.Cells(i, "K").FormulaR1C1)
Next i
End With
End Sub

This is a loop going through 4 cells in column K, but you can construct it
to look at any cells, columns or rows that you want.

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

Thanks Bob - Would I have to edit this macro monthly in order to get my
new
columns? I'm not well versed in constructing macros and was wondering how
new
columns would be captured.

Thanks.
--
Bob

Try this

Public Sub MoveFormulae()
Dim i As Long

With ActiveSheet

For i = 1 To 4
.Cells(i, "K").FormulaR1C1 = MoveIt(.Cells(i,
"K").FormulaR1C1)
Next i
End With
End Sub

Private Function MoveIt(Formula As String)
Dim mpFormula As String
Dim mpNew As String
Dim mpStart As Long
Dim mpColumn As Long
Dim i As Long

mpFormula = Formula
mpStart = 1
For i = 1 To Len(mpFormula)

If Mid$(mpFormula, i, 1) = "C" And _
Mid$(mpFormula, i + 1, 1) = "[" Then

mpColumn = Mid$(mpFormula, i + 2, InStr(i + 3, mpFormula,
"]") -
i - 2)
mpColumn = mpColumn + 1
mpNew = mpNew & Mid$(mpFormula, mpStart, i - mpStart + 2) &
mpColumn & "]"
i = InStr(i + 3, mpFormula, "]")
mpStart = i + 1
End If
Next i
mpNew = mpNew & Mid$(mpFormula, mpStart, i - mpStart)
MoveIt = mpNew
End Function



--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my
addy)

I've got the following 4 formulas in an excel wroksheet:

=SUM(E7/Sheet1!$B$3)*52 E7 = Feb $B$3 = 4

=SUM(D7+E7)/Sheet1!$C$3*52 D7+E7 = Jan + Feb $C$3 = 8

=SUM(D7+E7+F7)/Sheet1!$D$3*52 D7+E7+F7 = Jan+Feb+Mar $D$3 = 13

=SUM(P7/8)*52 P7 = YTD 8=number of accounting weeks thru Feb and 52
=
total weeks in a year.

I would like to create a macro in that all I would need to do is for
example
change the E7 to an F7 and D7+E7 to E7+F7 in the macro instead of
changing
them in the worksheet and copying and pasting.


Thanks.