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

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