I have a problem with an array formula. The file is attached for better comprehension.
I've built a multi-criteria lookup based on INDEX and MATCH.
The idea is in Sheet 1, in column F to put 1 (one) if each record of columns A,B,C and D (all combined) match any record in Sheet DATA. If not then 0 (zero).
The array formula works nice for each of the records. However, I get a ZERO in Zinc! If you check DATA, you can see that Zinc is there, from line 221 to 224!
I can't understand why this happens since i've pasted the formula for each of the lines in column F.
See attached sample workbook. Row 37 current has formulas between columns AQ and IP that return a value 2 or 4. Conditional formatting on these cells then gives them an appropriate colour.
Due to the large number of cells involved, I wanted to use a multi-cell array formula instead. I have attempted this on row 50 which should give the same results, but as you will see this is not happening.
I have this code that is to long for the vba window so I read around and found I had to add a space and _ to extend the line.For some reason I get an error in the code when I do that?
However I copied the formula right from the working cell?
I have a worksheet that I need to be able to easily copy and paste the formula/formatting from the row above, but not the Values entered, (if any). I am using columns A to DG.
I just really need to be able to copy all the formula in these cells down - nothing exciting or fancy.
I've tried formatting as a table as per some searches and that was difficult! My column headings changed, and when I inserted a row, two of the formula's wouldn't copy down - plus the shading and borders went wonky in places.
I have selected a range of multiple rows and columns. I can change the alignment, cell color, where border lines appear, and the color of border lines.
However, I can not change the border line style. No matter what style I select, when drawn on the sheet it apppear as a thin solid line. I've tried all the dashed/dotted line options, but they appear to have no effect.
Ultimately, I want to color alternating rows of cells, and (since coloring hides grid lines) I want to add border lines the same color and style as the hidden grid lines.
I've recently created a macro which takes a .csv file and scrubs the data and dresses it up for a report. I've finally got it running without any problems and have added it individually to 5 different user accounts on my network as an add-in, but need a way to share it across a computer network without allowing others to edit the file.
The report is generated multiple times a day by the different users, none of whom are very computer literate (not that I am much better, myself), and the macro over-writes the old file each time. I've read that it's possible to simply create a shortcut to the file that automatically opens the spreadsheet in read-only format, which would be perfect for what I'm trying to do, but I haven't been able to get it to work so far.
On my computer at work (Windows 7) the shortcut simply opens the spreadsheet so that anyone can edit it, instead of as a read-only file. On my computer at home (I'm trying to experiment) (windows 8.1) it won't even let me save the shortcut, saying I need to provide administrator permission & then cutting to a window that says "access denied".
I won't have the pathway for the actual file I'm trying to create a shortcut for until tomorrow when I get back to work, but I've formatted it essentially the same, directing to the excel.exe file, running the switch "/R", and then directing it to the actual file location.
The ultimate goal is to have multiple people be able to view the file without locking out those who need to update it.
Am currently working on a sheet (Attached), which is attempting to take data input in a sheet (PnL tab), and work them into manageable data on the Frontsheet, via the seperate F&O and Equities tabs.
Now, in the Equities section of the frontsheet, my array formula to pull through the data from the Equities sheet is bringing up an erroneous blank line, despite the forumal being told to ignore blanks.
As far as I can tell, this is referencing formulated tabs, and how to stop this from happening.
i am not good in programming.In an outlook i am trying to write a maro.
I am reading lines from a text using readLine(), how i can store each line into an array using vbscript. I write the code as follows,
While Not F.AtEndOfStream s = F.readline Start = InStr(s, "@") If (Start > 0) Then - Here i need an array, when start>0 , store that line into an array
I have a macro creating an array, populating it, and using the array to fill in values. The whole array works except for one entry. The one field, if changed to have a space, works perfectly.
EG:
Not working with macro: Redlife
Working with macro: Red life
Other entries that are similar (eg: redwork) work fine without having to create the space.
The second worksheet is pulling information off of a website, and the entry is downloaded as redlife. I could write in a section to replace "redlife" with "red life", but it's a bit more complicated than what I know how to deal with. The array is completing the values on the first worksheet, where the array is originally populated.
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.
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,
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).
I am trying to create a single cell formula that returns the min value of an array returned from a vlookup function. This is part of a more complex solution that I am trying to implement, but I think I have narrowed my problem down to this issue, so I have created a very simple example to demonstrate.
I thought I had been able to use array to have more than two criterias with Autofilter, but now I am unable to make the following code work. What I am trying to do is have all the records that does not contain either N/A, S/O or xx and also have a filter on column 125 for the value "OUI".
VB: Sub test() Set ws1 = ThisWorkbook.Sheets("SOMMAIRE_EN_ALL") Set ws2 = Workbooks("Fichier_central_2013_anglais_2_CLEAN").Sheets("DETAIL_CONCAT") Set r = ws2.Range("A1:du4783")
Say I have 3 columns of data: A1:C10 and I want to run a Match() function on them all together to see if I get a match any one those cells, say the value of have in X1.
Since, Match only allows a One-Column lookup array.. is there a way to "concatenate" or "append" the 3 columns together within a formula so now I would be looking to Match in an array that is 1 column * 30 rows?
Basically want to convert =Match(X1,A1:C10,0) to =Match(X1,A1:A30,0) without moving around the raw data in the sheet.
And I want to avoid doing an AND or OR formula that uses 3 separate MATCH() for each column.
I have a hunch that the MMULT or MMULT/TRANSPOSE functions are involved, but can't seem to get it right.
i need to replicate what i did using array formulas with VBA macro (array variable). to make things clear and simple i created an example for illustration only. look at it & u will find what i did & what i need to do ,much of it in writing so that i accurately describe my problem. attached is my example
I'm looking for a formula that can sum line 3 to X date. The dates are in row 1 starting in column A. So if i put in cell Z3 the date of 2009.Aug then I need it to sum from A3 to H3.
1. I use a number of x,y pairs 2. From x,y pairs I produce "scatter" type chart 3. From the chart I get trend line and its equation e.g. 5-order polinominal 4. I convert equation into excel formula 5. I apply formula onto the same range of x argument and produce graph 6. The graph is very much different from the trend line!!! I would expect exact match as I use equation/formula for the trend line.
I have a two different formulas the return a numbered result(PO Number) in the same column. I then vlookup both of them with the same formula into a pivot table, one returns the result one doesn't. format appears to be the same.
I have odds line numbers on this excel sheet which is the VL column. I'd like to be able to scratch a horse, (delete a horse from the sheet) and the odds line (VL column) will update to the new value after the horses is scratched from this sheet.
Now I do this in a program that uses VBA and transports this info to Access, where then I export to Excel to this sheet.
I added a couple of columns in the excel sheet to occomadate this formula which should work if coded into excel properly. Notice the (50), (51), (53) numbers.
(50) = TC in this excel sheet (51) = 2V in this excel sheet (53) = TCa in this excel sheet VL column is where this formula updates to.
I have one other minor problem with something else, but so not to confuse, I'll mention it later.
I'm trying to create some graphs with broken lines from lookup formulae, essentially what is explained at http://www.ozgrid.com/Excel/broken-line.htm. My problem, however, is that my charts are rather more complicated than those outlined, having multiple series that move across the page and overlap a lot of the time. This prevents the suggestion there from being an elegant or even sufficient solution. Does anyone know of a better way of doing this that might work for my situation?
I have a problem with the code below. The code pastes values into column c, depending on values in Column A.
The problem is that, at the end I want it to go to column d and execute the R1C1 formula, but the lastrow1 variable is always set to 1 ( I went through the code pressing F8) so the formula won't go down the column.
Code: If Range("A" & i).Value = 2 Then Worksheets("deacc").Activate Call SelectRndRow Worksheets("sheet1").Activate Range("c1").Select Selection.End(xlDown).Select
I'm using the formula '=LEFT(A2,4)+0 to shorten a numbered line of text.Is there a way to adopt that formula to do the same thing to an alphanumeric line of text (i.e. 80A0267AA1 to read 80A0267) or even better substitute a "-" in place of the "A" (i.e. 80A0267AA1 to read 80-0267)? I know this is really picky, but I figure it's worth a shot.
