VBA - Command Button to Multiply Value In Cell in Last Updated Column

**ollie** · 04-09-2020, 10:54 PM

Hi All

Major noob warning!

I would like to use VBA to multiply by 10, a specified cell in the last updated row when a command button is pressed.

e.g.

When the command button is clicked - If row 30 is the last updated row, then the value in G30 must be multiplied by 10.

Hopefully this makes sense? The command button is to be on sheet 1 and the updated cell on sheet 2.

I'm very sorry if my explanation is not clear. I'd be very happy to provide more information if required?

Thanks in advance

**DocAElstein** · 04-10-2020, 02:27 AM

Hello ollie.
Welcome to ExcelFox
I’ll answer what I understand from your question first, then we can take it further later if you still need /want any further help. I will keep it as simple as possible initially since you are new to VBA
( Your title and your description seem a bit contradictory, and I am not sure what worksheet is being updated, but I will give you/ us something to start with. … )

The uploaded workbook has 3 worksheets, Sht1 , Sht2 and GlobySht.
In the code module of worksheet 1 is an “event macro” that starts when any cells value is changed in the first worksheet.
That simple macro stores the address of the last changed cell. ( Typically we call the variable used a global variable. At least programmes do. I am not really a programmer so I did it a bit differently and store the address on the first cell of the third worksheet instead )

The coding behind the Command Button then uses that stored address to do the following: For example: If the last cell changed in the first worksheet was in row 3 ( of the first worksheet), then the value in cell G3 of the second worksheet will be multiplied by 10

Alan

Code:

Option Explicit ' http://www.excelfox.com/forum/showthread.php/2452-VBA-Command-Button-to-Multiply-Value-In-Cell-in-Last-Updated-Column
Public Globy As String ' Global variable
Private Sub Worksheet_Change(ByVal Target As Range) ' Target is pre  Set  by Excel to be the range changed
' Let Globy = Target.Address
 Let Worksheets.Item(3).Range("A1").Value = Target.Address ' store the address of the changed cell in the third worksheet's first cell
End Sub

Private Sub CommandButton1_Click() ' coding behind the command button
Worksheets.Item(2).Activate ' I don't need to do this.  it is just so i can see what is changed
Dim Rw As String
' Let Rw = Split(Tabelle1.Globy, "$", 3, vbBinaryCompare)(2)
 Let Rw = Split(Worksheets.Item(3).Range("A1").Value, "$", 3, vbBinaryCompare)(2) ' We have an address like  $A$1  We split it into 3 bits using the $ as the seperator.  So we have like  ""   "A"   "1"   The indicies are 0  1  2  for the array of things returned from the  split function   0 is empty  1 is the letter A  2 is the number 1
 Let Worksheets.Item(2).Range("G" & Rw & "").Value = Worksheets.Item(2).Range("G" & Rw & "").Value * 10
End Sub

**ollie** · 04-14-2020, 12:39 PM

Hey DocAElstein

Thanks so much for getting back to me and thanks for your efforts to help thus far. It's really appreciated.

I will offer a little more context to my problem as the solution you have provided isn't quite what I need. Although, that's due to me not being clear enough in my requirements.

I've created a Productivity Tracker to measure employees productivity throughout the working day. This workbook contains three worksheets

'Productivity Tracker (WFH)' - Sheet 1

'Productivity Output' - Sheet 2

'AVHT_Task Store' -Sheet 3 is just table being used to lookup average handling times (AVHTs) so we can immediately disregard.

Sheet 1 contains 3 drop downs which describe all the different tasks the user could do in the office. The user will select through the three drop downs and hit an already existing command button to log the piece of work they are doing and time and date stamp it. This is stored in sheet 2. The user repeats this process throughout the day logging the work they have completed. Every time a task is completed they select the drop downs, hit the command button and it populates a row on sheet 2 (Range A:J). Column A = ‘Activity’ (Drop Down 1), Column B = ‘Task’ (Drop Down 2), Column C = ‘Sub Task’ (Drop Down 3), Column D = ‘Reference Number’, Column E = ‘Time’, Column F = ‘Date’, Column G = ‘Average Handling Time (AVHT)’, Column H = ‘Time Taken’, Column I = ‘Percentage of time taken vs AVHT’, Column J = ‘Username’.
This is all working great!
However, I would like to add a second command button on sheet 1. The command button should multiply by 10, the value found in the last populated cell of column G (AVHT) of sheet 2. So in essence I need the user to be able to multiply the AVHT (Column G) of the last task they logged by 10.
I'm really sorry if I've confused things more! I'm happy to privately and securely share a copy of the tracker if this would help?

Many Thanks in advance

Ollie

**DocAElstein** · 04-14-2020, 01:28 PM

**ollie** · 04-15-2020, 01:45 PM

Hi Alan

I've taken your advice and have attached a trimmed down version of the workbook in question. It contains a small dummy data set. Hopefully this is enough to help you help me! The existing macro in the workbook has been cobbled together by me through various internet searches. It's definitely taught me a lot, however, running into this roadblock has also taught me how little I actually know! You may spot some glaring floors or find the existing Macro laughably basic.

Workbook contains multiple sheets but only two are relevant

Sheet1(Productivity Tracker (WFH))
Sheet2 (Productivity Output)

Users select the drop downs in sheet 1, hit the existing command button and it populates the next available row on sheet 2 (Range A:J). Column A = 'Activity' (Drop Down 1), Column B = 'Task' (Drop Down 2(C6), Column C = 'Sub Task' (Drop Down 3, Column D = 'Reference Number', Column E = 'Time', Column F = 'Date', Column G = 'Average Handling Time (AVHT)', Column H = 'Time Taken', Column I = 'Percentage of time taken vs AVHT', Column J = Username

I would like an additional command button on sheet 1 that multiplies (x10) the last populated cell in column G of sheet 2.

So if row 8 was the last row populated. The command button will multiply by 10 the value found in G8
Again - If row 10 was the last updated row the command button will multiply the value found in G10 by 10.

Hopefully this helps. IF not just let me know and I'll try to clarify. Once again thanks for your time and effort.

**DocAElstein** · 04-15-2020, 10:37 PM

Hello Ollie.
I doubt I personally would find anyone’s coding laughable, - because I am not a programmer, just an Excel VBA hobbyist. My coding does not follow any of the usual conventions!!

I may of miss-understood, but it sounds to me like the main thing you need to do is just to determine the last used cell in column G.

Finding the last used row in a particular column ( or said another way, the last used cell in that column ) , is very often done in this way… http://www.excelfox.com/forum/showth...ll=1#post11466
Basically you …_
_..take ( or got to ) the last cell on the worksheet in column G , which will be like at G & Rows.Count
_..Then you do the VBA equivalent of hitting the key combination of Ctrl+UpArrow – ( If you try that key combination yourself, when you are somewhere down column G , say row 50, then you will find that Excel shoots up to the last cell )….

But in your sample data workbook it shoots up to row 30, since that is where you have your last formula.

The VBA equivalent of that key combination is .End(Xlup)
That command takes you to the last cell with something in it. If you then look at the .Row property of the found cell, then it will tell you the row.

If you run this macro from in a code module on your sample data file, then it will tell you that the last used row in column G is 30

Code:

Sub LastRow()   '  http://www.excelfox.com/forum/showthread.php/2452-VBA-Command-Button-to-Multiply-Value-In-Cell-in-Last-Updated-Column?p=13055&viewfull=1#post13055
Dim WsProd As Worksheet
 Set WsProd = Worksheets("Productivity Output")
Dim LstUsdCel As Range
 Set LstUsdCel = WsProd.Range("G" & WsProd.Rows.Count & "").End(xlUp) ' At the last cell apply key combination  Ctrl+UpArrow ( .End(XlUp) )  That will take you back up to the last used cell
Dim Lr As Long
 Let Lr = LstUsdCel.Row

 MsgBox Prompt:="last cell in column G with something in it is at row  " & Lr
End Sub

So that is what you normally do, but it does not help you much, as it will just tell you where the last formula is.

One way that springs to my mind to find the last cell you used would be to look for a value of "" in the values. ( That will actually tell us the next cell, but from that we know the one before is what we want).
We can use the Range.Find method for that, https://docs.microsoft.com/en-us/off...cel.range.find
The Range.Find method looks a bit scary, but that’s just because it has lots of arguments allowing us to look for specific things in different ways.
The following use of it will I think find you the first row that has a "" in it. - It is applying the Range.Find method to column G. It startes at cell G2, then keeps looking at the next rows until , in the values, it finds ""

Code:

Sub LastValueInColumn()
Dim WsProd As Worksheet
 Set WsProd = Worksheets("Productivity Output")
Dim LstUsdCel As Range
 Set LstUsdCel = WsProd.Columns("G").Find(What:="", After:=WsProd.Range("G2"), LookIn:=xlValues, Lookat:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=True)
Dim Lr As Long
 Let Lr = LstUsdCel.Row

 MsgBox Prompt:="First cell in column G with """" in it is at row   " & Lr
 MsgBox Prompt:="Last cell in column G used is at row   " & Lr - 1
End Sub

So if I have understood correctly, then you just need to multiply the cell at Lr-1 by 10

But we might need to think again if you might have empty cells before the last used row in column G, or if I have not quite understood what you want.
For example , if you might have empty cells before the last used one , we could fiddle around with the arguments of Range.Find
For example we could start at the bottom of the worksheet, and look back up to find anything in the values like in this next macro. What this is doing is applying again the Range.Find method to column G. But this time we look for anything. And we start at the last cell in that column, and we keep looking at the previous row until we find anything
( The Range.Find method recognises "wild card things". What that means , for example , is that if you look for "*" , then it looks for anything. the * is the "wild card thing" representing anything

Code:

Sub LastValueInColumn2() ' Will also work if we have empty cells before the last used one
Dim WsProd As Worksheet
 Set WsProd = Worksheets("Productivity Output")
Dim LstUsdCel As Range
 Set LstUsdCel = WsProd.Columns("G").Find(What:="*", After:=WsProd.Range("G" & WsProd.Cells.Rows.Count & ""), LookIn:=xlValues, Lookat:=xlWhole, searchorder:=xlByRows, searchdirection:=xlPrevious, MatchCase:=True)
Dim Lr As Long
 Let Lr = LstUsdCel.Row

 MsgBox Prompt:="Last cell in column G used is at row   " & Lr
End Sub

So this is my current guess at what you want, ( this version assumes you have no empty cells before the last used cell in column G . So this is using the idea from macro Sub LastValueInColumn()
If you might have empty cells in cloumn G before the last used cell in column G, then you would need to modify it to use the idea of Sub LastValueInColumn2() )

Code:

Sub LastValueInColumn_x_10()
Dim WsProd As Worksheet
 Set WsProd = Worksheets("Productivity Output")
Dim LstUsdCel As Range
 Set LstUsdCel = WsProd.Columns("G").Find(What:="", After:=WsProd.Range("G2"), LookIn:=xlValues, Lookat:=xlWhole, searchorder:=xlByRows, searchdirection:=xlNext, MatchCase:=True)
Dim Lr As Long
 Let Lr = LstUsdCel.Row

 Let WsProd.Range("G" & Lr - 1 & "").Value = WsProd.Range("G" & Lr - 1 & "").Value * 10
End Sub