I have a name and address stored in cells A115 to C123 , the next address in cells A165 to C173, the next in cells A215 to C223 etc. Cell AM3 contains the cell number of the start of the range, eg 115 or 165 or 215 etc
Does that help ?
My name is Robin
Not quite. Not a single cell, but a range of cells, which could be from A115 to C123, or A165 to C173, or A215 to C223 etc. The value in cell AM3 will be 115 or 165 or 215 etc , which will give the start cell number of the range.
The spreadsheet is over 10mg and is difficult to email. This question is intended for a Visual basic expert.
Sorry, but I cannot spend any more time on this. I have an urgent appointment and will have to find someone else who can give me a quick answer.
Thanks for trying to help me
Just so there is no confusion, the following would be my coding if I were copying a fixed cell to a destination cell.
If the source cell could be one of 100 different cells, the cell AM3 will be set to the value of the appropriate one to action. Thus the code would now be :
myrow = Range("AM3")
Range("A" & myrow).Select
So, whatever value is assigned to cell AM3, that cell in column A will be selected.
Now I wish to go one step further. Instead of selecting a single cell, I have to select a range of cells.
If their location was fixed, the code would be
However, because the source range may be one of 100, I was hoping that there was some code to use the contents of cell AM3 to specify the specific range that I required.
If that all makes sense could you let me have the code.
Many thanks in anticipation
Great. Many thanks Richard.
One more thing. Could the source sheet, DATA, be variable as well?
In other words, is there code to access the sheet using a variable, such as AM3 is used for the cell, to set the Sheet which must be accessed ?
Thanks so much Richard. I have done the rating.
One final point. In your answer you use cell C120. I would like to use C and myrow + 25.
In other words, If AM3 contains 115 the range extends from A115 to C140.
Could you rewrite just that one line of code ?
Many thanks, Robin
Sorry Richard, but I am getting an Error 1004 , Method 'Range' of object'_Global' failed
Sorry. It is line number 4. Maybe I have got a space where I shouldn't have one.
My exact code is as follows :
mySheet - Range("AM3")
myRow = Range("AC9")
Range("A" & myRow & " : H" & myRow + 25).Select
File ID No is 440880. The file should open at the point where you can activate the macro using Control and f.
O.K. but the line before myrow = Range("AC9") should specify Sheet 1 and cell AC9 of sheet 1 contains 114.
When you open the uploaded version, you are in sheet 1. It is called 1 not sheet 1.
Great !! It now works fine.
Many thanks for all the time that you have spent Richard