Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Help Needed on This Excel Problem

  Asked By: Harry    Date: Mar 06    Category: MS Office    Views: 862
  

Description of the Excel Workbook:

Sheet 1
-------

VAR01 var1
VAR03 var03
VAR02 var02


Sheet 2
-------

var03 23
var02 34
var1 10

Sheet 3
-------

((VAR02+VAR01)-VAR03)
((VAR01*VAR03)/VAR02)

Question Here:

How can i do calc formulas on column B sheet 3 and output results on
column A sheet 3? All datas are not sorted. Search function may be
required.

The process as below....

the formulas has to refer from
1. UPPERCASE VAR -> lowercase var (at sheet 1)
2. lowercase var refer to its value (at sheet 2)
3. value from sheet 2 return to sheet 3
4. perform formula calc and output results in column A sheet 3.


Is this possible in Excel Macro?

Really need a solution as i'm just begin to experiment Excel Macro
for my studies.

Share: 

 

1 Answer Found

 
Answer #1    Answered By: Venkat Rulez     Answered On: Mar 06

Your spreadsheet layout seems very complex and cumbersome to me. I would
suggest doing a Google on spreadsheet layout etiquette as well. If a
problem is set out logically, the answer is usually much simpler. Now,
can you do the calculations without VBA code?

I would do it as follows: See below in your original message.

Now that we have done this in Excel, let's see how we can code it.

Your code will need to look something as follows:

Sub CalcAcrossManySheets()

Dim VAR01 as single (or long, however you need to define it)
Dim VAR02 as single
etc.

Worksheets("Sheet 1").Select
Range("A2").Value = VLOOKUP(Range("B2"),etc.

' For sheet  3 defining the formulas  will be similarly easy.

' Now select sheet 1 again as the active sheet, and transfer the
values across.

End

Personally, I would stick with the Excel formulas. But since you need
the practice, go for it. The advantage doing it with VBA code is that
the casual user will not be able to change the formulas in the
spreadsheet. Because there are no formulas. Only values. Lastly, you
will have to ensure that the user then has an easy way to activate this
piece of code, else they will type values all over the place, and will
never understand why nothing updates.

 
Didn't find what you were looking for? Find more on Help Needed on This Excel Problem Or get search suggestion and latest updates.




Tagged: