VBA: generate column values for 2nd, last row and in-between ( in Excel)

[zeno]

Hello,

I'd like to know if you could please help me with VBA code for generation of row values in 1 column.
I'm looking at how I can write in VBA a macro that generates values in a particular column (say 'x') that has a few hundred rows (of variable length) within a table. I would define these values as follows:

** Step 1:
2nd row in column x: value=1 (1st row of the table is title row)
last row in column x: value=1
all rows in between: value=n

** Step 2:
In addition, I would also need to use the same sign (either positive or negative) for each row in column x that corresponds to the sign of that row from another column 'y'.

** Possible part of solution:

Dim arr As Variant
    arr = Range("G2", Cells(Rows.coUnt, "G").End(xlUp)).Value
[\code]

In this case I would count the number of rows in column "G".

[COLOR=#333333]Thank you for your help and advice.[/COLOR]

 

[JackDanIce]

You haven't stated what your value for n is, I've used the number 2 below, but try:

FillMe()

Dim i as Long

Application.ScreenUpdating = False

i = Range("X" & Rows.Count).End(xlUp).Row
Range("X2, X" & i) = 1
For i = 3 to i - 1
  Range("X" & i) = Sgn(Range("Y" & i)) * 2'You've not stated your value for n
Next i

Application.ScreenUpdating = True

End Sub

 

[zeno]

Thank you JackDanIce! Your suggestion does the job!

 

[JackDanIce]

Glad it helped, thanks for the feedback