Fill Range From Multi-dimensional Array
I searched and found a few posts about transposing arrays into a range of cells, but none of them seemed to solve my problem. So, my problem is, I have a .Net assembly which provides various functions to allow Excel to access our Oracle DB stored procedures/tables, etc. This assembly is exposed via COM Interop. I call the GetSPINTypes() method, which returns me a list of type pairs (ID, Name), in a CSV string format.
I split the CSV into rows, and then put each row into a 2-dimensional array.
I then need to dump that array into one of my sheets in Excel, so I try to do the usual Range.Value = Array, but this sometimes tells me there is a type mismatch, and most times just doesn't fill the range. I've checked my arrays in the watch window, and they have definitely been filled in correctly, the values just don't appear when they are put into the sheet. See the code I'm using below:......
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Fill & Rearrange Multi-dimensional Array From Another
I've got the folowing array's date1>company1>price >Company2>price >company3>price >enz Date2>company1>price >company2>price >enz enz. But these are not the array's that i need for a correlation that i want to make. Is it possible to transform the array's above to an array such as: Company>date1>price >date2>price >date3>price >enz
View Replies!
View Related
Summing In A Multi Dimensional Array
I've created an array that is 60 pairs of cells wide. Each row in the array represents a different account. The 60 pairs of cells contain up to 60 payments in a payment stream. The first cell of each pair may contain an amount of interest to be paid, or zero if the time band in which it was/will be paid is not within the time range that I'm evaluating. The second cell of each pair may contain a number between 1 and 10 representing the time band in which the interest will be paid, or zero if the payment date falls outside of the time bands. I need to be able to sum the interest to be paid in each time band for all of the accounts in my database. Is there an Excel function that I could use or will I need to write some VBA code to loop through each row?
View Replies!
View Related
Multi-dimensional Array Solution
I am currently using a macro which highlights keywords and associated page views from an array in my website stats spreadsheet. Now I need to take it to the next level: I want to use a multi-dimensional array to pull out any keywords that are duplicated, and display them at the top of my spreadsheet with the totaled page views next to them in a different color (red). Here is my existing Keyword highlighting Sub Highlight_Keywords() Dim vntWords As Variant Dim lngIndex As Long Dim rngFind As Range Dim strFirstAddress As String Dim lngPos As Long
View Replies!
View Related
Populating A Multi-dimensional Array
I have a spreadsheet which I will be adding to over time. I need a macro that will look for the last 10 rows and then pull together a summary table based on data from several of the columns on each of those rows. I've tried to go about this by using an array. The array will always be the same size (it will always be 10 by 11, i.e. EngineArray(10,11)-basically the same size as my summary table). I've also set Option Base 1. What I’ve got so far is below. It isn’t working properly though. To start with I was getting ‘type mismatch errors’. Now instead of reading the numbers from the selected cells into the array, it just changes all the cells I’m trying to read from to ‘TRUE’?
View Replies!
View Related
Evaluate Multi-Dimensional Array Matrix
I'm trying to use VBA to do some matrix processing. I have successfully done matrix processing in the spreadsheet, but I'm looking to port the logic into VBA to more easily reuse central formulas and reduce the amount of code in the spreadsheet. The function shown below works. I had to hack around the variable declarations to eliminate processing/compile errors but it seems to function (no pun intended). While debugging I noticed that this function would seem to get called 4 times. I do have other modules running, although I don't think any other modules are affecting this. After some input from shg, I updated the variable declarations, but I'm getting an "Overflow" error reported. Sometimes (not always) a divide by zero also pops up. I do have the Msgbox for errors at the end, I haven't tried removing that part to see if the error reporting vanishes. I'm not sure 'hiding' error alerts is the best thing to do anyway...
View Replies!
View Related
Multi-dimensional Array Not Showing In Listbox
I have made the function below to return a variant multidimensional array. I pass the function an array of folder paths that I wish to search through looking at subfolders within that path where their name matches a search string that I pass to the function. eg., it will find a folder named "Catnap" if you pass the string searching for as "Cat*". The size of the array it builds depends on how many folders it finds that match the search string and so needs to be built dynamically. Hence, I believe it builds a 2 dimensional array horizontally and I transpose it at the end of the function. In each element I put the folder name that was found in the first dimension and the path to that folder in its second dimension. I have a 2 column listbox on a form that I set this array to. eg., Me.ListBox1.List = DirPaths("C:","Cat*",vbDirectory,100) This works fine and shows a list of folder items found by folder and path in the two columns of the listbox if there is greater than 1 search found. However, when the search only finds 1 then the listbox shows the folder name with the folder path in row 2 of the list box. (See below).....
View Replies!
View Related
Join Dynamic Multi-dimensional Array
the built in "Join" function can join all elements of a 1-dimensional array into a string with delimiter. Now, how do I do that with multi-dimensional array if I just want to join 1 dimension of it. For instance, I have: m = 10 n = 20 Redim my(m, n) 'assign values to array here... 'I want to join, say, my(5, 0 to n) only 'How to do that without declaring a new 1 dimension array? Also, I want to write a join sub/function to do the above for n-dimensional array, do I need to write each one for every number of dimension (1 sub for 2-dimension, 1 sub for 3 dimension, etc.)?
View Replies!
View Related
Fill ComboBox With 2 Dimensional Array
I can set up a 2 dimensional array by using array = Workbooks("Workbook.xls").Worksheets("Data"). Range("D_all") as range D-all contains 2 columns and 20 rows I can send that list to a combo box by using Me.ComboBox1.List() = array If I have ColumnCount set to 2 then both columns will be listed If I have ColumnCount set to 1 then the 1st part of the array will be listed But how do I list just the 2nd part of the array
View Replies!
View Related
Creating A 2-dimensional Array From A 1-dimensional List
I've been a lurker on this forum for a long while and it's always been able to provide me with lots of excel tips, and for that I am grateful! But this time I have a question that I can't find the answer to here, or anywhere else on the web after a few hours of looking. As a note, I'm not very experienced with Excel, probably somewhere between novice and intermediate. What I need to create is a 2-dimensional array of data. The vertical (y-axis) are the tools, and the horizontal (x-axis) are the jobs. Where the y-axis and the x-axis intersect, will be the quantity of tools needed for that specific job. There are almost 1500 tools, and 100 jobs.
View Replies!
View Related
Checking If Variable Is Multi-dimensional
Is there a quick way to check if a variable is an array or multi-dimensional? I am trying to send variables to a sub to print to excel but I am sending mixed variables - some are one dimension, some are two dimensional. Here is what I am using: Private Sub Send(Item As Variant, Top As Integer, ToSheet As String, Row As Integer, Column As Integer) 'Sends values to Excel ' Loop counter Dim i As Integer For i = 0 To Top - 1 ActiveWorkbook. Sheets(ToSheet). Cells(i + Row, Column).Value = Item(i) Next End Sub I suppose I could set up a second routine to handle one dimensional variables, but I thought I would ask the pros -
View Replies!
View Related
Lookup Values From A Multi Dimensional Table / Data Sheet
I have a sheet that has to look up value on a report from a sheet sheet that has more than value. Ex) On the report I have values 123A and 1234A On the data tab I have table that has. Ref Tot Value Desc Value1 Value2 Value3 1 123A Widgets 1 2 3 2 123A Widgets 4 5 6 Ref Tot Value Desc Value1 Value2 Value3 1 123B Nuts 7 8 9 5 123B Nuts 1 3 5 Ref Tot Value Desc Value1 Value2 Value3 7 1234A Bolts 2 4 6 11 1234A Bolts 3 5 8 So the report need find the values for Value 1, 2, 3 on where the value matches the data tab.
View Replies!
View Related
Fill Multi Columned ListBox Based On Range Meeting 2 Conditions
I'm trying to populate a dropdown Combo Box by using conditions. In the example I attached...I would be trying to use ComboBox1.ListFillRange to populate the ComboBox only with players who have "C" in the Position column and "ANA" in the Team Code Column. Which would give me all the Catchers on the Angels. I've been trying to use worksheetfuntion.Index.
View Replies!
View Related
Interpolate Two-dimensional Array
With a known X and Y, trying to solve for Z from a table. ie: IF a variable X is defined across a row, another variable Y defined down a column, the data field Z fills in-between. What do I need to use to interpolate for both X and Y to solve for Z?
View Replies!
View Related
Function For 2-dimensional Array
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 Replies!
View Related
Fill Array Variable From Range
I've created a userform that uses parallel arrays to display strings that another macro pulls from a worksheet and the 2nd array holds strings of what the user types in. I've uploaded a workbook with the userform in it. Part 1: I want to pass the 1st array (pSource) in during the initialization of the array and I want to send back the 2nd array (pUser) from the submit button, but I have no idea how to work it. I've tried a couple things from older posts in the forums, but had no luck with them. Part 2: The arrays will be the same size, but the size isn't a constant. Is there any way to change the size of an array? I've been using 0 to 3 for testing the rest of the userform, but for the final product, I'll be passing an array in of a variable size. Private pSource(3) As String 'Modify to work with variable size Private pUser(3) As String 'Modify to work with variable size
View Replies!
View Related
Data Type Of Coordinates Of 2-dimensional Array
I have a big array "DataArray" and want to access it: For i = 1 To 4 variable = DataArray(SourceArray(i)) Next i "DataArray" has two dimensions, so SourceArray has to consist of data like this: SourceArray(1) = 1,2 sourceArray(2) = 2,4 etc What data type does Sourcearray have to be? Integer doesnt seem to work, and DataArray doesnt like a string as coordinates. I have a workaround with two different arrays of integer for x and y coordinates, but this cannot be it.
View Replies!
View Related
Fill Multi Column Listboxes With Uniques
I am designing a userform that involves a listbox that I am having trouble with. Please note I have little experience with vba, so I don't understand most codes. I want to make a mulitcolumn listbox, populated with the attached pivot table. I really do not know where to start, and there is no "populating multicolumn listboxes for beginners" instructions anywhere on google or in here. At least, none that I understand. What I am trying to create is a 2-column listbox, where in the first column, the "code" fields are shown, and in the second column, the corresponding " Name" fields are shown. In the pivot table, you can see how several routes fall under a service, and several services fall under a pc, etc.
View Replies!
View Related
Bi-dimensional Dynamic Range
I've tried the below formula to get a bi-dimensional dynamic range that is defined by the last no blank column and last no blank row, too. =A1:INDEX(A1:D18,MAX(NOT(ISBLANK($A$1:$D$18))*ROW($A$1:$D$18)),MAX(NOT(ISBLANK($A$1:$D$18))*COLUMN($A$1:$D$18))) Does any one have a different approach to get this without using the offset function?
View Replies!
View Related
2-dimensional Date Range Criteria
Attached is a really simplified version of what I'm trying to accomplish. I'll attempt to get my brain working well enough to explain it: For each row, I have a person's name, their department, their site, and then a series of dates (representing dates of attendence). The four columns after the dates ("Counts" is the first) are me trying to keep all the right numbers in mind, and are the routes I've been trying to take on this. I have also tried to make a custom formula called ClassInRange, which isn't playing nicely. What I eventually need to populate is the little 2x3 section at the bottom where the three sites and the two departments I'm working with (down from 5 sites and 8 departments for the sake of sanity). What I need to be able to do is populate those cells with something that will tell me how many people from which site and department have attended something within the date range. So, for example, I need to know that the 2 guys in Wellesley attended something between January 1st and December 31st of 2007. (American date style, in case anyone's lost) If you're able to help, please keep in mind that I'm not the end-user; I'm just the slightly crazy dude trying to create this thing and make it simple to use. Eventually, the Dept/Site box will be part of a template which is copied and pasted up to four times on a report sheet, each fed by different date-range criteria. Also, the important information is the person's name, dept and site, which I hope gives some reason behind the organization; we want to find the person easily.
View Replies!
View Related
Fill Multi Cells In Row If Column Match Both Worksheets
I have an exported Excel worksheet1 from the parts database get every Friday. We get RFQ of parts list each week from multi customers about 3000+ parts as they send in MSword, MSoutlook or MSexcel to me I make into Excel worksheet2. I do not like to type in every part into the parts software I have my words for that software Cough Cough. The exported excel speadsheet tells the part numbers, Location, Qty, Price, ETC I would like to take the Excel RFQ list and have it populated from the other speadsheet SAMPLE A1 PART number that on both spreadsheet A2 QTY on both there request and our stock A3 Price A4 Location So want a compare worksheet1 with worksheet2 A1 = A* the populate A2-A4 with worksheet1 data
View Replies!
View Related
2-dimensional Date Range With Dynamic Copy And Paste
Some of you may have seen my 2-dimensional Date Range Criteria thread . By request, that thread has been closed and I am opening a new one on a related, but infinitely more complex note. The solution to the previous thread worked, but not well with my project. I'll go a little more in depth into how my project works, and try not to be too detailed and irritating. I am trying to create a homebrew Learning Management System for some of the Training courses that my department offers. I've posted here a few times on the same project, and the advice from all previous posts has been excellent. What I have so far allows users to input all of the relevant information about a participant, and then keep track of the dates when they took a particular course. They input this information with a Userform. This data goes into a Participant Master List, which is where the majority of the functions take place. There is a Workings sheet set to xlVeryHidden, which houses some other information, including results for searches, and finally there is the Report Template--the source of my original question. The original question was how to make a formula that would track how many people from each department and site have attended training within a date range. That question was answered in the 2-dimensional Date Range Criteria of the report. What makes it potentially more complicated is that a user might want to generate two or three reports at the same time to compare side-by-side. I'm pretty sure I can make something that will do this, and allow it, but the way it's looking could be really complicated--extreme headache, and a lot of VBA coding.
View Replies!
View Related
Multi-cel Array Formula Not Working
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.
View Replies!
View Related
Entering Multi-cell Array Formulas
I successfully built a multi- cell array that sorts a bunch names into ascending order. But now when I need to use the same array formula again, I get the wrong results in the array formula cells and I can't figure out how I'm entering the array incorrectly. The error I get in each of the 7 cells is the identical number, "1". Here's the deal. Cells B10 - B16 contain names and numbers that I need sorted in ascending order. Then, I highlight cells C10 - C16 and enter the following formula using shift-ctrl-enter: ...
View Replies!
View Related
Multi-result Array Formula With Max
I am using multi-result array formulas to reduce error checking when processing arrays of numbers. However I cannot get this to work with some of the functions as they use Max or other array aware functions and so ruturn the same result for each cell they are enetered into. Example for A1=2,B1=4 =2*A1:B1 array enetered gives 4,8 However =Max(2*A1:B1,5) array enetered gives 8,8 rather than 5,8. I have tried creating my own version of a max function, but am having problems getting it to evaluate array functions.
View Replies!
View Related
Fill Multi Select ListBox With File Paths & Open Selected Files
i'm having a problem with a script I have (script is below). What i'm doing is running a script that displays files in a certain folder, those files are then displayed on sheet1 column O and are linked so when they are clicked upon, the file will open. I also have a combo box with a input range of $O$O. That puts the values in column O in the combo box. Now here is a problem and request. Problem first, when I select the file names from the combo box, they don't open the file, all it does is display the file name in combo box and that's it. how to I get it to open up the file? Now for a request, I have another sheet that is almost exactly as the one i described above. The only thing different I want is when someone clicks on a file name from the combo box, instead of it automatically linking to the file, I want them to click on a button to display the file. Sub HyperlinkXLSFiles() ActiveSheet.Unprotect Dim lCount As Long Application. ScreenUpdating = False Application.DisplayAlerts = False Application.EnableEvents = False On Error Resume Next With Application.FileSearch .NewSearch 'Change path to suit .LookIn = "c:Sec" .FileType = msoFileTypeExcelWorkbooks ' .Filename = " Book*.xls"..................
View Replies!
View Related
Find All Row Values Selected In Multi Column ListBox & Fill Adjacent Cell
I have a listbox that has row and columns. The rows are combinations of options and the columns are Additives. After selecting a row in Listbox1, (first column are names) i would like the value of the second column to goto a specific area.... find that value and in the cell to the right of it place a "Y". The "Y" would indicate that "yes" it was part of the selection row of the listbox. Same for third column, forth, fifth and sixth. The result from the listbox is a number of Y's and N's in the result page. Then making all that were not part of selected line and equal to "N" to hide row (height = to zero). Listbox and expected result are in attached example worksheet. The attached does show the need much better then I can explain it.
View Replies!
View Related
Fill Array By Looping Worksheets
I am trying to write code that will loop through one sheet, fill an array and then use that array to populate cells in another sheet. I have a sheet with group names in column A, then in column B through Column IV there are the members of that group. There may be no members in a group or every cell through Column IV could contain data. I need to loop through each row, one at a time, see if a cell contains data, if it does, put it in the array, if it's empty then the array is done for that row. I then need it to go to another sheet and dump that data, however, now it needs to drop it in five consecutive cells in a row, then drop to the next row for the next five cells, etc. Once this is done it then goes back to the first sheet, drops to the next row nad starts over. I know how to loop through the rows, I just am not sure how to fill an array using variables for rows and columns. i.e., I can't say fill array with b1 to b30 instead I have to say fill array with intRow,intFirstColumn through intRow, intLastcolumn.
View Replies!
View Related
Fill Array With Userform ComboBox Names
Is it possible to count the number of comboboxes on a userform? Or better yet can I fill an Array with all the combobox names on a userform? I can count the controls on a userform but I'm trying to count just the comboboxes? Instead of all the labels and textboxes and frames. I would like to loop through them for validation instead of doing it by each one by itself!
View Replies!
View Related
Fill MultiColumn Listbox With Part Of Array
I have a multicolumn listbox, and a multidimensional array. I want to put just SOME values of the array into the listbox, but I ger errors using both .list or .column to access single items, and even using .additem . I have: dim variable(2000,2) as string variable(1,0)="aaa": variable(1,1)="bbb": variable(1,2)="ccc" variable(2,0)="www": variable(2,1)="awasd": variable(1,2)="asdfa" .... variable(2000,0)="www": variable(2000,1)="awasd": variable(2000,2)="asdfa" I don't want to store all 2000 elements, just some ones: how can I do it?
View Replies!
View Related
Fill In A Number Of Rows With Array Formula's In The Adjucent Cells
I got the following loop that is very slow. The main way (I think) to speed it up is to avoid looking at all the 65000 cells. The columns used in the calculations have data varying from a few hundred cells up to (rarely) dozens of thousands. I assume it would be better if I could replace the 65500 with something indicating the "end" of the data in the column. Also, is there another way to do this process without using a loop? I am trying to fill in a number of rows with array formula's in the adjucent cells. I use the loop because the number of rows depends on a selection of another parameter which sets the row number before calling the loop.
View Replies!
View Related
Recursively & Conditionally Move/Loop Through Folder Hierarchy To Fill Array
i am use dir to put into an array all the folders in a folder then enter the first folder and repeat. the problem i am having is when it encounters a file it also puts it into the array and then errors once it trys to enter that "folder" (which is a file) right now the code doesnt do anything but the plan is then to call another subroutine that lists all the files im looking for in a folder (that sub does work) what am i doing wrong? all the things i can find on google show it the way i am doing it. Sub GetDirList(topfolder As String) Dim FolderArray() As Variant Dim FolderCount As Integer Dim FolderName As String FolderCount = 0 FolderName = Dir(topfolder, vbDirectory) ' Loop until no more folders are found Do While FolderName <> "" If Not FolderName = "." Then If Not FolderName = ".." Then FolderCount = FolderCount + 1 Redim Preserve FolderArray(1 To FolderCount) FolderArray(FolderCount) = FolderName End If End If
View Replies!
View Related
VLookup Date Range And Return Multi Lines
I have a large table in the final tab of my excel book. This table contains data such as Company name, contact details, etc but also a date which I must contact them on (this is nothing sinister, just a curtsy call following a job ). The problem I am having is that I am running a vlookup on the first tab on a cell referring to todays date. This only brings shows one record which is the first occurrence of this date. This isn't very useful to me The problems I need solving are: I would like to be able to vlookup a range of dates (i.e. 7 days before or after todays date) - how do it do this? I would also like to be able to return multiple results (i.e. if there is more than one company within that date range I would like them to show).
View Replies!
View Related
Add Items: Multi Column ListBox: Non-Contiguous Range
i have several listbox with two columns in a userform in a sheet i have several columns whith values and i'm trying to put the values from the columns in the respective listbox. down is the code i wrote but is stressing with column command Private Sub UserForm_Initialize() Dim contar, j, i As Double Sheets("Historico").Select contar = 0 For j = 1 To 15 linha = j For i = 3 To 65536
View Replies!
View Related
Calculate The Range And Fill With A Formula Using The New Range
I have a range of values that are not a constant length. I want a macro that will calculate the range and fill with a formula using the new range. In this example, I need a formula to look at the values in range f464:f471 and calculate the number of values greater than $1000. Column B is a salesman identifier. I can do this manually, but it takes me about 1/2 hour to complete. At every change in "B", I need to place this formula in the open, blank cell in "E" such as highlighted below. week39 ABCDEF45939SCHTERR-WIEDEMANNDCCDIST-MUSCHANY046039SCHTERR-WIEDEMANNDCCDIST-MUSCHANY046139SCHTERR-WIEDEMANNDCCDIST-MUSCHANY046239SCHTERR-WIEDEMANNDCCDIST-MUSCHANY0463 SCH Total 14042.2146439SCITERR-SULLIVANDCCDIST-MUSCHANY15587.1546539SCITERR-SULLIVANDCCDIST-MUSCHANY7217.2246639SCITERR-SULLIVANDCCDIST-MUSCHANY5316.6246739SCITERR-SULLIVANDCCDIST-MUSCHANY2720.6946839SCITERR-SULLIVANDCCDIST-MUSCHANY520.446939SCITERR-SULLIVANDCCDIST-MUSCHANY195.7147039SCITERR-SULLIVANDCCDIST-MUSCHANY047139SCITERR-SULLIVANDCCDIST-MUSCHANY0472 SCI Total 31557.7947339SCJTERR-VANBUSKIRKDCCDIST-MUSCHANY4190.7147439SCJTERR-VANBUSKIRKDCCDIST-MUSCHANY3219.7647539SCJTERR-VANBUSKIRKDCCDIST-MUSCHANY3187.147639SCJTERR-VANBUSKIRKDCCDIST-MUSCHANY2209.3747739SCJTERR-VANBUSKIRKDCCDIST-MUSCHANY2132.7547839SCJTERR-VANBUSKIRKDCCDIST-MUSCHANY2034.56 Spreadsheet FormulasCellFormulaF463=SUBTOTAL(9,F435:F462)F472=SUBTOTAL(9,F464:F471) Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4
View Replies!
View Related
Fill Range With Values From Another
I am not even sure if my title matches my problem, but it was the best I could think of. Please take a look at the following code... Sub Test() Dim cell As Range For Each cell In Range("F4", Range("F65536").End(xlUp)) cell.Offset(0, 3) = cell.Offset(0, 2) Next cell End Sub I use similar codes quite a lot. The issue with this is that if I want to add columns or delete columns later on to my spreadsheet, I need to go and change all the previous codes so the columns match up. My question is, how else can I write a code that has similar functions but with changing references in case I do add or delete columns? I understand that I can define ranges, but I am not sure how I can work with that within a macro. I am almost sure there are different solutions to this problem, and I would appreciate and be interested in knowing all of them to put them in comparison.
View Replies!
View Related
Fill Number Down Range
Is it possible for a macro to enter the number that is in Range("SYear") into A4, fill that number down 30 rows (31 total A4-A34), then add 1 to the number and put in the next 31 cells..repeat untill the number = Range("EYear"). For example if SYear = 2010 and EYear = 2012, I need 2010 in A4:A34, 2011 in A35:A65, & 2012 in A66:A96.
View Replies!
View Related
Fill Color In Range Between Specified Value
Trying to find a way to fill in a color based on a specified value, up to the last entry. ie. Within a range ("Range1")T1:T9, anywhere the value is "R" , fill the color red within column P (offset -5 columns to the left) up to the last entry. ..this is something I would like to initiate via button assignment using a VBA script. eg: Column..P,.....T 1..................B 2........Red.....R 3........Red.....T 4........Red.....R 5........Red.....V 6........Red.....M 7........Red.....R 8..................X 9..................X
View Replies!
View Related
Use Arrays To Fill A Range
My code is extremely slow because it writes a row by row. I am trying to use arrays to fill a range for a problem which is essentilly this: Let x = 1 to 10 Let y = 1 to 10 Let R = f (z) f (z) is calculated for various pairs of x and y (coordinates) such as: (x1,y1), (x1,y2), (x1,y3)....(x10,y9), (x10,y10). I am looking for a code that will fill columns A' and 'B' with x and y in pairs (each x with each y) and values of R in column 'C'.
View Replies!
View Related
Dimensional Table
i have a problem with my report. there is a dimensional table: DIM RESULTSDIM_111.1DIM_111.8DIM_111.2DIM_25.3DIM_25.5DIM_380.2DIM_380.1DIM_380.35 i need to make a new table in wich all "dim" will repeat only once and the results will be the average of the results that belongs to the same "dim" the times that the "dim" repeats can change and the "dims" could be not only till 3
View Replies!
View Related
Macro – Two Dimensional Look Up
The workbook has two sheets. Sheet1 has numbers in column A going down the sheet and dates in row 2 going across the sheet starting in column B. Sheet2 is similar but the column and rows do not line up with sheet1. I would like the macro to look at the numbers in column A and the dates in row 2 on sheet1 and find the same match of number and date in sheet2 and enter the value from sheet2 into the appropriate cell in sheet1. I understand it can be done in a formula with index and match but I would prefer a macro.
View Replies!
View Related
Auto Fill From Cell Range
I have a spreadsheet that is downloaded from an accounting system and requires extensive editing to be normalized before it is combined with data in another database. To normalize the data in a simple manner, I use the following macro: Sub All() Rows("1:37").Select Selection.Delete Shift:=xlUp Sheets("DL").Select Columns("A:D").Select Selection.Insert Shift:=xlToRight Range("A1").Select ActiveCell.FormulaR1C1 = "Check" Range("B1").Select ActiveCell.FormulaR1C1 = "Benefitor"........................ It works great, however I would like to not limit my cell ranges for the auto fills. I would like to auto fill to the end of the data, which changes with every download (additional rows).
View Replies!
View Related
Setting A Range Of Fill Dates
I have a certain number of accounts for opening and close dates. What I wish to acheive is to fill a Range of date from the opening to the closing (or up to 200606 YYYY-MM) in another spreadsheet with the account number corresponding to that date next to it. The VBA I have right now manages to create the list of accounts but up till 2006. It isnt taking into consideration the closing dates of accounts. So For example What I want is to set up inside of the loop that to continue to do the loop until it reaches the closing date by YYYY-MM so if the clossing date is 200504 I will have data filled up to that date, and if the closing date is "" then fill the dates up til 200606. Ive tried out a number of ways of inserting the if statement but apparently the string create a problem. Here is my current VBA and a Sample Sheet of what it looks like....
View Replies!
View Related
Fill Color Change With Range
Function BG(InRange As Range) Range("InRange").Select With Selection.Interior .ColorIndex = 6 .Pattern = xlSolid End With End Function That so far but not quite sure why that isnt working. I want to change the fill color with a UDF that all they do is select a Range and it changes those fill colors to whatever the Colorindex may be. I didnt find anything while searching the forums with this already.
View Replies!
View Related
|
TRACKER
