Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Populating list of tabs in column based on using used range

0

Hi

I try to matching C2 for sheet1 with value is existed  across sheets, then should populate list of tabs names  in column D into sheet1 if value is matched with C2 for sheet1. actually I use usedrange propertise  because the value will be different in location across sheets, not in the same column.

here is the code

Sub n()
Dim sh As Worksheet, ws As Worksheet
Dim rng As Variant, rngCell As Range
Dim lngLstRow As Long, lngLstCol As Long
Set sh = Sheets("sheet1")
rng = sh.Range("C2").Value
For Each ws In Worksheets
lngLstRow = ws.UsedRange.Rows.Count
lngLstCol = ws.UsedRange.Columns.Count
For Each rngCell In Range(Cells(2, 1), Cells(lngLstRow, lngLstCol))
If rng.Value = rngCell.Value Then rng.Offset(, 1) = ws.Name
Next
Next
End Sub

it shwos error object required in this line

If rng.Value = rngCell.Value Then rng.Offset(, 1) = ws.Name
Answer
Discuss

Discussion

Halk

Please attach an Excel file showing test data- without that, it's difficult to know whether or not  UsedRange.Rows.Count (and Column.Count) will workas you think  e.g. if the data is in A20:A30 then that would make Count 10 and you'd look in A2:A10 (and miss all the data!).

There are other obvious errors in your current code incidentally.
John_Ru (rep: 6212) Jun 4, '24 at 4:08 pm
Hi John
I attached file and the expected result in column D based on C2 in sheet1.
Halk (rep: 32) Jun 4, '24 at 4:41 pm
Add to Discussion

Answers

0
Selected Answer

Halk

In the attached file, I commented out your code and added a revision as follows (main changes in bold):

Sub ReportFinds()
    Dim sh As Worksheet, ws As Worksheet
    'Dim rng As Variant, rngCell As Range
    Dim rng As Range, rngCell As Range
    'Dim lngLstRow As Long, lngLstCol As Long
    Dim p As Long ' for row offset results

    Set sh = Sheets("sheet1")
    ' need to set as a range
    Set rng = sh.Range("C2") '.Value

    For Each ws In Worksheets
       ' forget this approach!
       ' lngLstRow = ws.UsedRange.Rows.Count
       ' lngLstCol = ws.UsedRange.Columns.Count
        ' don't check sheet1
        If ws.Name <> sh.Name Then
            ' can't use that Range method so look only in User Range for the worksheet
            For Each rngCell In ws.UsedRange 'Range(Cells(2, 1), Cells(lngLstRow, lngLstCol))
                If rng.Value = rngCell.Value Then
                    ' write sheet and address
                    rng.Offset(p, 1) = ws.Name
                    rng.Offset(p, 2) = rngCell.Address
                    ' increment counter
                    p = p + 1
                End If
            Next rngCell
        End If
    Next ws
End Sub

Now the code will list (in column D) the sheets it found the C2 search value in but also the addresses it was found (in column E). You'll see this demonstarted since I added an extra "ASD" in cell F9 of worksheet ST.

Note that I haven't written code to clear the results before the search nor to code format the results but I hope this helps.

Discuss

Discussion

Amended my answer since I forgot to say that I added an extra "ASD" in cell F9 of worksheet ST.
John_Ru (rep: 6212) Jun 4, '24 at 5:17 pm
@John,

Thanks for asking for a sample file. Once again you have "beat me to the punch" to provide a solution. Nice work.
That's OK, now I don't need to spend more time on it. I too noticed several errors, but earlier without a sample file, it was challenging to determine what was desired.
WillieD24 (rep: 557) Jun 4, '24 at 5:49 pm
@Willie - thanks, you made a good attempt but the lack of a file made it really hard to resolve or to understand fully what the user really meant. Hope my answer helps him. 
John_Ru (rep: 6212) Jun 4, '24 at 6:07 pm
thanks john for soulation .
Halk (rep: 32) Jun 4, '24 at 6:26 pm
Thanks for selecting my Answer, Halk. Next time, please attach a sample file- you'll get a faster, better solution since it nearly always helps us better understand what data you have and the results you want. 
John_Ru (rep: 6212) Jun 4, '24 at 6:37 pm
Add to Discussion
0

Hello again Halk,

At the top of your code you assign a value to the variable "rng" :

rng = sh.Range("C2").Value

In the line which is throwing the error "Object Required", your code is asking VBA to get the value of a value (rng) which is not possible.

Just remove " .Value " so the line becomes:

If rng = rngCell.Value Then rng.Offset(, 1) = ws.Name

If this solves things , please mark my answer as selected.

Cheers   :-)

Discuss

Discussion

thanks 
the same error in this line
rng.Offset(, 1) = ws.Name
Halk (rep: 32) Jun 4, '24 at 2:47 pm

The error is triggered here for the same reason - "rng" as you have used it is a value and not a cell reference. You need to replace "rng" with the specific cell refenece where you want the sheet name to be entered. Something like this:
Range("C2").Offset(, 1) = ws.Name

Cheers   :-)
WillieD24 (rep: 557) Jun 4, '24 at 4:13 pm
the error is gone but doesn't show anything.
I attached file with random data and the expected result in column D based on C2 in sheet1..
Halk (rep: 32) Jun 4, '24 at 4:44 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login