Loop through a column to find a specific value and return the value of another column

[G12Consult]

Hi,

I am trying to find some vba code that will allow me to loop through a specific column of a specific sheet and return a value from another column and write that to a cell in another sheet.


So my data to loop may look like
A B
P100000 10
P134454 111
P100000 12
P123232 7
P100000 14


So in another sheet I will have the code write

P100000 10
P100000 12
P100000 14

Thanks in advance

 

[SkipVought]

Hi,

You don't need VBA code to get a result by supplying a parameter value from column A.

BTW, it is a best and accepted practice to name each column in tables in Row 1 with a unique value, most often a meaningful value. In order for my suggestion to be implemented,
1) your table must have unique values in Row 1
2) no other data than the table data on the sheet and
3) all rows and columns of data contiguous.

With those stipulations, you can use Data > Get External Data...

See FAQ68-5829.

By making this a Parameter Query, your application could use a DropDown selection to choose the desired value from your original table.

For someone who's done this before, this is a very quick solution, less than 30 minutes. It will take you a bit longer, but this will end up being an interactive process--Make a selection>>See the result.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[Andrzejek]

[pre]
A B
P100000 10[red]
P134454 111[/red]
P100000 12[red]
P123232 7 [/red]
P100000 14[/pre]

Is there any reason / logic that [red]those rows[/red] are excluded from your output?

If you want all rows with P100000, you can simply use Skip's 1), 2), 3) and simply do: Data - Filter, Select only P100000, Copy data, Paste to another Sheet, and you are done. No VBA needed.

---- Andy

There is a great need for a sarcasm font.

 

[G12Consult]

Sorry probably should have posted my code.

It is because I only wanted to find the value P100000.

However, now I need to loop through a column with different numbers, so how can I modify the code below.

Basically, I have an organisation structure in rows where there is a PositionID and ReportsToPositionID.


So in sheet xxx I have say 10 positions.

I want to loop through each of those positions and find from another sheet all the positions which report to that first position.

eg. Position ID P10000 has 5 people reporting to them. So I need my code to look through the structure and find all the positions who report to P10000 and write them in rows.

Then loop to the next position P10001 and do the same, then list all the positions reporting to P10001.

I guess I just need to change the hardcode P10000 in the code below?

Dim c As Range
Set c = Worksheets("xxx").Range("F:F")

For Each c In c
    If c.Value = "P10000" Then
    
        'Get Lookup value from column B and write to column E in the structure
               
        Dim LastRow As Long
        LastRow = Worksheets("Structure").UsedRange.Row - 1 + Worksheets("Structure").UsedRange.Rows.Count + 1
     
     
        'Get last used column on the last row, add 1 to move to next column
        Dim LastColumnNumber As Integer
        LastColumnNumber = Worksheets("Structure").Cells(LastRow - 1, Columns.Count).End(xlToLeft).Column


        'Get the letter address for the return column
        Dim LastColumnLetter
        LastColumnLetter = Replace(Cells(1, LastColumnNumber + 1).Address(False, False), "1", "")
     
        'Write the position to the structure
        Worksheets("Structure").Range("E" & LastRow) = c.Offset(rowOffset, -3).Value
        Worksheets("Structure").Range("F" & LastRow) = c.Offset(rowOffset, -4).Value
        'ws.Range("D" & LastRow).Value = Range(LastColumnLetter & LastRow).Value
    
        
        'Worksheets("Structure").Range("E4").Value = Range(strPosition).Value
      
    End If
Next c

 

[combo]

My comments to your code:
- you can limit searched range with [tt]Intersect[/tt] and [tt]UsedRange[/tt], test for [tt]Nothing[/tt] for security,
- [tt]For Each c In c[/tt] is confusing, it may work, but more readible are two range variables,
- [tt]Worksheets("...").Cells(IDrow, IDcol)[/tt] allows referencing cells without building the address string,
- you can easily get worksheet's last cell by [tt].UsedRange.SpecialCells(xlLastCell)[/tt], next refer to cells using its row, column or by [tt]Offset[/tt].

combo