Changing cell references to activecell reference

[vuyyuru]

Hi All ,
1.
I have a VBA code which auto populate cells using two dates as the parameters, which will present in A2 and B2.. and a montly std value present in C2, these value will autopopulated to the correct months ( column D is Jan 00 and column E is FEB 00 .......... goes upto DEC 2020 etc). the code is as following..

[VBA]Public Sub Test2()
Dim monthOff As Long
Dim numMonths As Long
With ActiveSheet

monthOff = DateDiff("M", DateSerial(2000, 1, 1), Range("A2"))
numMonths = DateDiff("M", Range("A2"), Range("B2")) + 1
.Range("D2").Offset(, monthOff).Resize(, numMonths).Value = .Range("C2").Value
End With
End Sub[/vba]

I try to change the cell references to active cell reference insted of " A2" or "B2" or "C2" or "D2". so that i can use this code for any rows... I try to change as following but not working.. any guy can help on this VBA code .. the intial cell postion before i run the code will be the cell A1 ... thanks

[vba]
Public Sub srini2()
Dim monthOff As Long
Dim numMonths As Long
With ActiveSheet

monthOff = DateDiff("M", DateSerial(2000, 1, 1), ActiveCell.Offset(1, 0).Select)
numMonths = DateDiff("M", ActiveCell.Offset(0, 0).Select, ActiveCell.Offset(0, 1).Select) + 1
.(ActiveCell.Offset(0, 2).Select).Offset(, monthOff).Resize(, numMonths).Value = .ActiveCell.Offset(0, -1).Select.Value
End With
End Sub [/VBA]

[Bob Phillips]

Very good. So what do you need us for?

[vuyyuru]

Very good. So what do you need us for?

when i try to change to activecell reference it's not working, can some one guide me on this. thanks

[Simon Lloyd]

What are you trying to change the activecell reference to?

[Bob Phillips]

Is this what you want

[vba]

Public Sub srini2()
Dim monthOff As Long
Dim numMonths As Long
With ActiveCell

monthOff = DateDiff("M", DateSerial(2000, 1, 1), .Offset(1, 0))
numMonths = DateDiff("M", ActiveCell, .Offset(0, 1)) + 1
.Offset(0, 2 + monthOff).Resize(, numMonths).Value = .Offset(0, -1).Value
End With
End Sub [/vba]

[Bob Phillips]

I thought this code looked familiar, and look what we turn up at Excel Forum http://www.excelforum.com/excel-prog...ate-range.html.

Now, I may not be bright, but as I read it, you said that answer was perfect. And yet you go and cross-post it here. Oh dear!

[vuyyuru]

Sorry Bob, about that. my laptop formated over weekend, i did n't saved the link before.

i try to run your code, I'm getting the runtime error 13 "type mismatch".. at numMonths = DateDiff("M", ActiveCell, .Offset(0, 1)) + 1

[vuyyuru]

Thanks BOB for your Guidance!, able to fix the issue as below. thanks.

[VBA]
Public Sub Bobtest()
Dim monthOff As Long
Dim numMonths As Long
With ActiveCell
monthOff = DateDiff("M", DateSerial(2000, 1, 1), .Offset(1, 0))
numMonths = DateDiff("M", .Offset(1, 0), .Offset(1, 1)) + 1
.Offset(1, 3 + monthOff).Resize(, numMonths).Value = .Offset(1, 2).Value
End With
End Sub
[/VBA]

[Simon Lloyd]

vuyyuru, please read the link in my signature with regards to cross posting!

Please always wrap your code in the VBA tags provided as xld did in the first post!

[Aussiebear]

Please always wrap your code in the VBA tags provided as you did in the first post!

Hi Simon, Bob kindly wrapped the code.

[Simon Lloyd]

Hi Simon, Bob kindly wrapped the code.

Edited my post to read correctly - thanks.

[vuyyuru]

vuyyuru, please read the link in my signature with regards to cross posting!

Please always wrap your code in the VBA tags provided as xld did in the first post!

Noted: now i understand what i need to do for cross posting and adding the VBA tag to the code. thanks

[Bob Phillips]

Be aware, that if you cross-post, some people, myself included, will not even look at your problem as we are at risk of wasting our own or some other person's time if both work on it at the same time.