Reference Numeric Variable in Macro

[Dave Birley]

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?

 

[Barb Reinhardt]

Try changing the name to myRowCount and see if it likes it any better. I'm
thinking that RowCount is reserved for something else.

 

[Tom Ogilvy]

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).

 

[Dave Birley]

Thanks for the idea, Barb. That doesn't seem to be it. Tom Ogilvie's answer
is more extensive, although I confess that much of it is still beyond my
reach <g>!

Actually I am now using the Variable with Sheet names, so, naturally I have
added extra confusion (just my way -- hoo boy! <g>)
--
Dave
Temping with Staffmark
in Rock Hill, SC

Try changing the name to myRowCount and see if it likes it any better. I'm
thinking that RowCount is reserved for something else.

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 Birley]

Well, something new for me to play with -- didn't know about the Immediate
Window, and when I copied and pasted your little sample there, of course
nothing happened -- until I went to Help and learned that I needed to whack
in an "ENTER" after each line. Then I got it.

Based on your wonderfully comprehensive answer (of which I only truly
understand about 10% at the moment <g>) I think I can hack my through this. I
am now messing with Sheet names, but I understand that the principle is still
the same.
--
Dave
Temping with Staffmark
in Rock Hill, SC

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

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 Birley]

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

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

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?

 

[Tom Ogilvy]

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

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

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

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 Birley]

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

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

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

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


:

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?