Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 5th, 2005, 04:31 AM
Authorized User
 
Join Date: Jun 2005
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
Default Problem with defining cells in Excel VBA

Hi,

I'm trying to fill row 1 with colour but the no. of columns is dynamic. As such, I'm trying to write a code to detect the no. of columns and apply the colour. The program keeps giving me errors at the line in blue but I'm not sure what is wrong. Anyone can give advice?

Dim Range2 As String

Workbooks("QTP.xls").Activate
Worksheets("Trade_Participants").Select
Worksheets("Trade_Participants").Range("A1").Selec t
ActiveCell.CurrentRegion.Select
Range2 = Selection.Columns.Count
Worksheets("Trade_Participants").Cells(2, Range2).Select
Worksheets("Trade_Participants").Range(Selection, Selection.End(xlLeft)).Select
    With Selection.Interior
        .ColorIndex = 36
        .Pattern = xlSolid
    End With

Thanks

 
Old July 5th, 2005, 04:51 AM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 180
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I think I understand your requirements, try this instead...

Dim myCols As Integer
Dim myRows As Integer

Worksheets(1).Select

myCols = Range("IV2").End(xlToLeft).Column
myRows = Range("A65000").End(xlUp).Row

Range(Cells(2, 1), Cells(2, myCols)).Select
Do Until ActiveCell.Row > myRows

    With Selection.Interior
        .ColorIndex = 36
        .Pattern = xlSolid
    End With
    ActiveCell.Offset(1, 0).Select
    myCols = Range("IV" & ActiveCell.Row).End(xlToLeft).Column
    Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(0, myCols - 1)).Select

Loop

cheers

Matt

 
Old July 5th, 2005, 05:05 AM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 180
Thanks: 0
Thanked 0 Times in 0 Posts
Default

the Cells object traditionally should be placed within a range object and you should have two dimensions in your range. In my example above ...
Range(Cells(2, 1), Cells(2, myCols)).Select
I have the starting cell Cell "A2" represented by the Cells(2,1) and the second cell reference Cells(2,mycols) is the dynamic reference according to the last column found on the row.

Is this helpful?

Matt


 
Old July 5th, 2005, 07:32 PM
Authorized User
 
Join Date: Jun 2005
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi, I tried to use the code you suggested but I'm still encountering "Run-time error '1004': Application-defined or object-defined error". What else could be wrong?



 
Old July 6th, 2005, 02:18 AM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 180
Thanks: 0
Thanked 0 Times in 0 Posts
Default

by the looks of it you are executing a module from outside the active workbook object, if this is the case replace the following line in my code...

Worksheets(1).Select

with these lines from your code...

Workbooks("QTP.xls").Activate
Worksheets("Trade_Participants").Select
Range("A1").select

cheers

Matt

 
Old July 6th, 2005, 02:58 AM
Authorized User
 
Join Date: Jun 2005
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi, tried as suggested but still the same error message.



 
Old July 6th, 2005, 04:59 AM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 180
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Can you go into the code and press F8 to step through the code one line at a time. This will tell us which line is failing.

It all works fine on my PC setup.

Matt






Similar Threads
Thread Thread Starter Forum Replies Last Post
Sorting Cells using VBA james_sellwood Excel VBA 7 October 8th, 2012 01:58 PM
Formatting the cells from VBA yogeshyl Excel VBA 1 July 20th, 2007 12:26 AM
Editing Unlocked cells in VBA hems Excel VBA 1 April 16th, 2004 10:07 AM
Editing Unlocked cells in VBA hems VB How-To 0 April 14th, 2004 11:08 AM
Editing Unlocked cells in VBA hems VBScript 1 April 14th, 2004 10:42 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.