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

[pbgexcel]

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:

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.

 

[JoeMo]

Maybe:

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]

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]

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]

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).

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

 

[JoeMo]

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 ...

 

[pbgexcel]

This is precisely the code I'm working with:

'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]

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.

 

[pbgexcel]

Absolutely, thanks for your help.

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]

Where's the loop?