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:

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
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?
 
Upvote 0
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!
 
Upvote 0
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 = .......
 
Upvote 0
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:
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

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
Back
Top