Solved how to pass row number into range of a cell

January 16, 2013 at 00:44:00
Specs: Windows Vista
hello all
I have a worksheet having a table. & I want to change the contents of some cell based on value of a cell.
e.g. In Table I have three columns say Name, Class, Marks of the student. B1:D10
now in a particular cell say A1 I have entered a name from the list , and I want to change the marks of the student whose name is written in this cell in the table.
say I write xyz in the cell A1
in a cell say A2 I get the row number of xyz in the table say 4
now I want to change the marks in the Fourth (4th) row
I got the row number using =Match(A1,B1:D10,0)
I am doing this using macro

please sort out the problem

Thanks in advance

See More: how to pass row number into range of a cell

January 16, 2013 at 04:43:21
✔ Best Answer

After re-reading your subject line, perhaps this is what you are looking for. If not, please read my original response, shown below.

Here are 2 syntax options to use the value in SHEET!A2 as the Row number for a Range reference.

With a 4 in SHEET!A2, this code will put the text shown in C4 and D4

Sub GetRowFromWorksheet()
  Sheets(1).Range("C" & Sheet1.Range("A2")) = "MyText in C4"
  Sheets(1).Cells(Sheet1.Range("A2"), 4) = "MyText in D4"
End Sub

Please note that there is no error checking shown, so if your MATCH function does not return a valid number, the code will fail.

I would suggest using a Data Validation drop down based on the column you are searching to ensure that you will only be able to choose a name that will be found via the MATCH function. If you don't, you should include some error handling in the macro.

Original Response

If you are using a macro, please post it so that we know what you are working with.

I don't understand how having the row number in A2 is used to help you "change the marks in the 4th row". How are you actually changing the marks?

Please click on the following line and read the instructions found via that link before you post your code. Thanks

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

January 16, 2013 at 21:55:14
hey thanks Derby
Your Code is working great.
yes I have data validation in the column I am Searching, and thanks again for your suggesstion.

The Code I am Using now is :

Sheets("Manual Reciept").Select
ActiveCell.FormulaR1C1 = "=MATCH(R[1]C[-9],List!RC[-11]:R[59999]C[-11],0)"
Sheets("List").Cells(Sheets("Manual Reciept").Range("P1"), 35) = Sheets("Manual Reciept").Range("O21").Value

Report •

January 18, 2013 at 07:57:54
Two comments:

1 - Please click on the blue line at the end of this post and read the instructions on how to post VBA code in this forum.

2 - Rarely, if ever, do you have to Select an object in VBA to perform an action on it.

For example, your code can probably be shortened from this:

  Sheets("Manual Reciept").Select
       ActiveCell.FormulaR1C1 = "=MATCH(R[1]C[-9],List!RC[-11]:R[59999]C[-11],0)"

to this:

  Sheets("Manual Reciept").Range("P1").FormulaR1C1 = _

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •
Related Solutions

Ask Question