Excel Macro Code Help

ASP Free Forums Sponsor:

July 2nd, 2009, 05:35 AM

TMD

Registered User

Join Date: Jul 2009

Posts: 1

Time spent in forums: 4 m 41 sec
Reputation Power: 0

Excel Macro Code Help

Hi there,

I'm trying to make a very simple excel macro but I'm struggling with range variables. This is the code i'm using:

vb Code:

Original - vb Code

Sub Process_CheckBox()
'
' Final Macro
 
'
    Dim cBox As CheckBox
    Dim LRow As Integer
    Dim LRange As String
    Dim Rone As String
    Dim Rtwo As String
    
 
    LName = Application.Caller
    Set cBox = ActiveSheet.CheckBoxes(LName)
 
    'Find row that checkbox resides in
    LRow = cBox.TopLeftCell.Row
    LRange = "H" & CStr(LRow)
    Rone = "B" & CStr(LRow)
    Rtwo = "H" & CStr(LRow)
    
   
 
    'Change date in column B, if checkbox is checked
    If cBox.Value > 0 Then
        Range("B4:H4").Select
    With Selection.Interior
        .ColorIndex = 3
        .Pattern = xlSolid
    End With
    'Clear date in column B, if checkbox is unchecked
    Else
        Range("B4:H4").Select
    Range("B4:H4").Activate
    Selection.Interior.ColorIndex = xlNone
    End If
 
End Sub

The range never change, just the row number. I thought i could have two variables, Rone and Rtwo, make then into references like B5 and H5 and then use them like: Range("Rone:Rtwo").Select

but that doesn't work.

where am i going wrong?

Moderator's Note: Syntax highlighting added by Nilpo

Last edited by Nilpo : July 16th, 2009 at 08:28 AM.

July 2nd, 2009, 05:39 AM

sync_or_swim

Moderator

Join Date: Mar 2006

Location: South Wales

Posts: 3,461

Time spent in forums: 2 Months 1 Day 16 h 50 m 26 sec
Reputation Power: 1806

--> Moved to VB Forum

Hi,

I've not had any experience with this but I would imagine that you need to concatenate your values in some way, try something like this:

Code:

Range(Rone & ":" & Rtwo).Select

Or you may need to surround the values with double quotes:

Code:

Range(""" & Rone & ":" & Rtwo & """).Select

Comments on this post

June7 agrees!

July 16th, 2009, 04:09 AM

June7

Contributing User

Join Date: Apr 2009

Location: The Great Land

Posts: 536

Time spent in forums: 1 Week 13 h 58 m 7 sec
Reputation Power: 124

Yes, you will need to concatenate. Here are some examples of using the Range, Cells, Columns objects/properties:
This one uses Range with a variable for the bottom row of range

Code:

'Sort records on Results sheet so they will be in Date then Point then LabNum order for the GEOReport
Worksheets("Results").Range("3:" & intResults + 2).Sort _
    Key1:=Worksheets("Results").Range("AO3"), Order1:=xlAscending, _
    Key2:=Worksheets("Results").Range("A3"), Order2:=xlAscending, _
    Key3:=Worksheets("Results").Range("B3"), Order3:=xlAscending, _
    Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:=xlSortNormal

This one uses Cells with variable for the row and index for the column. Am considering changing this to use Range.

Code:

With Worksheets("Results")
.Cells(intNCount, 1).Value = rsData.Fields("SampledFrom")
.... about 40 similar lines
End With

Here is Columns

Code:

xlApp.Worksheets("Sheet1").Columns("B").ColumnWidth = 12

Last edited by June7 : July 16th, 2009 at 04:29 AM.

July 16th, 2009, 08:29 AM

Nilpo

Shaolin Scripter!™

ASP Free Intermediate (1500 - 1999 posts)

Join Date: Jun 2006

Location: Salem, OH

Posts: 1,880

Folding Points: 214558 Folding Title: Super Ultimate Folder - Level 1

Time spent in forums: 1 Week 2 Days 8 h 47 m 8 sec
Reputation Power: 967

Thread moved to Windows Scripting.

__________________

Don't like me? Click it.

Scripting problems? Windows questions? Ask the Windows Guru!

Stay up to date with all of my latest content. Follow me on Twitter!

Help us help you! Post your exact error message with these easy tips!

Viewing: ASP Free Forums > System Administration > Windows Scripting > Excel Macro Code Help

« Previous Thread | Next Thread »

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump

Free IT White Papers!

How to Present Effectively Online
This white paper offers practical and actionable advice on the key steps that any presenter should consider as they plan and execute a Webinar or online meeting.

Open Source Security Myths
Open Source Software (OSS) is computer software whose source code is available to the general public with relaxed or non-existent intellectual property restrictions (or arrangement such as the public domain), and is usually developed with the input of many contributors.

Power and Cooling Capacity Management for Data Centers
This paper describes the principles for achieving power and cooling capacity management.

Scalable, Fault-Tolerant NAS for Oracle - The Next Generation
For several years NAS has been evolving as a storage alternative for Oracle databases, and for good reason: NAS is quite often the simplest, most cost-effective storage approach for Oracle. Learn about the benefits that HP's approach to scalable NAS brings to Oracle environments in this comprehensive white paper.

Understanding Web Application Security Challenges
This white paper discusses many common threats and preventive measures for Web application security, and explains what you can do to help protect your organization.

More Free IT White Papers!