loop variable range

[CR]

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

 

[Patrick Molloy]

for cl = 5 to 20 'whatever S is

if ( cells(3,cl) + cells(5,cl) ) > cells(1,cl) then
'do something
next

 

[Dave Peterson]

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.

 

[Jim Thomlinson]

One way...

Dim rng As Range

For Each rng In Range("D1:S1")
If Application.Sum(rng.Offset(2, 0).Resize(3, 1)) > rng.Value Then
MsgBox rng.Address
End If
Next rng

 

[Jim Thomlinson]

That adds cells in rows 3 and 5, not 3, 4 and 5.

 

[Jacob Skaria]

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

End Sub


If this post helps click Yes

 

[Dave Peterson]

I need to sum D3 and D5...

sounds like it's not D3 through D5.

 

[Jim Thomlinson]

Oops... I misread the question...

 

[Jim Thomlinson]

I misread the question...

Dim rng As Range

For Each rng In Range("D1:S1")
If rng.Offset(2, 0).value + rng.Offset(4, 0).value > rng.Value Then
MsgBox rng.Address
End If
Next rng

 

[CR]

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

 

[Jacob Skaria]

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

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]

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

.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]

Perfect!
Thank you.

CR

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) _
'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)