EMPTY Cell Value property when there is cell content

[FANAIT]

For some reason, I am having troubles in valuing this function: with 2 ranges in 2 different workbooks, the function is looking for matching cells and finding the column number of the matching cell in the 2-second range... but without any success. I've checked types, ranges, workbooks, etc...

Context:

1. "my_range_A" is located in workbook 1, and call Function (located in same workbook 1). Address range: A1:C3;
2. "anyRange " is located in workbook 2. Range address: A1:C3

---

dim my_varC as integer
my_varC = f_find_col_nbr (my_range_A, any_string)

Public Function f_find_col_nbr(anyRange As Range, my_varA As String) As Integer

Dim my_counter As Integer
Dim varB As String
Dim my_col_index As Integer
Dim anyRange_count_cols As Integer

   With workbooks(wkb).worksheets(wks).range(anyRange)
        anyRange_count_cols = .columns.count
        
          For my_counter = 1 To anyRange_count_cols
                varB = .Cells(1, jp)
                If varB = my_varA Then
                    f_find_col_nbr =  jp
              
                Else    ' Other values.
                    'Debug.Print "Not found...looking for..."
                End If
            
        Next
   End with
End Function

 

[Kaper]

You have argument in your function called anyrange, which is of type range
but then you use it as it would be the address of some range in wkb book wks sheet (address, so a string like "$A$1", not a range as such).

So start with replacing

   With workbooks(wkb).worksheets(wks).range(anyRange)

with

   With anyRange

 

[FANAIT]

You have argument in your function called anyrange, which is of type range
but then you use it as it would be the address of some range in wkb book wks sheet (address, so a string like "$A$1", not a range as such).

So start with replacing

   With workbooks(wkb).worksheets(wks).range(anyRange)

with

   With anyRange

Hi, Kaper. Yes, indeed, have replaced it.... but still getting the same value: empty!

 

[Kaper]

But what is jp value here?

varB = .Cells(1, jp)

How you really call the function what is the expected result?
Because in the description text you mentioned two ranges, whoile in the function you compare just one value with (some cells in a) range

 

[FANAIT]

But what is jp value here?

varB = .Cells(1, jp)

How you really call the function what is the expected result?
Because in the description text you mentioned two ranges, whoile in the function you compare just one value with (some cells in a) range

jp value is the column index number matching the cell address: the function is looping in the horizontal way, and when cells(1, jp).value matches the same cell content (value) then the passed argument, then the function result will be "jp2 (ie, the column number).

 

[FANAIT]

txs for your support, Kaper.