changing cell formula part of macro

  • Thread starter Thread starter thephoenix12
  • Start date Start date
T

thephoenix12

Hello. I have a program with a simple macro I am trying to make. Here
is the macro right now, it works fine as is: (I have highlighted in
bold what I would like to change)

Sub RequiredDate()
Dim rizange, rizaange, a_range, b_range, As Range
Set rizange = Range("H2:H8")
Set rizaange = Range("I2:I8")
For Each a_range In rizange
If a_range <> "" Then
Range("j" & a_range.Row).Select
ActiveCell.Formula = "*=E2+10*"
End If
Next

For Each b_range In rizaange
If b_range <> "" Then
Range("j" & b_range.Row).Select
ActiveCell.Formula = "*=E2+15*"
End If
Next
End Sub


I would like to change the cell formula part of the macro. Instead of
all of them being E2 plus a number, I would like it to be E2, then E3,
then E4, etc etc. But when I try to replace what I have with something
like:
="("e" & b_range.Row)+15" it gives me an error. Is there a way that I
can fix this?

Thanks for your help.

-Steve
 
I think this will get you started....

Sub RequiredDate()

Dim rizange, rizaange, a_range, b_range As Range
Dim Count As Integer

Set rizange = Range("H2:H8")

Count = 2
For Each a_range In rizange
If a_range <> "" Then
Range("j" & a_range.Row).Select
ActiveCell.Formula = "=E" & Count & "+10"
Count = Count + 1
End If
Next

End Sub
 
Back
Top