Solved: range value

[GARY MORAN]

in the below code i would like the list range value to equal the activecell after ActiveCell.Offset(0, -3).Select.
can anyone help.
Thanks


Sub Macro1()
Range("k9").Select
i = 9

While Cells(i, 5) <> ""

ActiveCell.FormulaR1C1 = ELCOMP(""FBI:OFFER"",offerseason,LISTRANGE)"
ActiveCell.Offset(1, 0).Select
ActiveCell.Offset(0, -3).Select
ActiveCell.Value = Range(LISTRANGE).Value




i = i + 1
Wend
End Sub

[lucas]

Hi Gary, Welcome to the forum.

Can you tell us what Listrange is? Is it a named range, a variable?

It would be best if you could describe exactly what you are trying to do as it doesn't come through to me at least in the code you posted.

ps when posting code, select it and hit the vba button to format it for the forum.

[GARY MORAN]

Hi
listrange is a number in a list, the list goes from 1 to 2000, i want this number to increment by 1 each loop, but i would like it to follow the listrange thats why im offsetting to the select the list each time.
Thanks

[GTO]

If ListRange is a number, then wouldn't:

ActiveCell.Value = Range(LISTRANGE).Value
be something like?
ActiveCell.Value = Range(587).Value

I'm with Steve, maybe post/attach a small example workbook?

Mark

[GARY MORAN]

Thanks for your help but i cracked it with the below, cheers gary
Private Sub CommandButton1_Click()
Dim LISTRANGE As Range
Set LISTRANGE = Range("a1")
LISTRANGE.Select
Range("k9").Select
i = 9
While Cells(i, 5) <> ""
ActiveCell.FormulaR1C1 = "=ELCOMP(""FBI:OFFER"",offerseason,LISTRANGE)"
ActiveCell.Copy
ActiveCell.PasteSpecial (xlPasteValues)
ActiveCell.Offset(1, 0).Select
ActiveCell.Offset(0, -3).Select
LISTRANGE = ActiveCell.Value
ActiveCell.Offset(0, 3).Select

i = i + 1
Wend
Cells(1, 1).Value = 1
End Sub

[lucas]

Gary didn't read my suggestion about selecting his code and hitting the vba button when posting code.

I have to wonder why.

Gary, if it wouldn't be too much trouble, could you mark your thread solved using the thread tools at the top of the page so that other people won't read the entire thread just to find that it's been solved?

Just a courtesy if you don't mind.