Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Simple Loop Help Please | Excel Programming | |||
Simple Loop Help Please | Excel Programming | |||
Simple loop? | Excel Programming | |||
If-Else Simple Loop function | Excel Programming | |||
Some help w/ simple loop, please? | Excel Programming |