Jump to cell

[Closed]
Report
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
Hello,


I have two columns A and B. Suppose i write 3 in Cell A1, then I want the cursor directly jumps to cell B3 and value 3 is filled in the cell B3. in the same way if i write 5 in cell A2 then i want cursor jumps directly to B5 and in this cell 5 is filled.
So my problem is that whatever i write in any cell of column A (i.e. cell value. from 1 to 100) the cursor jumps directly to the cell of column B (whatever vlaue is written in the cells of column A). Here it is to be noted that in columnA we will not leave any cell blank.. Is there any solution or macro for it. Please help

2 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
right click the sheet tab and click view code and copy paste this event code

save the file and then enter any number in column A and see what happens

Private Sub Worksheet_Change(ByVal Target As Range)
Dim j As Long, k As Long, m
If Target.Column <> 1 Then Exit Sub
Application.EnableEvents = False
j = Target.Value
Range("B" & j) = j
Application.EnableEvents = True
End Sub
Thanx Venkat for yr response. It is working. But still the problem remains on three grounds:-
1- when ever i delete the data on the sheet then Run-Time Error 13 Type mismatch comes. and then sheet does not response according to my need. I tried to debug it but failed.
2- secondly i want 50 sets of suuch columns in the same way i.e., A&B, C&D,E&F and so on. Set can be made in another way i.e., 50 on one side (in which i have to type ) and 50 columns on another side( i.e., in which data will go)
3- I want upper most row empty through out the sheet
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
803
try this modified event code. as explained right click tab and click view code and park it there.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim j As Long, k As Long, m
'If Target.Column <> 1 Then Exit Sub
If Target.Column Mod 2 = 0 Then Exit Sub
If Target.Column > 100 Then Exit Sub
If Len(Target) = 0 Then Exit Sub
Application.EnableEvents = False
j = Target.Value
Cells(j, Target.Column + 1) = j
Application.EnableEvents = True
End Sub



your item 3. regarding the first row blank can you enter only in row 2 or above. Does it require a code?( you can have if required)


try various experiments. If this had been told in the first message itself it would have avoided modification. In your message the cell referred to is A1 . perhaps you require first row to be blank