Function To Write An Array
Jun 20, 2008
I have the following assignment to do, problem is I'm new to arrays and functions in VBA and nothing is working for me, i get errors no matter what i try, (for example i don't know how to feed the array to the function, and it won't let me write a range to an array)
5.A) Write a function that takes a 2D array of doubles, a single cell range, an optional cap and an optional floor.
B)Write the array on to the sheet with the single cell range parameter being the location of the top left of the array on the sheet. When writing the array to the sheet, for each value in the array where it is below the floor put the floor on the sheet, where the value is above the cap put the cap value on the sheet, otherwise display the original value. The input array should not be modified.
All code should be what you consider production quality.
Dim topleft As Range
array1 = Range("B8:C18").Value
'array1(1, 1) = 2
'array1(2, 1) = 3
'array1(1, 2) = 6
'array1(1, 3) = 9
'array1(2, 2) = 4
'array1(2, 3) = 5
View 9 Replies
ADVERTISEMENT
Dec 2, 2008
Is there a more convenient way to write something like this:
View 2 Replies
View Related
Dec 25, 2009
i write array like this: Array(1,2,3,4,5,6,7 until 100) how to make it simple, not to write number until 100
View 9 Replies
View Related
Aug 8, 2006
I have 4000 rows of data with numbers in it (only one column...column C). These numbers are consecutive but these have breaks in between. So I am trying to find out where those breaks are and output those points in a list. So here is a macro that I came up with...but these array doesn't seem to write out results.... can anybody help please?
This macro tries to compare the two adjacent cells and if the difference between the values is greater than 1 then it copies that value in an array and writes that value in a cell in the same sheet.
Sub Macro1()
Dim temp(1, 1000) As Integer
i = 0
previouscell = 0
For Each cell In Range("C:C")
currentcell = cell.Value
abc = previouscell + 1
If currentcell <> abc Then
temp(1, i) = currentcell
i = i + 1
Range("D" & i).Value = temp(1, i)
End If
previouscell = cell.Value
Next cell
End Sub
View 10 Replies
View Related
Jul 1, 2014
I've been writing a UserForm in Excel that serves the purpose of a front-end contact/reporting spreadsheet.
I've managed to get a multitude of elements to work together as of yet, however have been having problems writing data to the correct place on the spreadsheet.
What I've already achieved:
The first interactive portion of the userform currently has a TextBox (ArtistAgentAddEmail) - this is the email that we would like inputting on the spreadsheet.
a ComboBox which is populated on 'Userform_Initialize' into an array (ArtistAgentAddDestination) - The range of this array is A1:AC1.
[Code]......
What I would like to do next:
The issue that I've been having is getting the email to be written into the correct column. Once I have typed in my email and chosen my destination from the combobox, I would like to write this email address in the column of the destination chosen.
E.G. The first 5 elements of my ComboBox array are: Neil O'Brien, CAA, CODA, ITB and PRIMARY. If i got an email from somebody in the CAA organisation to add a new contact to the reporting, I would like to be able to add this address via the userform underneath the previously added contact.
The code that I am currently using for my confirm button is:
[Code].....
Attached File : Userform Contact Sheet - 27th June.xlsm
View 2 Replies
View Related
Apr 14, 2008
In VBA, is it possible to write to a text file, on 1 single line (without carriage return or line feed) the values of a dynamic array? For instance, I have the array:
Dim my(n) As Double 'n is changeable
my(0) = 0
my(1) = 1
my(2) = 2
.....
I want to write to a text file, like this: Write #filename, my(0), my(1), my(2) '..... until my(n) but n is changing
I want them on 1 line, because I use the other direction (up - down) for other things).
View 2 Replies
View Related
May 23, 2012
Im using excel 2010 As it's 60 times quicker I was trying to speed up my code and replace all loops by putting the value into an array, and then transfer the array to the worksheet
It seems to be straightforward for math calculations like in this example:
[URL]
But no luck with the one below. I was trying to test it on a simple loop which replaces two types of string into the 3rd one:
Code:
Dim lastrow, lastrow2, i As Long
With Worksheets("KPI5")
lastrow2 = .Range("N" & Rows.Count).End(xlUp).Row
.Range("T7:T" & lastrow2).Value = .Range("F7:F" & lastrow2).Value
For i = 8 To lastrow2
If .Range("T" & i).Value = "Modification" Then
[code]....
View 4 Replies
View Related
May 21, 2008
I am trying to enter only the named ranges in the active worksheet into an Array.
The amount of named ranges can be from 4 to 7 per sheet.
I'm self taught at VB (This posts on this forum have taught me - thank you!) and have got the below code working on a Workbook - but not on a worksheet level.
ActiveSheet.Names.Count returns nothing, changing to ThisWorkBook.Names.Count returns the sum of all .Names in the workbook.
Sub aTest()
Dim sArray() As String
Dim sJoin As String
Dim y As Long
x = 1
y = ActiveSheet.Names.Count
z = ActiveSheet.Name
View 3 Replies
View Related
Sep 16, 2009
I have written a VBA program and using in Ms.Excel. The problem is when I try to write the VBA function through the function wizard it works but when I write the function in excel worksheet cell, it doesnt execute. I do not now the reason.
View 2 Replies
View Related
Jul 26, 2009
I'm wondering if an if then else function can be written for the problem that I'm having? Or is there a better way to accomplish this? I've detailed my problem within the example attached.
View 3 Replies
View Related
Jan 12, 2007
if there is an Excel function out there that will copy over the contents of one cell into another by what is typed in another cell. Did that make sense?
Lets say that I have info in
(A1) John (B1) Smith (C1)X (D1)
Now I want to be able to copy over A1 & B1 to another part of the spreadsheet/workbook if I were to type the words "leave" into D1.
So basically it would look something like this...
(A1)John (B1)Smith (C1)X (D1)leave (want info to go here---->) (M4)John (N4)Smith (O4)currently on leave
And then have it automatically add the names below the one in M4-O4. so it would add names in M5-O5, M6-O6, etc
I have this spread sheet that has over 1000 names on it & it would help out alot if I could just use a function that could copy the information to another part of the spreadsheet or another workbook.
I know that I can use the filter, but I may want to have this stuff on another worksheet alltogether.
Also, if this is possible, is there a way that it can be automatically removed if I were to delete the word "leave" from the cell?
View 8 Replies
View Related
May 19, 2009
I'm trying to make a "weight-based" spreadsheet. I want to show a difference between numbers so if an item lost 5 lbs, there would be a "-5" and it would be in green font showing good.
For example, a box weighs 20 lbs and then drops down to 15 lbs. This would be a good result. So I would have 20 lbs in A4 and 15 lbs in A6 and I want to show the difference in A8. However, since I want the box to lose weight, I want it to show a negative value of "-5" in green font meaning it is good. And vice versa, I want it to show "+3" in red font if the box gained weight.
I can't really figure out how to get the signs working properly. I'm assuming to use an "if" function in where if the difference is a loss, show the negative sign and have it in green font. And if the difference is a gain, show the positive sign in red font.
View 2 Replies
View Related
Aug 6, 2009
how to write a custom made IFERROR function which works on Excel 2003 that mimics as closely as possible the Excel 2007 IFERROR function without using any array formula.
View 2 Replies
View Related
Dec 2, 2013
In my spreadsheet, one user defined function i.e. pfizer is there which I want to convert into macro and use it in column H of sheet2 [sheets("sheet2").columns("H:H").select]
What should be the VBA code for this.
when I have started the macro with keyword as application. . the function is not populated.
-->I am able to populate the data with the formula like this = pfizer("I1")
View 1 Replies
View Related
Feb 16, 2010
Ihave this exel sheet i nedd function to write the starting date and time immediatly after i enter the name in the last colume ineed from exel to write the date and time emmideatly if the case is (done, cancelled ,or rejecteted)if the case is (select status )i want the cell empty but if the case is (pending)iwant to the program to calculte the deffirence between the ClosingDatetimee(which written by the program)and the delivering date(which entered manually by user)
View 9 Replies
View Related
Sep 25, 2008
ColumnA Column B ColumnC (Yes/No)
2121 345766
23423 6456546
4234 6456456
7567 64566456
76756 654645
67567 3344534
76575 34534534
756756 45345
7657 534545
756767 4534534
756756 435345
Hello,
What I am trying to do is
Search the number written at ColumnB in ColumnA and if found write YES to column C
if not write No
View 9 Replies
View Related
Oct 29, 2012
I'm trying to do a Vlookup on a file that gets automatically downloaded to the computer from a website. The data is in lots of different data sets, like so:
Loans to countries
Mar
Apr
May
Jun
Loans to banks
Mar
Apr
May
Jun
Every month a new row of data gets added to each table, meaning the start and end cells of the array also shift each time.
View 4 Replies
View Related
Jun 7, 2006
I'm trying to have a macro write down an array formula, but when I hit ctrl+shift+enter, the recorder says it can't record. If I write in the macro ...FormulaR1C1 = {=...} then I get the formula as a text. Is there a way to tell the macro that a formula should be entered as an array formula?
View 2 Replies
View Related
Aug 8, 2012
I use a existing code and want to add the Concatenate in the code.
VB:
Sub Subtotal()
Dim myAreas As Areas, myArea As Range, x As String
Dim y As Long
Worksheets("INVOICE_hulp").Activate
Range("Q1").Select
[Code] ....
The array sum function works but Concatenate not. In Excel I get the formula Concatenate(H2:H3) in stead of Concatenate(H2,H3). What should I change to get the code working?
View 9 Replies
View Related
Sep 3, 2012
I want to create a function in a module that will read an array from sheet1 preform an operation on it then return the result to the spreadsheet in a defined amount of cells in this example 8.
In Module1:
VB:
Function func1(ByRef arrayA())
Dim arrayB(8)
For k = 0 To 7
[Code].....
How does the resultant arrayB get populated onto the spreadsheet?
View 5 Replies
View Related
May 2, 2014
I want to use the countif function for a certain array. The range is set by another cell which is made up out of a percentile of an entire row.
The problem is is that excel doesn't see the value it displays so i continuously get a value of 0 in the countif cell. If i fill in the range by hand, which is exactly the same range as the outcome of the percentile the countif cell does give the correct value.
So in short the problem is i guess that the countif cell does not recognize the value because this value is made up by a formula.
View 8 Replies
View Related
Jan 24, 2007
I am working on a Monthly Vehicle Spread Sheet. One of the outputs I am trying to achieve is an automatic calculation of Mile Per Gallon. To do this, I need to know if there is a function that will return the value of the first entry of a group of cells.
In calculating the miles per gallon, I need to subtract the first gallon amount entry of the total gallons in the month, then divide that number into the difference of the mileage in the month recorded when the vehicles fueled up.
View 11 Replies
View Related
Oct 15, 2008
Is there anyway I can do the below formula without it being an array formula?
View 2 Replies
View Related
Feb 21, 2014
I am trying to write a code for simulating a stock price using geometric brownian motion. I have the function part down as follows:
Public Function SimStock(Initial_Stock_Price As Double, _
Expected_Return As Double, _
Volitility As Double, _
End_Time_Days As Double, _
Number_of_Steps) As Double
SimStock = Initial_Stock_Price * Exp((Expected_Return - Volitility ^ 2 / 2) * (Number_of_Steps / End_Time_Days) * NRnd2() * Sqr((Number_of_Steps / End_Time_Days)))
End Function
Now what i need to do is to apply this to an array. so that when i run the sub it will simulate prices from today up to some number of days that i specify. This number of days will be equal to "End_Time_Days" which is part of my function. Furthermore the simulation has to be such that it uses the above formula for the first entry but then replaces "Initial_Stock_Price" with the result of the preceding entry in each subsequent entry.
View 1 Replies
View Related
Oct 11, 2005
Is it possible to submit the MEDIAN() function as an array (ctrl + shift + enter)?
I.e., I would like to submit a function similar to this SUM() function:
{=SUM(IF(("a"=$A$1:$A$7),$B$1:$B$7,))}
However, when I submit
{=MEDIAN(IF(("a"=$A$1:$A$7),$B$1:$B$7,))}
it does not give me the intended result.
View 6 Replies
View Related
Feb 27, 2012
For some reason my array called "PriceSum" is not getting load with values or not being summed. I am not sure if it is because I am trying to load a value with a decimal in it or not.
For logic purposes:
lastrow = 2
Stock = Banking
Cell "W2" = 5
Cell "X2" = 71.84
Cell "U2" = Bought
Cell "V2" = Banking
Code:
Private Sub Purchase_Click()Dim MyArray As Variant
Dim x As Integer
Dim t As Integer
Dim lastrow As Integer
Dim QuantityArray(0 To 1000) As Variant,
[Code] ...........
View 9 Replies
View Related
Jan 9, 2014
I have a big macro that runs whenever there is a change made. I'm not gonna link it all because I don't think that would be useful... This macro does:
1. Disables Events.
2. Removes Protection
3.Calls some macros that check a few values and change a few cells.
4. Activates events.
5. Hides or shows ranges of cells depending on the results of the called macros.
6. Reactivates protection.
As well as the cells the called macro changes, there is a column of True/False results stretching from AE16 to AE491 with several blanks in the list as well. (All of this is decided upon a different macro that isn't linked with the change.) I want a loop that will read the True/False column and if it finds a false, make 8 buttons invisible.
I tried putting this code between Step 5 and 6 of the macro.
Code:
Dim J As Integer
J = 16
Do
[Code].....
This isn't working at all... I was thinking if there was gonna be an issue then it would be the opposite where it stops the buttons appearing all together but even when there are falses and I am changing things it doesn't seem to make a difference.
View 3 Replies
View Related
Jun 8, 2007
Column C contains the names of real-estate brokers.
Column E contains the colors of the homes for sale.
Column Q contains the values of the homes.
I want to return the sum of the 50 most expensive homes that are yellow and for sale by Century 21.
I'm familiar with array formulas, and with the large function. I just do not know how to incorporate the large function within the sum/array formula.
View 9 Replies
View Related
Oct 10, 2008
I'm just getting into functions, so please bear with me on this as I start to understand how they really work. I do have the VBA and Macros for MS Excel book which has been a great help!
Normally on this I would use a simple array formula to gather a total calculation from a timesheet, but in this case there can be numerous timesheets to add this up.
Hours would be recorded on a sheet with both a client and a task selected. I also have a summary sheet to show clients against tasks... adding all the hours from each timesheet.
If it were from a single Timesheet (possibly creating a single one of these for all sheets is an option?) I would be able to use the following array formula:
=SUM(IF((Timesheet1!$F$8:$F$22=C$5)*(Timesheet1!$G$8:$G$22=$A9),Timesheet1!$D$8:$D$22))
Timesheet1 Column D = hours
Timesheet1 Column F = clients
Timesheet1 Column G = tasks
Row 5 (starting Column C) = clients in summary sheet (across top)
Column A (starting Row 9) = tasks in summary sheet (down side)...........
View 9 Replies
View Related
Feb 27, 2007
I am trying to write a public function that fills a table for a 2-dimensional array and am having trouble with my named ranges. The x-axis is based on years (range F2:O2) and the y-axis is a q_factor (range E3:E23) so the data range would be (F3:O23. The following outlines my logic:
Public Function bondValue(years As Range, q_factor As Range, z As Double)
Dim nRow As Long, nCol As Long
Dim bondPort As Range
nRow = q_factor.Rows.Count
nCol = years.Columns.Count
sumTau = 0
View 6 Replies
View Related