Cursor position macro

[bluegold]

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.

​

Col A	B	C	D	E	F	AP	AQ
26/11/17	Anderlecht	Kortrijik	$1.17	$6.00	$10.00	4	0
26/11/17	Club Brugge	Waregem	$1.30	$4.50	$7.00
1/12/17	Charleroi	Oostende
2/12/17	Lokeron	Anderlecht

<tbody>
</tbody>

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.

Col A	B	C	D	E	F	AP	AQ
26/11/17	Anderlecht	Kortrijik	$1.17	$6.00	$10.00	4	0
26/11/17	Club Brugge	Waregem	$1.30	$4.50	$7.00	3	2
1/12/17	Charleroi	Oostende
2/12/17	Lokeron	Anderlecht

<tbody>
</tbody>

 

[Special-K99]

Try

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

 

[bluegold]

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

 

[My Aswer Is This]

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.

 

[bluegold]

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.

Col A	B	C	D	E	F	AP	AQ
26/11/17	Anderlecht	Kortrijik	$1.17	$6.00	$10.00	4	0
26/11/17	Club Brugge	Waregem	$1.30	$4.50	$7.00	2	1
1/12/17	Charleroi	Oostende	$1.80	$3.40	$3.75	3	2

<tbody>
</tbody>

-------------------------------------------------------------------------------------------------
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

Col A	B	C	D	E	F	AP	AQ
26/11/17	Anderlecht	Kortrijik	$1.17	$6.00	$10.00	4	0
26/11/17	Club Brugge	Waregem	$1.30	$4.50	$7.00
1/12/17
2/12/17

<tbody>
</tbody>

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

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.

Col A	B	C	D	E	F	AP	AQ
26/11/17	Anderlecht	Kortrijik	$1.17	$6.00	$10.00	4	0
26/11/17	Club Brugge	Waregem	$1.30	$4.50	$7.00
1/12/17	Charleloi	Oostende

<tbody>
</tbody>

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

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.

Col A	B	C	D	E	F	AP	AQ
26/11/17	Anderlecht	Kortrijik	$1.17	$6.00	$10.00	4	0
26/11/17	Club Brugge	Waregem	$1.30	$4.50	$7.00
1/12/17	Charleloi	Oostende

<tbody>
</tbody>

and so on.. does this help?

 

[Special-K99]

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.

 

[My Aswer Is This]

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.

 

[Special-K99]

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.