Need Help with event

[auto998]

Hello,
I have been looking into this for awhile. I have a very basic knowledge with excel 2003.
I am trying to create a macro based on events. The event should be triggered as soon as the user leaves the cell by clicking on a different cell or by using the arrows on the keyboard. So if the user type SONY in cell A1, A2-A10 will be populated right on the spot automatically based on the word Sony, for example A2 will have electronic populated automatically, A3 will have <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1:country-region w:st="on"><st1lace w:st="on">Japan</st1lace></st1:country-region>...and so on <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
If the user enter GMC instead in cell A1, A2-A10 will be populated right on the spot automatically based on the word GMC. A2 will have Automaker populated automatically, A3 will have <st1:country-region w:st="on"><st1lace w:st="on">USA</st1lace></st1:country-region>, ...and so on.
After I created a small VB sample it looks like I need to press the execute button every time to run the program in VB instead of triggering an event automatically.
Can you please help? Here is a small sample I am using to test:
<o></o>
Sub test()<o></o>
<o></o>
<o></o>
If Range("a1").Value = 10 Then<o></o>
<o></o>
Range("c1").Value = "Yes"<o></o>
Else<o></o>
Range("c1").Value = "No"<o></o>
End If<o></o>
<o></o>
<o></o>
End Sub

 

[auto998]

Sometimes I have to move columns around, add or even delete columns. So the location for the column could change and reference could be tricky and involved for every change. What I have in mind is to assign each column to a variable, and in case I need to move things around it will be the variables for the columns names.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
<o></o>
Also I am having (run-time error'13': Type mismatch) when I copy multiple cells from a different excel file in paste them in row 1<o></o>
Thanks again for the help<o></o>

 

[VoG]

The only thing fixed in the code is the row (1). Everything else is done using offsets.

This will fix the error:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
If Target.Row = 1 Then
    Application.EnableEvents = False
    For Each c In Target
        Select Case LCase(c.Value)
            Case "sony"
                c.Offset(1).Value = "electronic"
                c.Offset(2).Value = "Japan"
            Case "gmc"
                c.Offset(1).Value = "automaker"
                c.Offset(2).Value = "USA"
        End Select
    Next c
    Application.EnableEvents = True
End If
End Sub

 

[auto998]

Thanks...I have another problem. When I copy multiple cells from a different excel file and paste them in column A, I get the result in one row ONLY. For example, if I past multiple cells all at once in A2, A3 and A4. The code will generate the results in F2 and G2. What I want to do is to past the result of A2 in (F2 and G2), A3 in (F3 and G3) and A4 in (F4 and G4) ...Any ideas? Thanks again for the help.
Here is a copy of the code:

Function ColumnLetter(ColumnNumber As Integer) As String
  If ColumnNumber > 26 Then

    ' 1st character:  Subtract 1 to map the characters to 0-25,
    '                 but you don't have to remap back to 1-26
    '                 after the 'Int' operation since columns
    '                 1-26 have no prefix letter

    ' 2nd character:  Subtract 1 to map the characters to 0-25,
    '                 but then must remap back to 1-26 after
    '                 the 'Mod' operation by adding 1 back in
    '                 (included in the '65')

    ColumnLetter = Chr(Int((ColumnNumber - 1) / 26) + 64) & _
                   Chr(((ColumnNumber - 1) Mod 26) + 65)
  Else
    ' Columns A-Z
    ColumnLetter = Chr(ColumnNumber + 64)
  End If
End Function

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
Dim brand As Integer
Dim Mis As Integer
Dim dept As Integer
Dim made As Integer
Dim extra As Integer
Dim NextCell As String


brand = 1   'brand column
Mis = 2     'Mis column
dept = 6    'dept column
made = 7    'made column
extra = 5   'extra column

ColumnLetter (Target.Column)
NextCell = ActiveCell.Address

If Target.Column = brand Then
    Application.EnableEvents = False
    For Each c In Target
        Select Case LCase(c.Value)
            Case "sony"
                Range(ColumnLetter(dept) & Target.Row).Select
                ActiveCell.FormulaR1C1 = "electronic"
                Range(ColumnLetter(made) & Target.Row).Select
                ActiveCell.FormulaR1C1 = "Japan"
            Case "gmc"
                Range(ColumnLetter(dept) & Target.Row).Select
                ActiveCell.FormulaR1C1 = "automaker"
                Range(ColumnLetter(made) & Target.Row).Select
                ActiveCell.FormulaR1C1 = "USA"
        End Select
    Next c
    Application.EnableEvents = True
    Range(NextCell).Select
End If
End Sub

 

[auto998]

Hello again, I see the problem is Target.Row, it is not incrementing when I paste multiple cell<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>