Newbie question!

[Vestergaard]

Hi people at Excel forum

I have just begun using VBA for Excel, so I am a newbie I hope somebody can help me with the following problem:

Each of the cells from A1 until A10 contains different kind of text.
Cell B1 contains an integer between 1 and 10.
In cell B2 I want the same text as in the cell, which is pointed out by the integer value in cell B1. For example: If B1 contains the integer value 5, then B2 should automatically obtain the text from cell A5.

What VBA code is appropriate for cell B2?

Best regards,

Erik

[Greg Wilson]

This best way IMHO is to use a worksheet function instead of VBA. Place the
following in cell B2:

=INDIRECT("A" & B1)

If you want to use VBA then I would suggest pasting this to the worksheet's
code module:

Private Sub Worksheet_Change(ByVal Target As Range)
Range("B2").Value = Range("A" & Range("B1").Value)
End Sub

Note that worksheet functions execute faster than VBA and so IMHO should be
used preferentially. There are of course plenty of situations where this is
not feasible.

Regards,
Greg


"Vestergaard" wrote:

>
> Hi people at Excel forum
>
> I have just begun using VBA for Excel, so I am a newbie I hope
> somebody can help me with the following problem:
>
> Each of the cells from A1 until A10 contains different kind of text.
> Cell B1 contains an integer between 1 and 10.
> In cell B2 I want the same text as in the cell, which is pointed out by
> the integer value in cell B1. For example: If B1 contains the integer
> value 5, then B2 should automatically obtain the text from cell A5.
>
> What VBA code is appropriate for cell B2?
>
> Best regards,
>
> Erik
>
>
> --
> Vestergaard
> ------------------------------------------------------------------------
> Vestergaard's Profile: http://www.excelforum.com/member.php...fo&userid=7955
> View this thread: http://www.excelforum.com/showthread...hreadid=488493
>
>

[bpeltzer]

In B2: =INDEX(A1:A10,B1)


"Vestergaard" wrote:

>
> Hi people at Excel forum
>
> I have just begun using VBA for Excel, so I am a newbie I hope
> somebody can help me with the following problem:
>
> Each of the cells from A1 until A10 contains different kind of text.
> Cell B1 contains an integer between 1 and 10.
> In cell B2 I want the same text as in the cell, which is pointed out by
> the integer value in cell B1. For example: If B1 contains the integer
> value 5, then B2 should automatically obtain the text from cell A5.
>
> What VBA code is appropriate for cell B2?
>
> Best regards,
>
> Erik
>
>
> --
> Vestergaard
> ------------------------------------------------------------------------
> Vestergaard's Profile: http://www.excelforum.com/member.php...fo&userid=7955
> View this thread: http://www.excelforum.com/showthread...hreadid=488493
>
>

[PosseJohn]

If you want to dabble...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Address = "$B$1" Then
If IsNumeric(Target.Value) Then
Range("B2").Value = Cells(Target.Value, 1)
Range("B2").Font.Color = RGB(0, 0, 0)
Range("B2").Font.Bold = False
Else
Range("B2").Value = "Error"
Range("B2").Font.Color = RGB(255, 0, 0)
Range("B2").Font.Bold = True
End If
End If
End Sub

[Vestergaard]

That made the trick. Thanks to all of you!!!

Erik