Correct VBA Syntax To Reference Cells In Functions (Correlation)
Jan 27, 2009
I trying to populate cells with a correlation function using VBA, I have set up dynamic references/arguments for the fuction to take on however I just can't get it to work. Currently instead of populating the formula into the cell, it's just populates it as a string based on what's entered below =correl(Ystart &":"& yend, xstart&'":"'& xend").
FYI - The correl function takes on two ranges,. I've predefined these below.
I'm working on a workbook that will track staffing patterns.
The workbook has three worksheets: Sheet1 "RCS", Sheet2 "HCT' and Sheet3 "Hidden". I've attached the workbook to this thread. The password for the form is "j".
On Sheet3 "Hidden" I have two tables that are set up to collect the SUM of columns on Sheets1 "RCS" and Sheet2 "HCT". I'm finding the SUM of each range by way of the background color. I've set up the following formulas and when the "data collection tables" are in the same worksheets as the original information, the formula's work perfectly:
The following functions are pulling data from Sheet1 "RCS" and placing them into a table in Sheet2 "Hidden"
[Code] ........
The following functions are pulling data from Sheet1 "RCS" and placing them into a table in Sheet2 "Hidden"
[Code] .......
I have two more functions that aren't working due to the fact that the source values are percentages and NOT plain numbers. The above functions work great for SUM but not for percentages. EXAMPLE--Let's say, 3 sub percentages it gives me the SUM of the 3 percentages (i.e. 85% + 100% + 100% = 285% instead of giving me 95%.
[Code] ........
How might I use the following functions to find the average of the source fields instead of the SUM?
A UDF function seems to interfere sometimes with the rest of the workbooks entire process for some reason.
I could use the CEILNG function to work around the glitch, but it does not seem to allow the flexibility required because it takes everything to 1. So for example if a Value is 9.24, CEILING will Sum to 10 when when it must be 9, unless I'm missing something. But if the Value is 9.25 or 9.26 then rounded value must go to 10
The basic math required in the formula bar then is: ...
How to make a formula with references to certain cells, so I can sort the sheet but keep the formula referring to the same cells as before the sorting?
In the example I illustrated the problem: when I sort the data, the formula refers to the same cells which have now different values, so the calculation is not right anymore.
I have over 60 sheets within a workbook. There are some sheets that I want to hide or unhide depending upon the macro. I have the MR and searched in several areas but keep coming up blank with how to either select or hide these sheets.
I have the following code, which executes just fine:
[Code].....
The problem is that I need to run this again in a different column, and the data is held in another sheet that I have given the variable StepTwo to.
I'm not sure of the syntax to do this. Basically - this is what I need the code to do:
[Code] ....
Understandably this is totally incorrect, but it's the best I can do to explain what I'm actually trying to do....
The two parts of SUMIF are both held in a workbook that I've given the variable "StepTwo" to. Both sets of data are also held on Sheet(1) of that workbook.
What is the syntax for passing arrays to functions? In other words, for the following simple test example, what would work? It is highlighting the asValue parameter and saying, "Type mismatch: array or user-defined type expected". This occurs whether or not I follow it with the empty parentheses.
correctly using variables within functions used in formulas specified by VBA. Here's an example:
I want to place a formula within a workbook file called "Books 2003.xls" that goes to the version of the file for the previous year (or whatever year is specified) and does a Sum of a particular range (where that range is also specified as a variable) so it sums up the Total for the previous year for the same number of months that have data in them to date for the current year. Once VBA places the formula where it belongs, it should be able to always provide a running comparisson with the current year to date total and the totals for the same period for the previous year. Here's an example of the intent of this simple formula:
The workbook files will all be named the same way: Books 2002.xls, Books 2003.xls etc.... The range to be summed will always start at R4C2, but could then end anywhere from R4C2 (same as B4) up to R15C2 (same as B15).
I already have a routine that captures the value for the variable CurrentYear in the form "2003" and thus have another variable for PrevYear (= CurrentYear -1), and so then have a way to correctly specify the variable "BookName" to get the correct filename needed. I also have a variable for the CurrentMonth in the form of "1 through 12", and can use that to specifiy the correct RowNum needed to determine the end of the range (RowNum = CurrentMonth + 3).
I've tried to substitute the variables "BookName" and "RowNum" into the above formula with various combinations such as what follows, but I'm not getting the correct syntax with the right number of quotes etc...:
* I have a complex Excel workbook with several worksheets.
* The first worksheet is for raw data, which consists of a single string of data, separated by spaces, to be uploaded to a mainframe. There is no consistent pattern of length of data or strings in between pieces of data, which can vary widely. An example of the data would be something like this:
XXXXX YYYYY NN XXXXXXX
* For testing purposes, I need to come up with a way to parse the data for easier viewing. The current approach is this:
* Take the string and divide it up into individual cells by using the MID function. In the above approach, this would split the data into four (4) different cells.
This is going to be a very complex workbook with data strings that can have 20+ elements in them, and have lots of separate records, etc. While this way should work (if everything is entered perfectly and no data structures ever change, etc.),
I'm trying to create a formula that will added the correct amount in the correct cells, I have create a dunny sheet in trying to achieve this. If Cell B8:B11 = ABS or Dum that any points won should be added to Cell L8:L11 right now its adding it into K8:K11. If Cell B8:B11 = is Blank any player points should be added to cells K8:K11. I'm using this formula throughout cells K8:K28 =IF(J8>J25,1,IF(AND(J8<>0,J8=J25),0.5,0)) Any thing in red is incorrect anything in blue is what I'm trying to achieve.
We receive about 20 sales files of several hundred lines of data each day from various agencies. I want to create a macro / VBA code which checks that the data submitted is correct so that we can upload it into our database without import errors and / or having to manually check each line of data.
I envisage something like an output report:
##################### 149 entries Column A - Date - OK Column B - Customer_Phone - Errors (Should be 11 digits) Row 21 - Customer_Phone - Error (Not 11 digits) Row 108 - Customer_Phone - Error (Contains letters) Column C - Outcome - OK Please correct and re-check. #####################
The following sub to create and name wsheets results naming the new sheet with the value of "A9" only, but what I want to name the new sheet is "A9" + "B9". Sub CreateWorksheets() Dim newSheet As Worksheet, itemSheet As Worksheet Dim cell As Object Dim itemrange As String
Set itemSheet = Sheets("BIDFORM")
Application.ScreenUpdating = False itemrange = "A9:B9:" & itemSheet.Range("A9").End(xlDown).Address For Each cell In itemSheet.Range(itemrange) If SheetExists(cell.Value) = False Then Sheets.Add Before:=Sheets("BACK SHEET"), _ Type:="C:PathFile" Set newSheet = ActiveShee newSheet.Name = cell.Value End If Next cell Application.ScreenUpdating = True End Sub
However, when I try this with a different formula =IF(ISNUMBER(SEARCH(C2,H2)),"REPEAT","SAFE"), I'm getting a compile error: Expected end of statement with this code:
The error message highlights the word REPEAT. I suppose it's something with all the quotes throwing off where compiler thinks the end of the formula should be.
Disclaimer: I do not know the correct "lingo" to describe Excel functions and formulas, and I'm not really sure if Excel is capable of doing what I'm proposing, so please bear with me.
I'm not an avid Excel user, but I've got a question on my current problem. I've got a spreadsheet with several columns on it for my retail sales business. The spreadsheet is used to calculate my selling price based on my dealer cost of a unit plus a percent markup.
Currently, the spreadsheet is set up so that the "percent markup" column can be changed so that I can enter, say, 20%, and it will automatically spit out my selling price based on my cost divided by .80(for example).
This works fine, and I like it. However, now I have over 70 different items on the list, and the higher my cost is on an individual item, the lower percent markup I am able to make. So currently, I'm going in manually and entering a different "percent markup" for each item, depending on my cost of the item.
I'd like to learn how to define parameters for the "cost" field that will directly input to the "percent markup" field. For example, I'd like all items that cost me between $2,000 and $5,000 to have a "20" inputted into the "percent markup" field. For items that cost me between $5,001 and $10,000 I'd like to have a "15" automatically inputted into the "percent markup" field, and so on. I'm envisioning a small chart off to the side of the spreadsheet that would look something like this:
Price range percent markup
$2,000-$5,000 20 $5,001-$10,000 15 $10,001-$15,000 14 $15,001-$20,000 13 and so on and so on....
The chart (such as above) would allow me to simply change the "percent markup" inputs and the "price range" inputs at will to allow me to affect my spreadsheet and eliminate the tedious line-by-line insertion of each "percent markup" number by hand.
referencing constant cells in custom functions. This is a sample of my code:
Code: Select Case Name Case Is = "Ball" Valve_Days = [V4] + Cushion Case Is = "Check" Valve_Days = [V5] + Cushion
Now, the problem is when I open the workbook up, or someone downloads the file from the server, it resets all fields to div/0 based on what sheet the last user 'Saved' the document on. I think this is occurring because it doesn't know which sheet to pull the value V5 from? The problem never started to happen until I added another sheet in the workbook. Is there a way to tell VB that it isn't just cell V5, but it is cell V5 on Sheet 'X'?
It's just a simple percentage calculator for my purposes and works fine as is. However, I want to make it dynamic whereby from a user input the length of the array will increase or decrease, e.g. G9-G11 will become G9-G12 if there are four rows occupied with data. For all my other SUMming equations I have solved this and even came up with what I think is a perfectly valid solution for this one. The one problem is that it doesn't work when combined into a single equation.
My solution for the upper part of the fraction is this:
I´m writting a macro. It works find until a certain point. When I want to change some outputs of the macro without changing the syntax, it display an error mesage while runing the macro. It says Else without If. Which is quite disturbing because the Else was not creating any problem before. Here is my macro before I changed the conditions (this one work nicely)
Sub Copy_Sheet_Beta() Set wba = ActiveWorkbook On Error Resume Next If IsWorkbookOpened("Projekt.xls", "C:Documents and SettingsfrederikSkrivebordRedd Barna") Then Workbooks("Projekt.xls").Activate 'In case open, just activate "Projekt" Else Workbooks.Open Filename:="C:Documents and SettingsfrederikSkrivebordRedd Barnaprojekt.xls" End If Set wb = Workbooks("Projekt.xls") wb.Activate If Not SheetExists(wba.ActiveSheet. Range("C1").Value) Then MsgBox "overall doesn't exist!" Else........................................
I have a table with 3 columns of dates and then a column with Set # that I feel in the box #.
I need to see how many items processed for each set per day.
Example: [url]
The problem is that it counts the correct amount but not with the correct dates. The formula that I use is: =SUMPRODUCT(--($I$3:$I$8<>"")*(($C$3:$C$8=39601)+AND($E$3:$E$8=39601)+AND($G$3:$G$8=39601)))
I want to consolidate certains cells in 30 identical worksheets in a workbook. I understood that if I use the 3-D formula method, that I could insert new identical worksheets into the range of worksheets and the consolidated totals would update. Is that correct?
I cannot seem to get the syntax to work. When I read the Excel Help file it gives an example of =sum(sheet2:sheet7!B3) but I don't understand how Excel could answer this ( if the worksheets were named Sheet2,sheet3,sheet4 etc up to sheet7) In my example, the formula is clearly wrong. Why does it not use the single quotation marks '? I cannot find references to the proper syntax anywhere. I have your training program but this does not spell it out for me either. If I use Data>Consolidate then I have to labouriously do this for each cell that I want to consolidate.
I have a spreadsheet that has 5 columns, with the headers:
Code Description Colour Size Price
There are over 500 lines on this spreadsheet.
The Blue headers have all the information filled in, whereas the Red headers do not.
I have a second tab on that spreadsheet with the below information filled in.
Code Description Colour Size Price
Now my problem is that I need to merge the 2 tabs into 1...however....
The codes on tabs are not in the same order, and on the first tab, they are interspersed with merged rows with the category name, whereas the second tab they are just a full list.
I have lets say 12 months of data. I have formulas that reference the latest 6 months. When I insert a new column to input a new month, how can I make the formulas include the new months without manually updating them.
EXAMPLE:
12 months of data exist in cells B3:M3 going from B3(oldest) to M3(newest). Formulas reference latest 6 months of data in cells H3:M3. When a new month hits, I insert a column after column M.I would like the formulas to now reference cells I3:N3 which is now the newest 6 months.
I have a report that I pull that I pull fairly often that is in this format and shows which footage of products each store is getting (out of over 1800 stores)!
Above is the format that it comes back as. And here is a spreadsheet that shows what steps I take to find correct values in detail.
So each FTG has an ID# and Desc. The ID is in text format and each ID is seperated with a comma, no space. Description also. Date is seperate by a space and comma. Stores change footages a lot so I want to find out which footage is effective today (1/27/2014) For ex: Store 63 would have the 5ft effective right now because we are between 5/23/08 and 5/22/14. So each ID# and Ftg Description is in the same order as Date.
When I have hundreds of stores, it is difficult to go through and get each one (even with the way I've been doing it.) But my ultimate goal is to create a macro to put only the current footage ID#, Desc, and Date in Columns C,D,and E. I'm not sure even where to start with doing a macro.
I usually just find the store with most ftgs, count them, insert that many rows after ID and Description columns, then do a text to columns (comma, delimited), sort by 2nd date column (so they all come up top) and then manually go through them and delete unneeded columns once I have them all.
The code below works correctly on certain sheets. The code is supposed to loop through worksheets in an array, calculate the percent change from 1990 to 2012 and from 2005 to 2012, and put the calculations on the 4th and 5th row from the last non-empty row, respectively. All the sheets are identical except for 3. The sheets that are different only have a different number of years of data. For some reason this causes the macro to put the percent change calculations in random rows below the correct location. Also, the macro doesn't work correctly on one of the identical sheets.
[Code] ......
Example of how the macro runs correctly on an identical worksheet : correct.gif
Example of how the macro runs incorrectly on one of the 3 sheets that are not identical : incorrect.gif
