help needed with LOOPS please

[short_n_curly]

basically i have this code

Private Sub Worksheet_Activate()


If Range("AE1") = Range("A1") Then
Range("A1").Offset(0, 1) = Range("AD1")
Else
If Range("AE1") = Range("A2") Then
Range("A2").Offset(0, 1) = Range("AD1")
End If
End If

End Sub

so that when the worksheet is activated it will check cell a1 to see if
it equals cell ae1
if so then it will place the value in cell ad1 to the next cell i.e b1
if this is false it will move on to cell a2 with the same criteria
however it would be impractical to persue with the currant code even
though it works due to the fact that there may be several thousand
entries, is there a loop code that would change the variables as i have
never used loops before i am a bit lost

your help would be much appreciated thanks

 

[Kaak]

This will lopp ten times:

For i = 1 to 10

'here goes some code

Next i

 

[short_n_curly]

some of your reply seems to be missing do you mean change the variable
cell to i there for the code would resemble

If Range("AE1") = i Then
i.Offset(0, 1) = Range("AD1")

?

if this is right how do you make i (cell a1) change to the next cell a2
all the way down to the last cell in the column, to save time ending the
loop when the acenario is true?

range ae1 and ad1 are absoulte referances but a1 is relative and will
need to change down the column and finally when the loop ends ad1 will
then be sent to the cell NEXT to the final referance point, hence the
offset code

thanks for the reply

 

[Dave Peterson]

How do you know when to stop?

I used the last entry in column A in this sample:

Option Explicit
Private Sub Worksheet_Activate()

Dim myCell As Range
Dim myRng As Range

With Me
Set myRng = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

On Error Resume Next
Application.EnableEvents = False
For Each myCell In myRng.Cells
If myCell.Value = Me.Range("ae1").Value Then
myCell.Offset(0, 1).Value = Me.Range("ad1").Value
End If
Next myCell
Application.EnableEvents = True
On Error GoTo 0

End Sub

 

[alf bryn]

This is one possible solution:

Sub Macro1()
'
Dim i As Integer

For i = Range("A" & Rows.Count).End(xlUp).Row To 1 Step -1

If Cells(i, "A").Value = Range("AE1").Value Then

Cells(i, "B").Value = Range("AD1").Value

End If

Next i
'
End Sub

Asuming that you compaire all values in column A to a value in AE1. If you
are compairing A1 to AE1, A2 to AE2 and so forth cange
Range("AE1") to Cells(i, "AE")

so that when the worksheet is activated

Not certain what you want to do. You could rename this macro Auto_Open and
it will then run every time you open the workbook.

"short_n_curly" <[email protected]>
wrote in message

 

[Bob Phillips]

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iRow As Long

On Error GoTo ws_exit:
Application.EnableEvents = False

With Target
If .Address = "$AE$1" Then
On Error Resume Next
iRow = Application.Match(.Value, Range("A:A"), 0)
On Error GoTo 0
If iRow > 0 Then
Me.Range("A" & iRow).Offset(0, 1) = Me.Range("AD1")
End If
End If
End With

ws_exit:
Application.EnableEvents = True
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"short_n_curly" <[email protected]>
wrote in message
news:[email protected]...

 

[Dave Peterson]

Ignore this.

I didn't see the "Else" portion of your code.

 

[short_n_curly]

thanks dave code is bob on perfect great help much apprechiated also
thanks to all others for your help