Score: 84°       Point: 78254       Stars: 0        




This is an Excel/VBA discussion forum.

Get Adobe Flash player


R1C1 Formula for subtotal

Hi Guys,
I am writing a lengthy macro and am stuck in a part where I want to enter a subtotal formula in a cell. Since the whole macro would be creating many sheets and performing many actions on them, therefore, i want this formula to use R1C1 format for subtotal. But at the same time I want it to take a range which will start from a static cell e.g. U2 and go till the final row of U column.
When recording the same it gave me the below script:
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[-416]C:R[-1]C)"
This does not cater my problem as, the sheet would have different no. of rows every time, therefore I want the formula to take the range from cell U2 to final row of U column. I tried tweaking it as below but it wouldn't work :(
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,"U2:U" & Finalrow)"
Any help

2013-06-12 02:08:53

Searching for Todays() date

I am trying to write a macro where I looked for today's date and then copy a cell next to the date I searched for

2013-06-12 02:04:24

Using @ in excel formulas?

I looked online but can't find anything regarding using @ in formulas.
I was looking at an old spreadsheet from a previous worker and came across these formulas...can someone help with what they are especially the @ symbol?
=IF([@[Cost Model]]="CPM",[@[Unit Revenue]]*[@[Ordered Units]]*0.001,[@[Ordered Units]]*[@[Unit Revenue]])
=MAX(IF([@[Cost Model]]="CPM",[@[This Month''s Goal]]*[@[Unit Revenue]]*0.001,[@[This Month''s Goal]]*[@[Unit Revenue]]),0)
=IF([@[Cost Model]]="CPM",MIN([@[Month''s Units (Adv)]]*[@[Unit Revenue]]*0.001,[@[Revenue Cap]]),MIN([@[Month''s Units (Adv)]]*[@[Unit Revenue]],[@[Revenue Cap]]))

2013-06-12 01:43:44

Combine data from multiple sheets into one

Hello! (This is my first post on any sort of forum)
I'm working in a large database that has the same formatting on each sheet. I use the database to track vehicle orders from start to finish. When an order is placed it goes on one sheet, then as the order progresses I move it to the next sheet, and so on...) My sheets are: New Orders, Approved, On the Road, Returned.
What I am looking to do is have a master sheet of Customer names with out duplicates so that I can track how many orders that specific customer has had (Plus some other things involving a pivot table but we will get to that another time once I figure this out.)
To Clarify: I want to be able to list how many unique customer names I have in the entire database. Customer Steve could have 2 orders in Approved and 3 on Returned. B

2013-06-12 01:37:07

Recording a macro to link workbooks with conditional information

Hello. I am a new memeber with moderate excel experience. My problem is I am trying to create a macro to pull specific information if a condition is met. For example I would like to copy and paste A57:H57 from one work book to another, but only if K57 has a value. Or more specifically, I would like to seach a named cell range for values and copy and paste the text in the rows beside it. I don't even know if this is something I can do in excel or not. Please Help :confused:
Thank you,

2013-06-12 01:24:13

conditional check, action, then continue

Hi there,
My main goal here is to push a button, and based on the contents of a cell will either show or hide a worksheet.
I have a macro that checks the contents of a variable on a worksheet for a #DIV/0! error, if there is one, performs an action, and if there isn't, performs an action.
Public Sub unrequiredtabs()
If IsError(Sheets("Variables").Range("GROUP 1dayworkgangrate")) Then
End If
End Sub
My problem is I have a long list of these checks I wish to perform in one foul swoop, each with differing actions and reactions based on input. 30 in total. As I'm sure you can see, if there is a divisible by zero error I wish to hide the worksheet, and if there isn't, I wish to show it.
Long story short, can anyone help with this macro without coding 30 different IF s

2013-06-12 01:08:52

How to use the contents of a cell to direct a formula to a worksheet in another file

For Example:
In a spreadsheet whose file name is fruits.xls I have a series of worksheets with data sets for different things. One of these worksheets is called "apples" and another is called "pears." These sheets contain rows of data such as "color" and "weight."
I have another spreadsheet that I use to generate reports that I send to customers. Let's call this one "reports"
In Cell A1 of my "reports" spreadsheet I type "apples." I want cell A2 to display the color of the apples that I've recorded in cell B2 of fruits.xls "apples." That's easy. I know how to do that. However if I type "pears" in cell A1 I want cell A2 to instead call to the "pears" worksheet of fruits.xls.
Therefore if I type "apples" in A1 then A2 would fill in with B2 fruits.xls "apples" which is a value of "red." If I

2013-06-12 01:05:46

Need to find date from one sheet and paste in 2nd sheet

Hi all,
I have some data on sheet 1 with date and order no. i need to find only one certain date results, and i need to paste those in 2nd sheet. here we can't use filter because these dates are not in a single column these are in multiple columns. please help me....:(

2013-06-12 00:58:49

Help with if statement

Hello, I need help to develop a file to track profit on jobs. There is royalty charged on the amount of the job, and there are breaks for certain revenue levels.
The breakdown is like this:
Revenue: $0-$80000,$80001-$125000,$125001-$225000 ,$225000+
Royalty: 17%, 11%, 10%, 5%
The royalty break only lowers for the revenue after the break value.
What I'm looking to do is have the cell add up each jobs cost and categorize to the proper royalty level and spit out value.

2013-06-12 00:47:09

Adapting Conditional Formatting

Currently I'm using the top/bottom conditional rule formatting so that the highest figure is displayed in green and the lowest in red (as shown below). The issue is, as I have a set of data that is made up of 20 or 22 entries depending on what the user selects in the dropdown menu, there is an issue getting the conditional formatting to adapt to this. I can't seem to enter an IF statement for the top/bottom values option, so I'm unaware how I can achieve this. Only the first 20 rows of the data can be potentially highlighted but I want it so it adapts when there are 22 rows.

2013-06-12 00:39:36

VBA Compatibility between Excel 2007 and 2010

I have 4 Excel workbooks which were created in Excel 2007. Each workbook contain approximately 200 sheets, which contains information of clients. Each workbook has 3-5 VBA routines (some quite long) which were written to do routine work between the workbooks and inside each workbook (depending on the tasks to be completed). Both the sheets and VBA code in all the workbooks were originally created in Excel 2007 (nothing was done in older versions).
Every VBA routine run extremely fast and smooth if run in Excel 2007. A few months ago we upgraded to Excel 2010 and right there everything SLOWED DOWN to a crawl.
The VBA code (when executed in Excel 2010) run 20 times slower than in Excel 2007. At first I though it was something in the VBA code that were written in Excel 2007 which were not com

2013-06-11 20:17:26

Calculated fields in a pivot table

I have a pivot table that displays sums and an average of data relating to staff.
Staff Name Average of line rental Sum of total cost 1 Sum of total cost 2 Total of line rental and Cost 2
Bob 12 58 60 ?
I want to add a calculated field into the pivot table so I can add the 'average of line rental' and the 'sum of total cost 2' displayed values together as a new totalised field.
I have tried various formula in the calculated field, but it does not treat the average field as the displayed value 12. Instead it totalises the raw data that has been averaged to produce 12 as a result.
Excel seems to ignore the fact that we already have an average in place.
Is there are way to add the average value 12 to the cost 2 value of 60 within the pivot table?
Thanks in advance.

2013-06-11 20:12:14

Using Named Ranges to allow for Inserting Cells Within a VBA Coded Range

Hello all!
Attached is a sample of the sheet I am working on. I am pretty new to VBA and so far my code is working as intended. So far, this is what my code does: Whenever data is entered in any cell within the range K10:S41 excel will run a script which copies that cell information into other sheets (range K10:K41 copies into sheet 2 C9:C40, L10:L41 copies into sheet 3 C9:C40 etc.), next there is a script within sheets 2-9 that has automatically hides or unhides rows based on the value of cells C9:C40. So far, so good.
Now a problem arises when I run out of rows and need to insert rows within my sheets. The way I understand it, I have written the VBA code such that the coded range doesn't change when inserting cells, thus VBA doesn't apply the code to the new cells/rows. I believe I need

2013-06-11 20:12:13

How can I paste into merged cells? # of cells is equal

I have 373 rows that have been merged into 176 cells. On another sheet, I have 176 values, each in its own cell, in a column that I wish to paste into the 176 merged cells.
When I try to paste, it gives me an error - cell must be identical size...
Is there anyway to fix this?
Thanks for your help!

2013-06-11 20:11:20

Syntax error???

Does anyone know what is wrong with the syntax in this statement?
Sub DisableCount()
On Error
Application.OnTime EarliestTime:=CD, Procedure:="Counter", Schedule:=False
End Sub
I keep getting a syntax error on the On Error line as highlighted?

2013-06-11 20:09:11

Flag cells that are empty after 90 days

In my worksheet there are 4 columns - A, B, C, D.
If no date is entered in Column B 90 days after entering a date in Column A - Column B should be flagged up (conditional formatting I think).
If no date is entered in Column C 90 days after entering a date in Column B - Column C should be flagged up
If no date is entered in Column D 90 days after entering a date in Column C - Column C should be flagged up.
Is there a way of doing this? I have googled around but nothing that works for what I need above.
Any help would be greatly appreciated. I'm using Excel 2007.

2013-06-11 20:03:42

lookup formula problems

I found a nifty formula that lookups the last value in a table based on a lookup value. However, I don't quite understand it. It is as follows: =LOOKUP(2,1/(A1:A10=C1),B1:B10)
ID Value 1 40
1 10
2 10
3 10
4 10
1 20
3 10
1 40
What does the first number "2" refer to in the formula. And then what does the rest of the formula do? I'm trying to adapt for a spreadsheet that I have. Thanks.

2013-06-11 20:00:00

Removing duplicates from multiple columns (treating each column separately)

hi all
Hopefully the description is self explanatory
I know how to remove duplicates from a columns but i have 200+ columns and will need to perform this on all.
I couldnt find anything on the net. I found this
http://www.mrexcel.com/forum/excel-q...e -columns.html
but the vba came up with an error (not sure if its because I have excel 2003?)
Anyway, assistance would be greatly appreciated
thanks !!
Attached Files
(10.1 KB)

2013-06-11 19:43:03

excel charts too big in word

Once again I can't find the topic I posted here. I cc cleaned and still, went to an excel file of 406kb, copied one chart to an empty word file and the word file is 459kb.
Any hints?
Why does the topic go away?
joe b.

2013-06-11 19:41:39

Create VBA Pivot Table

I am trying to create a VBA pivot table to count the number of times column C occures for each year.
Here is the code I am trying to use. For some reason the code is not collecting the data and compiling into a pivot table. Please help me in fixing this code.
HTML Code:
Sub ptable()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
' delete sheet Pivot Table 1 if present in workbook
On Error Resume Next
Sheets("Pivot Table 1").Delete
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Pivot Table 1"
' selecting the source data in sheet name data
ActiveWorkbook.PivotCaches.Add(SourceTyp e:=xlDatabase, SourceData:= _
Sheets("Sheet1").Range("b1:c" & Sheets("Sheet1").Range("a65356").End(xlU p).Row)).CreatePivotTable TableDestination:=Sheets("Pivo

2013-06-10 19:46:21

View More (Log In and Will Fans)

Win the T-shirt

with only 4000 points

Who We Are Partner Advertising Contacts Privacy terms Help & FAQ

© Copyright 2010-2017 Fabrizio Fichera. All rights reserved.

© 2010-2018 Fabrizio Fichera


Fill out the form you see below. Registration is free, fast and simple.
If you are already registered, sign in page login.

Web Site :
Required Field
First Name :
Required Field
Last Name :
Required Field
Email :
Required Field
Sex :
Required Field

evolveStar.com is free for ever !

evolveStar.com provides a search engine that allows you to gather information to write their own blog.

evolveStar.com enhances the sources displaying the logo of the site.

If you want to remove your site or you believe a site listed infringes copyright, please report it to: info@evolvestar.com

Specifies the subject copyright violation and the url of the page

evolveStar.com respecting the law DMCA (Digital Millennium Copyright Act) will immediately remove whatever its merits.

Next will be effectual and relevant checks.