Syntax for setting an array item to the value of a cell

pbgexcel

New Member
Joined
Jan 2, 2018
Messages
26
Good afternoon all,

I'm having some trouble setting an array item to the value of cell. How can this be done?

Currently I'm working with this:
Code:
Dim ArrayName() As Variant
ArrayName(row no., column no.) = Range(column letter & row number).Offset(0, -1).Value ' I want the value of the cell directly behind the cell in question


Any and all help greatly appreciated! Please let me know if I can clarify in any way.

Thanks so much!


EDIT: I should mention that row nos. and column letters are correct in my code, I've just represented them in the code above.

 
Last edited by a moderator:

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,386
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Maybe:
Code:
Dim ArrayName as Variant
ArrayName = Range("A" & RowNumber).offset(0,-1).Value
This will produce a 2-D array with lower and upper bounds of 1 for both dimensions (i.e. an array of one cell).
Just curious, why do you want a single-cell array?
 

pbgexcel

New Member
Joined
Jan 2, 2018
Messages
26
Hi Joe,

My array is actually very large. The snippet we're looking at here is a part of a 'for' loop that stores the values of particular cells to the array given certain logic. So I need to set a specific item in the array to the cell value in question. Is the problem that the column letter needs to be in quotations? Because the column number in question is actually stored as a variable in my code.

Thanks for all your help!
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,386
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
The column letter needs to be entered as a string, meaning that you must use either a string variable or a hard-coded string.

Example using a variable:
Dim ColLetter as string
ColLetter = "A"
Range(ColLetter & RowNumber).Value = .......
 

pbgexcel

New Member
Joined
Jan 2, 2018
Messages
26

ADVERTISEMENT

Thanks Joe. The column letter is in fact a string variable. Could anything else (code-wise, at least) be the problem? Thanks for your help.

EDIT: This is precisely the code that is not working (I should note that no error is thrown).
Code:
'ValidRunHour. array initialized prior
ValidRunHourWindowsArray(ValidWindowsCounter, 2) = Range(SlidingStartingPointColumn & SlidingStartingPointRow).Offset(0, -1).Value
ValidRunHourWindowsArray(ValidWindowsCounter, 3) = Range(EndingPointColumn & LocalEndingPoint).Offset(0, -1).Value
 
Last edited:

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,386
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
I don't recall that you described the "problem" you have encountered in your earlier posts. Can you elaborate? And, it might be helpful to post more of your code including the assignments of values to your variables ...
 
Last edited:

pbgexcel

New Member
Joined
Jan 2, 2018
Messages
26

ADVERTISEMENT

This is precisely the code I'm working with:
Code:
'ValidRunHour. array initialized prior
ValidRunHourWindowsArray(ValidWindowsCounter, 2) = Range(SlidingStartingPointColumn & SlidingStartingPointRow).Offset(0, -1).Value
ValidRunHourWindowsArray(ValidWindowsCounter, 3) = Range(EndingPointColumn & LocalEndingPoint).Offset(0, -1).Value

Neither columns 2 nor 3 are being populated with the contents (words) of the cells they are set equal to. If the code syntax is proper perhaps something in the worksheet is the matter..I'll double-check that if so.
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,386
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Can you post enough of the code so we can see where all the variables in those two lines are dimensioned and initialized? It's hard to diagnose a problems cause w/o seeing the code.
 
Last edited:

pbgexcel

New Member
Joined
Jan 2, 2018
Messages
26
Absolutely, thanks for your help.
Code:
Public ValidRunHourWindowsArray() As Variant
ReDim ValidRunHourWindowsArray(1 To 148576, 1 To 3) 'Dimensioned in a Sub

Dim SlidingStartingPoint As String
SlidingStartingPoint = GlobalStartingPoint
Dim SlidingStartingPointAsRange As Range
Set SlidingStartingPointAsRange = Hourly.Cells.Find(SlidingStartingPoint, LookIn:=xlValues, LookAt:=xlPart)
If Not SlidingStartingPointAsRange Is Nothing Then
    SlidingStartingPointColumn = Split(SlidingStartingPointAsRange.Address, "$")(1)
End If
SlidingStartingPointRow = SlidingStartingPointAsRange.Row

Dim LocalEndingPoint As Long
LocalEndingPoint = 10

Dim EndingPointColumn As String
EndingPointColumn = SlidingStartingPointColumn

ValidRunHourWindowsArray(ValidWindowsCounter, 2) = Range(SlidingStartingPointColumn & SlidingStartingPointRow).Offset(0, -1).Value
ValidRunHourWindowsArray(ValidWindowsCounter, 3) = Range(EndingPointColumn & LocalEndingPoint).Offset(0, -1).Value
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,216
Office Version
  1. 365
Platform
  1. Windows
Where's the loop?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,370
Messages
5,595,768
Members
414,017
Latest member
surajks

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