|
SKIPPING A COLUMN
HI ALL,
I have a VLOOKUP function that automatically fills in Column E for me, when I put data into Column D. So how can I get Excel to automatically go to Column F instead of Column E when I hit the TAB button? Thanks! -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200510/1 |
SKIPPING A COLUMN
One way to skip over a cell (or a column) with the TAB key
is to protect the cell(s) and turn on protection. That would not work for you if you are adding more information, like inserting rows. Another way would be to use an Event Macro. http://www.mvps.org/dmcritchie/excel/event.htm#ws_sc Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.column < 5 then exit sub On error resume next 'MUST reenable events... Application.EnableEvents = False ActiveCell.Offset(0, 1).Select Application.EnableEvents = True End Sub --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "eternal_cat via OfficeKB.com" <u14645@uwe wrote in message news:55cc70c20d566@uwe... HI ALL, I have a VLOOKUP function that automatically fills in Column E for me, when I put data into Column D. So how can I get Excel to automatically go to Column F instead of Column E when I hit the TAB button? Thanks! -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200510/1 |
SKIPPING A COLUMN
oK, and how do I do this?
David McRitchie wrote: One way to skip over a cell (or a column) with the TAB key is to protect the cell(s) and turn on protection. That would not work for you if you are adding more information, like inserting rows. Another way would be to use an Event Macro. http://www.mvps.org/dmcritchie/excel/event.htm#ws_sc Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.column < 5 then exit sub On error resume next 'MUST reenable events... Application.EnableEvents = False ActiveCell.Offset(0, 1).Select Application.EnableEvents = True End Sub --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm HI ALL, [quoted text clipped - 3 lines] Thanks! -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200510/1 |
SKIPPING A COLUMN
Hi Eternal_cat,
As explained in the link supplied by David http://www.mvps.org/dmcritchie/excel/event.htm#ws_sc right-click the sheet tab and paste the code. Return to Excel with Alt-F11 --- Regards, Norman "eternal_cat via OfficeKB.com" <u14645@uwe wrote in message news:55cd17522c8b2@uwe... oK, and how do I do this? David McRitchie wrote: One way to skip over a cell (or a column) with the TAB key is to protect the cell(s) and turn on protection. That would not work for you if you are adding more information, like inserting rows. Another way would be to use an Event Macro. http://www.mvps.org/dmcritchie/excel/event.htm#ws_sc Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.column < 5 then exit sub On error resume next 'MUST reenable events... Application.EnableEvents = False ActiveCell.Offset(0, 1).Select Application.EnableEvents = True End Sub --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm HI ALL, [quoted text clipped - 3 lines] Thanks! -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200510/1 |
SKIPPING A COLUMN
You could also place your "looked up" column somewhere else.
Key sorting data like postcodes can be very handy placed in the column to the left, but I often place "looked up data" waaaaay over to the right. HTH Geoff |
SKIPPING A COLUMN
nO, i WANTED TO GO TO THE NEXT CELL, SO SKIP E AND GO TO F. YUP. NOT THE NEXT
ROW. Norman Jones wrote: Hi Eternal_cat, As explained in the link supplied by David http://www.mvps.org/dmcritchie/excel/event.htm#ws_sc right-click the sheet tab and paste the code. Return to Excel with Alt-F11 --- Regards, Norman oK, and how do I do this? [quoted text clipped - 23 lines] Thanks! -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200510/1 |
SKIPPING A COLUMN
Hi Eternal_Cat,
Try: '================= Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Columns("D:D")) Is Nothing Then Target.Offset(0, 2).Select End If End Sub '<<================= --- Regards, Norman "eternal_cat via OfficeKB.com" <u14645@uwe wrote in message news:55cf47fba1f30@uwe... nO, i WANTED TO GO TO THE NEXT CELL, SO SKIP E AND GO TO F. YUP. NOT THE NEXT ROW. Norman Jones wrote: Hi Eternal_cat, As explained in the link supplied by David http://www.mvps.org/dmcritchie/excel/event.htm#ws_sc right-click the sheet tab and paste the code. Return to Excel with Alt-F11 --- Regards, Norman oK, and how do I do this? [quoted text clipped - 23 lines] Thanks! -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200510/1 |
SKIPPING A COLUMN
I ENTERED THAT CODE IN AND NOTHING HAPPENED
Norman Jones wrote: Hi Eternal_Cat, Try: '================= Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Columns("D:D")) Is Nothing Then Target.Offset(0, 2).Select End If End Sub '<<================= --- Regards, Norman nO, i WANTED TO GO TO THE NEXT CELL, SO SKIP E AND GO TO F. YUP. NOT THE NEXT [quoted text clipped - 17 lines] Thanks! -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200510/1 |
SKIPPING A COLUMN
AH YES IT WORKS NOW, AFTER YOU ENTER TEXT IN THE PREVIOUS COLUMN, VERY GOOD,
ONLY WRONG COLUMN, I'LL PLAY WITH IT. thanks!!!!!!!!!!!!!!! eternal_cat wrote: I ENTERED THAT CODE IN AND NOTHING HAPPENED Hi Eternal_Cat, [quoted text clipped - 17 lines] Thanks! -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200510/1 |
SKIPPING A COLUMN
Hi Eternal_Cat,
ONLY WRONG COLUMN, I'LL PLAY WITH IT. No, I think not. Your original request stated: I have a VLOOKUP function that automatically fills in Column E for me, when I put data into Column D. So how can I get Excel to automatically go to Column F instead of Column E when I hit the TAB button? That is precisely what the suggested code does. --- Regards, Norman "eternal_cat via OfficeKB.com" <u14645@uwe wrote in message news:55cfe8af56d28@uwe... AH YES IT WORKS NOW, AFTER YOU ENTER TEXT IN THE PREVIOUS COLUMN, VERY GOOD, ONLY WRONG COLUMN, I'LL PLAY WITH IT. thanks!!!!!!!!!!!!!!! eternal_cat wrote: I ENTERED THAT CODE IN AND NOTHING HAPPENED Hi Eternal_Cat, [quoted text clipped - 17 lines] Thanks! -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200510/1 |
SKIPPING A COLUMN
change the D:D to E:E since E is the column you want to skip.
then, I think, it would work the same as my original code in the newsgroup which is modified from the one on the website -- the website has lots of other information that you should be aware of when working with event macros. When you land on column E you want to be immediately transferred to the next column. in other words ActiveCell.Offset(0, 1).Select the zero is same row, the 1 is the next column. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "eternal_cat via OfficeKB.com" <u14645@uwe wrote in message news:55cfc1c61cebc@uwe... I ENTERED THAT CODE IN AND NOTHING HAPPENED Norman Jones wrote: Hi Eternal_Cat, Try: '================= Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Columns("D:D")) Is Nothing Then Target.Offset(0, 2).Select End If End Sub '<<================= --- Regards, Norman nO, i WANTED TO GO TO THE NEXT CELL, SO SKIP E AND GO TO F. YUP. NOT THE NEXT [quoted text clipped - 17 lines] Thanks! -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200510/1 |
SKIPPING A COLUMN
onLY THING now is that when i hit "Enter", it returns to the column where the
postcode is entered, [G], and not the desired column, which is [C]. Is there a way I can fix this so it always returns to column C? Thank you, Norman Jones wrote: Hi Eternal_Cat, Try: '================= Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Columns("D:D")) Is Nothing Then Target.Offset(0, 2).Select End If End Sub '<<================= --- Regards, Norman nO, i WANTED TO GO TO THE NEXT CELL, SO SKIP E AND GO TO F. YUP. NOT THE NEXT [quoted text clipped - 17 lines] Thanks! -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200510/1 |
SKIPPING A COLUMN
It kind of hard to follow without a complete description but I
think you want to fill in C, D, skip column E (col 5), fill in F, then when you tab to G you will skip down to next row to column C (col 3) If that is correct then Please read my page on event.htm, including the part you were pointed to before that had examples similar Another way would be to use an Event Macro. http://www.mvps.org/dmcritchie/excel/event.htm#ws_sc also take a look at protection http://www.mvps.org/dmcritchie/excel/protection.htm Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.column = 5 then On error resume next 'MUST reenable events... Application.EnableEvents = False ActiveCell.Offset(0, 1).Select Application.EnableEvents = True Else if target.column = 7 then On error resume next 'MUST reenable events... Application.EnableEvents = False ActiveCell.Offset(1, -4).Select Application.EnableEvents = True End if End Sub In your orginal question you indicated when you hit the TAB key now you mention ENTER. You can set your tools, options to have the enter key move in different directions or not move at all. If you need more help you will have to be very specific as to what you want if someone is going to try to write code for you. The purpose of a newsgroup is to help you get started. The other way of getting to the next row to to use cell protection columns e would be protected --- locked columns g:iv would be protected -- locked as soon as you would have landed on G you would be taken to the next row specifically column A. I think you should be able to work with the information supplied with some experimentation. You are the only person who really knows what you want to do. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "eternal_cat via OfficeKB.com" <u14645@uwe wrote in message news:55f2bf8a189e7@uwe... onLY THING now is that when i hit "Enter", it returns to the column where the postcode is entered, [G], and not the desired column, which is [C]. Is there a way I can fix this so it always returns to column C? Thank you, Norman Jones wrote: Hi Eternal_Cat, Try: '================= Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Columns("D:D")) Is Nothing Then Target.Offset(0, 2).Select End If End Sub '<<================= --- Regards, Norman nO, i WANTED TO GO TO THE NEXT CELL, SO SKIP E AND GO TO F. YUP. NOT THE NEXT [quoted text clipped - 17 lines] Thanks! -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200510/1 |
| All times are GMT +1. The time now is 04:07 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com