Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
loop variable range | Excel Programming | |||
loop variable range | Excel Programming | |||
loop variable range | Excel Programming | |||
Referencing variable Range in a loop | Excel Programming | |||
How to reference variable range in a loop | Excel Programming |