• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VB Function to Copy Paste

S

sivakr68

New Member
Hi


Need some assistance in creating a function in Excel 2007.

Attached is a sample of a table I am working with Actual data table and Stored data table.


In the first table in cell B2..G2, that has formula which are based on live data. When the formula is correct, it will display a figure and when it is not, it will display blank. The rest of the rows 3 to 8 will populate accordingly based on row 2.


Anyway, I need to create a function that

• When cell B2 (for example) gets populated, the function will copy B2..B8 and paste to B11..B17. Each Column is independent of each other.


• When B2 goes blank, data in B11..B17 will still remain. For example in column F, the cell F2 is blank but the data in F11..F17 is still there.


• When B2 get populated again, it will overwrite cells B11..B17.


• If the date and time (row 7 & 8) can work with the function, it would be great.


Thanks in Advance

Regards


SivaK


Sample table :- www.sivasharm/sampledata.jpg
 
Hi Siva ,


The link you have given is invalid ; instead of posting a .jpg snapshot , can you upload your workbook itself , at least with unimportant sample data ?


Narayan
 
Hi Narayank


Please see the sample data file : http://www.sivasharm.com/sample_data.xlsx


The info on the first table is populated from another table. The Second table is the stored info from the first table.


Thanks


Regards

SivaK
 
Hi Siva ,


Thanks for the upload. Can you clarify the following ?


1. The data in cells B2:H8 ( or even beyond column H ) will be entered by the user.


2. When the data is entered , all of the data in a column ( B2:B8 or C2:C8 or any other column ) will be entered at the same time ; it will not happen that only B2:B4 ( just an example ) will be entered at one time , and B5:B8 will be entered later.


3. When the data is erased , whether B2:B8 is erased at the same time or not is not an issue ; only B2 ( and C2 , D2 ,... for the other columns ) will be looked at when data is to be transferred from the upper table to the lower table.


4. It will not happen that any column data will be changed without erasing it first e.g. when new data is to be entered in B2:B8 , first these cells will be cleared of all data and then data will be entered afresh.


I think rather than look at cells B2 , C2 and so on , for initiating the data transfer , it would be better if a command button is provided ; when the user clicks on this , the data is transferred.


Narayan
 
Hi Narayan


Thanks for the quick reply.


Answer to your questions.

1)The data in Cell B2:H8 will have formulas that will pull data from another table.

The formula is a True/False. If the formula is True it will populate the cell.


2)When cell B2 gets populated, B3:B8 will be auto populated. D2 is populated, D3:D8 will auto populate. The columns are independent of each other.


3) The cell B2:B8 will erase all together. The Cells B3:B8 are dependent to B2. When B2 erase, B3:B8 will erase. When B2 is populated, B2:B8 will be copied to the lower table. When B2:B8 goes blank, Cells B11:B17 will still remain. Eg. like in column F (sorry the time and date in the first table under F is suppose to be blank).


4)The data from the other table is a live data feed that may/will change every second/minute. The cell B2:H8 will come and go like a lights in a Christmas tree.


The Common Button will not work. It has to be automated.


Thanks


Regards

SivaK
 
Hi Siva ,


Try the following code :

[pre]
Code: 
Const NUMBER_OF_ROWS = 7
Const SECOND_TABLE_OFFSET = 9

Private Sub Worksheet_Change(ByVal Target As Range)
If Application.Intersect(Target, Range("B2:G2")) Is Nothing Then Exit Sub
If Target.Cells(1, 1) = "" Then Exit Sub
Application.EnableEvents = False
For i = 1 To NUMBER_OF_ROWS
Target.Offset(SECOND_TABLE_OFFSET + i - 1, 0) = Target.Offset(i - 1, 0)
Next
Application.EnableEvents = True
End Sub
[/pre]

Narayan
 
Hi Narayan


It doesn't seem to be working with my actual worksheet


The cells B2:G2 are cells with formulas.

=IF(E$52=1, "Black", IF(E$62=1,"White",""))


It does not seem to be working with the formulas there. If I manually key in something in B2, it works.


Regards

Siva
 
Hi Siva ,


That is the way the Worksheet_Change event functions. Give me some time , and I'll post the revised code.


Narayan
 
Hi, sivakr68!

The Worksheet_Change event is triggered when you change the contents of a cell, either it is a value or a formula, despite of what it displays. In your last post (differing from first ones) B2 has a formula, so NARAYANK991's event code will be executed if you change the formula in B2 and not if you change E52 or E62 cell values that are the precedents.

That's to say: one thing is the value displayed in a cell and another thing is the value or formula that it has. Change event acts when you change values/formulas stored in cell, not when cell displayed values changes because of calculations. And that's why it works when you enter manually a value in B2, overriding the nested IF formula described: in this case you're changing B2 contents not just B2 displayed.

Hope it aided.

Regards!
 
Hi Siva ,


Can you please let me know the formulae in each of the cells B2 through G2 ? Will these formulae remain the same , or are they likely to change from time to time ?


Narayan
 
@NARAYANK991

Hi!

From first post to third-one previous, contents of B2:G2 changed from values to formulas so I think those cells might have both of them, that's to say everything but the same. From a different viewpoint it's what I tried to explain to sivakr68.

Regards!
 
Hi Siva ,


Try out the following code , though this also has its own problems ! You can find them out and decide whether you can live with them.

[pre]
Code: 
Const NUMBER_OF_ROWS = 7
Const SECOND_TABLE_OFFSET = 9
Dim check_array() As Variant
Dim check_range As Range

Private Sub Worksheet_Change(ByVal Target As Range)
Set Precedent_range = check_range.DirectPrecedents
If Application.Intersect(Target, Precedent_range) Is Nothing Then Exit Sub
For i = LBound(check_array, 2) To UBound(check_array, 2)
Set target_range = check_range.Cells(1, 1).Offset(0, i - 1)
If target_range <> "" Then
check_array(1, i) = target_range
Application.EnableEvents = False
target_range.Resize(NUMBER_OF_ROWS, 1).Copy
target_range.Offset(SECOND_TABLE_OFFSET, 0).Resize(NUMBER_OF_ROWS, 1).PasteSpecial xlPasteValues
Application.EnableEvents = True
End If
Next
Application.CutCopyMode = False
End Sub

Public Sub worksheet_activate()
Set check_range = Range("B2:G2")
check_array = check_range.Value
End Sub
[/pre]

Narayan
 
HI Narayan


Thanks for your reply.


The formulas in B2:G2 are fixed, just the results changes.


I tried the codes above, but am getting an error on


"Set Precedent_range = check_range.DirectPrecedents"


Thanks

Regards

SivaK
 
Hi Siva ,


Sorry I did not incorporate any error checking ; this error means that one of the cells in the range B2:G2 does not contain a formula ; it contains data. I'll post the revised code in a short while.


Narayan
 
Hi Siva ,


The following should do it ; replace the first statement in the Worksheet_Change procedure :


Set Precedent_range = check_range.DirectPrecedents


by the following statement :


Set precedent_range = Union(check_range, check_range.DirectPrecedents)


Narayan
 
Hi Narayan


I am getting the following error


Run time error '91':

Object variable or With block variable not set


Regards

Siva
 
Hi Siva ,


Is it possible you can send me your workbook , so that troubleshooting becomes easier ? With limited data at my disposal , things are more difficult.


If you do not want to upload the file , send it to me at narayank1026[at]gmail[dot]com


Narayan
 
You must log in or register to reply here.
Back
Top