Need Help VBA Function

[Tom]

The function below allows to select a value (from drop-down list; Data Validation | List) in column A.
The from the combo box selected value is then copied into the adjacent cell in column B.

The problem is that it copies/pastes all values between A2:A150 and overwrites all values in B2:B150.

There are some values in the B column that should not be overwritten.

How do I modify the function so that it copies/paste only the selected cell (e.g. A25 and pastes the value into B25? (Examples of the process is provided below the function).


&&&&&&&&&&&&&&&&&&&&&&&&
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 1 Then
Range("A2:A150").Copy
Range("B2:B150").PasteSpecial (xlPasteValues)
End If
Application.CutCopyMode = False

End Sub
&&&&&&&&&&&&&&&&&&&&&&&&


Examples:

Before value in column A is selected:
Row 1 Col.A Col.B
Row 2 X
Row 3
Row 4 Y
Row 5
Row 6 Z

After value in column A is selected:
Row 1 Col.A Col.B
Row 2
Row 3 a a
Row 4
Row 5 b b
Row 6


Problem after the value in column A was selected is:
- Cell B2 lost value "X"
- Cell B4 lost value "Y"
- Cell B6 lost value "Z"


What it should look like after value in column A was selected:
Row 1 Col.A Col.B
Row 2 X
Row 3 a a
Row 4 Y
Row 5 b b
Row 6 Z


Any ideas are appreciated. Thanks in advance,
Tom

 

[Leo Heuser]

Tom

Here's one way to do it:

Private Sub Worksheet_Change(ByVal Target As Range)
'Leo Heuser, 28 Apr. 2004
Dim Cell As Range
Dim DestColumn As Long
Dim SourceColumn As Long

SourceColumn = 1
DestColumn = 2

If Target.Column = SourceColumn Then
For Each Cell In Target.Cells
Cell.Offset(0, DestColumn - SourceColumn).Value = Cell.Value
Next Cell
End If

End Sub



--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Tom" <[email protected]> skrev i en meddelelse
The function below allows to select a value (from drop-down list; Data
Validation | List) in column A.
The from the combo box selected value is then copied into the adjacent cell
in column B.

The problem is that it copies/pastes all values between A2:A150 and
overwrites all values in B2:B150.

There are some values in the B column that should not be overwritten.

How do I modify the function so that it copies/paste only the selected cell
(e.g. A25 and pastes the value into B25? (Examples of the process is
provided below the function).


&&&&&&&&&&&&&&&&&&&&&&&&
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 1 Then
Range("A2:A150").Copy
Range("B2:B150").PasteSpecial (xlPasteValues)
End If
Application.CutCopyMode = False

End Sub
&&&&&&&&&&&&&&&&&&&&&&&&


Examples:

Before value in column A is selected:
Row 1 Col.A Col.B
Row 2 X
Row 3
Row 4 Y
Row 5
Row 6 Z

After value in column A is selected:
Row 1 Col.A Col.B
Row 2
Row 3 a a
Row 4
Row 5 b b
Row 6


Problem after the value in column A was selected is:
- Cell B2 lost value "X"
- Cell B4 lost value "Y"
- Cell B6 lost value "Z"


What it should look like after value in column A was selected:
Row 1 Col.A Col.B
Row 2 X
Row 3 a a
Row 4 Y
Row 5 b b
Row 6 Z


Any ideas are appreciated. Thanks in advance,
Tom

 

[Tom]

Leo,

thanks so much... that works great!!! ' truly appreciate the feedback.

 

[Tom]

Leo,

one follow up question...

is there a chance to delete the value of cell A after is has been copied
into adjacent cell in B?

Thanks,
Tom

 

[Leo Heuser]

You're welcome, Tom.

Try this version instead:

Private Sub Worksheet_Change(ByVal Target As Range)
'Leo Heuser, 28 Apr. 2004
Dim Cell As Range
Dim DestColumn As Long
Dim SourceColumn As Long

On Error GoTo Finito

SourceColumn = 1
DestColumn = 2

If Target.Column = SourceColumn Then
Application.EnableEvents = False
For Each Cell In Target.Cells
Cell.Offset(0, DestColumn - SourceColumn).Value = Cell.Value
Cell.ClearContents
Next Cell
End If

Finito:

Application.EnableEvents = True
End Sub

 

[Tom]

Leo...

that's simply awesome!!! Thanks so much again.

Tom

 

[Leo Heuser]

You're welcome,Tom, and thanks
for the feedback

LeoH