Setting cell as a named range

[KarenGB]

Hi

Hopefully this is a simple thing to fix.
Been looking at this all morning and can't work out what I've done.

This is the beginning of a bit of code to go through a column of data, changing each cell to be 100% and then when it's copied and pasted it to another sheet, set it to 0% and the next cell to 100%.

Excel is coming up with a debugging error on the Range(PCT).Select part. Im sure I'm missing something entirley obvious.

Thanks for your help

Sub ExtractAll()
Dim PCT As Range, NextRow As Range
For x = 13 To 202
'go through population sheet
Set PCT = Range("D" & x)

Range(PCT).Select
ActiveCell.FormulaR1C1 = "100%"

'copy data
Sheets("Summary").Select
Range("2:2").Copy

'paste
Sheets("Compile").Select

NextRow = Range("A65536").End(xlUp).Row + 1
Range("A" & NextRow).Select

ActiveSheet.Paste
​

 

[njimack]

Replace:

Set PCT = Range("D" & x)

Range(PCT).Select
ActiveCell.FormulaR1C1 = "100%"

With:

Cells(x,4)="100%"

 

[Weaver]

The actual error was

Range(PCT).Select

if PCT is already a range, then

PCT.Select

is the correct form

However, njimack's suggestion is by far the preferred way to achieve the result you're after, as 'select'ing cells is not usually necessary.

 

[njimack]

Also, you could/should replace the following code:

'copy data
Sheets("Summary").Select
Range("2:2").Copy
 
'paste
Sheets("Compile").Select
 
NextRow = Range("A65536").End(xlUp).Row + 1
Range("A" & NextRow).Select
 
ActiveSheet.Paste
<!-- / message -->

with:

NextRow = Sheets("Compile").Range("A65536").End(xlUp).Row + 1
 
Sheets("Summary").Range("2:2").Copy Sheets("Compile").Range("A" & NextRow)

 

[KarenGB]

Thanks njimack and weaver

Looks like I've made my code far more complicated than it needs to be

 

[sous2817]

Do you even need to loop? Doesn't:

Range("D13:D202").FormulaR1C1 = "100%"

do the same thing as the loop?

 

[KarenGB]

I need to do 100% separately on each row for a location, copy the data that's calculated on another sheet and paste. Then go to the next location listed and repeat.
I'm having to use a government spreadsheet as a base so it's not the best.

 

[sous2817]

Well whatever works best for your situation. My point was that

Range("D13:D202").Value = "100%"

should give comparable results to:

For x = 13 to 202
     Cells(x,4)="100%"
next i

and avoiding the loop is more efficient. If the loop is part of a larger process, then it's hard to say if it's necessary without seeing your whole code.

Either way, seems your situation is fixed and ultimately that's the main thing.

 

[KarenGB]

Ah, I thought that the code would have set all rows to be 100% at the same time.

Well whatever works best for your situation.
My point was that

Range("D13:D202").Value = "100%"

should give comparable results to:

For x = 13 to 202
     Cells(x,4)="100%"
next i

and avoiding the loop is more efficient. If the loop is part of a larger process, then it's hard to say if it's necessary without seeing your whole code.

Either way, seems your situation is fixed and ultimately that's the main thing.

 

[sous2817]

Well it does, which doesn't seem to be your intent . Foot meet mouth...