Thanks a million -- I had hacked my way to something that works based on your
earlier guidance, but this, of source, is simpler <g!

Thanks so much for being there!!!
--
Dave
Temping with Staffmark
in Rock Hill, SC


"Tom Ogilvy" wrote:

SheetNum = 3
Set WorksheetA = Sheets("SE Part " & SheetNum)
Set NewCell = WorksheetA.Range("D2")
Application.Goto NewCell

or

SheetNum = 3
Set WorksheetA = Sheets("SE Part " & SheetNum)
Set NewCell = WorksheetA.Range("D2")
WorksheetA.Select
NewCell.Select

--
Regards,
Tom Ogilvy



"Dave Birley" wrote:

Ok, here's what I'm actually trying to do -- copied from the Immediate Window:

SheetNum = 3
Set WorksheetA = Sheets("SE Part " & SheetNum)
Set NewCell = WorksheetA.Range("D2")
Range(NewCell).Select

It's happier than a pig in poop with each line except the last one. That
delivers Run-time error '1004', Application-defined or object-defined error.

So what is the correct format for my Select expression?
--
Dave
Temping with Staffmark
in Rock Hill, SC


"Tom Ogilvy" wrote:

there is nothing wrong with the syntax

NewCell(RowCount, 4).FormulaR1C1 = "=RC[-3]"

but NewCell is still empty when you try to use it and that is problematic.

You need to set it to something first. Then newCell(rowcount,4) would be an
offset of that location - hard to tell if that is what you want or not.

do demo from the immediate window:

set newcell = Range("A7125")
rowcount = 10
? newcell(rowcount,4).Address
$D$7134

I suspect at the top of your code after you declarations you want to do

set newcell = Range("A7125")

then when rowcount = 1, you will refer to D7125
as an example.

also, a declaration like

Dim mycell, newcell as Range
declares mycell as variant and newcell as range

you have to declare the type for each variable. (variant can hold a range,
so it doesn't cause a problem here).

--
Regards,
Tom Ogilvy


"Dave Birley" wrote:

Although the original purpose of this macro is better done in
Data/Filter/Unique, I still need to know how to reference that numeric
variable within the macro as I need to write a new one doing something
different, but also using a numeric variable.

I thought this might work:

Sub ConsolidateRows()
Dim myCount, RowCount As Integer
Dim myCell, NewCell As Range
myCount = 0
RowCount = 7125
For Each myCell In Range("A7125", Range("A65536").End(xlUp))
With myCell
If .Value & .Offset(0, 1).Value & .Offset(0, 2).Value < .Offset(1,
0).Value & .Offset(1, 1).Value & .Offset(1, 2).Value Then
NewCell(RowCount, 4).FormulaR1C1 = "=RC[-3]"
NewCell(RowCount, 5).FormulaR1C1 = "=RC[-4]"
NewCell(RowCount, 6).FormulaR1C1 = "=RC[-5]"
myCount = 0
RowCount = RowCount + 1
Else
myCount = myCount + 1
End If
End With
Next myCell
End Sub

...but, of course, it doesn't like the call to RowCount in

NewCell(RowCount, 4).FormulaR1C1 = "=RC[-3]"

Am I close?

--
Dave
Temping with Staffmark
in Rock Hill, SC