Posts by Mumps

    I am writing some code to try to do the following:
    - copy a cell without any formatting (no style, no colour) from the active sheet to the first blank cell in column D in a sheet called RegionQualifiers
    - then to Fill Down this cell in column D to the last used row in column A


    I get the error message : "Object doesn't support this property or method." on the line starting with AutoFill.


    I would like to replace "D2" (both occurrences) with some code to find the first blank cell in column D so that if I run the macro more than once, the Copy and AutoFill start on the first blank line. Any help would be very greatly appreciated.


    [VB]
    Range("A2").Copy Destination:=Sheets("RegionQualifiers").Range("D2")
    Dim bottomA as Long
    bottomA = Range("a" & Rows.Count).End(xlUp).Row
    With Sheets("RegionQualifiers")
    .AutoFill Destination:=Range("D2" & ":D" & bottomA).End(xlDown)
    End With
    [/VB]

    I have written a piece of code to copy a range from the active worksheet and paste to the first blank row in another worksheet called "RegionQualifiers". When I run the code I get the "Application-defined or Object-defined error" message and the last line is highlighted as the location of the error. I've tried different approaches but nothing is working for me. Any suggestions would be greatly appreciated.


    [vb]
    Dim TeamRng As Range
    Set TeamRng = Range("A4:C25")
    TeamRng.Select
    Selection.Copy Destination:=Sheets("RegionQualifiers").Range("A1").End(xlDown).Offset(1, 0)
    [/vb]

    Referring to the attached file, I am trying to write code that will find the first three cells in column C based on the value of cell H4 in the sheet Boys8. Then I want to copy the contents of the corresponding cells in columns A, B, C and Cell A2 to the corresponding columns in sheet TeamQualifiers. For example copy these cells from rows 6, 10, 12 and A2:
    ANDREW L agn St. Agnes Grade 8 Boys
    ANTHONY J agn St. Agnes Grade 8 Boys
    MATTHEW B agn St. Agnes Grade 8 Boys


    and then paste them into sheet TeamQualifiers in the corresponding columns.
    Then I need to repeat the process using the value in cell H5 (St. Gerald) and paste the similar data starting with the first blank row in TeamQualifiers. I'm relatively new to VBA and would appreciate any help.

    Re: Object Variable or With Block Variable not set


    Thank you so much. It worked great! Now I have another problem to solve. The variables (FirstPosition & " " & SecondPosition & " " & ThirdPosition) return the values in Column D of the last School in each school group even though these values are not visible. I want them to return the values in Column D of the first school in each school group. I will work on it. Thank you again, my friend.

    Re: Object Variable or With Block Variable not set - PROBLEM SOLVED


    I have modified the previous code and I no longer get the "Object Variable or With Block Variable not set" error message. Now the problem is that the last MsgBox line returns a zero for each of the variables (FirstPosition & SecondPosition & ThirdPosition) rather than the three integers that I expected. Any suggestions?


    [VB]
    Sub Macro2()
    '
    ' Macro2 Macro
    '
    Dim r As Long
    Dim FirstTeam As String
    Dim SecondTeam As String
    Dim ThirdTeam As String
    Dim bottomH As Long
    bottomH = Range("h" & Rows.Count).End(xlUp).Row
    Dim bottomC As Long
    bottomC = Range("c" & Rows.Count).End(xlUp).Row
    For r = 4 To bottomH
    If Range("H" & r) = "1" Then FirstTeam = Range("I" & r).Text
    If Range("H" & r) = "2" Then SecondTeam = Range("I" & r).Text
    If Range("H" & r) = "3" Then ThirdTeam = Range("I" & r).Text
    Next r
    MsgBox (FirstTeam & SecondTeam & ThirdTeam)
    Dim FirstPosition As Integer
    Dim SecondPosition As Integer
    Dim ThirdPosition As Integer
    For r = 4 To bottomC
    If Range("C" & r) = FirstTeam Then FirstPosition = Range("D" & r).Value
    If Range("C" & r) = SecondTeam Then SecondPosition = Range("D" & r).Value
    If Range("C" & r) = ThirdTeam Then ThirdPosition = Range("D" & r).Value
    Next r
    MsgBox (FirstPosition & SecondPosition & ThirdPosition)


    End Sub
    [/VB]

    I have written this code and I get the message "Object variable or with block variable not set." The last line is highlighted as the location of the error. I used MsgBox to test if the correct variables are returned. The first MsgBox I used returns the correct values for the variables but the last line with MsgBox generates the error. Any help with this would be appreciated.
    [vb]
    Dim r As Long
    Dim FirstTeam As Range
    Dim SecondTeam As Range
    Dim ThirdTeam As Range
    Dim bottomH As Long
    bottomH = Range("h" & Rows.Count).End(xlUp).Row
    Dim bottomC As Long
    bottomC = Range("c" & Rows.Count).End(xlUp).Row
    For r = 4 To bottomH
    If Range("H" & r) = "1" Then Set FirstTeam = Range("I" & r)
    If Range("H" & r) = "2" Then Set SecondTeam = Range("I" & r)
    If Range("H" & r) = "3" Then Set ThirdTeam = Range("I" & r)
    Next
    MsgBox (FirstTeam & SecondTeam & ThirdTeam)
    Dim FirstPosition As Range
    Dim SecondPosition As Range
    Dim ThirdPosition As Range
    For r = 4 To bottomC
    If Range("C" & r) = FirstTeam Then Set FirstPosition = Range("D" & r)
    If Range("C" & r) = SecondTeam Then Set SecondPosition = Range("D" & r)
    If Range("C" & r) = ThirdTeam Then Set ThirdPosition = Range("D" & r)
    Next r
    MsgBox (FirstPosition & SecondPosition & ThirdPosition)
    [/vb]