Do while

[Guest]

Hi,

I have the following script in vb:
Dim LNID as range
Dim i as range
LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
Set LNID = Workbooks("FILE1").Worksheets("CONSTANTS").Range("I11")

Do
LNID = LNID + 1
Set i = Range("B2:B" & LNID)
LNID.Copy i
Loop Until LNID = LastRow

LNID is a constant (= 0) What I want is to add a line number to Column B. So
in Cell B2 there should be a 1 in there and in cell B3 a 3 should be copied.
Now all the cells have the same number (there are 200 lines in column C) so
the number 200 is copied to all cells in column B. it should be 1,2,3,4 etc.
Can anyone help with this?

 

[Guest]

Frank,

I'm not sure I understand what you are doing with the variable LNID but if
you simply want to start in B2 and number down for the length of column C
then try this:-

Sub strata()
Dim LNID As Range
lastrowcolc = Range("c65536").End(xlUp).Row
Set LNID = Workbooks("book1").Worksheets("CONSTANTS").Range("I11")
Set myrange = Range("b2:B" & lastrowcolc)
For Each c In myrange
c.Value = LNID
LNID = LNID + 1
Next
End Sub

Mike

 

[NickHK]

Frank,
You have declared LNID as a range, so this makes no sense, in fact will give
a type Mismatch error:
LNID = LNID + 1

Also, this statement is clearly wrong: "LNID is a constant (= 0)".
After that, I cannot understand your stated requirements, but possibly look
in the help for AutoFill.

NickHK

 

[Bob Phillips]

Dim LNID As Range
Dim i As Range
Dim lastrow As Long
With ActiveSheet
lastrow = .Cells(.Rows.Count, "C").End(xlUp).Row
Set LNID = Workbooks("FILE1").Worksheets("CONSTANTS").Range("I11")

.Range("B2").Value2 = 1
.Range("B3").Value2 = 2
.Range("B2:B3").AutoFill .Range("B2").Resize(lastrow - 1)
End With


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Hi Bob,

This works like clockwork!

Thanks a lot!