Loop through cells

[Sheeny]

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

 

[Bob Phillips]

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)

 

[George Nicholson]

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.

 

[kkknie]

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

 

[Sheeny]

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

 

[Bob Phillips]

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]

Thank you!

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

 

[Dana DeLouis]

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