Looping through selected Rows

[taltyr]

I have code that runs a series of actions on a single row. If I have ten rows of Data, I may not want to run the code on all Rows. Instead, what I want to do is select a number of rows and run the Macro only on those selected rows.

I imagine this must be some form of, For (each Row in Selection) Next, type loop, but I can't get it to work. At the moment If I select Rows 2, 3 and 5 the code will run three times on Row 5 (i.e. the last Row I selected). If I only select two Rows the code will run twice on the last Row I selected.

My main code does involve the selection of Cells on the active Row and I guess this must be throwing things off.

Any help greatly appreciated.

taltyr

 

[p45cal]

It would be helpful to see your code to understand why it's not processing things as you expect, however explore:

For Each rw In Selection.Rows
'rw.Select
'your code acting on a single row in the sheet, rw.
Next rw

 

[taltyr]

Thanks,

When I run the amanded Macro, I get the same result;

I select Row 2 and Row 5 and run the code. What happens is that it runs on Row 5 and then loops back and repeats on Row 5. It does not touch Row 2 at all.

Thanks for your time.

taltyr

 

[p45cal]

Again, it would be helpful to see your code to understand why it's not processing things as you expect.

 

[taltyr]

Here is the Code. I have some tidying up to do but it should let you see what I am trying to loop;

Sub FillCashFlow()
      
  '   Dim Cell As Object
     Dim Duration As Integer
     Dim Budget As Long
     Dim MthExp As Long
     Dim Mth1 As Long
     Dim MthLast As Long
     Dim Retention As Long
     Dim StartDate As Date
     Dim FinishDate As Date
     Dim PramA As Integer
     Dim PramB As Integer
     Dim Period As Integer
     Dim ToDate As Range
     Dim StartMth As Range
     Dim EndMth As Range
     Dim ContractExp As Long
     Dim Loading As String
    ' Dim Selection As String
     Dim Project As Range
     
     
  'Application.ScreenUpdating = False
     
 'Clear Previous
    'Range("F2:AC8").Select
    'Selection.ClearContents
    'Selection.Interior.Pattern = xlNone
    'Selection.Interior.TintAndShade = 0
    'Selection.Style = "Comma"
    'Range("A2").Select
     
  ' Set Do loop to stop when an empty cell is reached.
     ' Do Until IsEmpty(ActiveCell)
'Selection = ActiveWindow.RangeSelection.Address
For Each Project In Selection.Rows
         'ProjectSelected = Projects.Row
         
         
         Budget = 0
         Duration = 0
         StartDate = 0
         FinishDate = 0
         Loading = ""
       
        
                  
            'Get Start Date
            Cells(ActiveCell.Row, 1).Select
      
                 Do
                     If Cells(1, ActiveCell.Column) = "Start Date" Then StartDate = ActiveCell.Value
                    ActiveCell.Offset(0, 1).Select
                 Loop Until StartDate > 0
                                   
            
                    
            'Get Finish Date
            Cells(ActiveCell.Row, 1).Select
            
                Do
                     If Cells(1, ActiveCell.Column) = "Finish Date" Then FinishDate = ActiveCell.Value
                     ActiveCell.Offset(0, 1).Select
                 Loop Until FinishDate > 0
                 
            'Calculate Project Duration
            Duration = DateDiff("M", StartDate, FinishDate) + 1
            
                    
            'Get Budget
            Cells(ActiveCell.Row, 1).Select
                Do
                     If Cells(1, ActiveCell.Column) = "Budget" Then Budget = ActiveCell.Value
                     ActiveCell.Offset(0, 1).Select
                 Loop Until Budget > 0
                 
                                            
            ContractExp = Budget * 0.8
            MthExp = (Budget * 0.8) / Duration
            Mth1 = MthExp + Budget * 0.1
                 
            'Get Loading
            Cells(ActiveCell.Row, 1).Select
                Do
                     If Cells(1, ActiveCell.Column) = "Loading" Then Loading = ActiveCell.Value
                     ActiveCell.Offset(0, 1).Select
                 Loop Until Not Loading = ""
                 
                'MsgBox Loading
                 
                 If Loading = "F" Then PramA = 1 And PramB = 0
                 'If Loading = "M" Then PramA = 0 And PramB = 1
                 
                 If Loading = "B" Then PramA = 0 And PramB = 0 Else: PramA = 0 And PramB = 1
   
                 MsgBox PramA & PramB
                 
            'Start at Start Date
             
             Cells(ActiveCell.Row, 1).Select
                 
                 Do
                     
                     ActiveCell.Offset(0, 1).Select
                     If Cells(1, ActiveCell.Column) = StartDate Then _
                     ActiveWorkbook.Names.Add Name:="StartMth", RefersToR1C1:=ActiveCell
                     'If Cells(1, ActiveCell.Column) = StartDate Then ActiveCell.Interior.Color = 65535
                     If Cells(1, ActiveCell.Column) = StartDate Then ActiveCell.Value = 0
                 Loop Until Cells(1, ActiveCell.Column) = StartDate
                 
                            
                    Period = 0
                    Duration = Duration - 1
                    
                    ActiveCell.Offset(0, 1).Select
                    
                    Do
                    
                    Period = Period + 1
            'MsgBox Application.Sum(Range("StartMth"), "ActiveCell.Offset(0, -1))")
                    ActiveCell.Value = ((10 * (Period / Duration) ^ 2 * (1 - (Period / Duration)) ^ 2 _
                 * (0 + 1 * (Period / Duration)) + (Period / Duration) ^ 4 _
                 * (5 - 4 * (Period / Duration))) * ContractExp) - WorksheetFunction.Sum(Range("StartMth", ActiveCell.Offset(0, -1)))
                
                     ActiveCell.Offset(0, 1).Select
                     Loop Until Period = Duration
                            
                      ActiveCell.Offset(0, -1).Select
             ActiveWorkbook.Names.Add Name:="EndMth", RefersToR1C1:=ActiveCell
 
            'MsgBox Application.Sum("Range(StartMth)": Range("EndDate"))
            
            
            'Pay Advance Payment
            Application.Goto Reference:="StartMth"
            ActiveCell.Value = ActiveCell.Value + (Budget * 0.1)
              
            
            'Release 1st Half Retention
            Application.Goto Reference:="EndMth"
            ActiveCell.Value = ActiveCell.Value + (Budget * 0.05)
            
                     
            'Release 2nd half Retention
                     ActiveCell.Offset(0, 12).Select
                     'Selection.Interior.Color = 65535
                    ActiveCell.Value = Budget * 0.05
                     
                 
                ' MsgBox "Start Date  " & StartDate & vbNewLine & "Finish Date  " & FinishDate & vbNewLine & "Budget   " & Budget
         '   Cells(ActiveCell.Row, 1).Select
          '  ActiveCell.Offset(1, 0).Select
      
                
         'Loop
Next Project
' Application.ScreenUpdating = True
   End Sub

 

[p45cal]

This code is far too dependant on the activecell and its moving around, however, a quick fix might be to change within the For Each Project in Selection.Rows… …Next Project loop, where you have:

ActiveCell.Row
change it to:
Project.Row

 

[taltyr]

Thank You, will try and let you know how it works.

Regards

taltyr