Click here to Skip to main content
15,665,276 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am trying to use the find function to locate the location of a cell with a value. Once that value is found I need it to copy from that cell to another cell. This is giving me an error. After this it should paste the copied material under the final row.

What I have tried:

I am getting an error at the range line it is saying an object is required.

VB
Sub MatrixSize()

     Dim ValuesRange As Range
     Dim NumClass As Integer


     LastColumn = Cells(5, Columns.Count).End(xlToLeft).Column
     LastRow = Cells(Rows.Count, 17).End(xlUp).Row
     LastRowG = Cells(Rows.Count, 7).End(xlUp).Row

     i = 5
     'from the first class Ta total to the last
     Do Until Cells(i, "G") = LastRowG
          Set TestValue = Cells(i, "G")

          'Find the number of times the value shows
          rng = Range(Cells(7, "Q"), Cells(LastRow, "Q"))
          NumClass = WorksheetFunction.CountIf(rng, TestValue)

          'if numclass is not the correct value paste more
          If NumClass <> Cells(i, "H") Then
               'find the location of the Te
               FinClass = Range(Cells(7, "Q"), LastRow).Find(What:=TestValue).Address

               'copy the row that contains the test values from it to the final column
               Range(FinClass, Cells(FinClass.Row, LastColumn.Column)).Copy

               'Starting at the final row pasted the copy value
               Range(LastRow).Select
               Selection.PasteSpecial

          Else
               'test next value
               i = i + 1

          End If
     Loop
End Sub
Posted
Updated 22-Feb-22 5:56am
v2
Comments
Richard MacCutchan 22-Feb-22 11:35am    
Step through the code with the macro debugger to see what object is not present.

1 solution

Point 1
Always use Option Explicit in your code modules. It helps you to avoid errors and ensure your variables are correctly declared
e.g. You will need at least
VB
Dim LastColumn As Long, LastRow As Long, LastRowG As Long
    Dim i As Long
    Dim rng As Range
    Dim FinClass As Range
Doing this might also solve your problem if you include Dim TestValue As Object your problem goes away. Alternatively lose the "Set" on Set TestValue = Cells(i, "G")

Point 2
You will get an error in the following code
VB
'copy the row that contains the test values from it to the final column
    Range(FinClass, Cells(FinClass.Row, LastColumn.Column)).Copy
The first closing bracket should be after
VB
LastColumn </code><code>Range(FinClass, Cells(FinClass.Row, LastColumn).Column).Copy
Point 3
It's good practice to explicitly state which workbook and sheet you are using. So instead of Range(LastRow).Select use
ThisWorkbook.Sheets(1).Range(LastRow).Select

Point 4
It is also bad practice to use Copy/Paste in VBA. You can assign the values directly e.g.
VB
Range("A1").Copy
Range("Z5").Select
Selection.PasteSpecial
could become
VB
ThisWorkbook.Sheets(1).Range("Z5").Value = ThisWorkbook.Sheets(1).Range("A1").Value
 
Share this answer
 
v2
Comments
Maciej Los 23-Feb-22 11:06am    
5ed!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900