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
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
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
Hello Ollie.
You can pass me workbook if you like.
But, best in a forum usually , is if you can produce a heavily reduced sample workbook with just enough data in it to demonstrate what you want done. Then you can change any sensitive real data to made up data. Important is only that the format stays the same, and that there is enough data there to demonstrate all possible scenarios.
Generally speaking, a well designed macro will work just the same on a large amount of data as it does on a small amount of data, so usually only a small amount of data is needed to develop the macro with.
It is much easier to develop a macro on a small amount of data.
Your choice.
If you want to pass me a full workbook, give me some link to a file sharing site or whatever via a Private Message, ( https://imgur.com/q3JQVRe )
Alternatively upload a reduced sample workbook.
If you pass a workbook, then explain again what you want: Use the actual data you supply as an example , and explain again exactly what should happen.
I will take another look in later today. If no one else helps in the meantime , then I will have a go for you later. It does not sound very difficult, at first glance.
Alan
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=oVb1RfcSHLM&lc=UgwTq-jZlZLnLQ5VB8Z4AaABAg.9Hroz-OyWog9tYjSMc1qjA
https://www.youtube.com/watch?v=0pbsf6sox34&lc=Ugxp9JFvvejnqA68W1t4AaABAg
https://www.youtube.com/watch?v=kfQC-sQxMcw&lc=UgyCxQWypNIhG2nUn794AaABAg.9q1p6q7ah839t UQl_92mvg
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgyOh-eR43LvlIJLG5p4AaABAg.9isnKJoRfbL9itPC-4uckb
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugy1B1aQnHq2WbbucmR4AaABAg. 9isY3Ezhx4j9itQLuif26T
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgxxajSt03TX1wxh3IJ4AaABAg. 9irSL7x4Moh9itTRqL7dQh
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugxa2VYHMWJWXA6QI294AaABAg. 9irLgSdeU3r9itU7zdnWHw
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgwJAAPbp8dhkW2X1Uh4AaABAg. 9iraombnLDb9itV80HDpXc
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgzIzQ6MQ5kTpuLbIuB4AaABAg. 9is0FSoF2Wi9itWKEvGSSq
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgykemWTw-fGoPwu8E14AaABAg.9iECYNx-n4U9iK75iCEaGN
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgykemWTw-fGoPwu8E14AaABAg.9iECYNx-n4U9iK7XF33njy
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugy_1xkcndYdzUapw-J4AaABAg.9iGOq_leF_E9iKCSgpAqA1
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugy_1xkcndYdzUapw-J4AaABAg.9iGOq_leF_E9iKCy--3x8E
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwNaJiNATXshvJ0Zz94AaABAg. 9iEktVkTAHk9iF9_pdshr6
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgykemWTw-fGoPwu8E14AaABAg.9iECYNx-n4U9iFAZq-JEZ-
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgxV2r7KQnuAyZVLHH54AaABAg. 9iDVgy6wzct9iFBxma9zXI
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugx12mI-a39T41NaZ8F4AaABAg.9iDQqIP56NV9iFD0AkeeJG
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwnYuSngiuYaUhEMWN4AaABAg. 9iDQN7TORHv9iFGQQ5z_3f
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwJ3yzdk_EE98dndmt4AaABAg. 9iDLC2uEPRW9iFGvgk11nH
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgyDWAVqCa4yMot463x4AaABAg. 9iH3wvUZj3n9iHnpOxOeXa
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwvLFdMEAba5rLHIz94AaABAg. 9iGReNGzP4v9iHoeaCpTG8
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugy_1xkcndYdzUapw-J4AaABAg.9iGOq_leF_E9iHpsWCdJ5I
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
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. :)
Ollie
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.youtube.com/watch?v=oVb1RfcSHLM&lc=UgwTq-jZlZLnLQ5VB8Z4AaABAg.9Hroz-OyWog9tYjSMc1qjA
https://www.youtube.com/watch?v=0pbsf6sox34&lc=Ugxp9JFvvejnqA68W1t4AaABAg
https://www.youtube.com/watch?v=kfQC-sQxMcw&lc=UgyCxQWypNIhG2nUn794AaABAg.9q1p6q7ah839t UQl_92mvg
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgyOh-eR43LvlIJLG5p4AaABAg.9isnKJoRfbL9itPC-4uckb
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugy1B1aQnHq2WbbucmR4AaABAg. 9isY3Ezhx4j9itQLuif26T
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgxxajSt03TX1wxh3IJ4AaABAg. 9irSL7x4Moh9itTRqL7dQh
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugxa2VYHMWJWXA6QI294AaABAg. 9irLgSdeU3r9itU7zdnWHw
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgwJAAPbp8dhkW2X1Uh4AaABAg. 9iraombnLDb9itV80HDpXc
https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgzIzQ6MQ5kTpuLbIuB4AaABAg. 9is0FSoF2Wi9itWKEvGSSq
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgykemWTw-fGoPwu8E14AaABAg.9iECYNx-n4U9iK75iCEaGN
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgykemWTw-fGoPwu8E14AaABAg.9iECYNx-n4U9iK7XF33njy
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugy_1xkcndYdzUapw-J4AaABAg.9iGOq_leF_E9iKCSgpAqA1
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugy_1xkcndYdzUapw-J4AaABAg.9iGOq_leF_E9iKCy--3x8E
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwNaJiNATXshvJ0Zz94AaABAg. 9iEktVkTAHk9iF9_pdshr6
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgykemWTw-fGoPwu8E14AaABAg.9iECYNx-n4U9iFAZq-JEZ-
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgxV2r7KQnuAyZVLHH54AaABAg. 9iDVgy6wzct9iFBxma9zXI
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugx12mI-a39T41NaZ8F4AaABAg.9iDQqIP56NV9iFD0AkeeJG
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwnYuSngiuYaUhEMWN4AaABAg. 9iDQN7TORHv9iFGQQ5z_3f
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwJ3yzdk_EE98dndmt4AaABAg. 9iDLC2uEPRW9iFGvgk11nH
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgyDWAVqCa4yMot463x4AaABAg. 9iH3wvUZj3n9iHnpOxOeXa
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwvLFdMEAba5rLHIz94AaABAg. 9iGReNGzP4v9iHoeaCpTG8
https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugy_1xkcndYdzUapw-J4AaABAg.9iGOq_leF_E9iHpsWCdJ5I
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
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 ""
So if I have understood correctly, then you just need to multiply the cell at Lr-1 by 10Code: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
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
Alan
The file I have uploaded has a button that runs that last macro, Sub LastValueInColumn_x_10()