Set myrange not being recognized 1

[dstilson]

Hello and thanks in advance for your help. Having troubles using the Set myrange = Worksheets("LineHours16").range("C2:C500"). This is used for lcount which counts the number of rows equal to line value. I had this working yesterday morning, but it's not working anylonger. Here is the code:

Dim rstart, lcount, rvalue, i As Double
Dim ccolrow, pcolrow, line
Dim myRange As Range

Set myRange = Worksheets("LineHours16").Range("C2:C500")

rstart = 3
i = 1


'Insert Column at Column B of LineHours16 worksheet
Columns("B:B").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
'Insert row on row 2 of LineHours16 worksheet
Rows("2:2").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

Do

ccolrow = "C" & rstart
pcolrow = "B" & rstart - 1
line = Worksheets("Line").Range("A" & i)


'Insert a blank line inbetween each change of production line
Range(ccolrow).Select
Selection.Copy
Range(pcolrow).Select
ActiveSheet.Paste
'Count the number of entries for each production line
lcount = Application.WorksheetFunction.CountIf(myRange, line)
'Next Insert Row
rvalue = lcount + rstart

Rows(rvalue).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

rstart = rvalue + 1

i = i + 1

Loop While i <= 49

Columns("C:C").Select
Selection.Delete Shift:=xlToLeft

End Sub

 

[SkipVought]

" 'Insert a blank line inbetween each change of production line"
\\
RED FLAG to me!

It is almost never a good idea to insert empty rows in a perfectly good (we hope) table.

Has you checked out the Data > Subtotal Excel feature?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[dstilson]

Skip,

Yes, I did look at the subtotals, but I don't want to show subtotals, and I will be doing additional calculations to the data. I originally (before Excel 2007) was able to export data from a report that had my formating in place..I am trying to duplicate by importing the data from an Access table and format in Excel.

If Set myrange worked, which it did the previous day, the insert lines worked just fine. Am I not entering the Set properly?

Thanks

 

[electricpete]

I haven't read the logic of the program - but just some suggestions to troubleshot when you get an error at a step such as
Set myRange = Worksheets("LineHours16").Range("C2:C500")

I would go to the immediate window when you get the error and try to figure out which part of that statement is giving you trouble.

Is Worksheets("LineHours16") ok? Try
? Worksheets("LineHours16").name
(should give the name)

Is the range ok?
? Worksheets("LineHours16").Range("C2:C500").address
(should give the address)

Is the variable ok?
Try ? typename(myrange)
(Should be either range or nothing)

 

[electricpete]

and exactly what was the error message displayed?

 

[electricpete]

Some general comments on your declarations (probably not related to your problem

Dim rstart, lcount, rvalue, i As Double
Dim ccolrow, pcolrow, line

The first line: rstart,lcount and rvalue have no type assigned... is that what you intended? If you wanted them all double, then you would have to put "as double" after each one.

Likewise ccolrow, pcolrow, line are variant.

I would think that variables such as rstart and lcount and ccolrow and pcolrow and line (if I understand their meaning right) would be initialized as integers.

 

[electricpete]

'Insert Column at Column B of LineHours16 worksheet
    Columns("B:B").Select
    ETC

You haven't told the program which worksheet you are working in. It will go to whatever worksheet happened to be active when you launched this macro.

You might want to use Worksheets("LineHours16").... end with (if that's what you intended).

(And of course, there are usually other ways to get the job done without selecting anything)

 

[dstilson]

electricpete,

It appears the Set myRange is returning empty. I made Worksheets("lineHours16") the active window and created a msgbox indicating what the active sheet is and LineHours16 is returned. But for some reason the myRange is not working correctly.

I was unaware that you had to Dim rstart lcount, rvalue each had to be "As Double" I thought if AS Double was at the end of a line it applied to all within the line entry.

Thanks

 

[SkipVought]

COPY the Sheet Tab Name. Paste it into the name in your Set statement. Chances are that you have lower case L in one and a numeral ONE in the other.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[electricpete]

I was unaware that you had to Dim rstart lcount, rvalue each had to be "As Double" I thought if AS Double was at the end of a line it applied to all within the line entry.

Nope, you need "as double" for each one. See thread707-1489750 (soon to become a FAQ ?)

It appears the Set myRange is returning empty. I made Worksheets("lineHours16") the active window and created a msgbox indicating what the active sheet is and LineHours16 is returned. But for some reason the myRange is not working correctly.

What error message do you get? The code fragment you posted shouldn't have any problem if it can find the worksheet named "linehours16".

May I suggest for troubleshooting, add the following lines ahead of this step and tell us what the message boxes say?

Dim myRange As Range
[red]'New lines here:[/red]
MsgBox Worksheets("LineHours16").Name
MsgBox Worksheets("LineHours16").Range("C2:C500").Address
Set myRange = ActiveSheet.Range("C2:C500")
MsgBox myRange.Parent.Name
Set myRange = Worksheets("LineHours16").Range("C2:C500")
[red]' continue your code[/red]
End Sub

 

[dstilson]

Skip,

I thought about that one too. So I deleted, copied and paste but still to no avail.

 

[dstilson]

adding the code as electricpete suggested:

Dim myRange As Range

MsgBox Worksheets("LineHours16").Name ‘Returns LineHours16
MsgBox Worksheets("LineHours16").Range("C2:C500").Address ‘Returns $C$2:$C$500
Set myRange = ActiveSheet.Range("C2:C500")
MsgBox myRange.Parent.Name ‘Returns LineHours16
Set myRange = Worksheets("LineHours16").Range("C2:C500")

End Sub

Each Msgbox returns what I expect it to, but not the Set myRange. The code compiles with no errors and if the I let the code run. lcount = Application.Worksheetfunction.CountIf(myRange,line) returns 0. line returns as "CO-01" which is correct.

Is there a reference I need added?

 

[SkipVought]

BOTTOM LINE:

There is absolutely NOTHING wrong with the SYNTAX of the statement...

Set myRange = Worksheets("LineHours16").Range("C2:C500")

NOTHING!

I copied the first 4 lines of code, Copied the Worksheet name and PASTED it into the Sheet Tab RENAME. It runs.

ANY problem you are having, is in YOUR LOGIC, meaning the consistency of the name you are using as the sheet name and the name you are using in this Worksheets object statement.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[dstilson]

Thanks Skip and electricpete,

Worksheets("LineHours16").Activate

rstart = 3
i = 1

'Insert column at column B of LineHours16 worksheet
Columns("B:B").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
'Insert row on row 2 of LineHours16 worksheet
Rows("2:2").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

Set myRange = Worksheets("LineHours16").Range("C2:C500")

Do

By moving the "Set myRange" to just above the Do, the macro works.

Thanks for your input and forgive my ignorance.