move from active cell offset in macro

A

april

i have this macro
Sub loopthrough()
Dim myRow As Integer
myRow = 1

Range("AI225").Select
Do Until myRow = 10
If ActiveCell.Offset(0, -15).Range("a1").Select = "General Research" Then
ActiveCell.Offset(0, 15).Range("a1") = "MIT0000"
End If


ActiveCell.Offset(1, 0).Select

myRow = myRow + 1

Loop

End Sub
1.want to start at cell ai225.
2. go to cell t225 and if the contents = General Research
3 then go to cell ai and enter MIT0000
4. go the the enxt row and do the same thing

problem is if t225 does not equal "General Research" the active cell is t225
and the next iteration takes me 15 rows over from t225. i want to start the
if statement from ai226

thanks in advance for you help. i just thought of something - i could get
it back to ai 225 by making the alternative to MIT0000 a blank but i don't
know the code protocal for this.
 
M

Mike H

Hi,

Try this

Sub loopthrough()
Dim MyRange As Range
Set MyRange = Range("T225:T234")
For Each c In MyRange
If c.Value = "General Research" Then
c.Offset(0, 15).Value = "MIT0000"
End If
Next
End Sub


Mike
 
D

Dave Peterson

Check your other post.
i have this macro
Sub loopthrough()
Dim myRow As Integer
myRow = 1

Range("AI225").Select
Do Until myRow = 10
If ActiveCell.Offset(0, -15).Range("a1").Select = "General Research" Then
ActiveCell.Offset(0, 15).Range("a1") = "MIT0000"
End If

ActiveCell.Offset(1, 0).Select

myRow = myRow + 1

Loop

End Sub
1.want to start at cell ai225.
2. go to cell t225 and if the contents = General Research
3 then go to cell ai and enter MIT0000
4. go the the enxt row and do the same thing

problem is if t225 does not equal "General Research" the active cell is t225
and the next iteration takes me 15 rows over from t225. i want to start the
if statement from ai226

thanks in advance for you help. i just thought of something - i could get
it back to ai 225 by making the alternative to MIT0000 a blank but i don't
know the code protocal for this.
 
A

April

Thanks,Mike

i am unsure of what the "c" stands for in your macro. does it mean
ActiveCell? when i compile the macro i get an error message that says
"Variable required"

Please help. thanks

BDW
 
G

Gord Dibben

"c" is same as "cell"

If you have Option Explicit at top and "c" is not declared then you get the
error.

Try declaring "c"

Sub loopthrough()
Dim MyRange As Range
Dim c As Range
Set MyRange = Range("B225:B234")
For Each c In MyRange
If c.Value = "General Research" Then
c.Offset(0, 15).Value = "MIT0000"
End If
Next
End Sub


Gord Dibben MS Excel MVP
 

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

Top