Opening a workbook with VBA and incrementing values

I know that this could be done more easily though another tool, but I am doing the following to learn more on VBA.

I have one file with a column called "geography".

In geography, I have specific places, whihch macth to specific values in a colomn from another file. The other file provides the indication of the category and the last "amount" in a specific category.

The match can be described as follow: "Italy" is coded in the other file as "BC". "UK" is coded in the other file as "AB".

"ItalyZ" begins at 0000 and ends at 2000 "ItalyB" begins at 2001 and ends at 4000

"UKY" begins at 4000 and ends at 6000 "UKM" begins at 6001 and ends at 8000.

In this file, I want to write a macro which would enable me to do the following:

• look for another file

• loop in the columns to find a header (index)

• loop in the found column to find a highest amount in four categories

• Match the categories with each categories of the "Geography" column in my original file

• Increment the highest value of each category each time the item is found

Here is an illustration. My first file has several columns, but the only relevant column has the Geography header:

  |---------------------|------------------|------------------|
  |      Snouba         |       Col n      |    Geography     |
  |---------------------|------------------|------------------|
  |    Rab19881         |     ..........   |     ItalyZ       |
  |---------------------|------------------|------------------|
  |      Rab19882       |    .........     |     ItalyB       |
  |---------------------|------------------|------------------|
  |     fsfguba         |    .........     |      UKY         |
  |---------------------|------------------|------------------|
  |      Sggnouba       |   .........      |      UKM         |
  |---------------------|------------------|------------------| 

Here is an illustration of my second workbook, which could as well has several header, but the only relevant would be "Code", in which I would like to look for the highest amount of each category (categroy one being between 0000 to 2000; categroy 2 between 2001 to 4000; categroy 3 between 4001 to 6000; categroy 4 between 6001 to 8000)

    |---------------------|------------------|------------------|
    |     Bfgrhrrh        |       Col n      |      Code        |
    |---------------------|------------------|------------------|
    |    Rgtrhab19        |     ..........   |     BC0003       |
    |---------------------|------------------|------------------|
    |      grgrggrg       |    .........     |     BC2333       |
    |---------------------|------------------|------------------|
    |     grggrga         |    .........     |      AB4334      |
    |---------------------|------------------|------------------|
    |      Sgzhthzt       |   .........      |      AB6334      |
    |---------------------|------------------|------------------| 

Here is an illustration of what the results would look like:

    |      Snouba         |     Code         |    Geography     |
    |---------------------|------------------|------------------|
    |    Rab19881         |      BC0004      |     ItalyZ       |
    |---------------------|------------------|------------------|
    |      Rab19882       |       BC2334     |     ItalyB       |
    |---------------------|------------------|------------------|
    |     fsfguba         |       AB4335     |      UKY         |
    |---------------------|------------------|------------------|
    |      Sggnouba       |      AB6334      |      UKM         |
    |---------------------|------------------|------------------| 

Here is my current code, it is really rough and it lack many element to work by I would really like to understand how to transcribe all my operations in VBA.

Sub Find()

Dim WorkbookCode As WorkbookCode
Dim Test_Search As String

Test_Search = "(the workbook path)"
'Open workbook


Dim Header As Range 'Define the concept of header
Dim Code As Range 'Define the header that I a looking for

Set Code = Range("1:1") 'Look in the first row

StrVariable = Left(str1, 2) 'Select the first two characters of my column

If StrVariable = "AB" Then
 StrVariable = "Italy" 'Write specific values if AB is found strings


ElseIf StrVariable = "BC" Then
 StrVariable = "UK" 'Write specific values if BC is found in the first two strings


Worksheet("Test_Search").Cells(Count) = Application.WorksheetFunction.Max ( .range( .cells(m,1) , .cells(n,1) )

If Range("Geography").Value = "ItalyZ" Then
   ...
Else If Range("Geography").Value = "ItalyB" Then
 ...
Else If Range("Geography").Value = "ItalyY" Then
 ...
Else If Range("Geography").Value = "ItalyM" Then



End Sub