do loop problem

  • Thread starter Thread starter dwalkerc
  • Start date Start date
D

dwalkerc

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)
 
better yet, simply:

ActiveCell.Offset(1, 0).Select

(I never use Offset so I didn't know I could do it that way)
 
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 said:
better yet, simply:

ActiveCell.Offset(1, 0).Select

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


[email protected] said:
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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Loop to End 0
Looping through Worksheets_(excluding one) 4
How do I write a looping in VBA in Excel? 2
How do I make an absolute ref in a macro able to loop? 2
Need Help - How do I stop using select 3
For...Next or Select Case Statement 3
Between Dates Criteria 4
Do until loop 6

Back
Top