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


--
short_n_curly
------------------------------------------------------------------------
short_n_curly's Profile: http://www.excelforum.com/member.php...o&userid=21576
View this thread: http://www.excelforum.com/showthread...hreadid=384154

This will lopp ten times:

For i = 1 to 10

'here goes some code

Next i


--
Kaak
------------------------------------------------------------------------
Kaak's Profile: http://www.excelforum.com/member.php...fo&userid=7513
View this thread: http://www.excelforum.com/showthread...hreadid=384154

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


--
short_n_curly
------------------------------------------------------------------------
short_n_curly's Profile: http://www.excelforum.com/member.php...o&userid=21576
View this thread: http://www.excelforum.com/showthread...hreadid=384154

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"
wrote in message
news:short_n_curly.1rlbuf_1120388704.4465@excelfor um-nospam.com...

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


--
short_n_curly
------------------------------------------------------------------------
short_n_curly's Profile:
http://www.excelforum.com/member.php...o&userid=21576
View this thread: http://www.excelforum.com/showthread...hreadid=384154

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





short_n_curly wrote:

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

--
short_n_curly
------------------------------------------------------------------------
short_n_curly's Profile: http://www.excelforum.com/member.php...o&userid=21576
View this thread: http://www.excelforum.com/showthread...hreadid=384154

--

Dave Peterson

Ignore this.

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

Dave Peterson wrote:

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

short_n_curly wrote:

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

--
short_n_curly
------------------------------------------------------------------------
short_n_curly's Profile: http://www.excelforum.com/member.php...o&userid=21576
View this thread: http://www.excelforum.com/showthread...hreadid=384154

--

Dave Peterson

--

Dave Peterson

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


--
short_n_curly
------------------------------------------------------------------------
short_n_curly's Profile: http://www.excelforum.com/member.php...o&userid=21576
View this thread: http://www.excelforum.com/showthread...hreadid=384154

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"
wrote in message
news:short_n_curly.1rl92c_1120385103.2292@excelfor um-nospam.com...

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


--
short_n_curly
------------------------------------------------------------------------
short_n_curly's Profile:
http://www.excelforum.com/member.php...o&userid=21576
View this thread: http://www.excelforum.com/showthread...hreadid=384154