Hi again,

Ok I will now make a couple of assumptions and if my assumptions are wrong
then get back to me and Ill try again. You may not need all the info I give
you but it might still help in the future.

I assume that the combo box is populated by a list on one of your worksheets
using format control- Input range. When using a list like this you should
ensure that you have one column available to the left of the list of the
input range and you number the cells in this extra column 1,2,3,4 etc to the
bottom of your data as per the following example:-

1 John
2 Angela
3 Elizabeth
4 Peter
5 Paul
6 Mary

Assume that the data above is in cells A1 to B6. (Input range for the combo
box is B1:B6. Dont include column A for the combo box Input range.)

For your combo box I will assume that you have set the linked cell to C1
which will display the number of the selection.

Now when you make a selection say €˜Peter, the linked cell displays 4. To
get a cell to display €˜Peter, use the VLOOKUP function in ANOTHER cell like
this:-

=VLOOKUP(C1,$A$1:$B$10,2,FALSE)

The above assumes the lookup range is on the same worksheet as the VLOOKUP
formula. If the lookup range is on another worksheet then it would look like
this:-

=VLOOKUP(C1,Sheet2!$A$1:$B$10,2,FALSE)

Where C1 is the linked cell to the combo box.
$A$1:$B$10 is the lookup range with the numbers in column A and the data for
the combo box in column B.
2 is the second column of the lookup range because this is the data to
display.
FALSE says only display exact matches.

If I understand correctly, your specific question relates to how to address
a row in another worksheet from a number displayed on the worksheet. I am
assuming that this is the number displayed in the combo box linked cell. The
following samples of code may help you to understand.

The first macro is a very simplistic method of select, copy, select again
and paste as you would do in the interactive mode.

The second macro is simply an alternative method of doing the same as the
first macro does.

The third macro is just samples of code using the Cells method with numbers
in lieu of the A1 method.

Sub Test_Simplistic()
'Copy a row of data from one sheet
'and paste to another sheet

Dim numbRow As Long

numbRow = Sheets("Sheet1").Range("C1")

Sheets("Sheet3").Select
Rows(numbRow).Select
Selection.Copy
Sheets("Sheet1").Select
Rows(10).Select
ActiveSheet.Paste

End Sub


Sub Test_Alternative()

Dim numbRow As Long

numbRow = Sheets("Sheet1").Range("C1")

'Note the space and underscore at the end of
'the line is a line break and the 2 lines of code
'perform as if on one line.

Sheets("Sheet3").Rows(numbRow).Copy _
Destination:=Sheets("Sheet1").Rows(10)

End Sub

Sub Test_Address_Cells()

'Addressing cells using numbers
'Selects cell A1 of the active sheet
Cells(1, 1).Select

Dim rowNumb As Single
Dim colNumb As Single

rowNumb = 8
colNumb = 5
Cells(rowNumb, colNumb).Select

'Note that when you use the Cells function the
'row is first; unlike the alpha numeric method
'where the column is first.

'Following is same as Range("A1:J1").Select
Range(Cells(1, 1), Cells(1, 10)).Select


End Sub

Regards,

OssieMac

"ybkusz" wrote:

Hi,
Thanks for that clarification. I am using a Form control combo box. I
wouldn't know how to write it, but what I would want to program was every
time the combo box changed values, Excel would find the appropriate data
based on the selection contained in the separate worksheet and fill in some
cells. Any direction from here?