Cursor position macro

B

bluegold

Active Member
Joined
Jun 21, 2009
Messages
279
Hi guys I'm having trouble working this one out.

I want the cursor to default to the 1st empty cell in Column AP but ONLY if the date in Column A is less than today's date. If its not less than today's date than the cursor should move to the 1st empty cell in Column D.
So lets presume today's date is 27/11/17. In the table below the cursor should move to the 1st empty cell in column AP.

​
[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]Col A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]AP[/TD]
[TD="align: center"]AQ[/TD]
[/TR]
[TR]
[TD]26/11/17[/TD]
[TD]Anderlecht[/TD]
[TD]Kortrijik[/TD]
[TD]$1.17[/TD]
[TD]$6.00[/TD]
[TD]$10.00[/TD]
[TD]4[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]26/11/17[/TD]
[TD]Club Brugge[/TD]
[TD]Waregem[/TD]
[TD]$1.30[/TD]
[TD]$4.50[/TD]
[TD]$7.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/12/17[/TD]
[TD]Charleroi[/TD]
[TD]Oostende[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/12/17[/TD]
[TD]Lokeron[/TD]
[TD]Anderlecht[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


In this second example the cursor would move to the 1st empty cell in Column D because the 1st empty cell in column AP is not less than today's date.

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]Col A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]AP[/TD]
[TD="align: center"]AQ[/TD]
[/TR]
[TR]
[TD]26/11/17[/TD]
[TD]Anderlecht[/TD]
[TD]Kortrijik[/TD]
[TD]$1.17[/TD]
[TD]$6.00[/TD]
[TD]$10.00[/TD]
[TD]4[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]26/11/17[/TD]
[TD]Club Brugge[/TD]
[TD]Waregem[/TD]
[TD]$1.30[/TD]
[TD]$4.50[/TD]
[TD]$7.00[/TD]
[TD]3[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]1/12/17[/TD]
[TD]Charleroi[/TD]
[TD]Oostende[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/12/17[/TD]
[TD]Lokeron[/TD]
[TD]Anderlecht[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Sort by date Sort by votes
Try

Code: 
Sub k1()
lastrow = Worksheets("Sheet1").Cells(Rows.Count, "AP").End(xlUp).Row
If Cells(lastrow, 1) < Date() Then
    colsel = 42
Else
    colsel = 4
End If
Cells(lastrow + 1, colsel).Select
End Sub
 
Upvote 0
Ok this is my current code, its quite complicated as I didn't divulge all the details before to try and keep it simple. I tried to implement your code Special-K (see red text) but it keeps putting the cursor in AP columns even when the conditions for Column AP are not met?



Dim A As Long
Dim B As Long
Dim D As Long
Dim AP As Long
Dim H As Long
Dim J As Long
Dim Lastrow As Long
Dim FirstEmpty As Range


Application.ScreenUpdating = True

A = Range("A1").End(xlDown).Row
B = Range("B1").End(xlDown).Row
D = Range("D1").End(xlDown).Row
AP = Range("AP1").End(xlDown).Row
H = Range("H1").End(xlDown).Row
J = Range("J1").End(xlDown).Row
Lastrow = Cells(Rows.Count, "AP").End(xlUp).Row

If A = D And A = AP Then
Range("A" & Rows.Count).End(xlUp)(2).Select
ElseIf B < A Then
Range("B" & Rows.Count).End(xlUp)(2).Select
ElseIf Cells(Lastrow, 1) < Date Then
Range("AP" & Rows.Count).End(xlUp)(2).Select
ElseIf D < A And AP <= D Then
Set FirstEmpty = IIf(Range("D1") <> "", Range("D1").End(xlDown).Offset(1, 0), Range("D1"))
Range("D" & Rows.Count).End(xlUp).Offset(1).Select
FirstEmpty.Select
ElseIf H < A Then
Range("G" & Rows.Count).End(xlUp)(2).Select
ElseIf J < H And H = A Then
Set FirstEmpty = IIf(Range("J1") <> "", Range("J1").End(xlDown).Offset(1, 0), Range("J1"))
Range("J" & Rows.Count).End(xlUp).Offset(1).Select
FirstEmpty.Select
Else
Range("G" & Rows.Count).End(xlUp)(2).Select
End If

Application.ScreenUpdating = True


End Sub
 
Upvote 0
To do things using Vba you do not need to keep selecting cells.

It would be nice if you were to just tell us what your objective is here.

90% of your script just seems to be selecting ranges. I cannot see what your wanting to do other then just selecting things.
 
Upvote 0
This macro is all about selecting the appropriate free cell in a column depending on my conditions. Its intended to speed up data entry.

So for the 1st condition "If A = D And A = AP Then
Range("A" & Rows.Count).End(xlUp)(2).Select"
I want the cursor to go to 1st free cell in Column A because Columns A, D & AP are equal in rows of data.
The table below meets this condition.

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[/TR]
[TR]
[TD="align: center"]Col A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]AP[/TD]
[TD="align: center"]AQ[/TD]
[/TR]
[TR]
[TD]26/11/17[/TD]
[TD]Anderlecht[/TD]
[TD]Kortrijik[/TD]
[TD]$1.17[/TD]
[TD]$6.00[/TD]
[TD]$10.00[/TD]
[TD]4[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]26/11/17[/TD]
[TD]Club Brugge[/TD]
[TD]Waregem[/TD]
[TD]$1.30[/TD]
[TD]$4.50[/TD]
[TD]$7.00[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1/12/17[/TD]
[TD]Charleroi[/TD]
[TD]Oostende[/TD]
[TD]$1.80[/TD]
[TD]$3.40[/TD]
[TD]$3.75[/TD]
[TD]3[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
-------------------------------------------------------------------------------------------------
In the 2nd condition "ElseIf B < A Then
Range("B" & Rows.Count).End(xlUp)(2).Select"
I want the cursor to move to 1st free cell in Column B because Column B has less rows of data than Column A
The table below meets this condition

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[/TR]
[TR]
[TD="align: center"]Col A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]AP[/TD]
[TD="align: center"]AQ[/TD]
[/TR]
[TR]
[TD]26/11/17[/TD]
[TD]Anderlecht[/TD]
[TD]Kortrijik[/TD]
[TD]$1.17[/TD]
[TD]$6.00[/TD]
[TD]$10.00[/TD]
[TD]4[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]26/11/17[/TD]
[TD]Club Brugge[/TD]
[TD]Waregem[/TD]
[TD]$1.30[/TD]
[TD]$4.50[/TD]
[TD]$7.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/12/17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/12/17[/TD]
[TD][/TD]
[TD]

[/TD]
[/TR]
</tbody>[/TABLE]

---------------------------------------------------------------------------------------------------

In the 3rd condition "ElseIf Cells(Lastrow, 1) < Date Then
Range("AP" & Rows.Count).End(xlUp)(2).Select"
I want the cursor to move to 1st free cell in Column AP because Column AP's 1st free cell's date (26/11/17 is less than today's date (27/11/17).
The table below meets this condition.

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[/TR]
[TR]
[TD="align: center"]Col A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]AP[/TD]
[TD="align: center"]AQ[/TD]
[/TR]
[TR]
[TD]26/11/17[/TD]
[TD]Anderlecht[/TD]
[TD]Kortrijik[/TD]
[TD]$1.17[/TD]
[TD]$6.00[/TD]
[TD]$10.00[/TD]
[TD]4[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]26/11/17[/TD]
[TD]Club Brugge[/TD]
[TD]Waregem[/TD]
[TD]$1.30[/TD]
[TD]$4.50[/TD]
[TD]$7.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/12/17[/TD]
[TD]Charleloi[/TD]
[TD]Oostende[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

----------------------------------------------------------------------------------------------------

In the 4th condition "ElseIf D < A And AP <= D Then
Set FirstEmpty = IIf(Range("D1") <> "", Range("D1").End(xlDown).Offset(1, 0), Range("D1"))
Range("D" & Rows.Count).End(xlUp).Offset(1).Select
FirstEmpty.Select"
I want the cursor to move to 1st free cell in Column D because Column D has less rows of data than Column A and Column AP has <= rows of data than Column D.
The table below meets this condition.

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[/TR]
[TR]
[TD="align: center"]Col A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]AP[/TD]
[TD="align: center"]AQ[/TD]
[/TR]
[TR]
[TD]26/11/17[/TD]
[TD]Anderlecht[/TD]
[TD]Kortrijik[/TD]
[TD]$1.17[/TD]
[TD]$6.00[/TD]
[TD]$10.00[/TD]
[TD]4[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]26/11/17[/TD]
[TD]Club Brugge[/TD]
[TD]Waregem[/TD]
[TD]$1.30[/TD]
[TD]$4.50[/TD]
[TD]$7.00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/12/17[/TD]
[TD]Charleloi[/TD]
[TD]Oostende[/TD]
[/TR]
[TR]
[TD]

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
and so on.. does this help?
 
Upvote 0
Create a blank spreadsheet.

Place dates in column A
Place data in columns D and AP

Run the code, just the code I supplied.
It should follow the rules you specified depending on what dates you have in A.

I tested with numbers in column A not dates but it should work.

If it does work then, respectfully, the fault would lie with the rest of your code or the data to which it is referring.
Perhaps A does equal D as well as AP ?
Or B is indeed less than A ?
In which case the highlighted lines won't get executed.
 
Last edited:
Upvote 0
You said:
So for the 1st condition "If A = D And A = AP Then
Range("A" & Rows.Count).End(xlUp)(2).Select"
I want the cursor to go to 1st free cell in Column A because Columns A, D & AP are equal in rows of data.

But you did not say what you want to do when this condition is met.

I can assure you this is not the way you should need to do things

And then you have numerous more if statements.
But I'm just here trying to help people not argue over how things should be done.

Your determined this is the best way to do what you want.

And I have still not seen what is suppose to happen when all these if statements are me.
 
Upvote 0
Following on from th post #7

So for the 1st condition "If A = D And A = AP Then
Range("A" & Rows.Count).End(xlUp)(2).Select"
I want the cursor to go to 1st free cell in Column A because Columns A, D & AP are equal in rows of data.

This was not mentioned in the initial description of the problem.
It was simply cursor goes to column D or AP depending on a condition.
 
Upvote 0
You must log in or register to reply here.

Similar threads

MrDB4Excel
  • Question Question
Populate Date to Another Workbook
Replies
7
Views
140
MrDB4Excel
MrDB4Excel
F
  • Question Question
Enhance simply subtract of date/times of time spent, to including indexing and subtracting time blocked from another table (with multiple criteria)
Replies
0
Views
388
freeb1893
F
MrDB4Excel
  • Solved
If Exist in a Column Range
Replies
8
Views
2K
DanteAmor
DanteAmor
R
  • Question Question
Colour cell if Due Date is less than 8 days away
Replies
3
Views
366
Fluff
Fluff
B
  • Question Question
Controlling Cursor position with Macro
Replies
5
Views
703
Smitty
Smitty

Forum statistics

Threads
1,223,236
Messages
6,170,912
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top