Re: Range as cell numbers

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Since I don't use Access, I'm glad your noticing my having used Set helped you solve your problem as I wouldn't have what to tell you otherwise. For your future consideration, the Set keyword is always used when assigning an object reference to a variable or property.

Rick


"Ian" <me@xxxxxx> wrote in message news:QGD2j.60438$Eq.39404@xxxxxxxxxxxxxxxxxxxxxxx
Hi Rick

The code is running under Access VBA. The Excel application is defined as objExcel and, as you suggested there is a preceding With statement.

The problem I have with your suggested code the first line. Under Access I can not dimension as a range.

I've managed to get it working with:

Dim objRange as Object
Set objRange = objExcel.range(.Cells(3, lngMaxColum), .Cells(lngMaxRow, lngMaxColumn))

Thanks for the pointer, I hadn't thought to use Set.

--
Ian
--
"Rick Rothstein (MVP - VB)" <rickNOSPAMnews@xxxxxxxxxxxxxxxxx> wrote in message news:%23cDEHTEMIHA.1204@xxxxxxxxxxxxxxxxxxxxxxx
What is the 'dot' doing in front of the keyword Cells? Do you have a With statement somewhere?

Anyway, to see that you can create a range using Resize, copy/paste and then run the following code...

Sub Test()
Dim R As Range
Set R = Cells(1, 1).Resize(10, 10)
R.Value = "I'm yellow."
R.Interior.Color = vbYellow
End Sub

It will fill the first 10 columns and rows with the words "I'm yellow" and make the cell's interiors yellow.

Rick



"Ian" <ic2@xxxxxxxxxxxxxxxxxx> wrote in message news:K8y2j.36032$dN2.23443@xxxxxxxxxxxxxxxxxxxxxxx
Hi Rick

Is it possible to create a range variable from Resize?

strRange = .Cells(1,1).Resize(lngMaxRow, lngMaxColumn) gives Type mismatch.

Alternatively, how could I achieve this using Cells, rather than Range

strRange = .Range(.Cells(1, 1), .Cells(lngMaxRow, lngMaxColumn)) also gives Type mismatch


"Rick Rothstein (MVP - VB)" <rickNOSPAMnews@xxxxxxxxxxxxxxxxx> wrote in message news:%23Bnrd6fLIHA.4196@xxxxxxxxxxxxxxxxxxxxxxx
You can use Resize to create the range you want. I would think this will do what you want...

objExcel.Range("A1").Resize(lngMaxRow, 14).Sort Key1:="Date", Header:=xlYes

You could use Cells(1, 1) in place of the Range("A1")... this would allow you to use variables in order to specify the initial cell from which you perform the resize instead.

Rick


"Ian" <ic2@xxxxxxxxxxxxxxxxxx> wrote in message news:SeE1j.58825$Eq.47834@xxxxxxxxxxxxxxxxxxxxxxx
I have this line of code and I want to remove the hard-coded "N" and replace it with the last column (lngMaxColumn retruned by SpecialCells).

objExcel.range("A1:N" & lngMaxRow).Sort Key1:="Date", Header:=xlYes

I have a routine which turns the column number into a letter, but I'd rather use the column number if possible. I know I can change Range("N1") to Cells(14,1), but how do you specify a range of cells in this way?

I've tried

With objExcel
.range(.cells(1, 1), .cells(lngMaxRow, lngMaxColumn)).Sort Key1:="Date", Header:=xlYes
End With

but this returns "Run-time error '1004': Application defined or object defined error" and appears to be to do with the .cells references.

Excel VBA Help gives Range(Cells(1, 1), Cells(5, 3)).Font.Italic = True as an example, but I can't figure out how to adapt this to late-bound code. It needs to be late-bound as it's running under Access.

Any ideas?

Ian








.

Relevant Pages

  • Re: Range as cell numbers
    ... the Set keyword is always used when assigning ... lngMaxColumn)) ... Is it possible to create a range variable from Resize? ... Alternatively, how could I achieve this using Cells, rather than Range ...
    (microsoft.public.excel.programming)
  • Re: Range as cell numbers
    ... objExcel and, as you suggested there is a preceding With statement. ... lngMaxColumn)) ... Is it possible to create a range variable from Resize? ... Alternatively, how could I achieve this using Cells, rather than Range ...
    (microsoft.public.excel.programming)
  • Re: Range as cell numbers
    ... Is it possible to create a range variable from Resize? ... strRange = .Cells.Resizegives Type mismatch. ... Alternatively, how could I achieve this using Cells, rather than Range ... replace it with the last column (lngMaxColumn retruned by SpecialCells). ...
    (microsoft.public.excel.programming)
  • Re: Column resizing problems in table -- help??
    ... > In the default template I created, the cells are just wide enough for the ... > If I manually resize the column to fit the text, ...
    (microsoft.public.word.pagelayout)
  • Column resizing problems in table -- help???
    ... rows of cells contain identifying text for what's in that particular table. ... If I manually resize the column to fit the text, ...
    (microsoft.public.word.docmanagement)