Please help with the simple loop

[Evgeniygor]

Hi Everyone,

I desparately need your help.
I need to write a simple loop, but I am too new to VBA to do this properly.

Excuse me for such a simple question, but this place seems like the only one, where I can get some help.

the thing I need to do is:

I have several columns

A contains data already.
B has comments about this data.
C is used to input data by me.
What I need to do is that every time I isert some data in C, if it's already in A I need to have comments about this data in D.

Basically I need to check if the value in the cell having been changed is the same as in some data that already exists.

Please help. My loops are not working.
In reality my task is a little more complicated, but If you advice me the simple way of solving, I guess I will get how to apply that to the thing I really need to do.

The only thing I know is that I need to input the code into Sheet1, not into a module. I tried doing DO...LOOP, but I failed. It either doesn't work, or it runs an endless loop, which is bad.

[CreganTur]

I haven't looked at your workbook yet- I will in a minute- but for a detailed explination of the different loops available in VBA and how they work, click on Articles below my name and follow the links to the article I wrote about loops. It might help you.

NinjaEdit: You posted an Excel 2007 workbook. A lot of people here don't use 2007. Can you please upload a 2003 compatible version?

[Bob Phillips]

Why not just use a simple VLOOKUP

=IFERROR(VLOOKUP(C2,A:B,2,False),"")

[Paul_Hossler]

You're right that you need to the Worksheet 1 change event handler

When a cell or cells value changes, Target get the range of changed cells

So if you paste or fill a name into more than one cell, each will be checked

I did save in 2007 macro enabled format (XLSM)

[vba]
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rData As Range, rCell As Range
Dim sComment As String

'set working range
Set rData = ActiveSheet.Cells(1, 1).CurrentRegion

'do each cell in Target (the range that changed) -- always wanted to say that :-)
For Each rCell In Target.Cells

If rCell.Column = 3 Then

If Len(rCell.Value) = 0 Then
rCell.Offset(0, 1).ClearContents

Else
sComment = ""
On Error Resume Next
sComment = Application.WorksheetFunction.VLookup(rCell.Value, rData, 2, False)
On Error GoTo 0

If sComment <> "" Then
rCell.Offset(0, 1).Value = sComment
End If
End If
End If

Next
End Sub

[/vba]

[Evgeniygor]

Randy:

No need to upload 2003, I already have a solution, but thank you very much for anyway.

xld:

Formulas are not fun VBA is. Though they do work, I am just eager to learn how to handle VBA, because having troubles with such simple thins as loop I kind of feel dumb. Also, at my work I have about 5 excel tables I need to do similar records to and it drives me nutts!!! And I am not the only person in the office. I want to make things work much better, hopefully I will manage to.

Paul:

GREAT!!!
The code is so much more complicated and nice then something I tried to do.
(Plus it actually works)

Everyone:
First of all thank you, you've been really helpful and I will soon trouble you with more stupid questions (but they will get more and more complicated)
Just a simple one more thing I am having troubles with.

How do I refer to the other excel file?

Meaning I need to compare data in my active worksheet with data in another file?

Thank you all once again