Vba question

Question

QUESTION: Hello

I have a region of data that changes each day. I am trying to create a macro that will multiply each cell in the last row of data by a stand alone active cell up to the end of the range. Any ideas? ANSWER: AP

assume we can find the last row by using column A

Sub MultiplyLastRowbyActiveCell() Dim r as Range, cell as Range Dim r1 as Range set r1 = ActiveCell set r = cells(rows.count,"A").End(xlup) set r = Range(r,cells(r.row,Columns.Count).End(xltoLeft)) for each cell in r

 cell.value = cell.value * r1.Value

Next End Sub

test it on a copy of your worksheet. Make sure the correct cell is the activecell when you run the macro

---------- FOLLOW-UP ----------

QUESTION: sorry, i asked my question wrong. I meant I want each cell in the last column, not row, to be multiplied by a static cell. for example.

I have data in columns A & B and a static number in cell E1. The number of rows in columns A and B change each day but A & B always have the same number of rows. I want the formula to multiply each cell in column B by the static figure in cell E1.

ANSWER: AP,

If the same number of rows, then assume for demonstration that is 100

Sub MultiplyColumnB() Set r = Range("B1:B100") for each cell in r

 cell.value = cell.Value * range("E1").Value

next End Sub

if you mean the number of columns vary each day, then in your example, column E is that last column because it is not empty.

so you would need to specify what the last row is. Again, for demo I will assume row 100

MultiplyLastColumn() Dim r as Range, cell as Range set r = Cells(100,columns.Count).End(xltoLeft) set r = range(cells(1,r.column),cells(100,r.column)) for each cell in r

 cell = cell.value * Range("E1").Value

end if Next end sub

---------- FOLLOW-UP ----------

QUESTION: sorry, im not being clear. the region is comprised of columns A and B but the number of rows in that region changes everyday. for example. one day the number of rows in both columns A and B will be 100, the next day column A and B will have 45 rows and the next day it may be 200 etc. While the number of rows change everyday, columns A & B will always have an equal number of rows.

I need to drop the formula down column C all the way down to the end of the range of data from columns A & B. The formula will be for example: C1=B1*E1, then for C2=B2*E1 then for C3=B3*E1 etc all the way to end of the data in column B. does that make more sense?

Answer

AP,

first mention that you want a formula in column C. nevertheless, this is what I understand you to want.

Sub Addformula() Columns(3).ClearContents Set r = Range("B1", Cells(Rows.Count, "B").End(xlUp)) r.Offset(0, 1).Formula = "=B1*$E$1" End Sub

Question

I have a document that looks at expenses data located on a sheet called "expenses". I have each of the expenses in cells B8, B9, B10, etc. I have each of the months (January through December) in cells D3, E3, F3, etc. Each of the months have a separate sheet associated with it that has the expenses listed for that month. Those sheets are labeled "1", "2", "3", etc. The numbers correspond to the month (1 = january). What I want to do is make it so that when you DOUBLECLICK on cell "D8" on the expenses sheet (January, Loans)...the macro will go to sheet 1, and then filter the data that has "Loans" in Column D.

Is that possible?

Answer

Doug,

Yes, you can use the BeforeDoubleClick event in the Expenses code module. Right click on the sheet tab for the Expenses sheet and select view code

in the left dropdown of the resulting module select Worksheet. In the right dropdown at the top of the resulting module, select BeforeDoubleclick

this will put an event declartion in the module that looks like this:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

End Sub

Anytime you double click on a cell in sheet Expenses, this macro will fire (given that macros are enabled). So the first thing you want to do in that macro is check it the cell being doubleclicked needs to be reacted to.

So it looks like the range of cells to check are D10:O20 (let's say your last expense category is in row 20. (I am not sure what you have between row 3 and row 8 where your categories start, but what I understood you to say is that row 3 as the months in columns D to O (12 months) and the expenses start in B8 and go down column B.

I will assume you won't have any merged cells in this range. Merged cells require special, almost explicit handling.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Dim r As Range, dt As Date, rMonth As Range, sName As String Dim sExp As String, sh As Worksheet Dim r1 As Range, r2 As Range

Set r = Worksheets("Expenses").Range("D8:O20")

' set cancel = true to cancel the normal behavior of a doubleclick (go into edit mode)

Cancel = True

' the Target reference in the argument list will hold a reference to the cell ' or cells that triggered the firing of the event. ' check if more than one cell has triggered the event - if so, jump out of the procedure

If Target.Count > 1 Then Exit Sub

' check if Target is blank

If Len(Trim(Target)) = 0 Then Exit Sub

' now check if Target

If Not Intersect(Target, r) Is Nothing Then
 ' determine the sheet name/month
 Set rMonth = r.Parent.Cells(3, Target.Column)
 dt = DateValue(rMonth.Value & " 1, 2001")
 sName = Month(dt)
 ' determine the expense name
 sExp = r.Parent.Cells(Target.Row, "B")
 Set sh = Worksheets(sName)
 On Error Resume Next
 sh.AutoFilterMode = False
 On Error GoTo 0
 ' find the cells in column D of the monthly sheet that
 ' contain text values
 Set r1 = sh.Columns(4).SpecialCells(xlConstants, xlTextValues)
 ' assume the last area of these cells is the data to be filtered
 Set r2 = r1.Areas(r1.Areas.Count)
 ' apply the filter
 r2.AutoFilter Field:=1, Criteria1:=sExp
 sh.Activate
End If

End Sub

The above was tested and worked for me. You have told me very little about the monthly sheets where I am to apply the filter. I assume the data to be filtered is contiguous and If I selected column D and do F5 choose special and select Constants and uncheck everything but text, that the last range/area selected will be the data to be filtered. If that doesn't work, then you need to tell me specifically where the data starts (the header row in the monthly sheet)

If you want me to send you a sample workbook where this code is working, you can contact me at (or if you have specific questions - note that I have no knowledge of what your knowledge level is of VBA and events and so forth - and what you want to do is not trivial.