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
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
