Setting Up Loop for Data pull (match) from other tabs

erad314

New Member
Joined
Mar 13, 2012
Messages
1
Hi All,
So I have been struggling very much with setting up a series of Loops and Do While functions in order to pull results from specific tabs, and from specific rows and columns on these tabs (based on known information).

Simply: I have two columns to populate with data -CompanyA, CompanyB (but in reality I need a variant array for # of companies), each company has two tabs in this same workbook (for whether the item I am looking up is a Category or Subcategory class of the company's expenditures). The tabs are titled CategoryCompanyA, SubcategoryCompanyA, etcB. respectively.

I have a table/list of the items to look up (in the same format as the to-be-referenced cells), the tabs they are located on, and the column# of the value I need on these tabs, all on sheet1. For each item, I need to pull one data value from a single cell on the respective CatCompX tab.

I would like to have a formula that works horizontally through companies, then vertically through the items to look up.

Please advise on setting up the Do While parameters, and incorporating the For.. Next into such. I have really caught here and need some fresh eyes, Much appreciated.

-Eric


Dim numRows As Long
Dim CC() As String
Dim start_column As Integer
Dim CatOrSubCatCompanyCombo As String
Dim row As Integer
Dim arrayCounter As Integer
Dim CurrentRow As Long
Dim CurrentCol As Integer
Dim Sheet As Worksheet
start_column = 12
arrayCounter = 0
numRows = Sheet1.Range("A:A").End(xlUp).row

Do While (Sheet1.Cells(1, start_column) <> "")
If CC(arrayCounter) = Cells(1, start_column) Then
Sheet1.Cells(2, start_column).Activate
start_column = start_column + 1

Else
arrayCounter = arrayCounter + 1
ReDim Preserve CC(1 To arrayCounter)


Dim FoundRow As Long
Dim FoundCol As Long
CurrentCol = 12 + i - 1
CatOrSubCatCompanyCombo = Cells(CurrentRow, 6)


For CurrentRow = 2 To numRows
FoundRow = Sheet("CatOrSubCatCompanyCombo").Range("A:A").Find(CurrentRow, 8).row
FoundCol = Sheet1.Cells(CurrentRow, 10).Value
Sheets1.Cells("L2").Activate
Sheets1.Cells(CurrentRow, CurrentCol) = Sheets("CatOrSubCatCompanyCombo").Cells(FoundRow, FoundCol)
Next

End If

Loop




**Columns #
6-Tab location/name
8-Name of Item to lookup/match on other tab
10-Column number to look up on other tab
12-CompanyA
13-CompanyB
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,193
Messages
5,768,772
Members
425,492
Latest member
blueexcel123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top