• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to re position the cell w.r.t screen in random manner

nagovind

Member
Please refer to the below excel cell C38
This is at the left corner
Say after a macro runs it will scroll or moves, at the end of the code I need to re position the cell appearance to the same position
This position is random
I need to save the last cell display portion by knowing the corner of the screen and return the same
Is there is any way out? Kindly advise
Regards
Govind


59602
 
Use ActiveWindow.ScrollColumn & ScrollRow to save / restore the left corner
and Selection.Address (or from ActiveCell) to save / restore the selection or the active cell
as you can easily see within the Excel inner help …
 
I can get the top left corner Cell address
K = Cells(ActiveWindow.ScrollRow, ActiveWindow.ScrollColumn).Address(0, 0)
But unable to get the Row and column number within VBA
Say K = L456
How to write a macro to scroll to the row and column which I'm unable to get

If I know the row and column of the L456 cell by VBA I can complete the code by this
Kindly advise

ActiveWindow.ScrollRow = RS
ActiveWindow.ScrollColumn = CS
 
As you can save the column & row properties to variables like Variable = Property so you can restore them via Property = Variable …​
 
Dear Marc L
Actually, I don't know how to do
I can able to get ONLY the cell address of LEFT TOP CORNER CELL
With the cell address how to get the row and column number of the cell address. Once I get the row and column number of the cell address through VBA I can resolve it
Thank you
 
Say after a macro runs it will scroll or moves, at the end of the code
Generally (except very few exceptions) a good code does not scroll or move as it is very not necessary for VBA in order to work with objects ‼​
 
vletm
Could you please share the working code
I need the VBA code that returns the Column and Rownumber of a given cell address
Thank you
 
nagovind
Here, two 'the working code' samples, which You would like to get.
Dear
vletm
Thank you for the code.
Actually, we need to supply the random cell address to the code not the current location of the cell
So need the code that will provide the Row and column if I give the input as L134 = Which row and which column? not the selected cell row column instead the supplied cell address row column.
Kindly do the needful
 
nagovind
if I give the input as L134 = Which row and which column?
There are TWO (2) codes ...
The 1st code shows selected and
2nd code shows Input eg Your L134.
Could You check again with both eyes?
and do the needful?
 
nagovind
... didn't understand the code ... hmm?
Means that You didn't test before start to ask something ...
... just make clear for You,
USER gives valid input - there is no error handling!
 
@vletm
This I have used in my code
Thanks a lot
Sub Inb()
RCNUM = InputBox("Address: ", "RCNUM", "")
If RCNUM <> Empty Then
MsgBox "R: " & Range(RCNUM).Row & Chr(13) & "C: " & Range(RCNUM & 1).Column, vbInformation, "Given Cell Address: " & RCNUM
End If

End Sub
 
nagovind
As I wrote .. USER gives valid input.

Please, reread
 
Back
Top