I have written the following code and cannot understand why it does not
run past the first row of information. My worksheet currently has five
rows of data and the select/case works great on the first row but it
does not move to the next row to run select/case [or so I assume]

What am I missing

Dim job As String
Names.Add Name:="OTcal", RefersTo:="=r33:s33", Visible:=True
Do
job = ActiveCell
Select Case job
Case Is = "RehabNorcran"
Range("R44:s44").Select
Selection.Copy
Range("OTcal").Select.Paste
ActiveSheet.Copy
Case Is = "RehabCarman"
Range("R44:s44").Select
Selection.Copy
Range("OTcal").Select
ActiveSheet.Paste
Case Is = "Shop- Day"
Range("R47:s44").Select
Selection.Copy
Range("OTcal").Select
ActiveSheet.Paste
Case Is = "Operation"
Range("r54:s54").Select
Selection.Copy
Range("OTcal").Select
ActiveSheet.Paste
End Select

Loop Until IsEmpty(ActiveCell.Offset(1, 0))
MsgBox "all calcuation is done"


End Sub

Do
Select Case ActiveCell
Case Is = "RehabNorcran"
Case Is = "RehabCarman"
Case Is = "Shop- Day"
Case Is = "Operation"
End Select

Range(ActiveCell.Offset(1, 0).Address).Select
Loop Until IsEmpty(ActiveCell)


" wrote:

I have written the following code and cannot understand why it does not
run past the first row of information. My worksheet currently has five
rows of data and the select/case works great on the first row but it
does not move to the next row to run select/case [or so I assume]

What am I missing

Dim job As String
Names.Add Name:="OTcal", RefersTo:="=r33:s33", Visible:=True
Do
job = ActiveCell
Select Case job
Case Is = "RehabNorcran"
Range("R44:s44").Select
Selection.Copy
Range("OTcal").Select.Paste
ActiveSheet.Copy
Case Is = "RehabCarman"
Range("R44:s44").Select
Selection.Copy
Range("OTcal").Select
ActiveSheet.Paste
Case Is = "Shop- Day"
Range("R47:s44").Select
Selection.Copy
Range("OTcal").Select
ActiveSheet.Paste
Case Is = "Operation"
Range("r54:s54").Select
Selection.Copy
Range("OTcal").Select
ActiveSheet.Paste
End Select

Loop Until IsEmpty(ActiveCell.Offset(1, 0))
MsgBox "all calcuation is done"


End Sub

better yet, simply:

ActiveCell.Offset(1, 0).Select

(I never use Offset so I didn't know I could do it that way)


" wrote:

I have written the following code and cannot understand why it does not
run past the first row of information. My worksheet currently has five
rows of data and the select/case works great on the first row but it
does not move to the next row to run select/case [or so I assume]

What am I missing

Dim job As String
Names.Add Name:="OTcal", RefersTo:="=r33:s33", Visible:=True
Do
job = ActiveCell
Select Case job
Case Is = "RehabNorcran"
Range("R44:s44").Select
Selection.Copy
Range("OTcal").Select.Paste
ActiveSheet.Copy
Case Is = "RehabCarman"
Range("R44:s44").Select
Selection.Copy
Range("OTcal").Select
ActiveSheet.Paste
Case Is = "Shop- Day"
Range("R47:s44").Select
Selection.Copy
Range("OTcal").Select
ActiveSheet.Paste
Case Is = "Operation"
Range("r54:s54").Select
Selection.Copy
Range("OTcal").Select
ActiveSheet.Paste
End Select

Loop Until IsEmpty(ActiveCell.Offset(1, 0))
MsgBox "all calcuation is done"


End Sub

That didn't work, but I may have figured out why.

The process starts with active.cell being b33 - depending on what is in
b33 case determines which formula to paste into r33 and s33.

What I think is happening is after it determines what formula to paste
it stays in the cell that is too be copied and does
Range(ActiveCell.Offset(1, 0).Address).Select

so now I have to figure out how to get it back to b33 - when it is
variable.

any ideas?
Charlie wrote:
better yet, simply:

ActiveCell.Offset(1, 0).Select

(I never use Offset so I didn't know I could do it that way)


" wrote:

I have written the following code and cannot understand why it does not
run past the first row of information. My worksheet currently has five
rows of data and the select/case works great on the first row but it
does not move to the next row to run select/case [or so I assume]

What am I missing

Dim job As String
Names.Add Name:="OTcal", RefersTo:="=r33:s33", Visible:=True
Do
job = ActiveCell
Select Case job
Case Is = "RehabNorcran"
Range("R44:s44").Select
Selection.Copy
Range("OTcal").Select.Paste
ActiveSheet.Copy
Case Is = "RehabCarman"
Range("R44:s44").Select
Selection.Copy
Range("OTcal").Select
ActiveSheet.Paste
Case Is = "Shop- Day"
Range("R47:s44").Select
Selection.Copy
Range("OTcal").Select
ActiveSheet.Paste
Case Is = "Operation"
Range("r54:s54").Select
Selection.Copy
Range("OTcal").Select
ActiveSheet.Paste
End Select

Loop Until IsEmpty(ActiveCell.Offset(1, 0))
MsgBox "all calcuation is done"


End Sub