Setting a found item to a Variable

[simora]

I'm trying to set the result of Cells(rngFind.Column) to a variable,
and then to select the first cell in that column and use that value as another variable.

I can clearly see that Cells(rngFind.Column) is giving me the correct column, but for some reason, the variable doesn't get the value of the column.

How can I set this ? Any Ideas, suggestion, & code, all welcome.

Thanks

[Kenneth Hobs]

There is not value a column. There is a value for a cell in the column. Since you did not set the row so the return value is for row 1. Was that not what you wanted?

Sub Main()
Dim rngFind As Range
Set rngFind = Range("E2")
MsgBox Cells(rngFind.Column).Address
End Sub

[SamT]

MyVar = Column Number

MyVar = rngFind.Column

OtherVar = Value of designated cell

OtherVar = Cells(RowNumber, rngFind.Column).Value
' Same as
OtherVar = Cells(RowNumber, MyVar).Value

[SamT]

Ken,

MsgBox Cells(rngFind.Column).Address
Will return "$A$5"

The Column Parameter was not specified. "rngFind.Column" was placed in the Row Parameter position of Cells()

[Kenneth Hobs]

Sam, please run my code before drawing that conclusion without testing. It is a weird deal...

[SamT]

Ken,
Holy Moley. That is certainly unexpected.

Edit: No it's not. I forgot basic VBA rules. VBA counts from left to right, then down. The fifth cell on any sheet is the fifth cell in the first row.

Cells(n) will be the nth cell in in the first row, assuming that n is less than Columns.Count.



Edited to change "first Column" to "first row"

[simora]

Thanks guys.


MyVar = rngFind.Column
OtherVar = Cells(1, MyVar).Value

Was the solution I was aiming for.
Ken: (rngFind.Column) was actually returning Column 4 .

Again. Thanks a million.

[simora]

& MsgBox Cells(rngFind.Column).Value Gives the Column header Value. Row 1 of the found value.

Just tested it.