Match Function in Specific Column Excel VBA

Question

I'm trying to write a program in VBA for Excel 2011 that can search a column (which column that is is determined by another variable) for the number 1 so that it knows where to start an iteration.

Say that the number of the column is given by colnumvar. The only way I can think of is the Match function, which led me to write the following:

Dim rowvar As Integer
rowvar = WorksheetFunction.Match(1,Range(Cells(1,colnumvar),Cells(1000,colnumvar)),0) 

This gave me an error, however. After playing around with it some more, I realized that it must not accept the Cells([row],[col]) way of doing it, but rather wants something like Range("A1:A100"). Unfortunately, I can't do it that way, since the program is figuring out what column to look in. Any help for figuring out how to get past this would be greatly appreciated!

Answer 1

What you mean to do is better served with Range.Find.

Dim rngtrg As Range, rngsrc As Range
Dim ws As Worksheet
Set ws = ActiveSheet
Set rngsrc = ws.Range(ws.Cells(1,colnumvar),ws.Cells(1000,colnumvar))
Set rngtrg = rngsrc.Find(1,...)
rowvar = rngtrg.Row

Answer 2

this easy function retreive the positoin of that you find

Function rowvar(ByRef c As Integer) As Integer
    Dim keySrc As Integer
    keySrc = 22 'wath you want to find
    rowvar = WorksheetFunction.Match(keySrc, Range(Cells(1, c), Cells(1000, c)), 0)
End Function

use with rowvar(x)