Working with a range (Excel 2002)

Topic

New Reply

Hello all,

It’s been a long long time but I’m once again working with Excel programming and I find myself back here.

I’m exporting data from a program, SalesLogix, to Excel and I’m having trouble formatting a range.

This code works:
objExcel.Range(“A” & intRow & “:D” & intRow).Interior.colorIndex = 1
objExcel.Range(“A” & intRow & “:D” & intRow).Interior.Pattern = xlSolid
objExcel.Range(“A” & intRow & “:D” & intRow).Merge

But I need to be able to replace the column reference D with a variable, and I don’t know how I would do that?
I need to be able to select a Range so I can merge the cells.(Or center across selection)

Right now I have two integers, intCol and intRow.
This works, but it isn’t a range:
objExcel.Cells(intRow, intCol).Interior.colorIndex = 1
objExcel.Cells(intRow, intCol).Interior.Pattern = xlSolid

I can’t figure out how to use the variables to select a Range.
objExcel.Range(Cells(1, 1), Cells(3, 4)).Merge
Doesn’t work.

Any idea’s?

Thank you,
Justin K.

Reply | Quote

Replies

Welcome back!

From your code it appears that you are automating Excel from another program. If so, you must qualify ALL Excel objects. In the line

objExcel.Range(Cells(1, 1), Cells(3, 4)).Merge

Range belongs to objExcel but both occurrences of Cells don’t belong to anything. This causes confusion. It should be

objExcel.Range(objExcel.Cells(1, 1), objExcel.Cells(3, 4)).Merge

With your variables:

objExcel.Range(objExcel.Cells(intRow, 1), objExcel.Cells(intRow, intCol)).Merge

Reply | Quote

Hello,

I had forgotten how fast you work around here. Thanks! It of course worked.

Now I’m having an issue centering the cells after I’ve merged them.

objExcel.Range(objExcel.Cells(intRow, 1), objExcel.Cells(intRow, intCurrentMaxCol)).HorizontalAlignment = xlCenter
objExcel.Range(objExcel.Cells(intRow, 1), objExcel.Cells(intRow, intCurrentMaxCol)).Merge
objExcel.Range(objExcel.Cells(intRow, 1), objExcel.Cells(intRow, intCurrentMaxCol)).HorizontalAlignment = xlCenter

Just to be on the safe side I tried centering it before and after the merge, as well as separately. When recording a macro in Excel this is the property it uses, but it doesn’t appear to be working when passed in from another program?

Thanks again,
Justin K.

Reply | Quote

Have you set a reference to the Microsoft Excel 10.0 Object Library? If not, xlCenter is unknown. If you require all variables and constants to be declared explicitly, this would cause an error message, but if you don’t, VBA will assume that xlCenter is a new variable with value 0, which is obviously not what you want. Try replacing xlCenter with its value -4108.

BTW I don’t like merged cells in Excel – they cause problems when you try to copy/paste, among other things. You can get the same visual effect off merge and center without the disadvantages by using

objExcel.Range(objExcel.Cells(intRow, 1), objExcel.Cells(intRow, intCurrentMaxCol)).HorizontalAlignment = 7

where 7 is the value of xlCenterAcrossSelection.

Reply | Quote

Hello,

Once again that did it, using the value worked. Where would I be able to find these values out on my own?
Also, if I needed to, how would I set a reference to the Microsoft Excel 10.0 Object Library?

I agree with you about the merged cells, but that’s not what was asked for. I’ll have to see if I can change the design specs.

Thanks again!
Justin K.

Reply | Quote

You can find the value of Excel-specific constants in the Visual Basic Editor in Excel itself.

Method 1:
– Press Ctrl+G to activate the Immediate window.
– Type ? xlCenter
– Press Enter.

Method 2:
– Press F2 to activate the Object Inspector.
– Type xlCenter in the Search box.
– Press Enter.

You can set a reference by ticking the corresponding check box in Tools | References… in the Visual Basic Editor.

Reply | Quote

Hello,

Thanks again for all of your help. Hopefully now I can move forward without having to bother you every few hours.

Justin K.

Reply | Quote

It’s best of course if you can move forward yourself, but don’t hesitate to ask questions if necessary – that’s what we’re here for!

Reply | Quote