35936

VBA Loop for copying data from cell to array

Question:

I'm new to VBA in excel, I write code for copying cells from sheet to an array. When I run I got run time error. I don't know whats wrong.

Sub DistSystem() Dim count As Integer Dim i As Integer Dim array_rank() As Variant Dim array_city() As Variant Dim array_assign() As Variant count = Sheets("111").Range("Y2").Value For i = 0 To count array_city(i) = Range("A" & i).Value array_rank(i) = Range("E" & i).Value array_assign(i) = Range("F" & i).Value Next For i = 1 To 10 MsgBox array_rank(i, 1) Next End Sub

Answer1:

I suspect you are going to be battle errors in multiple places.

This section of code has 2 significant problems

array_city(i) = Range("A" & i).Value array_rank(i) = Range("E" & i).Value array_assign(i) = Range("F" & i).Value

First you are trying to assign values to array that do not have any dimensions. You decleared the arrays but you left them dimensionless. You need to define the dimensions before you try to assign values to the array

Something like

Redim array_city(1 to count)

Next you are trying to get a value from Range("A" & i) when the value of i is zero. Cell "A0" does not exists and will also throw an error.

So to rewrite your code as written, you would need to make a few changes:

Sub DistSystem() Dim count As Integer Dim i As Integer Dim array_rank() As Variant Dim array_city() As Variant Dim array_assign() As Variant count = Sheets("111").Range("Y2").Value Redim array_rank(1 to count) Redim array_city(1 to count) Redim array_assign(1 to count) For i = LBound(array_rank) To UBound(array_rank) array_city(i) = Range("A" & i).Value array_rank(i) = Range("E" & i).Value array_assign(i) = Range("F" & i).Value Next For i = 1 To 10 MsgBox array_rank(i) Next End Sub

However, you are over complicating how you are reading the values into the array. You can simply read the entire range directly into the array

Sub DistSystem() Dim count As Integer Dim i As Integer Dim array_rank As Variant 'Notice the arrays are not longer declared with () Dim array_city As Variant ' -> this is necessary Dim array_assign As Variant count = Sheets("111").Range("Y2").Value array_city = Range("A1:A" & count).Value array_rank = Range("E1:E" & count).Value array_assign = Range("F1:F" & count).Value For i = 1 To 10 MsgBox array_rank(i, 1) Next End Sub

The resulting array with be 2 dimensions, with the Row value as the first dimension and the column as the 2nd dimension. since all of the ranges are a single column, you would access any value by calling array_rank(Row,1) or array_city(Row,1) or array_assign(Row,1).

Recommend

  • lessen loading or processing Time in VBA
  • How can I pass this specific variable I'm using jquery-ajax
  • Parse fetchIfNeeded() not working
  • VBA - How to Clear a Connection String From a Connection
  • If Image contains specific color then
  • Multiple inclusion in multiple files
  • In objective C, when presenting a new scene, how do you remove the old scene?
  • How to get all forms in a VB.net (VS08) project in an array?
  • Variable between worksheet and module not cooperating
  • How to copy a Shape to another worksheet (not as a picture)?
  • Copy and Paste Entire Row
  • Unable to save a query as a view table
  • How do I pass worksheet and ranges as variables?
  • Adding/Removing Lines from a JPanel
  • How to update powerpivot pivot table filter via cell reference?
  • How to Translate texts contained in MsgBox in Inno Setup?
  • Creating a layer of gradient within an SVG path dynamically
  • VB.Net Double comparison after some additions
  • Generic/Unknown HTTP Error with response code 0 using UnityWebRequest
  • Vigenere cipher not working
  • WordPress > setting permalink option via script buggy?
  • Android: How to correctly use NotifyDataSetChanged with SimpleExpandableListAdapter?
  • Which open source license has no forking [closed]
  • How do I get the list of bad records that didn't load in Bigquery?
  • How can I replace the server in Web Component Tester
  • TFS 2015 - Waiting for an agent to be requested
  • ASP.NET MVC Application won't update some controllers
  • C# program and C++ DLL compiled for 32-bit system crash on 64-bit system
  • Web.config system.webserver errors
  • How to revert to previous XCode version?
  • How integrated is Collada to OpenGL ES
  • Make VS2015 use angular-cli ng at build time in a .NET project
  • Where to put my custom functions in Wordpress?
  • VBA Convert delimiter text file to Excel
  • Rearranging Cells in UITableView Bug & Saving Changes
  • Numpy divide by zero. Why?
  • Circular dependency while pushing http interceptor
  • Linker errors when using intrinsic function via function pointer
  • FormattedException instead of throw new Exception(string.Format(…)) in .NET
  • Reading document lines to the user (python)