Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4
Default Macro - How to increment cell reference by one row

I have a main sheet, and that references many other sheets with a specific
templet. Each templet sheet has a couple "special cells" that reference the
main page. Each row on the main page represents a sheet, and each column
represents these "special cells". What I need to do is have a macro that will
copy the last sheet, and make the "special cells"'s references/forumla
increment to next row on the main page.

I have the code that will copy the page, I just need to code to increment
the reference cells.

Sub Newsheet()

ActiveSheet.Select
ActiveSheet.Copy After:=ActiveSheet

'Select Special Cell1
'For example, Range("E4").Select
'Make it reference next row
'For example, change reference from ='Main Sheet'!R[248]C[1] to ='Main
Sheet'!R[249]C[2]
'Select Special Cell2
'For example Range("F20").Select
'Make it reference next row
'For example, change reference from ='Main Sheet'!R[248]C[2] to ='Main
Sheet'!R[249]C[2]

End Sub

I've tired to make my question as clear as possible....hopefully this makes
sense.
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: Macro - How to increment cell reference by one row

Yes, your question is clear. To increment the cell reference by one row, you can use the
Formula:
Offset 
property in VBA. Here's how you can modify your code:
  1. Find the last row in the "Main Sheet" using the
    Formula:
    End(xlUp
    method.
    Code:
    Dim lastRow As Long
    lastRow = Sheets("Main Sheet").Cells(Rows.Count, "A").End(xlUp).Row
  2. Copy the active sheet.
    Code:
    ActiveSheet.Copy After:=ActiveSheet
  3. Assign the formula to the "Special Cell1" using the
    Formula:
    Offset 
    property.
    Code:
    Range("E4").Formula = "='Main Sheet'!" & Range("E4").Offset(lastRow, 1).Address(False, False)
  4. Assign the formula to the "Special Cell2" using the
    Formula:
    Offset 
    property.
    Code:
    Range("F20").Formula = "='Main Sheet'!" & Range("F20").Offset(lastRow, 2).Address(False, False)

In this code, we first find the last row in the "Main Sheet" using the
Formula:
End(xlUp
method. Then we use the
Formula:
Offset 
property to increment the row reference by one. We concatenate the row number with the formula using the & symbol. Finally, we assign the formula to the cell using the
Formula:
Formula 
property.

Note that this code assumes that the "Main Sheet" is in the first column and that the "Special Cells" are in the second and third columns. If they are in different columns, you will need to modify the column reference in the formula accordingly.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,058
Default Macro - How to increment cell reference by one row

Anytime you have Select'ed a specific cell with something like:
Range("E4").Select
you can move down to the next row with:

Selection.Offset(1,0).Select
--
Gary's Student
gsnu200706


"lau_ash" wrote:

I have a main sheet, and that references many other sheets with a specific
templet. Each templet sheet has a couple "special cells" that reference the
main page. Each row on the main page represents a sheet, and each column
represents these "special cells". What I need to do is have a macro that will
copy the last sheet, and make the "special cells"'s references/forumla
increment to next row on the main page.

I have the code that will copy the page, I just need to code to increment
the reference cells.

Sub Newsheet()

ActiveSheet.Select
ActiveSheet.Copy After:=ActiveSheet

'Select Special Cell1
'For example, Range("E4").Select
'Make it reference next row
'For example, change reference from ='Main Sheet'!R[248]C[1] to ='Main
Sheet'!R[249]C[2]
'Select Special Cell2
'For example Range("F20").Select
'Make it reference next row
'For example, change reference from ='Main Sheet'!R[248]C[2] to ='Main
Sheet'!R[249]C[2]

End Sub

I've tired to make my question as clear as possible....hopefully this makes
sense.

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4
Default Macro - How to increment cell reference by one row

Yes, I'm aware of that.
However, I don't want to offset "E4", E4 is referencing a cell on another
page, I want to off set that reference.

For example, the formula in E4 ='Main Sheet'!A258
I want a macro that will change it to ='Main Sheet'!A259
But it needs to be dynamic, such that it doesn't fill in a specifical A259,
but just increments by one cell.

"Gary''s Student" wrote:

Anytime you have Select'ed a specific cell with something like:
Range("E4").Select
you can move down to the next row with:

Selection.Offset(1,0).Select
--
Gary's Student
gsnu200706


"lau_ash" wrote:

I have a main sheet, and that references many other sheets with a specific
templet. Each templet sheet has a couple "special cells" that reference the
main page. Each row on the main page represents a sheet, and each column
represents these "special cells". What I need to do is have a macro that will
copy the last sheet, and make the "special cells"'s references/forumla
increment to next row on the main page.

I have the code that will copy the page, I just need to code to increment
the reference cells.

Sub Newsheet()

ActiveSheet.Select
ActiveSheet.Copy After:=ActiveSheet

'Select Special Cell1
'For example, Range("E4").Select
'Make it reference next row
'For example, change reference from ='Main Sheet'!R[248]C[1] to ='Main
Sheet'!R[249]C[2]
'Select Special Cell2
'For example Range("F20").Select
'Make it reference next row
'For example, change reference from ='Main Sheet'!R[248]C[2] to ='Main
Sheet'!R[249]C[2]

End Sub

I've tired to make my question as clear as possible....hopefully this makes
sense.

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,058
Default Macro - How to increment cell reference by one row

How about:


Sub ref_bumper()

Set r = ActiveCell
Set h1 = Range("IV65535")
Set h2 = Range("IV65536")

h1.Formula = r.Formula
h1.Copy h2
r.Formula = h2.Formula

End Sub

This macro uses two helper cells. it:
1. moves the formula to the first helper cell - unchanged
2. copies the first helper to the cell just below it - increments the
reference
3. moves the new formula back to the Selected cell

If you don't like using the helper cells, you would have to get the formula,
somehow split it up, increment the reference part, put it back together,
replace the original.
--
Gary's Student
gsnu200706


"lau_ash" wrote:

Yes, I'm aware of that.
However, I don't want to offset "E4", E4 is referencing a cell on another
page, I want to off set that reference.

For example, the formula in E4 ='Main Sheet'!A258
I want a macro that will change it to ='Main Sheet'!A259
But it needs to be dynamic, such that it doesn't fill in a specifical A259,
but just increments by one cell.

"Gary''s Student" wrote:

Anytime you have Select'ed a specific cell with something like:
Range("E4").Select
you can move down to the next row with:

Selection.Offset(1,0).Select
--
Gary's Student
gsnu200706


"lau_ash" wrote:

I have a main sheet, and that references many other sheets with a specific
templet. Each templet sheet has a couple "special cells" that reference the
main page. Each row on the main page represents a sheet, and each column
represents these "special cells". What I need to do is have a macro that will
copy the last sheet, and make the "special cells"'s references/forumla
increment to next row on the main page.

I have the code that will copy the page, I just need to code to increment
the reference cells.

Sub Newsheet()

ActiveSheet.Select
ActiveSheet.Copy After:=ActiveSheet

'Select Special Cell1
'For example, Range("E4").Select
'Make it reference next row
'For example, change reference from ='Main Sheet'!R[248]C[1] to ='Main
Sheet'!R[249]C[2]
'Select Special Cell2
'For example Range("F20").Select
'Make it reference next row
'For example, change reference from ='Main Sheet'!R[248]C[2] to ='Main
Sheet'!R[249]C[2]

End Sub

I've tired to make my question as clear as possible....hopefully this makes
sense.



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4
Default Macro - How to increment cell reference by one row

Now that's a creative solution.
That worked perfectly. Not quite the direction I was taking. But it
definately uses less code.
Thanks for the help!

"Gary''s Student" wrote:

How about:


Sub ref_bumper()

Set r = ActiveCell
Set h1 = Range("IV65535")
Set h2 = Range("IV65536")

h1.Formula = r.Formula
h1.Copy h2
r.Formula = h2.Formula

End Sub

This macro uses two helper cells. it:
1. moves the formula to the first helper cell - unchanged
2. copies the first helper to the cell just below it - increments the
reference
3. moves the new formula back to the Selected cell

If you don't like using the helper cells, you would have to get the formula,
somehow split it up, increment the reference part, put it back together,
replace the original.
--
Gary's Student
gsnu200706


"lau_ash" wrote:

Yes, I'm aware of that.
However, I don't want to offset "E4", E4 is referencing a cell on another
page, I want to off set that reference.

For example, the formula in E4 ='Main Sheet'!A258
I want a macro that will change it to ='Main Sheet'!A259
But it needs to be dynamic, such that it doesn't fill in a specifical A259,
but just increments by one cell.

"Gary''s Student" wrote:

Anytime you have Select'ed a specific cell with something like:
Range("E4").Select
you can move down to the next row with:

Selection.Offset(1,0).Select
--
Gary's Student
gsnu200706


"lau_ash" wrote:

I have a main sheet, and that references many other sheets with a specific
templet. Each templet sheet has a couple "special cells" that reference the
main page. Each row on the main page represents a sheet, and each column
represents these "special cells". What I need to do is have a macro that will
copy the last sheet, and make the "special cells"'s references/forumla
increment to next row on the main page.

I have the code that will copy the page, I just need to code to increment
the reference cells.

Sub Newsheet()

ActiveSheet.Select
ActiveSheet.Copy After:=ActiveSheet

'Select Special Cell1
'For example, Range("E4").Select
'Make it reference next row
'For example, change reference from ='Main Sheet'!R[248]C[1] to ='Main
Sheet'!R[249]C[2]
'Select Special Cell2
'For example Range("F20").Select
'Make it reference next row
'For example, change reference from ='Main Sheet'!R[248]C[2] to ='Main
Sheet'!R[249]C[2]

End Sub

I've tired to make my question as clear as possible....hopefully this makes
sense.

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,058
Default Macro - How to increment cell reference by one row

You are very welcome.

The worksheet uses VBA to help all the time, no reason the sheet can't help
VBA in return for once.
--
Gary's Student
gsnu200707


"lau_ash" wrote:

Now that's a creative solution.
That worked perfectly. Not quite the direction I was taking. But it
definately uses less code.
Thanks for the help!

"Gary''s Student" wrote:

How about:


Sub ref_bumper()

Set r = ActiveCell
Set h1 = Range("IV65535")
Set h2 = Range("IV65536")

h1.Formula = r.Formula
h1.Copy h2
r.Formula = h2.Formula

End Sub

This macro uses two helper cells. it:
1. moves the formula to the first helper cell - unchanged
2. copies the first helper to the cell just below it - increments the
reference
3. moves the new formula back to the Selected cell

If you don't like using the helper cells, you would have to get the formula,
somehow split it up, increment the reference part, put it back together,
replace the original.
--
Gary's Student
gsnu200706


"lau_ash" wrote:

Yes, I'm aware of that.
However, I don't want to offset "E4", E4 is referencing a cell on another
page, I want to off set that reference.

For example, the formula in E4 ='Main Sheet'!A258
I want a macro that will change it to ='Main Sheet'!A259
But it needs to be dynamic, such that it doesn't fill in a specifical A259,
but just increments by one cell.

"Gary''s Student" wrote:

Anytime you have Select'ed a specific cell with something like:
Range("E4").Select
you can move down to the next row with:

Selection.Offset(1,0).Select
--
Gary's Student
gsnu200706


"lau_ash" wrote:

I have a main sheet, and that references many other sheets with a specific
templet. Each templet sheet has a couple "special cells" that reference the
main page. Each row on the main page represents a sheet, and each column
represents these "special cells". What I need to do is have a macro that will
copy the last sheet, and make the "special cells"'s references/forumla
increment to next row on the main page.

I have the code that will copy the page, I just need to code to increment
the reference cells.

Sub Newsheet()

ActiveSheet.Select
ActiveSheet.Copy After:=ActiveSheet

'Select Special Cell1
'For example, Range("E4").Select
'Make it reference next row
'For example, change reference from ='Main Sheet'!R[248]C[1] to ='Main
Sheet'!R[249]C[2]
'Select Special Cell2
'For example Range("F20").Select
'Make it reference next row
'For example, change reference from ='Main Sheet'!R[248]C[2] to ='Main
Sheet'!R[249]C[2]

End Sub

I've tired to make my question as clear as possible....hopefully this makes
sense.

  #8   Report Post  
Junior Member
 
Posts: 9
Default

I read this thread and is something that I was looking for as I do this summary for each branch and I take the information at different workbooks but I only need after i do the first summary manually to increment the reference with +1!!

Please, have a look at the thread if you can help me !! this would really save me a lot of time and a boring work:).


http://www.excelbanter.com/showthread.php?t=446032

P.S Sorry for the intereference!

Still I am trying to work on the code myself and try also your code.
Thank you
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I get a worksheet reference to increment when copied Raf Excel Worksheet Functions 9 March 26th 09 12:38 AM
variable cell reference in a macro [email protected] Excel Discussion (Misc queries) 6 January 25th 07 01:34 AM
Relative reference autofill increment other than +1 SteveB Excel Discussion (Misc queries) 3 June 14th 05 07:40 PM
how to make cell address reference increment? jacko Excel Worksheet Functions 3 June 1st 05 05:33 PM
Row reference increment but preserve column reference Pwanda Excel Worksheet Functions 1 April 28th 05 01:12 PM


All times are GMT +1. The time now is 04:22 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"