update used rows

[pwill]

Hi, can anyone help with a macro?

I have Data on sheet1 - AJ2 has the row Ref and AK2:AQ for the Data and column AS to add 1 for every row used.
I have two buttons on sheet2 I want to use as a Plus and Minus (E1 as Minus and F1 as Plus)
I want to click the Plus button to add 1 to next row in column AS(sheet1) and use the Minus button to remove 1 from the last row used in column AS and have the Ref from Column AJ(sheet1) show in D1(sheet2) for the row in use on sheet1

ie if the Plus button is clicked on sheet2 - then 1 would be added to AS5 and the Ref from Column AJ would show 4 in D1(sheet2) and if the Minus button is clicked - then 1 would be removed from AS4 and the Ref from Column AJ would show 2 in D1(sheet2)

Sheet1

	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT
1	ref		Data							Used
2	1	0	0	0	0	0	0	0		1
3	2	0	0	0	0	0	0	0		1
4	3	0	0	0	0	0	0	0		1
5	4	0	0	0	0	0	0	0
6	5	0	0	0	0	0	0	0
7	6	0	0	0	0	0	0	0
8	7	0	0	0	0	0	0	0
9	8	0	0	0	0	0	0	0
10	9	0	0	0	0	0	0	0
11	10	0	0	0	0	0	0	0
12

<colgroup><col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:1353;width:28pt" width="37"> <col style="mso-width-source:userset;mso-width-alt:768; width:16pt" width="21" span="7"> <col style="mso-width-source:userset;mso-width-alt:1170;width:24pt" width="32"> <col style="mso-width-source:userset;mso-width-alt:1353;width:28pt" width="37"> <col style="width:48pt" width="64"> </colgroup><tbody>
</tbody>

Sheet2 D1 is showing 3 as the Ref for the row in use in the above example

	D	E	F	G	H
1	3	- button	+ button
2
3

<colgroup><col style="width:48pt" width="64" span="6"> </colgroup><tbody>
</tbody>

Any help would be appreciated

Regards
pwill

 

[BrianJN1]

I am a little confused.
For your Sheet 1, is the first column meant to be the row numbers that Excel has in the border?
Are the words "ref" "Data" and "Used" also appearing on your sheet?

As an example of my confusion "in D1(sheet2) for the row in use on sheet1", what is row? Is the Excel row or the row in Column AJ?

Another few questions.
Are we always discussing the very last value in AS? So pressing the buttons toggles add 1 to the last cell in AS, ie two "+" presses adds two consecutive "1s" down the column?
Can AS have a value greater than 1?

 

[pwill]

Hi BrianJN1, thanks for your reply, sorry for the confusion.
the row number is from the Ref column not the excel border, the word Ref is in AJ1, the word data is centered across AJ1:AQ1 and the word used is in AS1,
Yes we are discussing the very last value in Column AS and adds 1 to the last empty cell in AS and two '+' presses would add two consecutive 1's down the column and vise versa if pressing the '-'button.

Hope that helps

Regards
pwill

 

[BrianJN1]

Thanks for that.
I'm about to go to dinner and won't get back to this until the morning.

 

[pwill]

Ok thanks, I will await your reply

Regards
pwill

 

[pwill]

Sheet1

	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT
1	ref		Data							Used
2	1	0	0	0	0	0	0	0		1
3	2	0	0	0	0	0	0	0		1
4	3	0	0	0	0	0	0	0		1
5	4	0	0	0	0	0	0	0
6	5	0	0	0	0	0	0	0
7	6	0	0	0	0	0	0	0
8	7	0	0	0	0	0	0	0
9	8	0	0	0	0	0	0	0
10	9	0	0	0	0	0	0	0
11	10	0	0	0	0	0	0	0
12

<tbody>
</tbody>

Sheet2 D1 is showing 3 as the Ref for the row in use in the above example

	D	E	F	G	H
1	3	- button	+ button
2
3

<tbody>
</tbody>

Hi BrianJN1,
(forgot to say column AS can't have a value greater than 1)

Just thought I would I give a further example to try and make it clearer what I am trying to achieve.

If I now clicked on the '+' button twice at the point showing in the example above, on sheet1 it would show 1 has been added to AS5 and AS6 and the Ref from column AJ would show 5 in D1(sheet2)

Sheet1 after clicking twice on the '+' button

	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT
1	ref		Data							Used
2	1	0	0	0	0	0	0	0		1
3	2	0	0	0	0	0	0	0		1
4	3	0	0	0	0	0	0	0		1
5	4	0	0	0	0	0	0	0		1
6	5	0	0	0	0	0	0	0		1
7	6	0	0	0	0	0	0	0
8	7	0	0	0	0	0	0	0
9	8	0	0	0	0	0	0	0
10	9	0	0	0	0	0	0	0
11	10	0	0	0	0	0	0	0
12

<tbody>
</tbody>

Sheet2

	D	E	F	G	H
1	5	- button	+ button
2
3

<tbody>
</tbody>

going the other way if I had clicked the '-' button twice it would show 1 has been removed from AS4 and AS3 and the Ref from column AJ would show 1 in D1(sheet2)

Sheet1

	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT
1	ref		Data							Used
2	1	0	0	0	0	0	0	0		1
3	2	0	0	0	0	0	0	0
4	3	0	0	0	0	0	0	0
5	4	0	0	0	0	0	0	0
6	5	0	0	0	0	0	0	0
7	6	0	0	0	0	0	0	0
8	7	0	0	0	0	0	0	0
9	8	0	0	0	0	0	0	0
10	9	0	0	0	0	0	0	0
11	10	0	0	0	0	0	0	0
12

<tbody>
</tbody>

Sheet2

	D	E	F	G	H
1	1	- button	+ button
2
3

<tbody>
</tbody>

so basically whenever the buttons are clicked it either adds 1 or takes away 1 in column AS and shows the Ref Number from column AJ in D1

Hope that helps?

Regards
pwill

 

[BrianJN1]

I think these two macros do the trick.
I've put in a safeguard to the TakeRows to advise when Used has no contents, ie, no row is "Used".

Sub [COLOR=#ff0000][B]AddRow[/B][/COLOR]()
Dim lastrow As Long
Sheets("Sheet1").Select
lastrow = Cells(Rows.Count, "AS").End(xlUp).Row
Range("Sheet1!AS" & lastrow + 1).Value = 1
Sheets("Sheet2").Select
Range("Sheet2!D2").Value = Range("Sheet1!AJ" & lastrow + 1).Value
End Sub

Sub [COLOR=#ff0000][B]TakeRow[/B][/COLOR]()
Dim lastrow As Long
Sheets("Sheet1").Select
lastrow = Cells(Rows.Count, "AS").End(xlUp).Row
If lastrow = 2 Then
MsgBox ("All rows unused")
Exit Sub
End If
Range("Sheet1!AS" & lastrow).ClearContents
Sheets("Sheet2").Select
Range("Sheet2!D2").Value = Range("Sheet1!AJ" & lastrow - 1).Value
End Sub

 

[pwill]

Thanks Brian,

I'm just on my way to work so will give it a try when I get home and let you know how I get on

Regards
pwill

 

[pwill]

I think these two macros do the trick.
I've put in a safeguard to the TakeRows to advise when Used has no contents, ie, no row is "Used".

Sub [COLOR=#ff0000][B]AddRow[/B][/COLOR]()
Dim lastrow As Long
Sheets("Sheet1").Select
lastrow = Cells(Rows.Count, "AS").End(xlUp).Row
Range("Sheet1!AS" & lastrow + 1).Value = 1
Sheets("Sheet2").Select
Range("Sheet2!D2").Value = Range("Sheet1!AJ" & lastrow + 1).Value
End Sub

Sub [COLOR=#ff0000][B]TakeRow[/B][/COLOR]()
Dim lastrow As Long
Sheets("Sheet1").Select
lastrow = Cells(Rows.Count, "AS").End(xlUp).Row
If lastrow = 2 Then
MsgBox ("All rows unused")
Exit Sub
End If
Range("Sheet1!AS" & lastrow).ClearContents
Sheets("Sheet2").Select
Range("Sheet2!D2").Value = Range("Sheet1!AJ" & lastrow - 1).Value
End Sub

Thanks BrianJN1,

works great much appreciated

Regards
pwill

 

[BrianJN1]

Glad I could assist.