| Home |
| Search |
| Today's Posts |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multidimensional Arrays - VBA
Post the code you tried.
Alan Beban Baba wrote: Sorry... I got pulled away from the post and did not know how to get back to it.. is there some way to bookmark it with using IE bookmarks? I tried the code.. but it says.. "Compile error: Can't assign to array" and it highlights the "arr= Evalu...." What am i missing? Thanks in advance for all your help. Ali Alan Beban wrote in message ... After some help from Tom Ogilvy, here is code that doesn't depend on the downloadable file of functions: Dim arr() As Variant, ws As Worksheet, rng As Range arr = Evaluate("VLookup(10035, tbl2, {2,3,4,5},False)") Set ws = Worksheets(arr(1)) Set rng = ws.Range(arr(2)) Range(rng(1, 1), rng(1, 4)).Value = arr Alan Beban Alan Beban wrote: Baba wrote: "then I wouldl like to add them together." Add *what* together? If the functions in the file at http://home.pacbell.net are available, and if SH1,SH2, etc. are actual Worksheet names, the following will work: Dim arr() As Variant, ws As Worksheet, rng As Range, i As Integer arr = VLookups(10035, Range("tbl2"), _ Application.Evaluate("{2,3,4,5}")) Set ws = Worksheets(arr(1, 1)) Set rng = ws.Range(arr(1, 2)) For i = 1 To 4 rng(1, i).Value = arr(1, i) Next It populates not only the location specified in the address specified in Columns 2 & 3, but also of course the next three cells to the right. Or if someone can furnish the corresponding VBA syntax for the equivalent VLOOKUP formula, that can be substituted for the VLookups function above. Alan Beban Can you help me expand on this idea.. What I need to do is to read a column of values (could be upto 300 rows) and depending on its value, place the values from the adjacent 4 columns in locations on other worksheets... So the idea is : [A] [b] [C] [D] [E] [1] 10002 SH1 B64 209.00 0.00 [2] 10035 SH1 C38 0.00 104.00 [3] 10056 SH2 B82 100.00 0.00 [4] 10083 SH3 C38 0.00 104.00 [5] 10124 SH3 C38 0.00 101.00 AND IF POSSIBLE, If you notice, I have the last 2 lines heading to same field.. then I wouldl like to add them together.. but that I will work around if this makes it "not possible" or too difficult. So basically I have to iterate through 300 rows and depending on the value in Col A (or B.. or C.. not sure which col it could come in.. so the col assignment has to be dynamic).. it will be the left most column for sure.. then to take the 4 columns next to the col with the values and populate the location specified in Col 2 and 3 combined. This is urgent.. I have been going in circles.. Thanks. Baba Brent McIntyre wrote in message ... Tom, Thanks very much for your help, but I think I have confused everyone, including myself. What I want to do is create a virtual table via an array of 151 rows and 6 columns. I have worked out the whole thing of writing in the array, ie setting up the virtual table, but I am not sure how to access it, I need to be able to get a certain value, check whether it appears in a certain column and if it does display the information from another column. ie Virtual Table One Cat Apartment Two Fish House Three Cow Flat And if the read in value is "Fish" I want it to output "House" I hope this makes it all a bit more clear. Thank you all for your help it is much appreciated, this is my first time using Multidimensional Arrays. Yours sincerely, Brent McIntyre *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Use of IF with arrays | Excel Worksheet Functions | |||
| How can I write multidimensional arrays in Excel Formulas? | Excel Worksheet Functions | |||
| using linest excel function from msaccess with multidimensional ar | Excel Worksheet Functions | |||
| Arrays | Excel Discussion (Misc queries) | |||
| Multidimensional Arrays - VBA | Excel Programming | |||
![Alan Beban[_3_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Threaded Mode
