Cursor focus in filtered list

[craigexcel]

Searched for, and tried, numerous variations of code, but haven't been able to find the right solution for actually changing the cursor focus in a filtered list (such as .Select, or .Activate). The goal is to change the cursor focus on the sheet (Sheet2) so the user can immediately enter data in the relevant cell without having to use Up/Down arrow keys, or, select the appropriate cell with the mouse.

I have 2 sheets:
> Sheet1: 1 with a year's worth of data for all Trial Balance accounts, by company code (Column A - account #, Column C - company code)
Sample data would be:
Acct # Acct descr Company code
1200-000 <name> 21
1200-000 <name> 22
1200-000 <name> 23
1210-000 <name> 21
1210-000 <name> 22
1210-000 <name> 23

> Sheet2: 1 with a year's worth of comments regarding the accounts' balances, by company code. (These analytical comments are pulled to the Sheet1 via VLOOKUP formula. This enables the current month's comments to be displayed on Sheet1 based on the month-end date in a cell on that sheet.)

Assuming the user places the cursor on the appropriate row (i.e. account # AND company code) for which a comment needs to be made on Sheet1, I can get both the account # and company code from Columns A & C. These can be used to filter the list on Sheet2, which will display only the (filtered) row for the account # and company code.

Then the remaining issue is how to get the cursor focus on the correct month's column (assume Columns A:L, for Jan thru Dec), with Row 1 being the header row containing the month names. Haven't been able to come up with code that will physically select / activate the correct column (for example, Column H, for August) in the filtered row so the user can immediately begin to enter the comment for that month. Other related solutions I've seen (both here, and an internet search, in general) don't seem to indicate how to go about this, especially since many will (rightly) remind us that it's usually not necessary to "select" a cell in order to do something with it.

Don't have the workbook at home, so will have to wait until I get to work to include the current code here, if necessary.

Hoping you have some suggestions to get me pointed in the right direction. Thanks.

Craig

 

[craigexcel]

Actually, the large majority of the code was rendered irrelevant, once it occurred to me last night that I could simply apply 2 filters, instead of just one (as the code I inherited is currently written). So now I don't have a need to loop thru each company code until the 'correct' one is found. But still would like to know if it's possible to activate the target month's cell so I can immediately begin typing a comment.

 

[craigexcel]

I solved the problem of placing the cursor focus on the cell to be edited. Code follows:

Sub EnterComment()

CurrRow = ActiveCell.Row
GotoAcct = Range("A" & CurrRow).Value           'initialize Account
GotoEntity = Range("C" & CurrRow).Value         'initialize Entity
CurrMonthOffsetValue = Range("X1").Value + 2    'initialize value to 'find' current month's comment cell

Sheets("Global TB GP").Select
Selection.AutoFilter Field:=23, Criteria1:=GotoAcct, Operator:=xlAnd    'filter Acct field - Col W
Selection.AutoFilter Field:=22, Criteria1:=GotoEntity, Operator:=xlAnd  'filter Entity field - Col V
Range("V1").Select

    Do
    ActiveCell.Offset(1, 0).Select
    Loop Until ActiveCell.EntireRow.Hidden = False

ActiveCell.Offset(0, CurrMonthOffsetValue).Activate

End Sub