CODEWITHSUNDEEP
vbaexcel
user7618694
user7618694

Reputation: 3

Finding specific data fields in worksheets that are different every time

I am trying to collate values from multiple spread sheets I am given. Unfortunately the fields I am interested in are never in the same location, and can have any number of blank cells in between the value I want and the corresponding reference number (that identifies it as the number I want). They are always in the same row as each other though.

For example, I need to find all values that relate to '1. Number of people'. In worksheet 1 '1. Number of people' is in cell B2 and the value is in cell B6. In worksheet 2 these are in C4 and C7 respectively.

I am using 'find' to assign the location of '1. Number of people' to a range, but getting stuck after that. I think I need to activate that cell, then offset until I find the next non blank cell and select that to copy, but can't work out how to do this.

So far all I have is:

Dim rgFound As Range

Set rgFound = Range("A1:E6").Find("1.Number of people", lookat:=xlWhole)

You can see I have not got very far! Thanks in advance.

Upvotes: 0

Views: 51

Answers (1)

Scott Holtzman
Scott Holtzman

Reputation: 27259

Give this a shot.

Dim rgFound As Range
Set rgFound = Range("A1:E6").Find("1.Number of people", lookat:=xlWhole)

Dim rgValue as Range

If Not rgFound is Nothing Then 
    If Len(rgFound.Offset(1)) Then 'if the very next row is the next non-blank cell
        Set rgValue = rgFound.Offset(1) 
    Else 'if blanks appear between found and value
        Set rgValue = rgFound.End(xlDown)
    End If
End If

Upvotes: 1

Related Questions