Posts by Mumps

    I am trying to write some code to delete an entire row if the cells in columns A, B and C are empty. When I try the code, I get the error message "Delete method of range class failed." Any suggestions would be appreciated.


    [VB]
    Dim bottomE As Long
    bottomE = Range("e" & Rows.count).End(xlUp).Row
    Dim i As Long
    For i = bottomE To 4 Step -1
    With Range("A4:E123")
    If .Range("A" & i) = "" And .Range("B" & i) = "" And .Range("C" & i) = "" Then .EntireRow.Delete
    End With
    Next i
    [/VB]

    Re: Method Range of object Global failed


    I managed to find a solution by inserting the formula into F5 if it was empty. This is what that section of code now looks like. I couldn't have done it without your help in finding the problem in the first place. Many thanks.


    [VB]
    'Copies formula in F5 down in Column F eqal to #rows in Column E
    bottomE = Range("e" & Rows.count).End(xlUp).Row
    Range("F5").Select
    If Range("F5") = "" Then Range("F5").FormulaR1C1 = _
    "=IF(((AND((RC[-5]=""""),(RC[-4]=""""),(RC[-3]="""")))),"""",IF((AND(RC[-5]<>"""",RC[-4]=R[-1]C[-4])),R[-1]C+1,IF (RC[-5]="""",R[-1]C,1)))"
    Range("f5:f" & bottomE).FillDown
    [/VB]

    I have written a lengthy macro which I run on 12 sheets and it works perfectly for all sheets except one. The sheets are identical in format, same number of columns, same column headers. The number of rows varies from one sheet to another. I cannot figure out why I get the error message on only one of the sheets. I have included an attachment with most of the sheets deleted but the macro included. If you run the macro with the Boys8 sheet active, it will run properly as it does on all the sheets that aren't in the attached file. If you run the macro with the Girls8 sheet active, you get the error message. I was hoping that someone might have some suggestions as to what might cause this to happen and what I could look for. Any suggestions would be of great help.

    Files

    I am using a loop to look for a range of cells based on certain criteria and then trying to copy that range to the first visible empty cell in Column H starting at cell H4. Each time the routine goes through the loop, it should paste the found range to the next visible and empty cell below. I am using the following code but I am missing the part that finds the first visible cell below H4. Any suggestions would be greatly appreciated.


    [VB]
    'Copies Teams to Team data range.
    Dim bottomF As Long
    bottomF = Range("f" & Rows.count).End(xlUp).Row
    For r = 3 To bottomF
    Set FirstCell = Range("C" & r)
    Set LastCell = Range("F" & r)
    If FirstCell Like "*Total*" And LastCell > 2 Then
    Range(FirstCell, LastCell).Copy
    ActiveSheet.Range("H1").End(xlDown).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
    End If
    Next
    [/VB]

    Re: Copy cell value only with no formatting to destination.


    Thank you for your suggestion. I actually tried the same thing but for some reason instead of copying only two rows and adding it to what is already on the sheet, it repeatedly copies the rows specified by the code directly before it to the 2000th row. This is the code directly before it. It simply copies 22 rows from one sheet to the other so in total both pieces of code should copy 24 rows.


    [VB]
    'Copies top 22 runners into CityQualifiers
    Dim TeamRng As Range
    Set TeamRng = Range("A4:C25")
    TeamRng.Select
    Selection.Copy
    Sheets("CityQualifiers").Range("A" & Rows.count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)
    [/VB]

    Re: Find cells based on values in different cell, copy and paste adjacent cells


    I had to modify my initial intent to find team runners if they finish outside the top 22 runners and then copy them into the RegionQualifiers sheet. This is the code.


    [VB]
    'Finds all three team runners if they finish outside of top 22 runners.
    bottomC = Range("c" & Rows.count).End(xlUp).Row
    bottomH = Range("h" & Rows.count).End(xlUp).Row

    For r = 4 To bottomH
    If Range("G" & r) = "1" Then FirstTeam = Trim(Range("H" & r))
    If Range("G" & r) = "2" Then SecondTeam = Trim(Range("H" & r))
    Next r

    Dim Counter As Integer
    Counter = 0
    For r = 3 To bottomC
    Set FirstCell = Range("A" & r)
    Set LastCell = Range("D" & r)
    If Range("A" & r).Offset(0, 2) = FirstTeam Then Counter = Counter + 1
    If Range("A" & r).Offset(0, 2) = FirstTeam And LastCell > 22 And Counter <= 3 _
    Then Range(FirstCell, LastCell.Offset(0, -1)).Copy _
    Destination:=Sheets("RegionQualifiers").Range("A1").End(xlDown).Offset(1, 0)
    Next r

    Counter = 0
    For r = 3 To bottomC
    Set FirstCell = Range("A" & r)
    Set LastCell = Range("D" & r)
    If Range("A" & r).Offset(0, 2) = SecondTeam Then Counter = Counter + 1
    If Range("A" & r).Offset(0, 2) = SecondTeam And LastCell > 22 And Counter <= 3 _
    Then Range(FirstCell, LastCell.Offset(0, -1)).Copy _
    Destination:=Sheets("RegionQualifiers").Range("A1").End(xlDown).Offset(1, 0)
    Next r

    'Copies Race Name into column D in RegionQualifiers.
    Dim myDest As Range
    Sheets("RegionQualifiers").Range("D" & Rows.count).End(xlUp)(2).Value = Range("a2").Value
    With Sheets("RegionQualifiers")
    Set myDest = .Range("a" & Rows.count).End(xlUp).Offset(, 3)
    With .Range("d" & Rows.count).End(xlUp)
    .AutoFill Sheets("RegionQualifiers").Range(.Cells, myDest), xlFillSeries
    End With
    End With
    [/VB]

    I have written this code to copy a range from one sheet to another and it works fine. However, I want to copy only the cell value without any formatting such as cell colour. I've tried a few different ways but it's not working for me. Any help would be great!


    [VB]
    Dim Counter As Integer
    Counter = 0
    For r = 3 To bottomC
    Set FirstCell = Range("A" & r)
    Set LastCell = Range("D" & r)
    If Range("A" & r).Offset(0, 2) = FirstTeam Then Counter = Counter + 1
    If Range("A" & r).Offset(0, 2) = FirstTeam And LastCell > 22 And Counter <= 3 _
    Then Range(FirstCell, LastCell.Offset(0, -1)).Copy _
    Destination:=Sheets("CityQualifiers").Range("A1").End(xlDown).Offset(1, 0)
    Next r
    [/VB]

    I have some code that copies and pastes a range ("A4:C25") from the active sheet to another and it works well. However, it copies the cell formatting such as cell colour. I would like to copy only the cell contents without the formatting. Any help would be appreciated.


    [VB]
    Dim TeamRng As Range
    Set TeamRng = Range("A4:C25")
    TeamRng.Select
    Selection.Copy Destination:=Sheets("RegionQualifiers").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    [/VB]

    I have to delete a worksheet and then replace it with a copy of the same worksheet, with the same name so that I can make changes to the copy. I am using formulas in a macro that reference that original worksheet but when I replace it with a copy and run the macro, the #Ref error replaces the sheet name in the formula and the error appears in the corresponding cell. I know that when you delete a sheet or rows or cells, the Ref error is generated because the reference is gone. but the replacement sheet that I am using has exactly the same sheet name as the original which I deleted. Why does the reference not work? Any assistance would be great!

    Re: Object doesn't support this property or method


    Hi jindon:


    Your suggestion worked very well except that when I run the macro on a different sheet, the data copied from the previous sheet is overwritten. I want to run the macro on several sheets so that the data is added to what is already there. I modified my posting because I attached a file that was missing the sheet you mentioned. Please look at my very last posting to get a better idea of what I am doing and use the larger of the two files. It has the missing sheet.


    Many thanks.

    Re: Object doesn't support this property or method


    Thank you and don't stay up too late!


    I have attached the file. I would like to copy Range("A4:D25") from the Boys8 sheet to the first blank row of the RegionQualifiers sheet. Then I would like to copy Range("A2") to each cell in column D in the RegionQualifiers sheet but only for the same number of rows as was in the first range copied ("A4:D25"). I would like to copy only the contents of A2 not the formatting like style and colour. When I run the macro again with the Boys7 sheet active, I want the same ranges copied but below the ranges copied from the Boys8 sheet. Here is the complete code of what I want to do. This part of a larger macro. Everything works well except for the last part that I've described above.
    I've attached 2 files. Use the larger file. I sent the smaller file but it was missing some data. I didn't know how to delete it.


    [VB]
    'Copies top 22 runners into RegionQualifiers
    Dim TeamRng As Range
    Set TeamRng = Range("A4:C25")
    TeamRng.Select
    Selection.Copy Destination:=Sheets("RegionQualifiers").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)


    'Finds third team runner outside of top 22 runners.
    Dim bottomC As Long
    Dim bottomH As Long
    bottomC = Range("c" & Rows.Count).End(xlUp).Row
    bottomH = Range("h" & Rows.Count).End(xlUp).Row


    For r = 4 To bottomH
    If Range("G" & r) = "1" Then FirstTeam = Trim(Range("H" & r))
    If Range("G" & r) = "2" Then SecondTeam = Trim(Range("H" & r))
    Next r


    Dim Counter As Integer
    Counter = 0
    For r = 3 To bottomC
    Set FirstCell = Range("A" & r)
    Set LastCell = Range("D" & r)
    If Range("A" & r).Offset(0, 2) = FirstTeam Then Counter = Counter + 1
    If Range("A" & r).Offset(0, 2) = FirstTeam And LastCell > 22 And Counter = 3 _
    Then Range(FirstCell, LastCell.Offset(0, -1)).Copy _
    Destination:=Sheets("RegionQualifiers").Range("A1").End(xlDown).Offset(1, 0)
    Next r


    Counter = 0
    For r = 3 To bottomC
    Set FirstCell = Range("A" & r)
    Set LastCell = Range("D" & r)
    If Range("A" & r).Offset(0, 2) = SecondTeam Then Counter = Counter + 1
    If Range("A" & r).Offset(0, 2) = SecondTeam And LastCell > 22 And Counter = 3 _
    Then Range(FirstCell, LastCell.Offset(0, -1)).Copy _
    Destination:=Sheets("RegionQualifiers").Range("A1").End(xlDown).Offset(1, 0)
    Next r


    Range("A2").Copy Destination:=Sheets("RegionQualifiers").Range("D2")
    With Sheets("RegionQualifiers")
    .Range("D2").AutoFill Destination:=.Range("D2", .Range("D2").End(xlDown))
    End With
    [/VB]

    Re: Object doesn't support this property or method


    I'm sorry jindon but I have to step away from my computer for a few hours. When I get back I will send you a copy of the worksheets and give you a more detailed description. It might be easier if you can see what I'm working with. Thank you so much for your help and your patience.