PDA

View Full Version : Sleeper: Code for Formulas & Input data via Userforms



cortiz1bog
09-06-2005, 09:34 PM
I have a very heavy workbook fill with formulas, since I started to know about VBA, I notice that I can write the formula while recording the macro and then have the code do the work without leaving the formula in cell, all I need in the cell is the actual value but not the formula, which is why the workbook is so heavy.

Since I am new at it, I make a test with the following code, which need lots of improvement as an example, can somebody give an idea how to improve the code, I know I am repeating myself in the code, but have no idea how else to do it...


ActiveCell.FormulaR1C1 = _
"=IF(AND(RC3>=INFORME!R2C3,RC3<=INFORME!R2C6),R2C2,""NO"")"
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
End Sub


I have made some user-forms for the user to input the data:
should the above code for the formulas, go together with the code for the user-form?

Need some direction as to how to put it all together

The objective is to input data into different sheets via user-forms and have a code for the actual formulas, but without leaving the formulas on the sheets. In which order and how o tie everything together?

Thanks

Cesar

royUK
09-07-2005, 12:51 AM
Can you explain exactly what you want to do, I don't think that you need a Formula at all. Probably an IF statement in the VBA.

cortiz1bog
09-07-2005, 06:26 AM
Thanks for your reply Roy.

1.- I have 4 sheets to input data - Each sheet has several columns, about 12-15 - Each cell of the columns has a formula, the formulas find information and data, names and values from 3 other sheets and a 8th sheet that keeps track of all the cum?s from all the sheets. ok so far...

2.- Since I have so many formulas in each cell, I think, that is why the workbook is so heavy and slow, my idea only... Each sheet has about 1500 rows, on & on.

3.- Since I have not try to do the formulas in VBA (don?t know how)the example is just a test of what I think I should be doing - not sure - the code is the result of a macro recorded while writing the formula.

4.- I think, if I have the formulas executed from VBA, that the workbook will be lighter and faster, it takes for ever once I input data in each cell.

5.-If I find the way of how to accomplish the above, I have made some user-forms, for the user to input the data into the first 4 sheets with a series of validation questions as the user goes along the process of inputting the data, just to be sure that the user is entering the correct data (my users are not excel-friendly)

6.- for each row on every sheet I have a bunch of formulas that needs to be applied to the data that the user is just about to input. - each of the 4 sheets consist of completely different information and different user-form.

7.- Once I find a way to write a macro for each formula that every row of info requires - comes the question - Should I have the macro-code for the formulas - at the beginning, as the user open the user-forms?

I am lost at the point of having a way of getting a macro for each row which has many cells - and - for each cell I have a different formula.

Then, I have to execute the macro with the formulas (which the macro translated to the VBA code) - to work together with the user-form.

Since I have the formulas ready, If I understand correctly, I have to record a macro process while writing the formula for each cell of the row - Am I correct?

Then put this code before the user-form opens or as it opens...

Am I going in the right direction?
Does anybody know a way to help me in this crazy adventure?
Is there something better to accomplish the process?
Thank you so much
Cesar