How to setup the ROW number as a VARIABLE within a RANGE

**nenadmail** · 08-13-2012, 05:27 AM

This is part of my code here:

    Dim ord As Range
    Dim pub As Range
    Dim prid As Variant
    Set xyz = Rows(13)
    Set ord = Sheets("Image Catalog").Range(xyz, ("AA:AF"))
    Set pub = Sheets("Image Catalog").Range(xyz, ("AD"))
    Set prid = Sheets("Image Catalog").Range(xyz, ("AC"))

I want the ROW NUMBER to be referenced as "xyz" in my code.
But DEBUG error comes up and highlights THIS portion when the MACRO is run.

    Set pub = Sheets("Image Catalog").Range(xyz, ("AD"))

I think it's because THIS part is written wrong:

    Range(xyz, ("AD"))

I don't know how to correct it!

Thanks!

**MaczaQ** · 08-13-2012, 05:38 AM

hi,
try one from below examples

with Sheets("Image Catalog")
Set ord = intersect(.rows(xyz), .Range("AA:AF"))
end with

or

Set ord = Sheets("Image Catalog").Range("AA" & xyz & ":AF" & xyz)

**nenadmail** · 08-13-2012, 05:58 AM

Ok so I tried this:

    Dim ord As Range
    Dim pub As Range
    Dim prid As Variant
    Set xyz = Rows(13)
    With Sheets("Image Catalog")
    Set ord = Intersect(.Rows(xyz), .Range("AA:AF"))
    End With
    Set pub = Sheets("Image Catalog").Range("AD" & xyz)
    Set prid = Sheets("Image Catalog").Range("AC" & xyz)

And it brings a DEBUG error and highlights: Set ord = Intersect(.Rows(xyz), .Range("AA:AF"))

I also tried this:

    Dim ord As Range
    Dim pub As Range
    Dim prid As Variant
    Set xyz = Rows(13)
    Set ord = Sheets("Image Catalog").Range("AA" & xyz & ":AF" & xyz)
    Set pub = Sheets("Image Catalog").Range("AD" & xyz)
    Set prid = Sheets("Image Catalog").Range("AC" & xyz)

And in the DEBUG error it highlights this: Set ord = Sheets("Image Catalog").Range("AA" & xyz & ":AF" & xyz)

I don't get it... what's wrong?

**patel45** · 08-13-2012, 06:25 AM

explain better what do you want, attach a sample

**jason.b75** · 08-13-2012, 06:35 AM

Some of your declaration types are wrong,

Try

Dim ord As Range, pub As Range, prid As Range, xyz As Long
    xyz = 13
    With Sheets("Image Catalog")
        Set ord = Intersect(.Rows(xyz), .Range("AA:AF"))
    End With
    Set pub = ord(1,3)
    Set prid = ord(1,4)

or an alternative would be

Dim ord As Range, pub As Range, prid As Range, xyz As Range
    With Sheets("Image Catalog")
        Set xyz = .Range("13:13")
        Set ord = Intersect(xyz, .Range("AA:AF"))
        Set pub = Intersect(ord, .Range("AC:AC"))
        Set prid = Intersect(ord, .Range("AD:AD"))
    End With

**nenadmail** · 08-13-2012, 06:47 AM

OK I figured it out....

I got it to work this way (referencing the row number to a cell "AD1") Which is an even better solution for my project

!!

    Dim ord As Range
    Dim pub As Range
    Dim prid As Variant
    Dim xyz As Variant
    Set xyz = Sheets("Image Catalog").Range("AD1")
    Set ord = Sheets("Image Catalog").Range("AA" & xyz & ":AF" & xyz)
    Set pub = Sheets("Image Catalog").Range("AD" & xyz)
    Set prid = Sheets("Image Catalog").Range("AC" & xyz)

AND the "AD1" has a MATCH formula looking for the product id and identifying what row it's in:

'The +4 is there because the product IDs begin in row 5
      =MATCH(AC1;AC5:AC500;0)+4

So looks like your code was fine! The problem was in the original: Set xyz = Rows(13)
That's what it didn't like.

Thanks for your help!!
The important thing is it works great now!