Reference Numeric Variable in Macro

G

Guest

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?
 
G

Guest

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

Guest

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

Guest

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


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

Dave Birley said:
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?
Click to expand...
Click to expand...
 
G

Guest

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


Tom Ogilvy said:
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 said:
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?
Click to expand...
Click to expand...
 
G

Guest

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 said:
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 said:
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?
Click to expand...
Click to expand...
 
G

Guest

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 said:
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 said:
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 said:
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?
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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 said:
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 said:
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 said:
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?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Odd macro behavior 6
Anyone speak Japanese excel? 1
how to: c.value = "a cell value" 5
Data written to wrong cells 2
how to generate date error message 5
Efficiency of Code Help 4
Changing rows 2
Merge data from two temp files into a master 1

Top