How to loop through a specified range? 1

[reportingbuzz]

Hi,

How can I loop through a range? Within the loop, I wish to find the value. If it exists, then get the cell reference of the that value? How can do this too?

My code now is:

For i = 0 To UBound(aPlan, 2)
    'Take the value of the C_Plan of the i row and dump it into a variable.
    varPlan = aPlan(C_Plan, i)
    'MsgBox (Count & "." & varPlan)
    
    'Loop through the PlanUIs sheet (specifically column 'B')
    'and find the associated UniqueIdentifier (which exists in column 'A'
    
    'Specify the name of worksheet, range of the worksheet to find the value
    Set myRng = Worksheets("PlanUIs").Range("B2:B105")
    
    'For Each cel In myRng
    For iRow = 1 To myRng.Count
        'If cel.Value = varPlan Then
        If myRng.Cells.Value = varPlan Then
            MsgBox (varPlan)
            'Since the value exists, get the cell reference of the matched value.
            'Reference would point to B(RowNumber)
            
            'Get the reference to the A column which would be A(RowNumber)
            
            'Now get the UniqueIdentifier of the A(RowNumber)
            
            'Insert this value into the worksheet - Plan_PlanComponent_Link into cell A2
            
        End If
    Next
    'Next cel
    
    'see if there is a value in the Booking columns of the array - aPlan
    varBookings = aPlan(C_Bookings, i)
    
        Count = Count + 1
Next

Any suggestions appreciated.

Thanks.

 

[macropod]

Hi reportingbuzz,

Try something based on:

Sub Test()
Dim iRow As Long
Dim varPlan As String
varPlan = InputBox("varPlan?")
With ActiveSheet
    For iRow = 2 To 102
        If .Cells(iRow, 2).Value = varPlan Then
            MsgBox "varPlan found in B" & iRow & vbCrLf & "A" & iRow & " = " & .Cells(iRow, 1).Value
        End If
    Next
End With
End Sub

If you want to exit at the first match, add an 'Exit Sub' line before the 'End If'.

Cheers

[MS MVP - Word]

 

[reportingbuzz]

That was really nice. But since the focus was on the active sheet - PlanUIs, is there a way I can replace the Activesheet with the named sheet - PlanUIs?

Thanks.

 

[PHV]

Simply replace ActiveSheet with Worksheets("PlanUIs")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[reportingbuzz]

In this code:

.Cells(iRow, 2)

what does the 2 mean? Does it refer to a 2 dimensional array or does it refer to the 2nd column of the array?

Thanks.

 

[PHV]

Place the cursor inside the Cells word and press the F1 key.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[reportingbuzz]

Yeah it is the column index. I got that. Thanks for the tip.

 

[reportingbuzz]

I am really getting confused how to accomplish this:

1st sheet: Plan_Mapping

A                  B             C
Plan               Bookings      RS Bonus AllRevenue
Channel Manager    Bookings      RS Bonus AllRevenue
Dir, Channel Sales Bookings      RS Bonus AllRevenue
Account Executive  Bookings

Above sheet means that the Plans in Column A have the components defined in Column B and Column C.

For example, Plan "Account Executive" has only component "Bookings.

2nd Sheet: PlanUIs (this has the primary key for the Plan)

A                  B
UniqueIdentifier   Plan
3004661            Channel Manager
3004588            Account Executive
3004908            Dir, Channel Sales

3rd Sheet: PlanComponentUIs (this is the primary key for the PlanComponents)

A                  B
UniqueIdentifier   Components
46387              Bookings
46382              RS Bonus AllRevenue

4th Sheet: Plan_PlanComponent_Link
(I want to build this sheet where there will be as many components for the Plan. So Channel Manager has 2 components. So there will be 2 rows of the Channel Manager where in Column A, it will display the UniqueIdentifier of Channel Manager and in Column B, it will display UniqueIdentifier of 1st component - Bookings (cell B2) and UniqueIdentifier of 2nd component - RS Bonus AllRevenue (cell B3))

Hence it should like this:

A                          B
Plan_UniqueIdentifier      PlanComponent_UniqueIdentifier
3004661                    46387
3004661                    46382

My code now grabs the 1st Plan from Plan_Mapping sheet, gets the UniqueIdentifier from the PlansUIs sheet and inserts it into the Plan_PlanComponent_Link sheet.

But for that Plan, I would like to insert the UniqueIdentifier of PlanComponents How to do that?

Here is the code with what I started with:

Function getPlan()
    
Count = 1

Set wSht = Worksheets("Plan_Mapping")

With wSht
    For iRow = 2 To 105

    'grab the value of the 'AiRow'
    varPlan = .Cells(iRow, 1).Value
    Exit For
    Next
    
    getPlanUI (varPlan)
End With

End Function


Sub getPlanUI(varPlan)

Dim iRow As Long
Dim wSht As Worksheet

Set wSht = Worksheets("PlanUIs")

With wSht
    For iRow = 2 To 105
        If .Cells(iRow, 2).Value = varPlan Then
            MsgBox (varPlan & " found in B" & iRow & vbCrLf & "A" & iRow & " = " & .Cells(iRow, 1).Value)
            
            'grab the value of the 'AiRow'
            varPlanUI = .Cells(iRow, 1).Value
            
            'insert it into the Plan_PlanComponent_Link sheet at A2.
            Worksheets("Plan_PlanComponent_Link").Range("A2").Value = varPlanUI
            
            Exit Sub
        End If
    Next
End With

End Sub

Sub getPlanComponentUI(varPlan)

Dim iRows As Long
Dim wSheet As Worksheet

Set wSheet = Worksheets("PlanComponentUIs")

With wSheet
    For iRows = 2 To 30
        'How to grab the UIs for the component where the PlanComponents has not plan name
        'but only the PlanComponents name. So how to grab the PlanComponents for that plan
    Next
End With

End Sub

 

[SkipVought]

This also works...

.Cells(iRow, "B")

in case you're way out in the boondocks, like column DV.

Skip,
[sub]
[red][/red]
[/sub]

 

[macropod]

Hi reportingbuzz,

The code I originally posted wasn't meant to be the whole solution - just a demonstration of how to get the reference you were after. That's why I suggested you "Try something based on" it.

I see you're using a Function, but your problem description implies you aren't after a UDF - just a macro to populate your 'Plan_PlanComponent_Link' sheet. If that's what you want, see if the following code does what you want - it outputs four columns of data on your 'Plan_PlanComponent_Link' sheet: 'Plan', 'Plan_UniqueIdentifier', 'Category' and 'Component_UniqueIdentifier'. You can delete the extra columns from the code if you don't want them.

Sub BuildLinks()
' MapSht coordinates
Dim i As Long, j As Integer
' UIsSht & CmpSht rows
Dim k As Long, l As Long
' LnkSht rows
Dim m As Long
Dim PlnID As Long
Dim CmpID As Long
Dim StrPln As String
Dim StrCmp As String
Dim MapSht As Worksheet
Dim UIsSht As Worksheet
Dim CmpSht As Worksheet
Dim LnkSht As Worksheet
With ThisWorkbook
    Set MapSht = .Sheets("Plan_Mapping")
    Set UIsSht = .Sheets("PlanUIs")
    Set CmpSht = .Sheets("PlanComponentUIs")
    Set LnkSht = .Sheets("Plan_PlanComponent_Link")
    m = 1
    For i = 2 To MapSht.Cells.SpecialCells(xlCellTypeLastCell).Row
        StrPln = MapSht.Cells(i, 1).Value
        For j = 2 To MapSht.Cells.SpecialCells(xlCellTypeLastCell).Column
            StrCmp = MapSht.Cells(i, j).Value
            For k = 2 To UIsSht.Cells.SpecialCells(xlCellTypeLastCell).Row
                If UIsSht.Cells(k, 2).Value = StrPln Then
                    PlnID = UIsSht.Cells(k, 1).Value
                    For l = 2 To CmpSht.Cells.SpecialCells(xlCellTypeLastCell).Row
                        If CmpSht.Cells(l, 2).Value = StrCmp Then
                            CmpID = CmpSht.Cells(l, 1).Value
                            Exit For
                        End If
                    Next l
                    Exit For
                End If
            Next k
            m = m + 1
            With LnkSht
                .Cells(m, 1).Value = StrPln
                .Cells(m, 2).Value = PlnID
                .Cells(m, 3).Value = StrCmp
                .Cells(m, 4).Value = CmpID
            End With
        Next j
    Next i
End With
End Sub

Cheers

[MS MVP - Word]

 

[PHV]

macropod, I suggest this:

...
            StrCmp = MapSht.Cells(i, j).Value
            [!]If Trim(StrCmp) = "" Then Exit For[/!]
            For k = 2 To UIsSht.Cells.SpecialCells(xlCellTypeLastCell).Row
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[macropod]

Hi PH,

Yes, if any of the rows is short that could be a useful addition, but only if there aren't gaps resulting from users keeping the data on the 'Plan_Mapping' sheet aligned. A safer way would be:

[red]If Trim(StrCmp) <> "" Then[/red]
    m = m + 1
    With LnkSht
        .Cells(m, 1).Value = StrPln
        .Cells(m, 2).Value = PlnID
        .Cells(m, 3).Value = StrCmp
        .Cells(m, 4).Value = CmpID
    End With
[red]End If[/red]

Thanks for the prompt!

Cheers

[MS MVP - Word]

 

[reportingbuzz]

Thanks for your responses. I got it working through a UDF. However, I noticed that if the Plan Name in the Plan_mapping sheet has an extra white space after the Plan Name, it would definitely not find a match in the PlanUIs sheet in order to get that UniqueIdentifier. So I get a blank row.

So would Trim work then?

I tried this, but I still get a blank column because of the white space.

Function getPlanUI(varPlan)
Dim iRow As Long
With Worksheets("PlanUIs")
  For iRow = 2 To 105
    If Trim(varPlan) <> "" Then
    If .Cells(iRow, 2).Value = varPlan Then
        'grab the value of the 'AiRows'
        getPlanUI = .Cells(iRow, 1).Value
        'MsgBox ("Plan: " & varPlan & " / PlanUI: " & getPlanUI)
        'Exit this for loop on finding the 1st match.
        Exit For
    End If
    End If
  Next
End With
End Function

 

[PHV]

Perhaps this ?
If [!]Trim([/!].Cells(iRow, 2).Value[!])[/!] = [!]Trim([/!]varPlan[!])[/!] Then

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[reportingbuzz]

Thanks for that tip. It worked.