code to automatically copy/paste formulae on entering data in another column...?

[wiklendt]

hello everyone,

so i'm working on an excel sheet to collect and collate data on some specimens.

i pretty much have things set up as i want them. i have validation data lists, from which users can pick their common/frequent data and then i also have formulae down the track in each row which interpret that data, collate it and compare data from one platform (phenotypic) to another platform (genotypic). i may want to put data interpretation into a different sheet of the same workbook.

the only thing that is nagging me is: i know i'll eventually build up a list of many specimens, so i know i need the formulae to be 'copied' down the sheet to accommodate new data HOWEVER, if i copy, say, 100 or 1000 rows, then print, it will print ALL those rows with formulae, whether or not i've put data in.

the validation data lists work great - i've applied them to the whole column, and excel only prints to the bottom of the last entered row.

i think i can demonstrate what i mean by showing a screenshot of my sheet in page-break view...

this image (attached) shows how i can merrily add new samples and data and excel will only print to the bottom of that data (and even shows how the formulae interpret the data all the way on the right hand side: the second row i manually copied from the first for my post)

this image (attached) shows how, if i were to prefill the formulae to interpret results for the expected number of samples, excel prints those rows as well, even though i have not entered a sample or data yet (just the formulae).

what i want to do is this, and it probably will require some VBA:

when a new row of data is entered, say, row 8 in column E is no longer empty, then the formulae from:

AL5:BL5

are dutifully copied and pasted to:

AL8:BL8 (or whatever the new row will be).

i didn't really find anything useful in AWF, but i have found these two pages on the web which seem to touch on what i might want to do, i just have no idea how to adjust them to my purposes:

http://www.computing.net/answers/office/excel-programming/7578.html

and this one: which seems to be doing exactly what i want, BUT the code offered is only for one instance - it won't work for continually updating specimen/row numbers:
http://www.xtremevbtalk.com/showthread.php?t=112727

has anyone had to do anything like this before? can anyone point me in the right direction?

edit: i should add that, while i can easily change the "print area" each time, i don't want to have to do that AND this workbook will be sent to collaborating institutes, and i don't want to have to 'train' them in such features - i just want it to work "out-of-the-box"

 

[chergh]

You want to use the Workbook "SheetChange" event for this. The following code should accomplish what you want.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

'change "Sheet1" to whatever your workshhet is called
'target column is 5 as it is based on column e
'to have this fire on another row e.g. column "A" you would change target.column to 1


If Sh.Name = "Sheet1" And _
    Target.Column = 5 Then

    Application.EnableEvents = False
    
    Sh.Range("AL5:BL5").Copy Destination:=Sh.Range("AL" & Target.Row & ":BL" & Target.Row)
        
    Application.EnableEvents = True
    
End If

End Sub

 

[wiklendt]

wow, sweet - i'll check that out and let you know how it goes.

 

[wiklendt]

hm... i'm going to have to work on it / learn about it a bit more over the weekend - i've put the code in but nothing at all happens, not the expected outcome, no errors, no debugging messages, nothing.

i've tried tweaking the code in the fumbly way i know how, but admittedly i've only used VBA in access, so i'm finding Excel quite hard to get my head around... (today!)

i've even tried debug.print "your code fired", but that's not triggering anything either. admittedly, i've never used debug.print feature before, so not actually sure what it IS supposed to do (bring up a dialog? print to the immediate window?)

i'll post back with progress after the weekend.

thanks for your assistance.

 

[wiklendt]

i'm not getting very far.

just to see if my worksheet was broken, i tried to macros record and execute, which worked. but i don't want to use a macro, i want to use VBA. i can't seem to trigger my VBA in my worksheet.

i've saved the file as a macro-enabled workbook (i'm using excel 2007) and i've added the book to my trusted locations (just in case), but i can't even get a message box to trigger at the expected time. this is all a little weird and confusing, considering i can mostly do what i want/expect in access... i just can't see where i'm going wrong in excel...

to clarify what i need, when someone puts in a new specimen (which can be triggered in column C), the range of cells from AL5:BL5 be copied to the new row created as a result of the new sample being added ("target.row", i presume).

FYI (if you're interested in what the formulae do) these forumulae interpret any (numerical) data in the antibiogram (effect of antibiotic on that specimen (a bacterium)) part of the worksheet (range K:AK) with logical/usable/reportable results (resistant, susceptible or intermediate).

i've attached my workbook if anyone cares to have a look (thanks in advance!) while i thrust my attention to my recently borrowed "excel 2007 bible" - god i love the library!

 

[wiklendt]

YAAAAAY!!!! i'm getting an error!! woohoo!

i AM *genuinely* happy about that because before i got nothing at all! no error, but also nothing working! now i have at least excel acknowledging that there's code there to run and check!

what i did to get excel to acknowledge the existance code is changed:

Private Sub Work[COLOR=Red][B]book[/B][/COLOR]_Change(ByVal Sh As Object, ByVal Target As Range)

to

Private Sub Work[COLOR=Red][B]sheet[/B][/COLOR]_Change(ByVal Sh As Object, ByVal Target As Range)

the error i'm getting is attached in the image, but the text goes:

"compile error: procedure declaration does not match description of event or procedure having the same name."

the whole code currently is:

Private Sub Worksheet_Change(ByVal Sh As Object, ByVal Target As Range)

'first, make sure the sheet and target column the code is refering to are correct.
'(target column is 3 as it is based on column C (A = 1, B = 2, C = 3 ...etc))

If Sh.Name = "Data entry" And _
    Target.Column = 3 Then
    Application.EnableEvents = False
    Sh.Range("AL5:BL5").Copy Destination:=Sh.Range("AL" & Target.Row & ":BL" & Target.Row)
    Application.EnableEvents = True
End If

End Sub

i'll continue to dabble on it on my end, but a push in the right direction has always been appreciated thanks!

 

[HaHoBe]

Hi, wiklendt,

if you use the events that Excel gives to classes your very own way runtime errors will most certainly happen...

The procedure you installed is a workbook event and thus must be placed in ThisWorkbook. If you want to change it to a Worksheet-Event change to the according sheet, select Worksheet form the left Combobox in the code window and look for the appropriate method in the right one:

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

should be put into the code window because we don´t need to give in a name for the sheet (or an object) as the code will only fire from the sheet where the code is placed:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 Then
  With Application
    .EnableEvents = False
    Range("AL" & Target.Row & ":BL" & Target.Row).Value = Range("AL5:BL5").Value
    .EnableEvents = True
  End With
End If
End Sub

Ciao,
Holger

 

[wiklendt]

yay! thankyou Holger!

ah, i thought checking for sheet name was redundant, but was not confident enough to take it out.

we're getting SO much closer - it now copies from AL5:BL5 to the target row, which is the new row - EXCELLENT.

HOWEVER, it's copying on the values, not the formulae! copy paste in VBA does not seem to be the same as copy paste when a user does it manually!

so - i hit F1 to invoke Excel help, and got a clue there - Range.Formula

so i changed the code to this:

    Range("AL" & Target.Row & ":BL" & Target.Row).[COLOR=Red]Formula[/COLOR] = Range("AL5:BL5").[COLOR=Red]Formula[/COLOR]

but what it does now is copies the underlying formulae AND the value from AL5:BL5, and DOES NOT refresh if the raw data changes (and especially annoying is it has a result for a sample where there is no raw data at all! and if i ADD data, the result also does not refresh on that formula). please find attached workbook if i've just confused you (i know, it makes no sense! i'm confused too)

----------------------------------------------------------------------
the workbook description:

i've got three samples in this workbook (edit: actually, four, the last was to try Range.FormulaLocal): row 5 is the original row. and you can see that the raw data for column AJ (pip/tazobactam) is 128, which means the bug is resistant, so the interpretation of the formula is "R" for that antibiotic (column BK)

now, in row 6, the formulae copied fine (click to select a cell between AL5:BL5 and you'll see my formulae) but i've not entered data in AJ for row 6, however, excel/VBA copied not only the formula in BK, but also the "R" from row 5??

i did another test for row 7, and again the formulae AND value of the cell copied, but i've added raw data which, you'll see is "<=4", which means the bug is sensitive to the antibiotic and the interpretation should be "s". HOWEVER, the 'value' copied from row 5 doesn't change when i change the raw data - if i manually copy the range AL5:BL5 to the new target row, the formula only is copied AND it returns the correct value if i play with the raw data.

----------------------------------------------------------------------

(and even tried combinations of .value and .formula or no distincition at all (just Range() but all with the same result)

anyone have a shove for me for the right direction?

i did more digging in excel help, and found this:

Formula Returns or sets a Variant value that represents the object's formula in A1-style notation and in the language of the macro.
FormulaLocal Returns or sets the formula for the object, using A1-style references in the language of the user. Read/write Variant.

but using FormulaLocal produced the same result as using Formula.

 

[wiklendt]

oooooooh! i see the problem: it's not updating the reference.

row 5 original formula for one column is:

=IF(AND(C5<>"",K5<>""),IF(OR(K5="<=2",K5<=16),"s",IF(OR(K5=">64",K5>32),"R","I")),"")

and the new rows generated also have the formula:

=IF(AND(C5<>"",K5<>""),IF(OR(K5="<=2",K5<=16),"s",IF(OR(K5=">64",K5>32),"R","I")),"")

(which is identical even in reference)

so maybe i need to use R1C1 reference style in teh VBA? lemme try it!

 

[wiklendt]

yay! that was it! i just needed to use .FormulaR1C1Local (or .FormulaR1C1 works the same in this case)

thanks everyone! problem solved!

 

[bookmarkmaster]

I have a sheet where I list each item of my business outgoings. Column C
lists the amount spent. In Column D I allocate a code from a drop-down
list, to allocate that item of expenditure against a particular cost centre.
I have thirty cost centres. So once the code is allocated, I can filter the
list to see all items for any particular cost centre.

But what I really want is this: I have created a separate sheet in the
workbook, with all the cost centres as column headings. I'd like each
amount to be automatically copied to the cost centre sheet, and placed in
the correct column, depending on the code which I have allocated in Column D
of the first sheet. Then, by having an autosum at the foot of each column,
I can see a running total for each cost centre really easily, and I can use
this autosummed cell to provide cost centre data for comparisons
year-on-year, in another sheet.

I've tried messing about with lookup and if functions, but I can't find a
way to achieve this, even though I feel sure it must be possible.

I hope you understand what I'm trying to achieve, and that its not a stupid
question. I really want to get this set up right so I hope you can help..
Thanks for being here,

 

[HaHoBe]

Hi,

I think it´s a totally different subject to the original threadwhich was to paste Formulae (by use of VBA). You ask for a Formula solution.

The easiest way for me would to sum up the original data (using Sumproduct) instead fo copying the values and then summing up. And thinking about it I believe a Pivot Table to be the easiest way to obtain the costs (assign a name for the DataBase or use a list (2003) or Table(2007) for it), if you have a time field put it in the rows so grouping can be done by month as well as year.

Ciao,
Holger

 

[wiklendt]

Hi,

I think it´s a totally different subject to the original threadwhich was to paste Formulae (by use of VBA). You ask for a Formula solution.

The easiest way for me would to sum up the original data (using Sumproduct) instead fo copying the values and then summing up. And thinking about it I believe a Pivot Table to be the easiest way to obtain the costs (assign a name for the DataBase or use a list (2003) or Table(2007) for it), if you have a time field put it in the rows so grouping can be done by month as well as year.

Ciao,
Holger

ah! pivot tables! yes, that would certainly do the trick as far as i understand pivot tables.

 

[fitflop]

For each. What I'm looking for is a way to do a mass paste (from Excel maybe) of part numbers and serialnumbers into a Recall form, but storing the associated primary key for each pasted item insteadopening a file dialog for the operator to select the excel spreadsheet and they're done.