View Full Version : [SOLVED:] Setting a found item to a Variable
simora
01-28-2017, 12:11 PM
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
01-28-2017, 12:42 PM
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
MyVar = Column Number
MyVar = rngFind.Column
OtherVar = Value of designated cell
OtherVar = Cells(RowNumber, rngFind.Column).Value
' Same as
OtherVar = Cells(RowNumber, MyVar).Value
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
01-28-2017, 01:00 PM
Sam, please run my code before drawing that conclusion without testing. It is a weird deal...
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
01-28-2017, 10:24 PM
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
01-28-2017, 10:33 PM
& MsgBox Cells(rngFind.Column).Value Gives the Column header Value. Row 1 of the found value.
Just tested it.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.