PDA

View Full Version : [SOLVED] Insert Values into - Offset Cells Array



dj44
09-05-2018, 01:30 AM
Good morning folks,

I am trying to insert a value into offset cells from Z3

example - the offsets
T,Q,M








Sub Insert_Value()

Dim oCell As Range
Dim i As Long
Dim vOffset As Variant


vOffset = Array(-13, -9, -6) ' COLUMN OFFSETS from z3 T,Q,M

For i = LBound(vOffset) To UBound(vOffset)
For Each oCell In ThisWorkbook.Worksheets("test").Range("Z3").Cells

oCell.vOffset(i).Value = "YES"



Next oCell
Next i


End Sub



There is a flop somewhere but I cant work it out

please do have a kindly look

thank you

Kenneth Hobs
09-05-2018, 07:59 AM
Sub Insert_Value()
Dim oCell As Range, vOffset, e

vOffset = Array(-13, -9, -6) ' COLUMN OFFSETS from z3 T,Q,M

For Each e In vOffset
ThisWorkbook.Worksheets("test").Range("Z3").Offset(, e).Value = "YES"
Next e
End Sub

dj44
09-06-2018, 01:25 AM
Hello Kenneth,

nice to see you.

thank you for the help.

it works nicely.

Now that it worked - I moved on to my next stage to try and make my array of values
go into it




Sub Insert_Value_Array()
Dim i As Long
Dim oCell As Range, vOffset, e
Dim vCell_Value As Variant

vOffset = Array(-6, -4, -2) ' COLUMN OFFSETS from Z3 T,Q,M
vCell_Value = Array("apple", "pear", "bananna") ' Insert these values



'For Each e In vOffset

For i = LBound(vOffset) To UBound(vOffset)

ThisWorkbook.Worksheets("Test").Range("Z3").Offset(, vOffset(i)).Value = vCell_Value(i).Value


Next i
'Next e




The above makes sense to me but it doesnt work
any final tips would be appreciated

thank you

Kenneth Hobs
09-06-2018, 07:24 AM
Only an array of Ranges would a Value property.


ThisWorkbook.Worksheets("Test").Range("Z3").Offset(, vOffset(i)).Value = vCell_Value(i)

dj44
09-06-2018, 10:00 AM
Thanks Kenneth,

staring at the obvious always :doh:

thanks for the help again

and Stellar weekend to you and forum