Loop through cells
 

Hi,
Im an amateur in the Excel/ VB world and could really appreciate som
help here. Here's what Im doing.

Based on what a user selects in a list box, I want Excel to searc
througuh all the records in column A (range A4:A135) and if an entry i
column A matches the section from the list, to print 'Old' in the nex
column, same row.

Here's what I have:

Private Sub CmdIdentifyOld_Click()
Sheet2.Activate
Sheet2.Range("A3").Select

For i = 1 To 135
If ActiveCell.Text = ListOld.Value Then
ActiveCell.Next.Value = "Old"
Else
ActiveCell = ActiveCell.Offset(0, 1)
End If
Next i


End Sub

-------------------------------------------------------------------------

However, this doesn't really do much.
It does go to teh second worksheet and cell A3. However it doesnt loo
through the cells and match a value - it simply deletes the value i
A3.

Any help will be GREATLY appreciated.

Thanks

--
Message posted from http://www.ExcelForum.com

Loop through cells
 

For i = 4 To 135
If Cells(i,"A").Value = ListOld.Value Then
Cells(i,"A").Offset(0,1).Value = "Old"
End If
Next i



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Sheeny " wrote in message
...
Hi,
Im an amateur in the Excel/ VB world and could really appreciate some
help here. Here's what Im doing.

Based on what a user selects in a list box, I want Excel to search
througuh all the records in column A (range A4:A135) and if an entry in
column A matches the section from the list, to print 'Old' in the next
column, same row.

Here's what I have:

Private Sub CmdIdentifyOld_Click()
Sheet2.Activate
Sheet2.Range("A3").Select

For i = 1 To 135
If ActiveCell.Text = ListOld.Value Then
ActiveCell.Next.Value = "Old"
Else
ActiveCell = ActiveCell.Offset(0, 1)
End If
Next i


End Sub

-------------------------------------------------------------------------

However, this doesn't really do much.
It does go to teh second worksheet and cell A3. However it doesnt loop
through the cells and match a value - it simply deletes the value in
A3.

Any help will be GREATLY appreciated.

Thanks!


---
Message posted from http://www.ExcelForum.com/

Loop through cells
 

Try something like:

Private Sub CmdIdentifyOld_Click()
Sheet2.Activate
Sheet2.Range("A3").Select

For i = 1 To 135
If ActiveCell.Text = ListOld.Value Then
ActiveCell.Next.Value = "Old"
End if
ActiveCell = ActiveCell.Offset(0, 1)
Next i

End Sub

You don't want the Offset as an Else condition. You want it to execute every
time whether a match is found or not.

Also: generally there is a performance hit when using Select, Activate,
ActiveCell etc. extensively. It can't always be avoided, but trying is good
habit to get into. If you are interested (this is untested air code)

Dim rng as Range

Set rng = Sheet2.Range("A3")

For i = 1 To 135
If rng = ListOld.Value Then
rng = "Old"
End if
set rng = rng.Offset(0, 1)
Next i

--

HTH,
George Nicholson

Remove 'Junk' from return address.


"Sheeny " wrote in message
...
Hi,
Im an amateur in the Excel/ VB world and could really appreciate some
help here. Here's what Im doing.

Based on what a user selects in a list box, I want Excel to search
througuh all the records in column A (range A4:A135) and if an entry in
column A matches the section from the list, to print 'Old' in the next
column, same row.

Here's what I have:

Private Sub CmdIdentifyOld_Click()
Sheet2.Activate
Sheet2.Range("A3").Select

For i = 1 To 135
If ActiveCell.Text = ListOld.Value Then
ActiveCell.Next.Value = "Old"
Else
ActiveCell = ActiveCell.Offset(0, 1)
End If
Next i


End Sub

-------------------------------------------------------------------------

However, this doesn't really do much.
It does go to teh second worksheet and cell A3. However it doesnt loop
through the cells and match a value - it simply deletes the value in
A3.

Any help will be GREATLY appreciated.

Thanks!


---
Message posted from http://www.ExcelForum.com/

Loop through cells
 

Code
-------------------
Private Sub CmdIdentifyOld_Click()

Dim i as Long

Sheet2.Select

For i = 1 To 135
If LCase(Cells(i,1).Value) = LCase(ListOld.Value) Then
Cells(i,2).Value = "Old"
End If
Next i

End Su
-------------------

You could also change the loop to be: For i = 1 t
Range("A65536").End(xlUp).Row to keep from having to increase from 13
if you add values to the A row.



--
Message posted from http://www.ExcelForum.com

Loop through cells
 

Thanks a lot!

My final code:

Private Sub CmdIdentifyOld_Click()
Sheet2.Activate
Sheet2.Range("A3").Select

For i = 1 To Range("A65536").End(xlUp).Row
If ActiveCell.Text = ListOld.Value Then
ActiveCell.Next.Value = "Old"
Else
Sheet2.Range("A" & i).Select
End If
Next i

works wonderfully


---
Message posted from http://www.ExcelForum.com/

Loop through cells
 

It's absolutely unnecessary and wasteful to keep selecting the cells

Private Sub CmdIdentifyOld_Click()

With Sheet2

For i = 1 To .Range("A" & Rows.Count).End(xlUp).Row
If .Cells(i,"A").Value = ListOld.Value Then
.Cells(i,"A").Offset(0,1).Value = "Old"
End If
Next i
End With

is much more efficient


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Sheeny " wrote in message
...
Thanks a lot!

My final code:

Private Sub CmdIdentifyOld_Click()
Sheet2.Activate
Sheet2.Range("A3").Select

For i = 1 To Range("A65536").End(xlUp).Row
If ActiveCell.Text = ListOld.Value Then
ActiveCell.Next.Value = "Old"
Else
Sheet2.Range("A" & i).Select
End If
Next i

works wonderfully


---
Message posted from http://www.ExcelForum.com/

Loop through cells
 

Thank you!

I was trying to figure out how to make it run faster

--
Message posted from http://www.ExcelForum.com

Loop through cells
 

Another option that is similar:

Private Sub CmdIdentifyOld_Click()
Dim cell As Range
Dim r As Long

Sheet2.Activate
For Each cell In Range("A4", Range("A65536").End(xlUp))
If StrComp(cell.Text, ListOld.Value, vbTextCompare) = 0 Then
cell.Offset(0, 1) = "Old"
Next cell
End Sub


--
Dana DeLouis
Using Windows XP & Office XP
= = = = = = = = = = = = = = = = =


"Sheeny " wrote in message
...
Thank you!

I was trying to figure out how to make it run faster!


---
Message posted from http://www.ExcelForum.com/