PDA

View Full Version : Solved: Auto populate a column



speedy dave
05-16-2010, 07:04 PM
Hi all, just wondering if anyone has any ideas ( code or formula ) that can copy the value in a cell, say cell "a1" into the next available blank cell in column "B" every time the value in cell "A1" changes ?

"A1" is auto populated via a data (web) lookup every hour.
( spot gold price )

I can then graph the resulting data.


Thanks heaps

GTO
05-16-2010, 07:34 PM
I think that the change event may not occur, but try that first. If it does not, try calculate event, and put a formula someplace, let's say in B1

=$A$1

No promises, but this should force a calc whenever A1 changes.

Then in the sheet's module:

Option Explicit

Private Sub Worksheet_Calculate()
Cells(Rows.Count, 2).End(xlUp).Offset(1).Value = Range("A1").Value
End Sub


Hope that works,

Mark

speedy dave
05-16-2010, 11:22 PM
Thanks for that Mark, it does work well and updates whenever the data sheet is updated !

Just one small addition .......
would it be possible to include a "date" field as well ??

ie. if the gold price is in "A1" and the date ( =today() ) is in "B1"

This way I would get two neet columns of price and date that I could then generate a nice graph off.


What would I need to add to the code to copy the date across as well ( would obviously need to be aligned with the value )

I did try to get it to happen by putting "=today()" into "h7" (random cell )
and then put formula of " =$H$7 " into cell "B1"

*** as soon as i entered the "date" formula to the sheet, it went into loop mode. ***

I also tried by adding to the code with reference to "B1" and "row.count 3" but the code still went into a loop and populated down to row 95.
I have no idea why it stopped at 95 :dunno


any ideas ??
cheers

GTO
05-17-2010, 03:42 AM
Thanks for that Mark, it does work well and updates whenever the data sheet is updated !

Just one small addition .......
would it be possible to include a "date" field as well ??

ie. if the gold price is in "A1" and the date ( =today() ) is in "B1"

Admiitedly, I did not really look at the looping issue. To add a date akin to the price, maybe:


Private Sub Worksheet_Calculate()
With Cells(Rows.Count, 2).End(xlUp).Offset(1)
.Value = Range("A1").Value
.Offset(, 1).Value = Date
End With
End Sub



"A1" is auto populated via a data (web) lookup every hour...

I have not done something like this, least as I recall. If not overly involved, can you explain how this is done?

Mark

speedy dave
05-17-2010, 04:04 PM
Thanks Mark, works perfectly !! Thankyou SO MUCH !!
( I will study the code to try and decifer just how it works. Hopefully I can learn a little bit more abo VVA this way )

As for the auto data thing, thats pretty straight forward .
In the "data tab", select "Get External Data/From Web" ( you can also get data from other sources if you want, other spreadsheets etc )
Enter the web address and hit go.
On the web page that is diisplayed is a series of yellow arrows that hightlight various data areas.
Select the one that has the data that you want and hit "import"
The data area select will be imported to the active sheet.
I normally set up a seperate sheet ( called data or data import or whatever)
This sheet gets updated according to the rules that have been set in the "data/connections" area.
I then just link any formulas etc to the data on the "data" sheet.
The main one that I use is for tracking my "Pot of Gold"
I get the Gold Price data from our local mint's web site.
My spreadsheet then calculates the data etc etc and lets me know how the investment is tracking. ( + - % $ )

Hope this helps, and again, thanks very much for your help !