deleting formulas from a varible no of rows spreadsheet

[basbrian]

this is my first attempt at programming with VBA in Excel.
I am trying to automate a spreadsheet for our lab technician. Simply, he imports a number of records each week into sheet 1 then copies 2 columns to col A and B on sheet 2. he has a formula in col C. Row 1 has Headers. Depending on how many rows there were last time and this time, he has to copy down the formula in col C or clear it because it contains "#value". how can I do this last step?

 

[Anthony047]

Hi basbrian,
I suggest the following approach.
-On Sheet2, A2 and B2, set the formulas to copy the data from the corresponding cell of Sheet1; probably they will be =Sheet1!A2 and =Sheet1!B2 (if you now copy col A and B)
-on C2 set your formula
-assign to the range A2:C2 the name "BFormula" (via Menu /Insert /Name etc etc)

Now the macro:
-open the vba editor via Alt-F11
-Menu /Insert /Module; copy the following code and paste it into the frame at the right:
Sub PopData_Form()
ISheet = "Sheet1" '<<< Sheet that data are initially imported
WSheet = "Sheet2" 'The sheet were you need to rework the data
RCol = "A1" '<<< column that will be used to check for the last line
'
Application.EnableEvents = False '###
Sheets(WSheet).Select
UsCols = Range("BFormula").Columns.Count
Sh1Lines = Sheets(ISheet).Cells(Rows.Count, Range(RCol).Column).End(xlUp).Row
If Sh1Lines < 2 Then GoTo Esci
Cells(3, 1).Resize(Rows.Count - 2, UsCols).ClearContents
Range("BFormula").Copy Destination:=Cells(3, 1).Resize(Sh1Lines - 2, UsCols)
Esci:
Application.EnableEvents = True
End Sub

Check the instructions marked <<< and modify if necessary.

Return to excel and assign a shortcut to the macro, for example Contr-Shift-U (U for "Update").
Run the macro; it will:
-select the rework sheet (sheet2)
-clear the existing data from line 3 down
-copy the formulas (range BFormula) for as many lines exists in Sheet1

If you wish, you can run the macro whenever you enter Sheet2:
-from excel Sheet2, rightclick on the tab with the name of the sheet; you will be brought to the "sheet2 code mudule"
-insert the following code into the right frame:
Private Sub Worksheet_Activate()
Call PopData_Form
End Sub

Do this only if running the macro whenever to switch to sheet2 is reasonable.

Hope that this will help; post again for any clarification.
Bye.



Anthony047 (GMT+1)

 

[SkipVought]

Hi,

I try to use features other than COPY 'n'PASTE whenever reasonable.

This sort of thing can be easily accomplished using MS Query. faq68-5829

One of the davantages of a query is that you can set a switch to automatically propogate formulas adjacent and to the right of the resultset, thru all rows of the resultset.

When you say that, "he imports a number of records each week," is that copy 'n' paste "import" or is he using the Data > Import external data feature?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]