Inserting a variable into a formula 1

[timbuk22]

My file has many, many worksheets that are named according to a convention (date - e.g. 1010 is oct. 10th). So, I have say 31 pages in October 1001, 1002, etc.

I have two related questions:

first, my macro needs to be able to activate one of these worksheets based upon a variable that is either set or part of a loop that, how can I insert or use a variable to accomplish the same thing as Workseets("sheet1").Activate does for fixed name?

second, In creating a summary page, I want to refer to values or formulas on the individual pages. how can I insert the worksheet name as a variable? Is there another formula entry method besides .formula and formulaR1C1? I'd like to be able to loop through the worksheet names and enter formulas with the worksheet name equal to the loop variable.

 

[SkipVought]

Hi,

Every time I see...

"My file has many, many worksheets that are named according to a convention..."

and, believe me, I have seen it all too often, there is the cry for HELP: how to summarize the mess.

Ths solution is SIMPLE, but many times not easy.

The simple solution is to organize your data in a SINGLE TABLE. From such a table, you can easily, using the plethora of Excel Data Analysis and Reporting tools, report by DAY, WEEK, FORTNIGHT, MONTH, QUARTER, YEAR. Such a report could be generated in a matter of SECONDS.

Having your data segmented as you have described, will afford you constant pain and suffering, rending you data nearly as useless as a sheet of paper in a file drawer.

Put your effort into consolidating your data correctly and gaining an understanding and facility of AutoFilter, Advanced Filter, Pivot Table Wizard, Subtotal. It will be effort well spent!

BTW

sSheet = Format(MyDate, "mmdd")
With Sheets(sSheet)
   ' now you can reference a sheet
   .Range("A1").Value = SomeOtherValue
End With

Skip,
[sub]
[red][/red]
[/sub]

 

[timbuk22]

Skip,

Sorry, but putting data in a table is not the problem. That is how the data arrives, putting it in a table is far more work and generates far more risks and issues than leaving it on a single, uncorrupted page.

There are many applications where it would be useful to activate a worksheet and depending upon a variable or insert a variable into a formula, anyone know how to do that?

 

[Fenrirshowl]

timbuk22

I'm not overly clear on what you want to do, perhaps you could provide an example with some sample data?

If you want to cycle through the sheets in your workbook you can either refer to them in VBA by their name (i.e. Sheets("Sheet1")), in which case you will need a list of the sheet names, or by their position (i.e. Sheets(1)).

Using their position you can cycle through them easily (as Skip shows) or use a FOR x=1 TO n loop where n is the number of sheets in the workbook.

For your summary page, you may want to look at the Indirect, Index, VLookup spreadsheet functions.

Fen

 

[HorseGoose]

If I understand correctly you want to be able to insert a worksheet name into a formula, although it is not clear what you are trying to do.

Sub dump()
Dim varwks As String
varwks = Sheets("sheet1").Name
Range("$B$1").Formula = "=" & varwks & "!$A$1*$A$2"
End Sub

you can store the name of any sheet into a variable in so many ways that until i know your specific request it is difficult to anticipate what you want. Sorry if this is not exactly what you wanted.

HG

 

[SkipVought]

"Sorry, but putting data in a table is not the problem. That is how the data arrives, putting it in a table is far more work and generates far more risks and issues than leaving it on a single, uncorrupted page."

Sorry, but it IS the main problem! You just do not understand. Your multiple-sheet data source is not best practices and will continue to be a LIMITING factor in the usefullness of your data. Just trying to help.


Skip,
[sub]
[red][/red]
[/sub]

 

[xlbo]

Got to agree with Skip. I have not been working in this type of role for as long as him - a relatively short 7 years but in that time, I have not come across a single instance where it is better to seperate your stored data than to consolidate it.

Many people make the mistake of not seperating their reporting requirements from their data storage requirements.

Data storage should always be consolidated. This is for both data integrity AND for ease of reporting

If the data is consolidated, rather than having 300 worksheets to maintain and then have something happen when you click on one of them, it is actually easier to have all your data on 1 sheet and a "report" sheet which can then use formulae or code to display exactly what the user wants

HorseGoose has given you the code example on how to achieve your goal but I would seriously consider your data layout as well

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[timbuk22]

This is my first time on this board and frankly you people are arrogant as all get out.

I know my data and I know what I am trying to do and I know it is not necessary or USEFUL to consolidate it.

Geoff - the data STARTS separate...COMBINING it is the extra step.

I just want to a piece of data off of it WITHOUT combining my data.

What I don't need is the lecture on how to organize my data. Geesh.

 

[timbuk22]

Thank you to HorseGoose for the idea.

 

[timbuk22]

re: Skip. As "useless as paper in a file drawer"? That is EXACTLY what it is and all it needs to be and by having separate pages it maintains the integrity of the file drawer.

 

[timbuk22]

Conclusion: HorseGoose gave me what I needed to know - the syntax for how to put a variable into a formula. As a non-programmer, I use VBA for excel to automate certain steps. I am not using excel to replace access or another database.

A simplified example of the program that creates a summary of that data and tests it against a known value to identify data errors:

Sub FormulainsertsREV()
'
'
' Worksheets("revdata").Activate
'create worksheet names in format needed to use them in the variable loop
Range("e2").Select
ActiveCell.Formula = "=+concatenate(""'"",right(A2,4),""'!"")"
Selection.Copy
Range("e2:e32").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("e2:e32").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'start loop
Range("e2").Select
For x = 1 To 31
Dim varwks As String
varwks = ActiveCell.Value
' ActiveCell.Offset(0, 1).Activate
' ActiveCell.Formula = "'" & varwks & ""
ActiveCell.Offset(0, -1).Activate
ActiveCell.Formula = "=+$c2+" & varwks & "$c$17"
ActiveCell.Offset(1, 1).Activate
Next x
'
End Sub

 

[stevexff]

timbuk22

By its nature, VBA tends to be an entry point to programming for many people, who start to dabble in it when they find that the existing functions are too cumbersome or not up to the job of what they are trying to achieve. There are a number of common traps and pitfalls.

Quite a few people on this forum have many years professional programming experience. In most cases, they have fallen into those traps and pits themselves, or had to pick up the mess after someone else has. As a result, they can be quite vocal about their particular hobby-horses[sup]1[/sup] in an attempt to make sure other people don't do the same thing. Don't mistake this for arrogance. Even if it may not seem like it, they really are trying to help. They wouldn't be posting here otherwise.

[sup]1[/sup] My own: whenever I see the same code cut and pasted repeatedly all over a module just because it was 'easier' than putting it in a loop, subroutine, or function. That is, until you need to change it a few months later...

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]