Solved Setting the Range in VBa

December 20, 2013 at 09:01:25
Specs: Windows 7
    colNum = Sheets("SheetA").Range("A33")
        ColNumA = Cells(3, colNum - 5)
            ColNumB = Cells(51, colNum + 2)
 
' F3:L51 info only telling me the cells
    AutoSort_V3 Range(ColNumA, ColNumB)

Public Sub AutoSort_V3(sortRange As Range) is the Public Sub.
What I am trying to do is to replace many AutoSort_V3 Range("Cell:Cell") with a single instruction that generates from the colNum

AutoSort_V3 Range(ColNumA, ColNumB) Object doesn't support this Property or Method error occurs

Have I input my coding incorrectly or is this format not possible


See More: Setting the Range in VBa

Report •

✔ Best Answer
December 22, 2013 at 09:06:16
    colNum = Sheets("Sheet1").Range("A33")
    
  AutoSort_V3 Range(Cells(3, colNum - 5), Cells(51, colNum + 1))
    
    Sheets("Sheet2").Range(Cells(3, colNum - 5), Cells(52, colNum + 1)).Copy
    
    With Sheets("Sheet3").Cells(3, colNum - 5)
      .PasteSpecial Paste:=xlPasteValues
      .PasteSpecial Paste:=xlPasteFormats
      
    End With
    Application.CutCopyMode = False

Hi there, have been messing around with this and I have finally found the solution. The above bit of code does exactly what I require.
From your comments, I was going down the wrong route with the type of code I was trying to use. I needed to determine the cell name/location, ie F3 and L51, to determine the Range to be sorted, then copied and then pasted.
As you stated I was comimg up with a value that served no purpose as to what I required.
Many thanks for putting me in the right direction with this.



#1
December 20, 2013 at 20:16:19
Since I don't have your full code or workbook, I can't duplicate your error, but something doesn't look right to me.

Are colNumA and colNumB numbers, e.g. 47 and 55? If so, then isn't

AutoSort_V3 Range(ColNumA, ColNumB)

referring to Range (47,55)? That's not a valid syntax for the Range property, so it's going to give you an error, probably an "Run-time error 1004 Application-defined or object-defined error"

Assuming that colNumA and colNumB are numbers, I was able to get this to work, but I really don't know if I am dealing with your issue or not.

Sub JustTesting()
  colNum = Sheets("SheetA").Range("A33")
        ColNumA = Cells(3, colNum - 5)
        ColNumB = Cells(3, colNum + 2)
 
    AutoSort_V3 Cells(ColNumA, ColNumB)
End Sub

Public Sub AutoSort_V3(sortRange As Range)
'Place the string "myString" in whatever cell
'Cells(ColNumA, ColNumB) refers to. e.g. Cells(47,55)
   Range(sortRange.Address) = "myString"
End Sub

Now, if colNumA and colNumB are supposed to be the arguments for the Cells property like I used them, then using a variable that looks like it should be referring to a Column (colNumA) as the Row argument can be confusing.

Again, I'm not even sure if I am addressing your actual problem. You may need to provide a little more information, such as what values are being assigned to colNumA and colNumB by the first part of your code.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
December 21, 2013 at 08:19:10
Hi DerbyDad03,

colNum = Sheets("SheetA").Range("A33")
ColNumA = Cells(3, colNum - 5)
ColNumB = Cells(51, colNum + 2)

colNum =11, therefore ColNumA = F3
ColNumB = L51

I can get the autosort to work by doing the following
Set ColNumA = Cells(, colNum - 5), and
Set ColNumB = Cells(51, colNum + 2)

However I am also trying to perform this following bit of code

    Sheets(Sheet3).Range(ColNumA, ColNumB).Copy
    
    With Sheets(Sheet4).Range(ColNumA)
      .PasteSpecial Paste:=xlPasteValues
      .PasteSpecial Paste:=xlPasteFormats
      
    End With
    Application.CutCopyMode = False

This now throws up an error. Cant see what is wrong with this. Any ideas


Report •

#3
December 21, 2013 at 15:51:04
First, telling us that "this now throws up an error" doesn't help much since you posted 6 lines of code without telling us which one throws up the error. Tell us precisely which line is throwing up the error.

Second, you said: colNumA = F3 and colNumB = L51. Unless I'm mistaken, those variables will equal the contents of F3 and L51. That's very different than being equal to F3 and L51. Did you set a watch on those variables to see exactly what they are equal to?

I'll repeat what I said earlier: if colNumA and colNumB are numbers, I don't think this line will ever work:

Range(colNumA, colNumB)

As far as I know the Range property will not accept numbers as arguments, only the Cells property will.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

Related Solutions

#4
December 22, 2013 at 09:06:16
✔ Best Answer
    colNum = Sheets("Sheet1").Range("A33")
    
  AutoSort_V3 Range(Cells(3, colNum - 5), Cells(51, colNum + 1))
    
    Sheets("Sheet2").Range(Cells(3, colNum - 5), Cells(52, colNum + 1)).Copy
    
    With Sheets("Sheet3").Cells(3, colNum - 5)
      .PasteSpecial Paste:=xlPasteValues
      .PasteSpecial Paste:=xlPasteFormats
      
    End With
    Application.CutCopyMode = False

Hi there, have been messing around with this and I have finally found the solution. The above bit of code does exactly what I require.
From your comments, I was going down the wrong route with the type of code I was trying to use. I needed to determine the cell name/location, ie F3 and L51, to determine the Range to be sorted, then copied and then pasted.
As you stated I was comimg up with a value that served no purpose as to what I required.
Many thanks for putting me in the right direction with this.


Report •

#5
December 23, 2013 at 10:22:36

Report •

Ask Question