Building an Array in VB - Help Needed

matt_d

New Member
Joined
Feb 16, 2005
Messages
9
Hi all,

I am trying to write some visual basic to take a selection of data from a worksheet, put it into an array, and then upload it into a table on our teradata database. I've managed to do it all except for one small hitch.

One of the columns that's being pulled into the array shows a four digit location code, and then a description (all within the same cell). I want to to do the equivalent of a "=LEFT(B5,4)" type formula within the code so as to only pick up the piece I need. My current code is shown below:

Code:
Sub MakeUploadRange()

Dim myArray(9 To 15008, 1 To 3)
Dim i As Integer
Dim j As Integer

For i = 9 To 15008
        myArray(i, 1) = Worksheets("Order Sheet DETAIL").Cells(i, 3).Value
        myArray(i, 2) = Worksheets("Order Sheet DETAIL").Cells(i, 5).Value
        myArray(i, 3) = Worksheets("Order Sheet DETAIL").Cells(i, 24).Value
Next i

Call ExportOrders(myArray)

End Sub

The line "myArray(i, 1) = Worksheets("Order Sheet DETAIL").Cells(i, 3).Value" is the one I want to change.

Thanks in advance for any help.

Matt
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi,

did you try
Code:
myArray(i, 1) = Left(Worksheets("Order Sheet DETAIL").Cells(i, 3).Value, 4)

remark: is your code running quickly ?

kind regards,
Erik
 

matt_d

New Member
Joined
Feb 16, 2005
Messages
9
Hi,

did you try
Code:
myArray(i, 1) = Left(Worksheets("Order Sheet DETAIL").Cells(i, 3).Value, 4)

remark: is your code running quickly ?

kind regards,
Erik

Thanks Erik, I'd tried it lots of ways, but not that way.

It doesn't run especially quickly. Any ideas on speeding it up?

Matt
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
try this
Code:
Sub MakeUploadRange()

Dim myArray(9 To 15008, 1 To 3)
Dim helpArray(3)
Dim i As Integer
Dim j As Integer

Const LR = 15008
    With Worksheets("Order Sheet DETAIL")
    helpArray(1) = .Range("C9:C" & LR)
    helpArray(2) = .Range("E9:E" & LR)
    helpArray(3) = .Range("X9:X" & LR)
        For i = 9 To LR
        myArray(i, 1) = helpArray(1)(i - 8, 1)
        myArray(i, 2) = helpArray(2)(i - 8, 1)
        myArray(i, 3) = Left(helpArray(3)(i - 8, 1), 4)
        Next i
    End With

'testline
'Sheets("test").Range("A9:C" & 15008) = myArray

Call ExportOrders(myArray)

End Sub
 

mortgageman

Well-known Member
Joined
Jun 30, 2005
Messages
2,015

ADVERTISEMENT

try this
Code:
Sub MakeUploadRange()

Dim myArray(9 To 15008, 1 To 3)
Dim helpArray(3)
Dim i As Integer
Dim j As Integer

Const LR = 15008
    With Worksheets("Order Sheet DETAIL")
    helpArray(1) = .Range("C9:C" & LR)
    helpArray(2) = .Range("E9:E" & LR)
    helpArray(3) = .Range("X9:X" & LR)
        For i = 9 To LR
        myArray(i, 1) = helpArray(1)(i - 8, 1)
        myArray(i, 2) = helpArray(2)(i - 8, 1)
        myArray(i, 3) = Left(helpArray(3)(i - 8, 1), 4)
        Next i
    End With

'testline
'Sheets("test").Range("A9:C" & 15008) = myArray

Call ExportOrders(myArray)

End Sub


Erik - two questions from a learner if you don't mind

1) Why didn't the 9 get its own constant?
2)How come the constant didn't get defined before the dim statement and accordingly used in the dim statement?

Gene, "The Mortgage Man", Klein
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hello, Gene,

didn't do things which much thinking: first purpose was to speed it up
1. do you mean this ?
Code:
Const FR = 9
'........
.Range("C" & FR & :C" & LR)
I've put the LR in as constant because it was a long number :) & because I can imagine the next step will be to make it a variable

2. you can put the const ... lines before the dim if you want

as a conclusion of both questions
Code:
Dim LR as Long

LR =15008
which can be modified to
Code:
Dim LR as Long

LR = Cells(Rows.Count, "C").End(xlup).Row
at the condition the last row will be found in column C

best regards,
Erik

EDIT: I've seen your "Mishegas": why not put your avatar here and sign up at DRAFT :wink:
 

mortgageman

Well-known Member
Joined
Jun 30, 2005
Messages
2,015

ADVERTISEMENT

EDIT: I've seen your "Mishegas": why not put your avatar here and sign up at DRAFT

I actually tried. Didn't succeed. I'm not sure how to do it. Just as well actually - if you've seen "My Mishegas", then you know what an ugly puss I got!

Gene, "The Mortgage Man", Klein
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Hi
try
Code:
Sub MakeUploadRange()

Dim myArray(9 To 15008, 1 To 3), a
Dim i As Integer
Dim j As Integer
a = Sheets("Order Sheet DETAIL").Range("c9:x15008").Value

For i = 9 To 15008
        myArray(i, 1) = Left(a(i-8,3),4)
        myArray(i, 2) = a(i-8, 5)
        myArray(i, 3) = a(i-8, 24)
Next i

Call ExportOrders(myArray)

End Sub
Gene, don't you have problem with myArray(9 To 15008, 1 To 3) when the array passed to Sub ExportOrders?
 

mortgageman

Well-known Member
Joined
Jun 30, 2005
Messages
2,015
Gene, don't you have problem with myArray(9 To 15008, 1 To 3) when the array passed to Sub ExportOrders?

I don't have a problem with anyone's code - yours or Eriks. It has been quite some time since I worked in a software field, and not only have my skills atrophied, but there is a great deal of new concepts as well. Moreover, a number of people on this board seem to know a lot about speeding up code - something I never cared about when I was in the field.

IOW, my questions were just that - questions.

Gene, "The Mortgage Man", Klein
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Gene, don't you have problem with myArray(9 To 15008, 1 To 3) when the array passed to Sub ExportOrders?

I don't have a problem with anyone's code - yours or Eriks. It has been quite some time since I worked in a software field, and not only have my skills atrophied, but there is a great deal of new concepts as well. Moreover, a number of people on this board seem to know a lot about speeding up code - something I never cared about when I was in the field.

IOW, my questions were just that - questions.

Gene, "The Mortgage Man", Klein

OOps, so sorry Gene. I thought that you asked this question...
 

Forum statistics

Threads
1,136,262
Messages
5,674,708
Members
419,520
Latest member
Jennifer4Dillon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top