Loop through and lookup a specific value

**armando12345** · 01-23-2020, 01:59 PM

Hi All,

I am posting a sample sheet with a simple version of my problem.

Basicallly, I have 2 sheets. Sheet1 and Actual data. What I want to do is loop through the list of names in Sheet1 and pull the specific value for each one from actual data. This would be pretty easy but I'm struggling with looping through and also making sure that i'm pulling the right data.

For example, the Person1 horses column in sheet1 should pull the number of horses associated with person1 from the actual data sheet.

**jeffreybrown** · 01-23-2020, 02:11 PM

Not sure you need a macro for this. Here's an example with a formula approach and also with a pivot table.

**armando12345** · 01-23-2020, 02:29 PM

Hi Jeffrey,

I'd like it in a macro since it's going to be a part of another macro.

Is that possible?

**jeffreybrown** · 01-23-2020, 03:17 PM

How about with your sample

Sub CreateTotals()
    Dim wsSrc As Worksheet: Set wsSrc = Sheets("Sheet1")
    Dim LastRow As Long
    Dim LastCol As Long
    Application.ScreenUpdating = False
    With wsSrc
        LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row - 2
        LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column - 2
        With .Range("C3").Resize(LastRow, LastCol)
            .Formula = "=SUMIFS('Actual Data'!$D:$D,'Actual Data'!$B:$B,$B3,'Actual Data'!$C:$C,C$1)"
            .Value = .Value
        End With
    End With
    Application.ScreenUpdating = True
End Sub

**armando12345** · 01-24-2020, 11:02 AM

So when I try to move it into my other macro i get "application defined or object defined error" on the .range("c3") line. Even when I update the sheets and everything. Any clue what's causing this?

**jeffreybrown** · 01-24-2020, 02:09 PM

Sorry, don't know what's happening without seeing your work product.

Loop through and lookup a specific value

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Loop through and lookup a specific value

Re: Loop through and lookup a specific value

Re: Loop through and lookup a specific value

Re: Loop through and lookup a specific value

Re: Loop through and lookup a specific value

Re: Loop through and lookup a specific value

Thread Information

Users Browsing this Thread

Similar Threads

Change all loop to specific loop

Loop for adding specific worksheets in specific workbooks

[SOLVED] Vlookup in UserForm - Get Input from listbox, Lookup in specific table from specific sheet

[SOLVED] Lookup & Loop

Loop to lookup a list names, match them on another worksheet and pull specific header

Excel 2007 : Lookup table Loop

Lookup Loop

Bookmarks

Bookmarks

Posting Permissions