entering value without acivating sheet

[RobcPettit]

Hi, Ive a workbook with 5 sheets. For simplicity there named "one" to
"five" as words not numbers. With sheet("one") acive im using
'ThisWorkbook.Sheets("Five").Range("A2:A" &
Range("A65536").End(xlUp).Row) = "TEST2"'. My problem is if the active
sheet is blank this works fine, but if there is say 100 values in say
a1:a100, test2 gets entered 100 times in sheet five. I cant workout
why. Any ideas please.
Regards Robert

 

[GS]

RobcPettit formulated the question :

Hi, Ive a workbook with 5 sheets. For simplicity there named "one" to
"five" as words not numbers. With sheet("one") acive im using
'ThisWorkbook.Sheets("Five").Range("A2:A" &
Range("A65536").End(xlUp).Row) = "TEST2"'. My problem is if the active
sheet is blank this works fine, but if there is say 100 values in say
a1:a100, test2 gets entered 100 times in sheet five. I cant workout
why. Any ideas please.
Regards Robert

You're setting a ref to the target cells (which includes A2:A100), and
assigning a value to every cell in that range. What is it, EXACTLY,
that you want to happen?

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[RobcPettit]

Hi, thanks for reply, Originally what i was doing was trying to count the cells with 'ThisWorkbook.Worksheets("Five").Range("b1:b" & Range("b65536").End(xlUp).Row).Count but this didnt work without the sheet being active. Which is when I tried entering a value in a cell. I was thinking that it wouldonly write a value in the first cell if no values existed. Why does it write values to the same number of cells as active sheet?
Regards Robert

 

[GS]

RobcPettit expressed precisely :

Hi, thanks for reply, Originally what i was doing was trying to count the
cells with 'ThisWorkbook.Worksheets("Five").Range("b1:b" &
Range("b65536").End(xlUp).Row).Count but this didnt work without the sheet
being active.

That's because the 'Range("b63556").End(xlUp).Row).Count' part is
implicitly referencing the active sheet as there's no qualifying ref to
any sheet.

Also, that's not how to find the last used row in a column. Do it like
this...

Dim rng As Range, lLastRow As Long
With ThisWorkbook.Sheets("Five")
lLastRow = .Cells(.Rows.Count, .Columns("B")).End(xlUp).Row
.Range("A2:A" & lLastRow) = "TEST2"
End With

...so if there's 100 rows of data in columnB then "TEST2" will appear in
all cells in "A2:A100"!

Which is when I tried entering a value in a cell. I was
thinking that it would only write a value in the first cell if no values
existed. Why does it write values to the same number of cells as active
sheet? Regards Robert

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[RobcPettit]

Hi, thanks again for your reply. I thought were i put 'ThisWorkbook.Worksheets("Five").Range(etc....)' was referencing the worksheet, now I can see why it was not working. I will have to do a bit of reading in this area. Thankyou for your help
Regards Robert.