Can I use a formula in place of OffsetRows?

While writng a macro, it occurred to me that I don't know the syntax for
inserting a formula rather than a hard value for .Offset(ROWS,COLUMNS)

Here is the gist of my line:

Instead of Offset(4, -1), say...

I would like to Offset ROWS by looking at the current month (in A1),
timesing that by 25, then subtracting (29 minus the row that the active data
entry cell is in.)

I've tried a bunch of stuff, but it takes nothing...

..Offset(MONTH(A1)*25-(29-Row(ActiveCell)),-1).Address & ","

is its latest incarnation.

Does anybody know how this can be done?

Thanks for your continued support.

Arlen

I got this to work

ActiveCell.Offset(Month(Range("A1")) * 25 - (29 - ActiveCell.Row), 0).Select

Arlen,

It's not the use of a formula that is the problem, but
there are a couple problems with your formula.

1) I think what you meant to say with Row(ActiveCell) is
actually Activecell.Row. I suspect this is the source of
your latest error message.
2) In order to get the month in cell A1 you should write
Month(Range("A1")). Month(a1) will result in 12
regardless of what is in cell A1.

Make those changes and see if it helps.

Erin

>-----Original Message-----
>While writng a macro, it occurred to me that I don't know
the syntax for
>inserting a formula rather than a hard value for .Offset
(ROWS,COLUMNS)
>
>Here is the gist of my line:
>
>Instead of Offset(4, -1), say...
>
>I would like to Offset ROWS by looking at the current
month (in A1),
>timesing that by 25, then subtracting (29 minus the row
that the active data
>entry cell is in.)
>
>I've tried a bunch of stuff, but it takes nothing...
>
>..Offset(MONTH(A1)*25-(29-Row(ActiveCell)),-1).Address
& ","
>
>is its latest incarnation.
>
>Does anybody know how this can be done?
>
>Thanks for your continued support.
>
>Arlen
>.
>

Erin,

Thank you. That solved the syntax issue.

However, here's the whole picture.

Based on the Month and ActiveCell.Row on Sheet 1, I want to copy cell data
into specific cells on Sheet 2.

By using .Offset(MONTH(range("A1")*25-(29-ActiveCell.Row)),-1)), months
start over every 26th row on Sheet 2.

When the month in Sheet 1, A1 = January, all data in Sheet 1, Range C5:C30
gets sent to Sheet 2, Range A1:A25.

When the month in Sheet 1, A1 = February, all data in same Sheet 1, Range
C5:C30 gets sent to Sheet 2, Range A26:A50 -- without disrupting January's
numbers.

That's what this is all about. It's my attempt to make a comprehensive
archive using the least amount of sheets possible.

Thanks for your help so far.

For future reference, is there a Syntax checker in VBA so I can figure out
the syntax on my own and leave the real programming issues to you guys?

Bye


"Erin" wrote:

> Arlen,
>
> It's not the use of a formula that is the problem, but
> there are a couple problems with your formula.
>
> 1) I think what you meant to say with Row(ActiveCell) is
> actually Activecell.Row. I suspect this is the source of
> your latest error message.
> 2) In order to get the month in cell A1 you should write
> Month(Range("A1")). Month(a1) will result in 12
> regardless of what is in cell A1.
>
> Make those changes and see if it helps.
>
> Erin
>
> >-----Original Message-----
> >While writng a macro, it occurred to me that I don't know
> the syntax for
> >inserting a formula rather than a hard value for .Offset
> (ROWS,COLUMNS)
> >
> >Here is the gist of my line:
> >
> >Instead of Offset(4, -1), say...
> >
> >I would like to Offset ROWS by looking at the current
> month (in A1),
> >timesing that by 25, then subtracting (29 minus the row
> that the active data
> >entry cell is in.)
> >
> >I've tried a bunch of stuff, but it takes nothing...
> >
> >..Offset(MONTH(A1)*25-(29-Row(ActiveCell)),-1).Address
> & ","
> >
> >is its latest incarnation.
> >
> >Does anybody know how this can be done?
> >
> >Thanks for your continued support.
> >
> >Arlen
> >.
> >
>

First, I'm not sure how the ActiveCell fits in. It looks as though you're
always copying C5:C30.

I'd break it into little pieces.

dim myMonth as long
Dim DestCell as range
dim RngToCopy as range

with worksheets("sheet1")
with .range("a1")
if isdate(.value) = false then
msgbox "Please put a date in A1!"
exit sub
end if
myMonth = month(.value)
end with
set rngtocopy = .range("c5:c30")
end with

'month startrow
' 1 1
' 2 26
' 3 51
' etc
set destcell = worksheets("sheet2").cells(25*(mymonth-1)+1,"A")

rngtocopy.copy _
destination:=destcell






Arlen wrote:
>
> Erin,
>
> Thank you. That solved the syntax issue.
>
> However, here's the whole picture.
>
> Based on the Month and ActiveCell.Row on Sheet 1, I want to copy cell data
> into specific cells on Sheet 2.
>
> By using .Offset(MONTH(range("A1")*25-(29-ActiveCell.Row)),-1)), months
> start over every 26th row on Sheet 2.
>
> When the month in Sheet 1, A1 = January, all data in Sheet 1, Range C5:C30
> gets sent to Sheet 2, Range A1:A25.
>
> When the month in Sheet 1, A1 = February, all data in same Sheet 1, Range
> C5:C30 gets sent to Sheet 2, Range A26:A50 -- without disrupting January's
> numbers.
>
> That's what this is all about. It's my attempt to make a comprehensive
> archive using the least amount of sheets possible.
>
> Thanks for your help so far.
>
> For future reference, is there a Syntax checker in VBA so I can figure out
> the syntax on my own and leave the real programming issues to you guys?
>
> Bye
>
> "Erin" wrote:
>
> > Arlen,
> >
> > It's not the use of a formula that is the problem, but
> > there are a couple problems with your formula.
> >
> > 1) I think what you meant to say with Row(ActiveCell) is
> > actually Activecell.Row. I suspect this is the source of
> > your latest error message.
> > 2) In order to get the month in cell A1 you should write
> > Month(Range("A1")). Month(a1) will result in 12
> > regardless of what is in cell A1.
> >
> > Make those changes and see if it helps.
> >
> > Erin
> >
> > >-----Original Message-----
> > >While writng a macro, it occurred to me that I don't know
> > the syntax for
> > >inserting a formula rather than a hard value for .Offset
> > (ROWS,COLUMNS)
> > >
> > >Here is the gist of my line:
> > >
> > >Instead of Offset(4, -1), say...
> > >
> > >I would like to Offset ROWS by looking at the current
> > month (in A1),
> > >timesing that by 25, then subtracting (29 minus the row
> > that the active data
> > >entry cell is in.)
> > >
> > >I've tried a bunch of stuff, but it takes nothing...
> > >
> > >..Offset(MONTH(A1)*25-(29-Row(ActiveCell)),-1).Address
> > & ","
> > >
> > >is its latest incarnation.
> > >
> > >Does anybody know how this can be done?
> > >
> > >Thanks for your continued support.
> > >
> > >Arlen
> > >.
> > >
> >

--

Dave Peterson

Dave,

So far, this is right on.

However, I need to make 2 modifications to this code, and then it will be
perfect.

First, I'm actually trying to copy sheet 1's ranges C5:C30, D5:D30, and
K5:K30 to sheet 2's columns A, B and C.

And then I would like the whole Macro to perform on a Worksheet Change
event. I have toyed with the code but I can never figure VBA out.

If you get back here, thank you again.

Arlen

"Arlen" wrote:

> Erin,
>
> Thank you. That solved the syntax issue.
>
> However, here's the whole picture.
>
> Based on the Month and ActiveCell.Row on Sheet 1, I want to copy cell data
> into specific cells on Sheet 2.
>
> By using .Offset(MONTH(range("A1")*25-(29-ActiveCell.Row)),-1)), months
> start over every 26th row on Sheet 2.
>
> When the month in Sheet 1, A1 = January, all data in Sheet 1, Range C5:C30
> gets sent to Sheet 2, Range A1:A25.
>
> When the month in Sheet 1, A1 = February, all data in same Sheet 1, Range
> C5:C30 gets sent to Sheet 2, Range A26:A50 -- without disrupting January's
> numbers.
>
> That's what this is all about. It's my attempt to make a comprehensive
> archive using the least amount of sheets possible.
>
> Thanks for your help so far.
>
> For future reference, is there a Syntax checker in VBA so I can figure out
> the syntax on my own and leave the real programming issues to you guys?
>
> Bye
>
>
> "Erin" wrote:
>
> > Arlen,
> >
> > It's not the use of a formula that is the problem, but
> > there are a couple problems with your formula.
> >
> > 1) I think what you meant to say with Row(ActiveCell) is
> > actually Activecell.Row. I suspect this is the source of
> > your latest error message.
> > 2) In order to get the month in cell A1 you should write
> > Month(Range("A1")). Month(a1) will result in 12
> > regardless of what is in cell A1.
> >
> > Make those changes and see if it helps.
> >
> > Erin
> >
> > >-----Original Message-----
> > >While writng a macro, it occurred to me that I don't know
> > the syntax for
> > >inserting a formula rather than a hard value for .Offset
> > (ROWS,COLUMNS)
> > >
> > >Here is the gist of my line:
> > >
> > >Instead of Offset(4, -1), say...
> > >
> > >I would like to Offset ROWS by looking at the current
> > month (in A1),
> > >timesing that by 25, then subtracting (29 minus the row
> > that the active data
> > >entry cell is in.)
> > >
> > >I've tried a bunch of stuff, but it takes nothing...
> > >
> > >..Offset(MONTH(A1)*25-(29-Row(ActiveCell)),-1).Address
> > & ","
> > >
> > >is its latest incarnation.
> > >
> > >Does anybody know how this can be done?
> > >
> > >Thanks for your continued support.
> > >
> > >Arlen
> > >.
> > >
> >

Maybe just adding a few more copy|pastes:

.....same code...

rngtocopy.copy _
destination:=destcell

rngtocopy.offset(0,1).copy _
destination:=destcell.offset(0,1)

rngtocopy.offset(0,8).copy _
destination:=destcell.offset(0,2)


And I'm guessing you want something to happen when you change A1.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myMonth As Long
Dim DestCell As Range
Dim RngToCopy As Range

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("a1")) Is Nothing Then Exit Sub

With Target
If IsDate(.Value) = False Then
MsgBox "Please put a date in A1!"
Exit Sub
End If
myMonth = Month(.Value)
End With
Set RngToCopy = Me.Range("c5:c30")

Set DestCell = Worksheets("sheet2").Cells(25 * (myMonth - 1) + 1, "A")

On Error GoTo errHandler:

'stop any other events from firing
Application.EnableEvents = False


RngToCopy.Copy _
Destination:=DestCell

RngToCopy.Offset(0, 1).Copy _
Destination:=DestCell.Offset(0, 1)

RngToCopy.Offset(0, 8).Copy _
Destination:=DestCell.Offset(0, 2)

errHandler:
Application.EnableEvents = True

End Sub


======
Just a word of warning. This kind of thing scares me. I make enough typing
errors that I would be afraid of typing in the wrong date and screw up sheet2.

I think I'd put a button on that sheet (in B1???) that I could click when I was
sure my date was correct in A1.

Just a thought.




Arlen wrote:
>
> Dave,
>
> So far, this is right on.
>
> However, I need to make 2 modifications to this code, and then it will be
> perfect.
>
> First, I'm actually trying to copy sheet 1's ranges C5:C30, D5:D30, and
> K5:K30 to sheet 2's columns A, B and C.
>
> And then I would like the whole Macro to perform on a Worksheet Change
> event. I have toyed with the code but I can never figure VBA out.
>
> If you get back here, thank you again.
>
> Arlen
>
> "Arlen" wrote:
>
> > Erin,
> >
> > Thank you. That solved the syntax issue.
> >
> > However, here's the whole picture.
> >
> > Based on the Month and ActiveCell.Row on Sheet 1, I want to copy cell data
> > into specific cells on Sheet 2.
> >
> > By using .Offset(MONTH(range("A1")*25-(29-ActiveCell.Row)),-1)), months
> > start over every 26th row on Sheet 2.
> >
> > When the month in Sheet 1, A1 = January, all data in Sheet 1, Range C5:C30
> > gets sent to Sheet 2, Range A1:A25.
> >
> > When the month in Sheet 1, A1 = February, all data in same Sheet 1, Range
> > C5:C30 gets sent to Sheet 2, Range A26:A50 -- without disrupting January's
> > numbers.
> >
> > That's what this is all about. It's my attempt to make a comprehensive
> > archive using the least amount of sheets possible.
> >
> > Thanks for your help so far.
> >
> > For future reference, is there a Syntax checker in VBA so I can figure out
> > the syntax on my own and leave the real programming issues to you guys?
> >
> > Bye
> >
> >
> > "Erin" wrote:
> >
> > > Arlen,
> > >
> > > It's not the use of a formula that is the problem, but
> > > there are a couple problems with your formula.
> > >
> > > 1) I think what you meant to say with Row(ActiveCell) is
> > > actually Activecell.Row. I suspect this is the source of
> > > your latest error message.
> > > 2) In order to get the month in cell A1 you should write
> > > Month(Range("A1")). Month(a1) will result in 12
> > > regardless of what is in cell A1.
> > >
> > > Make those changes and see if it helps.
> > >
> > > Erin
> > >
> > > >-----Original Message-----
> > > >While writng a macro, it occurred to me that I don't know
> > > the syntax for
> > > >inserting a formula rather than a hard value for .Offset
> > > (ROWS,COLUMNS)
> > > >
> > > >Here is the gist of my line:
> > > >
> > > >Instead of Offset(4, -1), say...
> > > >
> > > >I would like to Offset ROWS by looking at the current
> > > month (in A1),
> > > >timesing that by 25, then subtracting (29 minus the row
> > > that the active data
> > > >entry cell is in.)
> > > >
> > > >I've tried a bunch of stuff, but it takes nothing...
> > > >
> > > >..Offset(MONTH(A1)*25-(29-Row(ActiveCell)),-1).Address
> > > & ","
> > > >
> > > >is its latest incarnation.
> > > >
> > > >Does anybody know how this can be done?
> > > >
> > > >Thanks for your continued support.
> > > >
> > > >Arlen
> > > >.
> > > >
> > >

--

Dave Peterson

Dave,

This is awesome. Thanks you for your time. Now it's onto another puzzler.

James

"Dave Peterson" wrote:

> Maybe just adding a few more copy|pastes:
>
> .....same code...
>
> rngtocopy.copy _
> destination:=destcell
>
> rngtocopy.offset(0,1).copy _
> destination:=destcell.offset(0,1)
>
> rngtocopy.offset(0,8).copy _
> destination:=destcell.offset(0,2)
>
>
> And I'm guessing you want something to happen when you change A1.
>
> Option Explicit
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> Dim myMonth As Long
> Dim DestCell As Range
> Dim RngToCopy As Range
>
> If Target.Cells.Count > 1 Then Exit Sub
> If Intersect(Target, Me.Range("a1")) Is Nothing Then Exit Sub
>
> With Target
> If IsDate(.Value) = False Then
> MsgBox "Please put a date in A1!"
> Exit Sub
> End If
> myMonth = Month(.Value)
> End With
> Set RngToCopy = Me.Range("c5:c30")
>
> Set DestCell = Worksheets("sheet2").Cells(25 * (myMonth - 1) + 1, "A")
>
> On Error GoTo errHandler:
>
> 'stop any other events from firing
> Application.EnableEvents = False
>
>
> RngToCopy.Copy _
> Destination:=DestCell
>
> RngToCopy.Offset(0, 1).Copy _
> Destination:=DestCell.Offset(0, 1)
>
> RngToCopy.Offset(0, 8).Copy _
> Destination:=DestCell.Offset(0, 2)
>
> errHandler:
> Application.EnableEvents = True
>
> End Sub
>
>
> ======
> Just a word of warning. This kind of thing scares me. I make enough typing
> errors that I would be afraid of typing in the wrong date and screw up sheet2.
>
> I think I'd put a button on that sheet (in B1???) that I could click when I was
> sure my date was correct in A1.
>
> Just a thought.
>
>
>
>
> Arlen wrote:
> >
> > Dave,
> >
> > So far, this is right on.
> >
> > However, I need to make 2 modifications to this code, and then it will be
> > perfect.
> >
> > First, I'm actually trying to copy sheet 1's ranges C5:C30, D5:D30, and
> > K5:K30 to sheet 2's columns A, B and C.
> >
> > And then I would like the whole Macro to perform on a Worksheet Change
> > event. I have toyed with the code but I can never figure VBA out.
> >
> > If you get back here, thank you again.
> >
> > Arlen
> >
> > "Arlen" wrote:
> >
> > > Erin,
> > >
> > > Thank you. That solved the syntax issue.
> > >
> > > However, here's the whole picture.
> > >
> > > Based on the Month and ActiveCell.Row on Sheet 1, I want to copy cell data
> > > into specific cells on Sheet 2.
> > >
> > > By using .Offset(MONTH(range("A1")*25-(29-ActiveCell.Row)),-1)), months
> > > start over every 26th row on Sheet 2.
> > >
> > > When the month in Sheet 1, A1 = January, all data in Sheet 1, Range C5:C30
> > > gets sent to Sheet 2, Range A1:A25.
> > >
> > > When the month in Sheet 1, A1 = February, all data in same Sheet 1, Range
> > > C5:C30 gets sent to Sheet 2, Range A26:A50 -- without disrupting January's
> > > numbers.
> > >
> > > That's what this is all about. It's my attempt to make a comprehensive
> > > archive using the least amount of sheets possible.
> > >
> > > Thanks for your help so far.
> > >
> > > For future reference, is there a Syntax checker in VBA so I can figure out
> > > the syntax on my own and leave the real programming issues to you guys?
> > >
> > > Bye
> > >
> > >
> > > "Erin" wrote:
> > >
> > > > Arlen,
> > > >
> > > > It's not the use of a formula that is the problem, but
> > > > there are a couple problems with your formula.
> > > >
> > > > 1) I think what you meant to say with Row(ActiveCell) is
> > > > actually Activecell.Row. I suspect this is the source of
> > > > your latest error message.
> > > > 2) In order to get the month in cell A1 you should write
> > > > Month(Range("A1")). Month(a1) will result in 12
> > > > regardless of what is in cell A1.
> > > >
> > > > Make those changes and see if it helps.
> > > >
> > > > Erin
> > > >
> > > > >-----Original Message-----
> > > > >While writng a macro, it occurred to me that I don't know
> > > > the syntax for
> > > > >inserting a formula rather than a hard value for .Offset
> > > > (ROWS,COLUMNS)
> > > > >
> > > > >Here is the gist of my line:
> > > > >
> > > > >Instead of Offset(4, -1), say...
> > > > >
> > > > >I would like to Offset ROWS by looking at the current
> > > > month (in A1),
> > > > >timesing that by 25, then subtracting (29 minus the row
> > > > that the active data
> > > > >entry cell is in.)
> > > > >
> > > > >I've tried a bunch of stuff, but it takes nothing...
> > > > >
> > > > >..Offset(MONTH(A1)*25-(29-Row(ActiveCell)),-1).Address
> > > > & ","
> > > > >
> > > > >is its latest incarnation.
> > > > >
> > > > >Does anybody know how this can be done?
> > > > >
> > > > >Thanks for your continued support.
> > > > >
> > > > >Arlen
> > > > >.
> > > > >
> > > >
>
> --
>
> Dave Peterson
>