Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Apr 2002
    Location
    Busselton, Western Australia, Australia
    Posts
    59
    Thanks
    1
    Thanked 2 Times in 2 Posts

    Unhappy Coding Problem (Excel 2010 VBA)

    Hi All,
    Having a brain fart moment and can not figure out the best way of coding the following issue in VBA.
    Problem:
    A work sheet has a 'Month' laid out in a grid with each day having 8 cells worth of data associated with the 'day'.
    I have specified a series of Const values that specify the location of each 'Day' of the month.
    I then want to be able to use the 'Const' value in code to prevent losing track of any changes to the cell locations later on, i.e. only one place to update the cell location rather than multiple places.

    The loop code in the cub routine builds the name of the Constant I have specified without a problem, but obviously when it comes to using the 'built up' value in the range(xxx) code, it fails since the Range(xxx) command requires either a "B7" or a cells address entry and not the name of the identifier that is being used to hold the address.

    My Question is thus:
    How do I get the value of the constant via a built up name like I am doing?

    Thanks for any answers.
    Regards
    Brian.

    What I have coded so far:

    'Cell locations for data for machine monthly Report
    Const Day1Cell As String = "B7"
    Const Day2Cell As String = "D7"
    Const Day3Cell As String = "F7"
    Const Day4Cell As String = "H7"
    Const Day5Cell As String = "J7"
    Const Day6Cell As String = "L7"
    Const Day7Cell As String = "N7"
    Const Day8Cell As String = "B13"
    Const Day9Cell As String = "D13"
    Const Day10Cell As String = "F13"
    Const Day11Cell As String = "H13"
    Const Day12Cell As String = "J13"
    Const Day13Cell As String = "L13"
    Const Day14Cell As String = "N13"
    Const Day15Cell As String = "B19"
    Const Day16Cell As String = "D19"
    Const Day17Cell As String = "F19"
    Const Day18Cell As String = "H19"
    Const Day19Cell As String = "J19"
    Const Day20Cell As String = "L19"
    Const Day21Cell As String = "N19"
    Const Day22Cell As String = "B25"
    Const Day23Cell As String = "D25"
    Const Day24Cell As String = "F25"
    Const Day25Cell As String = "H25"
    Const Day26Cell As String = "J25"
    Const Day27Cell As String = "L25"
    Const Day28Cell As String = "N25"
    Const Day29Cell As String = "B31"
    Const Day30Cell As String = "D31"
    Const Day31Cell As String = "F31"


    Sub ClearSheet()
    Dim i As Integer
    Dim sDayNumber As String

    Worksheets("Machine Report").Activate
    For i = 1 To 31
    sDayNumber = "Day" & Trim(Str(i)) & "Cell"

    Range(sDayNumber).Offset(1, 0).Value = 1
    'gives Range("sDay1Number").Offset(1, 0).Value = 1 rather than Range(day1cell).Offset(1, 0).Value = 1 which would resolve to Range("B7").Offset(1, 0).Value = 1
    Range(sDayNumber).Offset(2, 0).Value = 2
    Range(sDayNumber).Offset(3, 0).Value = 3
    Range(sDayNumber).Offset(4, 0).Value = 4
    Range(sDayNumber).Offset(1, 1).Value = 5
    Range(sDayNumber).Offset(2, 1).Value = 6
    Range(sDayNumber).Offset(3, 1).Value = 7
    Range(sDayNumber).Offset(4, 1).Value = 8

    Next

    End Sub

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts
    Hi BRoby

    I admire your tenacity in defining 31 string constants!
    If only vba had the equivalent of the INDIRECT function!

    However, a simpler solution is attached..
    All it requires is a single named cell to define your start position on the sheet.
    As you insert columns and rows etc, your named start position will adjust automatically.
    In the attached file, I assigned the range name day1Cell to the start cell position.

    From that location, vba can use Offset to move down and across relative to this start position.
    And we can use the vba Resize option to specify a block size rows x columns to work with.

    In my file, I give 2 buttons: one will set all day values 1 to 8, the other button will clear all entries.

    Let me know if this does what you want.

    zeddy
    Attached Files Attached Files

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    BRoby,

    You could have also done this using an array variable such as day(K) to hold the cell addresses and then cycling through the index K to store and display your values. I kept the numbers 1-8 in the same format as you must have coded them in that order for a reason.

    HTH,
    Maud

    CodingProblem.png

    Code:
    Sub LoadSheet()
    'DECLARE VARIABLES
    Dim i As Integer
    Dim sDayNumber As String
    Dim day(31) As Variant
    '-------------------------------
    'SET ARRAY VARIABLE day(k)
    k = 1
    For i = 7 To 31 Step 6  'ROWS
        For j = 2 To 14 Step 2  'COLUMNS
            day(k) = Cells(i, j).Address  'LOAD ARRAY VALUES
            Range(day(k)).Value = day(k)  'OPTIONAL ADDRESS DISPLAY
            If k = 31 Then Exit For
            k = k + 1
        Next j
    Next i
    '-------------------------------
    'INSERT VALUES ON SHEET IN UNCHANGED FORMAT
    Worksheets("Machine Report").Activate
    For i = 1 To 31
     Range(day(i)).Offset(1, 0).Value = 1
     Range(day(i)).Offset(2, 0).Value = 2
     Range(day(i)).Offset(3, 0).Value = 3
     Range(day(i)).Offset(4, 0).Value = 4
     Range(day(i)).Offset(1, 1).Value = 5
     Range(day(i)).Offset(2, 1).Value = 6
     Range(day(i)).Offset(3, 1).Value = 7
     Range(day(i)).Offset(4, 1).Value = 8
    Next
    
    End Sub
    Attached Files Attached Files

  4. #4
    Star Lounger
    Join Date
    Apr 2002
    Location
    Busselton, Western Australia, Australia
    Posts
    59
    Thanks
    1
    Thanked 2 Times in 2 Posts
    Thanks Zeddy and Maud,
    Two great solutions for my problem and both rather different, but both work great.
    Def have some food for thought on how I will resolve the issues involved with this project.
    Never ceases to amaze me how many different ways Excel+VBA can solve a problem.
    Regards
    Brian.

  5. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    You're welcome bro.....by

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •