how do you set a return value for a function in VBA? I've looked at examples of functions and I don't understand.
All I want to do is call a function to see if a workbook is open and have it return true or false. Here is the code I have, but it won't let me use go "Return isOpen" at the end like VB would! The IDE complains.
Function IsWorkBookOpen(ByVal name As String) As Boolean Dim wBook As Workbook Dim isOpen As Boolean
wBook = Workbooks(name)
If wBook Is Nothing Then isOpen = False Else isOpen = True End If
'I can't reutrn the value of is open? Return isOpen
End Function
I've noticed other VBA functions I've looked at don't use "Return"....how does does the function return a value then?
When I am saving my spreadsheet as a text file, I have 1 column with formulas that I would like to not show up in my text file. I have tried hiding the column, and have Googled for awhile now.
Is there an easy way to determine which rows in a spreadsheet are hidden, rather then scrolling through manually and trying to find nonsequential row numbers?
1 workbook, 2 worksheets (or tabs). On tab 1, I want a formula/alert that tells the user if any duplicate values exist in Column A of tab 2
Tab 2, Column A, has Unique ID's (6 digit numeric values)
The user manually inputs the ID's on new rows in Column A
Row 1 is reserved and in use for something else Row 2 is my header, so cell A2 says "ID" Row 3-623 currently contain unique ID's
When the user inputs a new ID into cell A624, then they return to Tab 1, I want my formula/alert on Tab 1 to tell the user that they have duplicates in Column A of tab 2. I know the Conditional Formatting, but if the user copies in 100 new values, they won't necessarily see the highlighted cells. My tab 1 is my "checks and balances" and the last place the user is suppposed to look to ensure that they haven't created any duplicate ID's. If the user sees a warning message that says duplicates exist, then I'll tell them that they need to look at column A (for cells that have been conditionally highlighted).
One issue that I'm running into with the conditional highlighting is that I want cells A3:A1048576 to already have the conditional formatting - this way when the user inserts a value into Cell A624, then A625, etc they conditional formatting is already there. Right now with data in cells A3:A623, cells A624:A1048576 are all highlighted with the Red/Bold Red Font (which is okay I guess), but ideally it would be nice to not count 2+ empty cells as duplicates and I'll have to have my formula on Tab 1 not include the blank cells.
I DO NOT want to use the Remove Duplicates feature of Excel 2010. If I remove them I could be removing data in columns B, C, D, etc that belong to the Unique ID. I just need the user to be told in Tab 1 that they DO have duplicates and I'll train the user how to research this and fix it.
The reason I want to look for duplicates in the entire Column A is because the list of Unique ID's will grow over time.
The following concatenate function was working, then mysteriously stopped. When working it returned the data that I requested, now if I click on any concatenated cell and hit enter I get the function string, see below.
InputData is my tab that is being referenced. All other functions/formulas are working as designed. The data in the referenced cells is still there and I changed from text to number to general, etc but I still get the same result.
i have some how messed up my sheet. I am generating a random number on sheet calculations that is then moved to sheet coach. From here i am moving the header from that clumn and putting the header on the sheet catcher in the box that corresponds with the number in the column from sheet on. What is happening know is i am getting #### in a lot of the columns.
I would like to choose an option (C, Q, T) and get a result in 1 column.
In other words, if I type C in H6, I must get a different result in I6 as opposed to if I choose Q or T in the same column. However if I leave it blank I should have nothing or a 0 in I6.
In the attachment above you will see that I gave a sum for C (A3), Q (A4) and T (A5). The sum for each one is broken down into a percentage rate and a flat rate.
These rates are dependent on the initial amount entered into G6. C, Q, T stand for Cash, Cheques, Transfers. The form of transaction determines the % and flate rate to be charged.
Normally, when we use the =MAX() function, figures / numbers will appear. Is there any formula that can show the row the highest data is in? Let's just say that the figure is 3080 and it is under 'Transportation' row. Generally, when I apply =MAX() fx, 3080 will appear. Is it possible for it to show Transportation by using formula instead?
I have got a multisheets database where both rows and columns may be hidden according to a given criteria. Only blanks rows and columns should be hidden.
Now I need a formula to check if there is no "valuable" data hidden.
Checking test might be: SUBTOTAL(9,reference) = SUBTOTAL(109,reference)
I am trying to make a function that will only show the value of visible cells (so I can have excel calculate a slope/offset of a filtered table. I made the function below, and it seems to work pretty well.
Option Explicit
Function Visible(x) As Variant Application.Volatile If x.Rows.Hidden Then Visible = "" ElseIf x.Columns.Hidden Then Visible = "" Else: Visible = x.Value End If End Function
However, when I try to use it in an array formula instead of with an individual cell (example, {=SUM(Visible(A1:A10))} ), it only evaluates the hidden property on the first cell. So if the first cell is hidden, all cells will be blank, and if the first cell is not hidden, all values are summed, regardless if some of the later cells are hidden.
I usually use the IF and IFERROR functions with the SUM functionto filter out values I don't want, and I assumed I could do the same with this custom function.
I have the following formula (taken from the web) in my spreadsheet which let me know if a cell have bold text or not. But when the cell has 'mingled' text, i.e. only partly bold, he gave a #VALUE error.
When I hide columns in a column chart the different fill colors I used on specific columns no longer show up with the colors I originally had. Is there a way to maintain the proper sequence of column fill colors even when some columns are hidden?
I know this is an easy one but I'm unsure why I can't easily modify a formula. The key event in this formula is "+190". Every time I change the value the formula no longer works. I've copied to another cell and the formula no longer works. My question is when you have a formula like this one if you have to modify how do you do it and keep the formula working.
I can't get the zero's to show up in the part number formula in the wooksheet. Also how do you get the zero's to stay when you type numbers such as (03,04,ect.) the zero always disapears. I need it to stay as a number and not text.
why formula's I type in to a particular worksheet show all the time(this is a workbook containing numerous macro's). I have checked under 'Options' that the show formula's box is not checked
I created a formula in a cell and it worked fine but then I created the same formula in the cell just below it and I changed some of the numbers and when I hit the enter button the formula showed up in the cell and not the final result which would be the answer to the formula. Does anyone know why it would do this? I have an example below....
We have one excel file for monitoring of action items generated by the management after the study. As since there were around 3000+ rows has been generated since in the beginning of 1990's till to-date. So I was thinking of instead of getting the result through filter manually, I want to create a formula that will count of how many has been closed this year and this month out of the total numbers of action items.
Is it possible to use the COUNTIF function formula to count the number of items in column A, and date of column B, and closed in column C.
In below, we can see that there were 4 items under Revalidation has been closed this month and the total number of closed this year is 6.
Is there a way to create a shortcut/button by hiding or showing a column? Example: I want to hide or show the whole column E by pressing a assign button or shortcut.
On sheet 1 I have a list of 1000 firstnames On sheet 2 I have a list of 1000 emails,
I need a function that states If a cell in the email column contains a string or value from the names column, it will result in a true statement so that I can separate out the emails that have these peoples first names.
I have a spreadsheet (attached) that has a formula, which calculates the number of days between two dates if another field contains data. My problem is that when I chart the data, it isn't showing up in the chart. Cell G1 (Tab 1) contains a 1 because the document was returned 1 day late. This isn't showing up on the Chart (Tab 2).
I have the formula (found in cell "C2") on the Report sheet. I need to perform a function, but I cannot get it to work on the sheet I need to pull information from. The sheet RecapWk12 has a small section pasted (with some cells edited for obvious reasons) from the actual workbook. I can get the formula in Report cell (A10) to work on pulling information from sheet2. You can see I am getting (#REF!) in cell C2.
I'm trying to have a fixed date entered into column B when data is entered into column D. Here's the formula in column B:
=IF(D3="", IF(B3=";", FixedDate(),B3))
When I enter data into cell D, I get the following result in B:
0-Jan-00
Taking this a step further, even if I do a "control + ;" I still get a value of 0-Jan-00 in the cell but the formula bar shows me "=1/9/2007'. ??
I've gone through all of the options but cannot fix this error. I have two spreadsheets using this formula. The first one worked fine last year but now I have this error in it. I also started to create a new sheet and still have the same error.
I have a formula in a cell and when I use the formula bar it shows me the correct answer but it doesn't show me it in the worksheet. Only the formula shows up.
The same formula could be another place in my worksheet and work fine.
I have tried changing format. Copying formula from another location and changing the information to fit my needs it won't show me the result.