MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Insert data in special way


Posted by paul on January 23, 2002 12:39 AM

Hi
sorry for dumb question. I need to insert
data in special way. Just imaging every 2 seconds
value in A1 is changing. I need to copy
old value down in A2, A3, A4 etc each time
when A1 is updated. It easy to do via Insert SQL
statement... but is there an easy way to write
similar function in VBA to keep old values
in down cells?

Sincerely
Paul


Posted by Staffan on January 23, 2002 2:00 AM

Hm,

Isnt this problem solved by the following.
(I am using the ActiveCell but any cell referens should do)
The trick here being to just insert a new cell and shift to the right.

Dim myCell As Cell
Dim newValue As String 'Or Whatever

Set myCell = ActiveCell 'Or Whatever
myCell.insert Shift:=xlShiftToRight
myCell = newValue

//Staffan

Posted by Chris D on January 23, 2002 12:12 PM

Hope it's not a problem, but with 65,536 rows you can only carry on for 131,071 seconds : or 2,184 minutes : approx 36 hours until you run out of space with your original data

Posted by paul on January 23, 2002 12:18 PM

Thanks a lot Staffan
tried to write the function
Function update_cell()

Dim myCell As Cell
Dim newValue As String 'Or Whatever

Set myCell = ActiveCell 'Or Whatever
myCell.Insert Shift:=xlShiftToRight
myCell = newValue

End Function


but it says user defined type not defined (on line Dim myCell As Cell)

Can you provide final code. Sorry I am new with Excel but got your idea and appreciate a lot!

Paul

Posted by paul on January 23, 2002 4:15 PM

Hi
Can share what is not problem for you. I don't
need macro. I need fully automated function
with analyzing of appearence of new data in A1.
Can you HELP please?

Sincerely
Paul