Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Parent.Column (Excel 2003)

    I'm having problems with the following code. I get an object error on the Response line where I get to determine the header for that column.

    Public Sub Outlook_Calendar()

    Dim oCell As Range
    Dim Response As String

    Set oCell = Sheets("Calender").Range("B2")
    For Each oCell In Sheets("Calender").Range("B2:G400")
    If oCell.Value <> "" Then
    Response = Cells(1, oCell.Parent.Column)
    MsgBox Response
    End If
    Next

    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Parent.Column (Excel 2003)

    You must use oCell.Column instead of oCell.Parent.Column. (The Parent of oCell is the worksheet)

    Note: the line

    Set oCell = Sheets("Calender").Range("B2")

    can be removed, it is superfluous.

  3. #3
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parent.Column (Excel 2003)

    Thank you, another question.

    When I'm giving through my range of values I want to ignore cells that have the text "Contact" or "Supplier" or "Etc.1" or "Etc.2". What is the best way to do this? I don't want to create multiple if statements.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Parent.Column (Excel 2003)

    You could use a Select ... End Select block:
    <code>
    Select Case oCell.Value
    Case "Contact", "Supplier", "Etc. 1", "Etc. 2"
    ' Ignore these - do nothing
    Case Else
    ' Your code goes here
    ...
    End Select</code>

  5. #5
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parent.Column (Excel 2003)

    Thanks again. Another question, how do I determine the size of my array or do I have to. I want to collect data the following way. I'm not sure who many points it will collect.

    Option Explicit

    Public Sub Outlook_Calendar()

    Dim oCell As Range
    Dim Response As String
    Dim database_array(2000) As Variant

    For Each oCell In Sheets("Calender").Range("B2:G400")
    If oCell.Value <> "" Then
    For i = 1 To 2000
    database_array(i) = oCell.Value
    database_array(i) = Cells(1, oCell.Column).Value
    database_array(i) = Cells(oCell.Row, 1).Value
    End If
    Next
    End Sub

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Parent.Column (Excel 2003)

    You can declare a dynamic array:

    Public Sub Outlook_Calendar()
    Dim oCell As Range
    Dim database_array() As Variant
    Dim n As Long

    For Each oCell In Sheets("Calender").Range("B2:G400")
    If oCell.Value <> "" Then
    n = n + 1
    ReDim Preserve database_array(1 To n)
    database_array(n) = oCell.Value
    End If
    Next oCell
    ' do something with the array here, hopefully
    End Sub

    You cannot assign three values to the same array element.

  7. #7
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parent.Column (Excel 2003)

    So would I have three arrays to collect data like that.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Parent.Column (Excel 2003)

    Or a two-dimensional array:

    Public Sub Outlook_Calendar()
    Dim oCell As Range
    Dim database_array() As Variant
    Dim n As Long

    For Each oCell In Sheets("Calender").Range("B2:G400")
    If oCell.Value <> "" Then
    n = n + 1
    ReDim Preserve database_array(1 To 3, 1 To n)
    database_array(1, n) = oCell.Value
    database_array(2, n) = Cells(1, oCell.Column).Value
    database_array(3, n) = Cells(oCell.Row, 1).Value
    End If
    Next oCell
    ' do something with the array here, hopefully
    End Sub

  9. #9
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    132
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parent.Column (Excel 2003)

    Hans, you rock. So close to finishing. Just need to dump the array on to a sheet called Calendar, one row for each 1 to 3. Not sure how to do this.

    Thanks again to help.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Parent.Column (Excel 2003)

    Try this:

    Dim r As Long
    Dim c As Long
    For r = 1 to UBound(database_array, 2)
    For c = 1 To 3
    Worksheets("Calendar").Cells(r, c) = database_array(c, r)
    Next c
    Next r

    I used the UBound function to determine the size of the array in its second dimension (the first dimension has a fixed size of 3).

Posting Permissions

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