How to set a range in excel VBA after using the find function

Question

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 22-Feb-22 4:56am

Human Person

Updated 22-Feb-22 5:56am

CHill60

v2

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

CHill60 · Answer 1 · 2022-02-22T05:56:00

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