Entering info in the next column

[splendidmonkey]

Ok here is the problem I need to write a macro that will enter information in an empty column.
If row 1 through 100 is has a column with a value of closed in it, it must paste =INT((J**-C**)/7) into the empty column and if the column says Open it must paste =INT((TODAY()-C**)/7) into it
The list will be changing in length about 10 times a day so it cannot be row number specific
Any idea’s welcome

 

[Loomah]

Hi mr monkey
You're not from Hartlepool are you?

The following assumes your list in column A and put your formula in column C. I've assumed that will be empty.

I was getting application errors on your formulae so I changed them for something that worked!!

Sub x()
Dim c As Range
For Each c In Range("A1&quot.CurrentRegion.Columns(1).Cells
If c.Text = "open" Then
c.Offset(0, 2).Formula = "=" & c.Address '"=INT((TODAY()-C**)/7)"
ElseIf c.Text = "closed" Then
c.Offset(0, 2).Formula = "=" & c.Address '"=INT((J**-C**)/7)"
Else
'??????????
End If
Next
End Sub

;-)
If a man says something and there are no women there to hear him, is he still wrong?