Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old April 19th, 2005, 09:36 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Copenhagen N, , Denmark.
Posts: 217
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to mega
Default Looping controls and array redim

My problem is two fold and if you only know the answer to one of them I'll appreciate it (almost) just as much.

What I want to do is to populate an array based on two indexes. The first being the number of rows in my Range, the latter being the number of columns in my Range plus one. The extra one is the interior color of the row/cell. The code below shows what I've come up with so far.
[CODE]
' The spreadsheet data is in n number of rows and 7 columns
counter = 1
For Each oCell In myRange
    i = i + 1
    ReDim Preserve myArray(counter, i)
    If i < (myRange.Columns.Count) + 1 Then
      myArray(counter, i) = oCell
    Else
      myArray(counter, i) = oCell.Interior.Color
      i = 0
      counter = counter + 1
    End If
Next oCell
[CODE]
While this is fine the first iteration, it doesn't work the next because I can't stop/hold the For Each loop and insert the extra data. I simply loose the first cell value in the next row.

My second problem is how I dim and redim an arrray. When I get to the second iteration I get a
Quote:
quote:Run-time error '9':
Subscript out of range
I obviously can't dim my array with the desired values because one need to use constants and I don't know how many rows I need to add to my array.
So I just Dim it like this:
Code:
Dim myArray() As Variant
Any help would be a life saver - need to make this work within this week at latest. Thanks in advance - mega
;)

 - mega
Moving to C# .NET
__________________
 - mega
Aspiring JavaScript Ninja
Reply With Quote
  #2 (permalink)  
Old April 19th, 2005, 10:19 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Copenhagen N, , Denmark.
Posts: 217
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to mega
Default

I'm trying to update a workbook with data from SAP workbooks. I've made query from SAP in October and know it's pretty much outdated. What I want to do is get the data from my newest SAP queries and update the new fields, but I also want to suppress an update if the interior color isn't white (a background color is given to each row to keep track of its status). Instead of just using the Range object I want to use arrays because of the speed that opt gives me. The code for getting the data from the updated workbook was pretty much straight forward but this part is messy.

 - mega
Moving to C# .NET
Reply With Quote
  #3 (permalink)  
Old April 19th, 2005, 11:57 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Copenhagen N, , Denmark.
Posts: 217
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to mega
Default

I finally sorted it out my self. You can see how here: http://www.ozgrid.com/forum/showthread.php?p=164762

 - mega
Moving to C# .NET
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
looping through static array mister_mister XSLT 9 March 21st, 2008 01:30 PM
3D Array - Looping jordan23 XSLT 5 April 27th, 2007 07:08 AM
Looping through Array Values in T-SQL trufla SQL Server ASP 4 April 13th, 2007 06:14 AM
redim array specifically Trojan_uk Classic ASP Basics 3 March 24th, 2004 01:17 PM
Looping Through Controls stu9820 ASP.NET 1.0 and 1.1 Professional 2 March 4th, 2004 01:40 PM



All times are GMT -4. The time now is 02:18 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.