Select next cell after dynamic range

[kieran989]

Hi there,

I have a dynamic range in my workbook (going across the page), setup in name manager using =OFFSET($A$1,0,0,1,COUNT($A1:$ZZ1))

I need to be able to add a value to the next blank cell using VBA? This needs to be able to work even when there is only 1 entry in the dynamic range (ie xlright wont work)

I have tried the following but still cant get it to work:


With Range("namedrange")
.Cells(1, .Cells.Count).Offset(0, 1).Value = somevalue
End With

[onlyadrafter]

Hello,

does this work as expected?


Sub test1()
ActiveWorkbook.Names("test").RefersTo = "=OFFSET(A1,0,0,1,COUNTA(A1:ZZ1))"
With Range("test")
.Cells(1, .Cells.Count).Offset(0, 1).Value = "somevalue"
End With
End Sub

[SamT]

Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1) = somevalue

Range(NamedRange).Offset(0, 1).= Somevalue

With Range(NamedRange)
.Cells(1, .Columns.Count + 1) = somevalue
End With

Range(Split(NamedRange.Address, ":", 2)).Offset(, 1) = somevalue

[kieran989]

thanks guys ended up being a typo by me originally dammit

[SamT]

:D That ain't never happened to me. No sir, never.