Formula Reference and VBA

[Guest]

I am having a certain cell formula grabbed off of another sheet and inserted into current sheet. How do I get the row reference to change to the particular row that it is being inserted? Here is the Formula also: Unless there is another easier way
=CHOOSE(MONTH($H3+1,"jan","feb","mar","apr","may","jun","jul","aug","sep","oct","nov","dec","jan","feb","mar","apr","may","jun","jul","aug","sep","oct","nov","dec"
The 3 is the row that I need adjusted to the fit the row I am on in my other sheet
Any Help I would greatly appreciate it. Thanks

Private Sub CommandButton2_Click(
ActiveCell.Selec
Selection.EntireRow.Inser
CaseManager = InputBox("Please Enter Case Manager:"
Transfer = "*
ActiveCell = CaseManager + Transfe
ActiveCell.Offset(0, 1).Selec
ActiveCell = CaseManage
Client = InputBox("Please Enter Client Name:"
ActiveCell.Offset(0, 3).Selec
ActiveCell = Clien
Mnths = Sheets("Formulas").Range("M1:R1"
ActiveCell.Offset(0, 5).Selec
ActiveCell.Selec
'Range(ActiveCell, ActiveCell.Offset(0, 5)).Selec
Selection = Sheets("Formulas").Range("M1").Formul
End Sub

 

[Bernie Deitrick]

Jason,

You can simply copy and paste the formula, which Excel will properly update.

Or you can use a formula like this instead:

=LOWER(TEXT(INDIRECT("H" & ROW())+1,"mmm"))

In VBA, that would be

ActiveCell.FormulaR1C1 = "=LOWER(TEXT(INDIRECT(""H"" & ROW())+1,""mmm""))"

HTH,
Bernie
MS Excel MVP

I am having a certain cell formula grabbed off of another sheet and

inserted into current sheet. How do I get the row reference to change to the
particular row that it is being inserted? Here is the Formula also: Unless
there is another easier way?

 

[Bernie Deitrick]

Jason,

Of course, I failed to state that you original formula had an error, and I
wasn't sure if your meant

=CHOOSE(MONTH($H3+1),"jan","

or

=CHOOSE(MONTH($H3)+1,"jan","

The solution I gave was for the first one. If you wanted the second, you
would need to use

=LOWER(TEXT(DATE(2004,MONTH(INDIRECT("H" & ROW()))+1,1),"mmm"))

Sorry about that....

HTH,
Bernie
MS Excel MVP

 

[Guest]

Thanks, I just got back from lunch. Sometimes the simplest things are the hardest when you have been working with codes all day

----- Bernie Deitrick wrote: ----

Jason

Of course, I failed to state that you original formula had an error, and
wasn't sure if your mean

=CHOOSE(MONTH($H3+1),"jan",

o

=CHOOSE(MONTH($H3)+1,"jan",

The solution I gave was for the first one. If you wanted the second, yo
would need to us

=LOWER(TEXT(DATE(2004,MONTH(INDIRECT("H" & ROW()))+1,1),"mmm")

Sorry about that...

HTH
Berni
MS Excel MV