Find all loop in VBA

[wibni]

Hello,

I'm using Excel 2003 and have the table below.
I'm looking for a way to fill the columns in yellow (each month) with a macro.
The values for those cells come from the top of the table ( row 2 to 17).
What my current macro does is to go through each cell in column I starting at row 21. Then search for this value in range D2:Z5.
If the value exists, copy the site description in column B to column I.
I basically link every rig to a site.

My current macro only works for the 1st month, or the top rows.
I know I need to somehow do another loop to go through the months as well but I couldn't find a solution.
Would anyone here please be able to assist me?

My code so far works, but only for 1 month becasue I do not evaluate column A yet.

Sub Site_Lookup()
    
    Dim rngSites As Range, rngLookup As Range, rngMonths As Range, cell As Range, found As Range
    Dim LR As Long, i As Long
        
   
    LR = Range("I" & Rows.Count).End(xlUp).Row
    Const FR As Long = 21 '<-- First Row of actual data
           
    Set rngMonths = Range("A2:A17")
    Set rngSites = Range("D2:Z17")
    Set rngLookup = Range("I" & FR & ":I" & LR)
      
    For Each cell In rngLookup
        If cell.Value <> "" Then
            If IsNumeric(cell) Then
                Firstfound = ""
                Set found = rngSites.Find(cell.Value, , xlValues, xlWhole, xlByRows, xlNext, False)
                If Not found Is Nothing Then
                    ' Site found
                    cell.Offset(, 1).Value = Range("B" & found.Row).Value
                Else
                    ' No Match
                    cell.Offset(, 1).Value = "N\A"
                End If
            Else
                ' Text
                cell.Offset(, 1).Value = cell.Value
            End If
        End If
    Next cell  
End Sub

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Months	Site Desc	Sum Rigs	Rigs	*	*	*	*	*	*	*	*	*
2	April	GGM	6	008	028	038	055	066	073	*	*	*	*
3	April	NMM	7	005	011	021	024	032	042	048	*	*	*
4	April	MWA	13	008	028	038	055	066	073	005	011	042	048
5	April	KNC	13	008	028	038	055	066	073	005	011	042	048
6	May	GGM	6	008	028	038	055	066	073	*	*	*	*
7	May	NMM	8	005	011	021	024	032	042	048	049	*	*
8	May	MWA	14	008	028	038	055	066	073	005	011	042	048
9	May	KNC	14	008	028	038	055	066	073	005	011	042	048
10	June	GGM	*	*	*	*	*	*	*	*	*	*	*
11	June	NMM	*	*	*	*	*	*	*	*	*	*	*
12	June	MWA	*	*	*	*	*	*	*	*	*	*	*
13	June	KNC	*	*	*	*	*	*	*	*	*	*	*
14	July	GGM	*	*	*	*	*	*	*	*	*	*	*
15	July	NMM	*	*	*	*	*	*	*	*	*	*	*
16	July	MWA	*	*	*	*	*	*	*	*	*	*	*
17	July	KNC	*	*	*	*	*	*	*	*	*	*	*
18	*	*	*	*	*	*	*	*	*	*	*	*	*
19	*	*	*	*	*	*	*	*	*	April		May
20	AcctID	Acct Desc	Header	Category	SubCategory	Country	AccSegment	ManCo	RigSite	Site Jan	Original	Site Feb	Original
21	10-4110-005	Revenue - Drilling	EBITDA	Revenue	Revenue	10	4110	10	005	NMM	-207,270	NMM	-142,615
22	10-4110-008	Revenue - Drilling	EBITDA	Revenue	Revenue	10	4110	10	008	GGM	-165,284	GGM	-158,815
23	10-4110-011	Revenue - Drilling	EBITDA	Revenue	Revenue	10	4110	10	011	NMM	-149,812	NMM	-205,000
24	10-4110-021	Revenue - Drilling	EBITDA	Revenue	Revenue	10	4110	10	021	*	-45,375	*	-99,427
25	10-4110-024	Revenue - Drilling	EBITDA	Revenue	Revenue	10	4110	10	024	*	-226,452	*	-234,746
26	10-4110-028	Revenue - Drilling	EBITDA	Revenue	Revenue	10	4110	10	028	*	-154,882	*	-140,032
27	10-4110-029	Revenue - Drilling	EBITDA	Revenue	Revenue	10	4110	10	029	*	-42,456	*	0
28	10-4110-032	Revenue - Drilling	EBITDA	Revenue	Revenue	10	4110	10	032	*	-173,345	*	-205,194
29	10-4110-038	Revenue - Drilling	EBITDA	Revenue	Revenue	10	4110	10	038	*	-30,278	*	-229,486
30	10-4110-042	Revenue - Drilling	EBITDA	Revenue	Revenue	10	4110	10	042	*	-196,846	*	-184,471
Sheet1

 

[wibni]

Nobody has an idea?

 

[Marcelo Branco]

Hi,

Some ideas (try it on a test-workbook)

Create a macro that calls Site_Lookup with an argument = column-number

Sub Call_Site_Lookup()
    Dim i As Long, MyColumn As Long
 
    MyColumn = 9 ' Column I
    Call Site_Lookup(MyColumn)
 
    For i = 3 To 17
        If Range("A" & i) <> Range("A" & i - 1) Then
            MyColumn = MyColumn + 2
            Call Site_Lookup(MyColumn)
        End If
    Next i
 
End Sub

(You can improve it making the lastrow (17) a variable, etc)

Small changes in Sub Site_Lookup

Sub Site_Lookup([B]AColumn as long[/B])
     ......
 
[B]  LR = Cells(Rows.Count, AColumn).End(xlUp).Row[/B]
 
[B]  Set rngLookup = Range(Cells(FR, AColumn), Cells(LR, AColumn))[/B]
[B] .....[/B]

HTH

M.

 

[wibni]

Thanks Marcelo,
Thats an excellent idea.

I'll just try and find a way to make sure that when the macro is in column April it only searches in rows 2 to 5.
Or for May it only searches in rows 6 to 9 and so on.

 

[Marcelo Branco]

Thanks Marcelo,
Thats an excellent idea.

I'll just try and find a way to make sure that when the macro is in column April it only searches in rows 2 to 5.
Or for May it only searches in rows 6 to 9 and so on.

You are welcome and tks for the feedback

M.