loop variable range
 

Dave Thank you
It does exactly what I wanted.

What is the best way to check for illegal values? ie if either or both of
the two summed cells have a value of 0, I would want to skip to the next
icol

CR



"Dave Peterson" wrote in message
...
Dim iCol as long
dim FirstCol as long
dim LastCol as long

with worksheets("Somesheetnamehere")
firstcol = .range("d1").column
lastcol = .range("s1").column

for icol = firstcol to lastcol
'no check for illegal values!
if (.cells(3,icol).value + .cells(5,icol).value) _
.cells(1,icol).value then
'do something
else
'do nothing
end if
next icol
end with

Untested, uncompiled. Watch for typos.

CR wrote:

I have a Range with values D1:S5

I need to sum D3 and D5 and if it is D1 value then
Do something

Then loop through E to S, doing the same thing

Any help would be appreciated

Thanks
CR

--

Dave Peterson

loop variable range
 

Try

Sub Macro()
Dim rngCol As Range
For Each rngCol In Range("d1:S5").Columns
If rngCol.Cells(5, 1) + rngCol.Cells(3, 1) < 0 Then
If rngCol.Cells(5, 1) + rngCol.Cells(3, 1) rngCol.Cells(1, 1) Then
'dO SOMETHING
End If
End If
Next

End Sub

If this post helps click Yes
---------------
Jacob Skaria


"CR" wrote:

Dave Thank you
It does exactly what I wanted.

What is the best way to check for illegal values? ie if either or both of
the two summed cells have a value of 0, I would want to skip to the next
icol

CR



"Dave Peterson" wrote in message
...
Dim iCol as long
dim FirstCol as long
dim LastCol as long

with worksheets("Somesheetnamehere")
firstcol = .range("d1").column
lastcol = .range("s1").column

for icol = firstcol to lastcol
'no check for illegal values!
if (.cells(3,icol).value + .cells(5,icol).value) _
.cells(1,icol).value then
'do something
else
'do nothing
end if
next icol
end with

Untested, uncompiled. Watch for typos.

CR wrote:

I have a Range with values D1:S5

I need to sum D3 and D5 and if it is D1 value then
Do something

Then loop through E to S, doing the same thing

Any help would be appreciated

Thanks
CR

--

Dave Peterson

loop variable range
 

I'd use:


Option Explicit
Sub testme()
Dim iCol As Long
Dim FirstCol As Long
Dim LastCol As Long

With Worksheets("Somesheetnamehere")
FirstCol = .Range("d1").Column
LastCol = .Range("s1").Column

For iCol = FirstCol To LastCol
If Application.IsNumber(.Cells(3, iCol).Value) Then
If Application.IsNumber(.Cells(5, iCol).Value) Then
If .Cells(3, iCol).Value < 0 Then
If .Cells(5, iCol).Value < 0 Then
If (.Cells(3, iCol).Value + .Cells(5, iCol).Value) _
.Cells(1, iCol).Value Then
'do something
Else
'do nothing
End If
End If
End If
End If
End If
Next iCol
End With

End Sub

(compiled, but not tested)



CR wrote:

Dave Thank you
It does exactly what I wanted.

What is the best way to check for illegal values? ie if either or both of
the two summed cells have a value of 0, I would want to skip to the next
icol

CR

"Dave Peterson" wrote in message
...
Dim iCol as long
dim FirstCol as long
dim LastCol as long

with worksheets("Somesheetnamehere")
firstcol = .range("d1").column
lastcol = .range("s1").column

for icol = firstcol to lastcol
'no check for illegal values!
if (.cells(3,icol).value + .cells(5,icol).value) _
.cells(1,icol).value then
'do something
else
'do nothing
end if
next icol
end with

Untested, uncompiled. Watch for typos.

CR wrote:

I have a Range with values D1:S5

I need to sum D3 and D5 and if it is D1 value then
Do something

Then loop through E to S, doing the same thing

Any help would be appreciated

Thanks
CR

--

Dave Peterson

--

Dave Peterson

loop variable range
 

Perfect!
Thank you.

CR


"Dave Peterson" wrote in message
...
I'd use:


Option Explicit
Sub testme()
Dim iCol As Long
Dim FirstCol As Long
Dim LastCol As Long

With Worksheets("Somesheetnamehere")
FirstCol = .Range("d1").Column
LastCol = .Range("s1").Column

For iCol = FirstCol To LastCol
If Application.IsNumber(.Cells(3, iCol).Value) Then
If Application.IsNumber(.Cells(5, iCol).Value) Then
If .Cells(3, iCol).Value < 0 Then
If .Cells(5, iCol).Value < 0 Then
If (.Cells(3, iCol).Value + .Cells(5,
iCol).Value) _
.Cells(1, iCol).Value Then
'do something
Else
'do nothing
End If
End If
End If
End If
End If
Next iCol
End With

End Sub

(compiled, but not tested)



CR wrote:

Dave Thank you
It does exactly what I wanted.

What is the best way to check for illegal values? ie if either or both of
the two summed cells have a value of 0, I would want to skip to the next
icol

CR

"Dave Peterson" wrote in message
...
Dim iCol as long
dim FirstCol as long
dim LastCol as long

with worksheets("Somesheetnamehere")
firstcol = .range("d1").column
lastcol = .range("s1").column

for icol = firstcol to lastcol
'no check for illegal values!
if (.cells(3,icol).value + .cells(5,icol).value) _
.cells(1,icol).value then
'do something
else
'do nothing
end if
next icol
end with

Untested, uncompiled. Watch for typos.

CR wrote:

I have a Range with values D1:S5

I need to sum D3 and D5 and if it is D1 value then
Do something

Then loop through E to S, doing the same thing

Any help would be appreciated

Thanks
CR

--

Dave Peterson

--

Dave Peterson