How does one pick up the data contained in a worksheet cell (say B5) and use it in the vba code of a custom function without passing the cell as an arguement
View 3 RepliesThis time ive created a visual basic form that asks for a row number and when you put a row number in this form it deletes what is in column A, B and C of that row and shift the rows up so that there are no gaps between the data in the rows.
I need to know what code i would have on the 'delete' button of the form.
If you want me to describe it any further i would be happy to, just ask.
I have written a custom function to be called from worksheet cells. The function is basically a wrapper function for VLOOKUP on a single table. It makes cell formulas shorter, easier to read and self-documenting. Here's a much simplified version of the function:
View 3 Replies View RelatedSo far I have 2 Functions with a sub to test to do certain calculations.
VB:
[B]Private Sub test_getBaristaSalary()
Dim InputYear As Integer
InputYear = InputBox(Prompt:="How many years of Experience?")
[Code].....
I have this formula:
=IF(C38="C"*F38>=0,"1","")
which is meant to return the number 1 in a cell if C38 has the letter C in it and F38 is equal or greater than 0. I have met both criteria and it returns an error of #VALUE! What am i doing wrong? NB in F38 there is a Vlookup formula that returns a number (in this case it is 0).
I have a number of different formulae I want to invoke according to a choice. I am able to request the formula I want by entering them ‘in line’ as value1, value2 etc into the CHOOSE formula … e.g. =CHOOSE($H$1,IF(N3>O3,1,""),IF(N3<O3,1,""),IF(N3=O 3,1,””))
but this is cumbersome and confusing since I have a growing number of choices and some of the formulae are quite long. I want to enter them in a list and extract the formula which corresponds with the Index_num in the CHOOSE formula. I also need the cells in the formulae to be relative so this rules out using NAMES to refer to the formulae.
I'm trying to use VBA to write from one workbook to another. I'm trying to populate other workbooks from a single 'control' workbook with a template.
I've managed to figure out how to open up Excel workbooks within a directory (this needs to be done multiple times) supplied by the user, then retrieve information from it to be collated, but I can't for the life of me figure out how to write to the file that I've opened.
I've got the code below, and it's very scrappy as I've been trying lots of different things but nothing's worked. I'll try and highlight as best as I can where I'm having difficulties, as I get the "object not defined" or some such error.
My first version is this:
Code:
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection(1).XValues = "=DATA!$A$8:$A$19
Works without problems, but I really don't want to use "ActiveSheet" there
I changed it to:
Code:
Dim chtTR As ChartObject
Set chtTR = shtData.ChartObjects("Chart 1")
chtTR.SeriesCollection(1).XValues = "=DATA!$A$8:$A$19"
and it gives me
"Object doesn't support this property or method."
I am trying to use the LinEst function in a program. There are some good examples on the web but they all refer to a range in the format “A1:A45”. Is there a way to refer to a column in an existing array instead?
For example (thanks to Shg for this code): ....
If I have a button on each of numerous sheets, how do I get all of the buttons to refer to the same bit of code, without having to copy it to the click event of each?
View 4 Replies View RelatedI have the following formula that I want to copy to a worksheet named Tankersley:
=IF('Tankersley Input'!B5=0,"",'Tankersley Input'!B5)
I would like the formula to read the worksheet name rather than entering the word Tankersley directly, so that if I copy it to another worksheet named Tyson, the formula would read:
=IF('Tyson Input'!B5=0,"",'Tyson Input'!B5)
How would this formula need to read?
I am trying to insert an If statement using the
With Sheets("Sheet2").Select
Range("c" & rBegRow).Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=IF(Sheet1!R[-8]C[4]<>0,Sheet1!R[-8]C[4]*-1,Sheet1!R[-8]C[5]*-1)"
Range("C" & rBegRow).Select
Selection.Copy
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.Paste
End With
The reason is that once the formula is inserted in to the cell in worksheet 2 then I drag it down kind of like a copy paste so it changes automatically the if statement cell reference to worksheet 1 automatically. the trouble I am having is that I do not want to use the R[#]C[#] since when the contents of the information changes the formula does not work properly. If i substitute them for G9 instead of the R[-8]C[4] and H9 R[-8]C[5] but it inputs it in the actual excel worksheet as =IF(Sheet1!'G9<>0,Sheet1!'G9*-1,Sheet1!'H9*-1) therefore creating a value error.
Basically i want to use a UDF to count cells in a range of a certain colour.
But i want to make it easy for the user to be able to change the colour the function counts, so i thought i could ask them to colour the cell in which the function is written.
Is there a way to tell the function to pick up the Interior.ColorIndex of the cell it is written in?
Use Application.caller.interior.colorindex
I’m trying to write a custom function that always references the cell above it but I can’t figure out the proper syntax to do so i.e.
Function Multiple_Cell_Above()
Multiple_Cell_Above = cellabove * 10
End function
The below is a data sheet which is going to be designed to keep the records of "sending and receiving" details of wash-garments from a garment factory.
Descriptions;
01. Starting from B12, the dates of sending and receiving are entered in the sequence they occur. Both sending and receiving can occur on the same date.
02. Starting from C12, there are reference codes related to the activity, if Sending the letter is entered as "S", and if receiving it is entered as "R". I included this for the calculation or sorting purpose.
03. Columns D to J includes the break down of sizes of the quantities sent or received.
04. column K simply calculates the totals of the columns from D to J.
Requirement : I need to have the totals of each size wise quantities sent and received in the "summary table" as follows;
>> Size wise totals of "sent qtys" to be shown in D5 to J5(referring to the code "S")
>> Size wise totals of "received qtys" to be shown in D6 to J6(referring to the code "R")
>> The dates to be updated and displayed"automatically" in the rows of the column B, when the code letters("S" or "R") is entered in the column "C"
Special Remarks : The last date of sending or receiving cannot be predefined, the rows(dates)will be kept adding according to the way sending and receiving may occur.
I'm reading data, from specific cells off a closed workbook. When the sheet that needs said cell data is activated, it automatically opens the workbook and references the sheet nessecary. The issue I've come across, is I now need to access another workbook (Easy to open) with 12 sheets 1 for each month, and only read from the worksheet of the actual Month. Kind of lost on how to possibly make this work. I basically need something like: =location/[workbook.xls]worksheet!cell - where worksheet = B12 and B12 =month(today()) and is formatted to MMMM
View 4 Replies View RelatedI'm trying to create a user-defined function, but the function needs to know what cell it has been placed in. I want to use this cell reference to get some other offset values. I can easily find out offset values based on the ActiveCell, but not for those offset values based on where the UDF is actually placed, which could be almost anywhere on the worksheet.
View 5 Replies View RelatedI understand the specification of a Function procedure does not provide for it to change the contents of cells. However, I have the need/preference to be able to change cell data by using a custom function (instead of a Sub) because of its ease of use and execution of multiple what-if scenarios - displayed jointly.
Is there a way around this specification/limitation - any way? Are there different techniques (standard or non-standard) to achieve thisr goal?
I saw there were codes to sum or count cells that have a specified Fill Color
http://www.ozgrid.com/VBA/sum-count-cells-by-color.htm
However it says the changing of a cells fill color will not cause the Custom Function to recalculate, I really need to recalculate the data if there are any changes in the selected range
I am trying to use a dynamic range in VBA. But I am getting compile error.
Range("D19:G19").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("D19").Offset(0, 0, _
Application.WorksheetFunction. CountA( activesheet.name!Master)) , 3)
In the above code Master is a dyanmic range. I need to autofill the copied data in the 3 coloumns.
How would you write a custom function that you could plug in a cell that would do the following.
If the value in the cell is greater than 0, traverse up that column to clear the values until it reaches the cell in that column that has been colored orange.
I have a FOR loop which is supposed to loop through all the worksheets in my workbook, create a named range, then add some data to the right of the range. However, when I run my code, all the named ranges refer to the last worksheet in my workbook. The loop only seems to be partly working, and I just can't work out why.
Sub maxLifData()
' for each worksheet in the workbook, do dmax formulae and add results to new sheet. bhole id on left, depth across top.
Dim ws As Worksheet
Dim wsName() As String
Dim i As Integer
Dim mCount As Integer
Dim nr1 As Range
Dim nr2 As Range
Dim head1 As String
Dim head2 As String
Dim wsRangeName() As String
Dim maxF As String
Dim fRange As Range
Dim rng As Range
Dim mRng As Range
head1 = "STCN_DPTH"
Set fRange = Range("G3")
Set mRng = Range("A2")
Redim wsName(Worksheets.Count)
Redim wsRangeName(Worksheets.Count).............
I have come across this wonderful piece of code that kind of accomplishes what I am after, to a point. Though I would like the output string to be displayed with a few extra characters.
Code:
Function Concat(rng As Range, Optional sep As String = " , ") As String
Dim rngCell As Range
Dim strResult As String
For Each rngCell In rng
If rngCell.Value "" Then
strResult = strResult & sep & rngCell.Value
End If
[code].....
I want the output to be:
["cell1" , "cell2" , "cell3" , "cell4" , "cell5"]
I am trying to write a function in excel to use the worksheet function "small" In the vba immediate window it doesn't return anything and in the spreadshhet I get a # NAME? error when called in a cell the code is below.
Sub UseFunction()
Dim myRange As Range
Dim answer As Integer
Set myRange = Worksheets("Sheet1").Range("A1:F6")
answer = Application.WorksheetFunction.Small((myRange), 1)
MsgBox answer
End Sub
I am performing a lookup using the populare user defined function nlookup, which does not take a lookuparray argument to find lookup values. But I only want to return lookup values that fall into a certain array. To accomplish this, I figured out that I need a way to specify a condition of the type "if cell address of lookup value falls within range"
It should be possible if I find a way to return the cell address of the nlookup value, but as nlookup is not limited by a lookuparray argument, so I was not able to use the address/index/match that come up in similar questions.
I need a better way to refer to an empty cell. I'm using “” but if the cell contain a formula but show empty it affect the conditioning format I'm using this =AND(cell <> "", existing_rule)
View 9 Replies View RelatedCan you set up countif to work to refer to a certain cell instead of a specif name or text.. for instance
HTML Code:Â
countif(a36:b36, "c1"
I'm trying to find the details required to refer to contents of a cell and then treat the value as the formula. IE for Sum(B5:B8), i'd like to have two cells, one with a 5 in it and one with the 8.
I know its possible if i put "b5" as the cell content using indirect - but I'll be using the same cell value to update formulas in different columns so I cant afford to include the B. I assume theres a very simple method along the lines of Sum((B&(Cell(a4)):B&(Cell(a5)))?
"Smith B" is text in C1.
My current formula in C3 is a simple reference to a cell in another workbook:
=+'Z:Time Records2007[Smith B.xls]Daily Time'!$C1352
I would the formula to refer to C1 to get the "Smith B" part.
This would allow me to copy the formula to the right and reference different text as it looks at D1, E1, F1, etc.
To say it another way. I want to know if there's a way to make a formula that would result in looking at the file Smith B.xls by inserting a refernce to C1 in it?
=+'Z:Time Records2007["C1".xls]Daily Time'!$C1352
I am using Excel 2003
Cell B13 contains the row number of the first row of data on the sheet and B14 contains the row number of the end row of data. This is because the number of rows vary over time.
I'm trying to do a COUNTIF to test for duplicates in column R (contains integers) and I want to specify the range to count by referring to the value in B13 and B14 in the formula and so I have the following which returns FALSE for some reason even if the value in R19 appears more than once in the range ...
=COUNTIF(INDEX(R:R,$B$13):INDEX(R:R,$B$14),R19)>1