PDA

View Full Version : Changing cell references to activecell reference



vuyyuru
08-28-2011, 11:43 PM
Hi All :hi: ,
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..

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

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


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

Bob Phillips
08-29-2011, 03:09 AM
Very good. So what do you need us for?

vuyyuru
08-29-2011, 05:15 AM
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
08-29-2011, 06:21 AM
What are you trying to change the activecell reference to?

Bob Phillips
08-29-2011, 01:35 PM
Is this what you want



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

Bob Phillips
08-29-2011, 01:44 PM
I thought this code looked familiar, and look what we turn up at Excel Forum http://www.excelforum.com/excel-programming/789721-auto-populating-cells-from-a-date-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
08-30-2011, 04:52 AM
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
08-31-2011, 09:02 PM
Thanks BOB for your Guidance!, able to fix the issue as below. thanks.


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

Simon Lloyd
09-01-2011, 01:44 PM
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
09-01-2011, 04:21 PM
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
09-01-2011, 09:46 PM
Hi Simon, Bob kindly wrapped the code.Edited my post to read correctly - thanks.

vuyyuru
09-02-2011, 01:02 AM
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
09-02-2011, 05:39 AM
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.