Loop and simple calculation
 

I am trying to add 1 to cells that have a neighbouring cell with the value
"Open" using VBA. This is what I have and it has melted my brain, I have a
feeling that its something blindingly obvious but for the life of me I cannot
figure it out. Please help.

Dim d As Range
Dim s As Range

Set d = Range("Days")
Set s = Range("Status")

Do Until IsEmpty(d)
If s.Value = "Open" Then
d.Value = d.Value + 1
Else
d.Value = d.Value
End If
Loop

Thanks in advance,
Tom

Loop and simple calculation
 

On 16 Nov, 12:46, Tom wrote:
I am trying to add 1 to cells that have a neighbouring cell with the value
"Open" using VBA. This is what I have and it has melted my brain, I have a
feeling that its something blindingly obvious but for the life of me I cannot
figure it out. Please help.

Dim d As Range
Dim s As Range

Set d = Range("Days")
Set s = Range("Status")

Do Until IsEmpty(d)
If s.Value = "Open" Then
d.Value = d.Value + 1
Else
d.Value = d.Value
End If
Loop

Thanks in advance,
Tom

Hi Tom,

The loop is not advancing, therefore d would never be empty.

You could try this:

Dim d As Range
Dim s As Range

Set d = Range("Days")
Set s = Range("Status")

Do Until IsEmpty(d)
If s.Value = "Open" Then
d.Value = d.Value + 1
Else
d.Value = d.Value
End If
activecell.offset(1,0).select
Loop

This is only a guess as I cant tell what your trying to do.

James

Loop and simple calculation
 

Try this

Sub addone()
Dim jmax as integer, j as integer, d as integer
Dim mydays, mystatus
jmax = Range("status").Count
Set mystatus = Range("status")
Set mydays = Range("days")
For j = 1 To jmax
If mystatus(j) = "open" Then
d = d + 1
mydays(j) = d
End If
Next
End Sub

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Tom" wrote in message
...
I am trying to add 1 to cells that have a neighbouring cell with the value
"Open" using VBA. This is what I have and it has melted my brain, I have a
feeling that its something blindingly obvious but for the life of me I
cannot
figure it out. Please help.

Dim d As Range
Dim s As Range

Set d = Range("Days")
Set s = Range("Status")

Do Until IsEmpty(d)
If s.Value = "Open" Then
d.Value = d.Value + 1
Else
d.Value = d.Value
End If
Loop

Thanks in advance,
Tom

Loop and simple calculation
 

Hi,

Thanks for the quick reply. I had to make a slight change to the code
(below) but it worked a treat.

Many thanks,
Tom


Dim jmax As Long, j As Long, d As Integer
Dim mydays, mystatus

jmax = Range("Status").Count
Set mystatus = Range("Status")
Set mydays = Range("Days")

For j = 1 To jmax
If mystatus(j) = "Open" Then
d = mydays(j)
d = d + 1
mydays(j) = d
End If
Next

End





"Bernard Liengme" wrote:

Try this

Sub addone()
Dim jmax as integer, j as integer, d as integer
Dim mydays, mystatus
jmax = Range("status").Count
Set mystatus = Range("status")
Set mydays = Range("days")
For j = 1 To jmax
If mystatus(j) = "open" Then
d = d + 1
mydays(j) = d
End If
Next
End Sub

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Tom" wrote in message
...
I am trying to add 1 to cells that have a neighbouring cell with the value
"Open" using VBA. This is what I have and it has melted my brain, I have a
feeling that its something blindingly obvious but for the life of me I
cannot
figure it out. Please help.

Dim d As Range
Dim s As Range

Set d = Range("Days")
Set s = Range("Status")

Do Until IsEmpty(d)
If s.Value = "Open" Then
d.Value = d.Value + 1
Else
d.Value = d.Value
End If
Loop

Thanks in advance,
Tom

Loop and simple calculation
 

Hi,

Thanks for the quick reply but it didnt seem to work, it had a problem with
the Range.Value being "open".

Many thanks,
Tom

" wrote:

On 16 Nov, 12:46, Tom wrote:
I am trying to add 1 to cells that have a neighbouring cell with the value
"Open" using VBA. This is what I have and it has melted my brain, I have a
feeling that its something blindingly obvious but for the life of me I cannot
figure it out. Please help.

Dim d As Range
Dim s As Range

Set d = Range("Days")
Set s = Range("Status")

Do Until IsEmpty(d)
If s.Value = "Open" Then
d.Value = d.Value + 1
Else
d.Value = d.Value
End If
Loop

Thanks in advance,
Tom

Hi Tom,

The loop is not advancing, therefore d would never be empty.

You could try this:

Dim d As Range
Dim s As Range

Set d = Range("Days")
Set s = Range("Status")

Do Until IsEmpty(d)
If s.Value = "Open" Then
d.Value = d.Value + 1
Else
d.Value = d.Value
End If
activecell.offset(1,0).select
Loop

This is only a guess as I cant tell what your trying to do.

James