How to go to a particular cell while input its content in a box [Closed]

Report
Posts
4
Registration date
Monday April 14, 2014
Status
Member
Last seen
April 17, 2014
-
 Blocked Profile -
Hello Trowa & Experts,

I have an excel data sheet with columns 250 odd.
Column headers have numbers like 100,101,102,103 and so on till 250 plus.
Under each column Header data is entered in 10 rows which are in date format.

Is it possible to go to a particular Column Header '178' (since there are so many columns) say with entry 178..........after we enter 178 in cell A1.?

Example:

In A1 i enter 189 then the selection will go to 189 Column header ?


Regards
Vinay



3 replies


Ok, give this a shot, paste into the VBA Code Pane. Let us know how it works:


Private Sub Worksheet_Change(ByVal Target As Range)
Dim thecolumn
thecolumn = Cells(1, 1).Value
'MsgBox (thecolumn)
Cells(1, thecolumn).Select
End Sub


Very Simple.

1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
4
Registration date
Monday April 14, 2014
Status
Member
Last seen
April 17, 2014

Thanks ac3mark,

Love the quote of yours !

Thanks for the code and it works too, but i have an exception for it as well.

My data is arranged in columns but the column headers have data as '100', '101' till '250'.
Now can the same code be modified so that i can enter only the data and the cursor moves to the corresponding column position.

Do you get the requirement ?

Regards
Vinay

So, your first column is labled as column 100. Take the code and subtract an offset number from the data entered, would that work?

So:
thecolumn = Cells(1, 1).Value - 98

...or some other OFFEST value?

Notice in my example, the first column is reserved for entry, so the offset number would now 98 as the column labeled 100 is in the second position of the indexed columns.





It works for me.

Or, are you asking to select the whole column, not just the cell?

"If you can't soar with the eagles, then don't fly with the flock!" - Oliver Sykes; Bring Me The Horizon