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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
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
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 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 & 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
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
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
Ranking Spreadsheet (multi Variable Hlookup)
1. The most amount of wins 2. if two or more people tie the # of wins then the person with the lowest tiebreaker guess wins and the other person becomes the next place. 3. If two people tie the number of wins and the tiebreaker for say 1st place the next highest win and lowest tiebreaker should place 3rd since there are two people above him. Attached is a spreadsheet of the problem. The solution must only use the info in the "Given" section. You may reorder the data (rows moving up or down, not columns). Solution required is in Yellow. I could figure out the logic IF there is a way to do a multi conditional hlookup. So if you know of a way to use an "And" in the condition and pull up one row as a result, i can probably figure out the rest.
View Replies!
View Related
Calculate A Multi-variable Equation Based Off Of Data In A Table With Different Criteria
I'm trying to calculate a multi-variable equation based off of data in a table with different criteria. If you reference the attached spreadsheet I am trying to calculate cost based on weight and zone. For the data entered in cells B1 and B2 944.01 would be the cost. If you look at the table in G2:N3 you'll notice that the values in G2 and G3 are the weight limits G2 is from 0-4999 and G3 is 5000+, this should impact where the value in B1 falls. B2 should select from H1 to N1.
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
Dimensional Weight Formula
I have attached a sheet that contains a pretty simple formula that helps me figure out the dimensional weight of a shipment. It is just a simple vlookup that returns the proper rate (Column H) depending on DIM Weight and Zone. It works great. The issue I'm running into is with various exceptions that I need to put into the formula. The criterias that I need to put into the rate formula (Column H) are: 1-If the one of the Length (Column A), Width (Column B), and Height (Column C) is greater than 60 I need to add 7.50 to the the returned lookup value in Column H. 2-The second longest side the Length (Column A), Width (Column B), and Height (Column C) is greater than 30 I need to add 7.50 to the the returned lookup value in Column H. 3-If the grith (2x Column C + 2x Column B) is greater than 130 I need to add 45 to the the returned lookup value in Column H. I'm working with a few IF formulas but I can't seem to get all of them to work at the same time. Does anyone have any thoughts?
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
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
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
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
Double Lookup From Two Dimensional Table
I am trying to get a cell in my spreadsheet to look up a value based on two values. I have a dropdown list that lists the worksheets in the workbook, and each worksheet has a table with width measurements for the columns and height measurements for the rows. I have a function that is mostly working, it calls the data from the proper worksheet, but it rounds the measurement values down, and I need it to round to the next highest value on the table. For instance, the measurement may be 55" x 55" in, but the table has values for 54" and 60". The current formula rounds down to the 54" measurement, but I need it to round up to the 60". I have attached what I have so far with further notes and cells highlighted.
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
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
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
Checking
what will be the code if commandbutton1 is clicked, it will check if the text in the combobox is in the range A2:A10 in the worksheet, if not in the range it will display a message box that the text is not in the range :D
View Replies!
View Related
Checking For Time
I only want then to be able to make entries between 07:45 and 08:45. If TimeValue(Now()) > "0:745:00" And TimeValue(Now()) < "08:45:00" Then code if OK Else code if not OK MsgBox "TIME HAS EXPIRED .....SORRY." End If
View Replies!
View Related
For Each Not Checking Through Range
All it does is step the cursor down 1 cell from where it starts on the worksheet, rather than sorting through the defined range and then selecting the cell I want. I'm not sure why as I've used almost exactly the same code successfully on a different worksheet in the same workbook: Dim mycheckrng as Range myupdatedate = "04/06/2008" Set mycheckrng = Range("D5:AR5") For Each c In mycheckrng If c.Value = myupdatedate Then c.Select End If Next c mycsdcdateref = ActiveCell ActiveCell.Offset(1, 0).Select
View Replies!
View Related
Odd Or Even Checking
Did Microsoft do this for a reason. But it seems to me there is no built in formula to check weather a number is odd or even. =isodd(A1) or =iseven(a1) would be nice. curent if I have to do something like, which works but is a lot of effort. =IF(A1/2=INT(A1/2),"Even","Odd")
View Replies!
View Related
Checking For Duplicates
On the attached example i am concatenating surname with the first letter of the forename. However, what i want to add into the formula is if the answer is a duplicate of another answer in a range that these duplicates are changed to surname and full forename. Is this possible with a formula or would it need a bit of vba programming?
View Replies!
View Related
Checking That A Value Is A Multiple Of Another Value
Is there a way to check a value, that is a multiple of another value. E.G. say the user specifies a height of 225 and then specify laminations of 45 is there a way to check the value of 225 to ensure 45 divids into it perfectly?? and if it doesnt then gives an error message of some kind. So if someone specifies height of 200 and lamination of 45 an error would show because 45 cant be divided into 200 perfectly.
View Replies!
View Related
Checking For Values
I am just having trouble getting vba to recognize cell values. All I want it to do is if cell g2 = 5, then end sub, Else, continue. But I am so clueless that I cant even get it to recognize that what is in cell g2 is the number 5. I would think that I could just put, If range(g & 2) = 5 Then End Sub Else Resume End If
View Replies!
View Related
Checking For Numbers In String
I have data like follwing strings. WAY-308312-1 WAY-521340-1 233-6767-676-6 First two strings are correct. If I get number like in third string first part of string then we have to move row to some other sheet. The first part of string should have text like WAY-123-13-1. I used Val function it checking first charector only. It the string is like 2A1A-243-233-2 the it is not checking.
View Replies!
View Related
Checking For Missing Lines
I have a file of users and security codes. each user should have at least one security code. How can I get rid of all the good combinations and only leave the bad ones? Example: user: joe blo security code = 1 user: jim white security code = 1 user: sam song security code=5 user: jo jo user: billy boy security code=1 You can see Jo Jo does not have a security row. I need to the good ones to be removed so only the user with the missing security code is left:
View Replies!
View Related
Checking Date In Cell
Is there any way through VBA that I can check whether the date in cell A2 is Monday or not. Also the code should allow me to continue if its Monday else should promt a message saying that its not Monday and whether the user still wants to continue. If no it should terminate if yes the next part of the code should continue.
View Replies!
View Related
Checking Data In A Userform
I have a user form that writes data to a worksheet (Sheet1) Is there any way of getting a macro to check what is in "textbox1" against all the entries in column A of "Sheet1"?...........and bring back a message if it already exists?
View Replies!
View Related
Checking For Changed Data
I'm looking to display a message if data in the active cell is changed. I'm using the code below to check if data in a cell matches certain criteria: Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Target.Column = 9 Then ThisRow = Target.Row If Target.Value Like "As*" Then MsgBox "Please add a note about why the biology of this species is distinct the Comments column." End If End If But I have another column where the cells are prefilled with data. This column stores data from a dropdown, such as Critically Endangered, Endangered, Vulnerable etc. I allow the user to change the data in these cells, by selecting an alternative value from the dropdown, but I want to display a message if they change the data in one of these cells.
View Replies!
View Related
Checking The Values Of The Autofilter
I am using the autofilter in my code. Selection.AutoFilter field:=11, Criteria1:="reena" Count = Sheets("ABC").AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Count - 1 In the field for of the worksheet 'ABC' there are some values. I want to sort the value 'reena' and save the count in the variable Count. But sometimes the value 'reena' is not available in the field 11. Then it gives error for the above Count variable. How to check this? How to check that the autofilter value is 'null'.
View Replies!
View Related
Checking A Row To See Who Got All A Grades
I've got all the grades sorted out for the Principal, she is now looking for me to tally up all the students who got straight 'A' grades. I know there is going to be anotehr combination here but I'll start with the basics of straight 'A's The screen below shows how the data is presented although I've slimmed down the headings to fit better. How do I insert a formula to look at each row to see where based on the subjects taken, there are straight 'A's ************************************************************************>Microsoft Excel - Y13 & 14 Combined Worksheet.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutZ6= ABCDEFGHIJKLMNOPQRSTUVWXYZ1ArtArt*2BiologyBSChemistryD&TDramaEconomicsE.*LitFrenchGeographyHistoryH.*EconICTICT2IrishLatinMathematicsMaths*PureMusicPhysicsPoliticsPsychologyR.ESociologySpanish2********A****A**********A*3********AA*A*******A******4******A*C*****D****B******5***********B**********BAA*6*******C***D************BBSheet1* [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
View Replies!
View Related
Checking All Inputs In A Userform
I've a userform with just textboxes and have written code that checks each textbox in turn to ensure a correct value has been entered. I want to run the code after any changes are made; where then would I insert the code? Userform.click doesn't appear to work nor keypress. Would I have to enter the code in each textbox_afterupdate?
View Replies!
View Related
Checking A Range For Data
I am trying to check and see if column "A" has a certain number if it does not then keep going and if it does then find wich row in column "A" has that number then in the same row increase column "B" value by 1. Dim rngPartNumber As Range Dim Rws As Long Dim R As Long Set rngPartNumber = ActiveSheet.Range(Cells(2, 1), Cells(Rows.Count, 1).End(xlUp)) Rws = rngPartNumber.Rows.Count + 1 For R = Rws To 2 Step -1 If Application.WorksheetFunction.CountA(Rows(R)) = compName Then Quantity = Quantity + 1 Next
View Replies!
View Related
Vba For Checking Dates
Is there a code in vba, that will return today's date (or the date of the week before, [today-7]) I need a vba code that will check a current cell - let say cell "A1", that contains a date( for exmpel: 12/7/09), and compare this date to the date last week, if the cell (A1)contains a date that is older then last week , then fill in A2 with "False", if the date in A1 is between the date of today and a week ago then fill A2 with "True". (For the exmple that I gave of course in A2 the value will be "False")
View Replies!
View Related
Checking Cell Values
I have a spreadsheet with 2 columns of values. I need to check that the two columns have the same values, however the last digit of the values will differ every time, in the first column the value will always be a 3 and in the 2nd column it will always be a 0. Unfortunately the amount of digits before the last, differs from 6 to 9 so its not as easy to do a trim. Example Column 1 Column 2 11111113 11111110 222222223 222222220 4444444443 4444444440 I need to check that the first digits (no mater how many) are all the same except the last digit.
View Replies!
View Related
|
TRACKER
