Per attached I have a range of columns (variable) which I need to sum, the number of rows are also variable. I have defined the variables:
firstrow
lastrow
firstcol
lastcol
I have tried the following code which isnt quite working.
I'm trying to create a sum that gives the output with the currency format but also change column D from from D2 to the Total the currency format. (it changes based on how many customers). Here is the code
Code:
Windows("Test1.xls").Activate
Dim rng As Range
Set rng = Sheets("Sheet1").Range("D65536").End(xlUp).Offset(1, 0)
Range("D" & lastrow + 4).Select
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
Cells(lastrow + 1, 4).Formula = "=sum(D2:D" & lastrow & ")"
rng.Select ("D" & lastrow)
Selection.NumberFormat = "$#,##0.00"
I know it has to do with the rng.select statement but I'm just not sure how to make it work for all of Column D from D2 to the Total/Sum.
The cells A2:A25 are merged in my excel sheet and I would like to create subsequent merged cells below the A2:A25 set; however, I would like the user to determine the number of merged cell boxes.
The code I have written is as follows:
Dim lr As Long
lr = Application.InputBox("How many days was the monitor deployed?", Type:=2)
If lr = 0 Then Exit Sub Else lr = lr
Range("A2:A25").Select
Selection.AutoFill Destination:=Range("A2:A" & lr * 24), Type:=xlFillDefault
Range("A2:A" & lr * 24).Select
End Sub
So I am creating a macro where the user opens a message box and types in a value representing the number of days. That value is then calculated into an autofill equation but I keep getting an error.
Due DateInvDocument numberDebitCreditBalance
17/09/08L01607610714211201825.44
13/10/08L01637010284345841020
13/10/08L01637131713874501020
13/10/08L016373852202473461020
13/10/08L0163748.5222E+111020
15/10/08L01641811103746832316
The above is a part of a w/sheet. Rather than determining the last row in the debit column and credit column (to know which is greater) and use a loop to put a formula in each of the cells in the balance column (adding the debit and credit), I know I can use the with ... statement to input the formula at a go , but cannot figure it out.
Sub sumtotal()
With Sheets(1)
With .Range("G14", Range("G" & Rows.Count).End(xlUp))
.Clear
.Formula = "=sum(E14+F14)"
On Error Resume Next
End With
End With
End Sub
I have few range of data that may range from 0 to 24 and the return to "0 " after 24 and the 2nd set of value keep repeating 0 to 24, but I would like to keep adding 24 when the value return to 0 and if the number less than 5 will be adding 24 and the number from 6 to 8 will be equal to 11 and also the number from 9 to 23 will not change. I have attached an example that Colum A needed to be converted to column B (which I done it manually).
View 2 Replies View RelatedSay you define a public range variable called Inputworksheet and you set it to refer to the worksheet called Inputworksheet. You have a separate string variable with the value Inputworksheet. How do you get this string variable value to call/control the range variable Inputworksheet?
I am getting an excel worksheet value from a lookup function that corresponds to the name of a VBA range variable. Once I have this worksheet value, I would like to use the range variable that has the same name as the worksheet value.
I have data from 100 trials. Each trial has a variable number of data points (rows), but each row is labeled in the first column with the trial number. I would like a macro to identify the first row of each trial, calculate the difference of the value of the cell in column G of that row from a constant (718), and then add that difference to that cell and all other cells in that column of that trial.
So based on the attached sample, I would like all values in column G for trial 2 to be +1, for trial 3 to be -5, and for trial 4 to be +2.
In the file, I've cleared all the data from the impertinent columns. Eventually, I will also want to perform a similar process on column H but with a different constant.
Hi, looking for help desperately in fine tuning a formula. I have a formula at the moment (which works) for searching through a list on a separate file and totalling up all values which relate to it, see below:
=sumif([filename.xls]1’!$B:$B,D10,’[filename.xls]1’!$H:$H)
The tab ‘1’ in the formula relates to the first of the month so this month there are 28 different tabs with similar information.
With C10 containing the date in this instance, does anybody know a way of making ‘1’ a variable so that entering ‘04/02/10’ would change it automatically into a 4? (Unfortunately for me changing the 1 to =c10 didn’t work).
I am trying to autofill dynamic ranges that have column variables (d) and row variables (x)... I am having a hard time with the syntax on this
View 9 Replies View RelatedI am using a variable named " Totals" as a range type to refference the range in a formula. It works the way I have it.
Dim Totals As Range
Set Totals = [U37: AE37]
Now instead of the absolute refference, I would like to change the row refference by an offset of my current row, using a formula with a varriable. The columns stay the same.
I am trying to write a macro which will autofill specific columns. The macro will set the range from the start of my autofill to the end of my autofill as a constant range.
The problem I need to get around is the end of my range can always change each time I run the macro. For instance, the first time I run the macro I may only need to autofill from row 4 to row 15. The next time, I may only need to autofill from row 4 to 23 (because of user updates). How can I make the end of my range not be a constant address but variable?
I am trying to write code to select a range in a worksheet where the last cell in the range is variable.
Sub DataTest()
Dim LastColumn As Integer
Dim LastRow As Long
Dim LastCell As range
[Code].....
I have a named range, called SubjectNamesPastoral on a worksheet called Worksheets("Group to Teacher")
I can't assign the named range to the rngSubjectFamilyRangeOnSubjectUsedSheet variable in vba.
the first two lines of code work fine, the msgbox shows "E100:E105", happy days!
However when I try to assign the same range to the rngSubjectFamilyRangeOnSubjectUsedSheet variable, the debugger runs past the 'Set' line without error, but throws 'error 91' at the second msgbox.
VB:
thisString = "SubjectNames" & strSubjectFamilyOfGroup
MsgBox Range(thisString).Address
Set rngSubjectFamilyRangeOnSubjectUsedSheet = Worksheets("Group to Teacher").Range(thisString)
I am using the code below to copy a range and paste it over a variable range.
View 4 Replies View RelatedI have been working on part of the code for my spreadsheet and it works fine in the spreadsheet “Databaseform” however when I copied the code to my master spreadsheet “Paul_PartLocDBCombo” it does not work, I get the error:
Method ‘ range’ of object ‘_worksheet’ failed
The code is then highlighted in yellow, the code is:
Set rng = wksPartsData.Range("a1", Range("a65536").End(xlUp))
Meaning this part is incorrect but I don’t know why? To work it: go to Databaseform and press start. Enter 7mm in the product field and press find all. It will then return all the matching results in the userform. Its this I want to try and achieve on the other spreadsheet when the button find label is pressed.
every month i download a sales list of which there are about 200 transactions.
each day there is a different amount of sales ie
feb 1st 9.99
feb 1st 14.99
feb 1st 7.99 (3 sales)
feb 2nd 8.99
feb 2nd 16.99
feb 2nd 14.99
feb 2nd 8.99 (4 sales)
"" "" "" ""
etc throughout the month
at the moment i am using the SUM to maunually add the sales per day
feb 1st 32.97
feb 2nd 49.96
as i need a daily record of income, however this is very time consuming
is there any way i can request the worksheet to add say column b for the 1st feb and show total
column b for 2nd feb and show total, thus saving myself hours of work ??
way to add up the total for each day
I've got a range of cells that I'd like entered into other cells, followed by _SheetName.
For example, I've tried the following:
Sheets("AllData").Range("D6:J6").Value = Sheets(1).Name & "_" & Sheets(1).Range("D6:J6").Value
But it runs a run-time error '13', type mismatch.
However, both
Sheets("AllData").Range("D6:J6").Value = Sheets(1).Name & "_"
and
Sheets("AllData").Range("D6:J6").Value = Sheets(1).Range("D6:J6").Value
Do their respect parts of the process so I'm not sure why the combined version doesn't work.
how I would get the top 5 highest values from a group of 20 and add them together?
View 9 Replies View RelatedI need to add times of employess over a 2 week schedule.I have used a string variable to select the rows and columns as employees change.How do i add the times in this case.Sample code is below:
j = 12
k = 20
For i = 16 To 276
row_str = "A" & i & ":B" & i
Windows("TimeSheet.xls").Activate
Range(row_str).Select
Application.CutCopyMode = False
Selection.Copy
Windows("Report.xls").Activate ...................
I need to identify any cells in a range that do not contain text then colour and comment them. I intended to use the following code, however it doesn't work as I expected it to.
With rRange.SpecialCells(xlCellTypeBlanks)
If .Count > 0 Then
.Interior.ColorIndex = zErrorColour
.AddComment "This cell must contain a value"
End If
End With
The blanks cells are coloured OK but only the first blank cell gets a comment. Is there a reason why I can't add comments to all the cells in the range? And if there is a good reason, then I guess I'm going to have to write a loop to add the comments.
I'm trying to add a range to this email code that will then paste the information into the body of this email however instead of getting the data I need i'm getting "-1" in the body instead.
I've tried defining the range below and adding that line to the main code but it doesn't work, the code will run but i get "-1" in the body.
adding an second range (J1089:AI1147) to the follwing code.
Code:
Sub Clear_Range()
Application.EnableEvents = False
With Range("H3:AL1058")
[Code]....
I'm looking to easily drag the sum of certain cells in a different column BUT keeping a specific range, it's hard to explain so i'll show an example...
A1
A2
A3
A4
A5
A6
A7
A8
B1=SUM(A1:A4)
B2=SUM(A4:A7)
B3=SUM(A8:A11)
And so on...
Is there any way I can do this by dragging down the cell formula from B1 and it remembering the range of 4, so I don't have to manually select each range...?
I am trying to add the highest 4 or
View 9 Replies View RelatedWe have a client that has to total the number of days that people received medical care (for insurance purposes). BUT...they can't count duplicate days. Some of the dates show a range of days, others only one day. They've asked for my help. (We're actually a computer services firm, but I'm pretty good with Excel...until now!)
In column C I have placed the correct totals for the date ranges, according to the payment policies. But I can't figure out how to write the formula that accommodates all the variables (mostly since all the unique dates aren't entered--only ranges).
Each line is a different medical charge, so line 1 might be 22 days of hospital stay. Line 2 is for a different charge, but covers the same dates, so it can't count. Lines 3, 4, and 5 all occur within the previously calculated range, so they don't count. The date range in line 6 is six days but 4 of them have been counted already, only 2 days fall outside an already calculated range.
It would be easy enough to do this manually for a small list, but this patient has 1367 entries in 4 months of care! And there are others that are much larger.
Fortunately, all the dates are in calendar order. Also, fortunately, they don't need line-by-line totals, just the grand total at the end of all the unique days in the range. So if I can figure out how many unique days are in the columns ....
I'm trying to come up with a formula which will add the cells in a date range. This date range may be within the same month or span several months but will always be within the year.
On the results page, Cell A3 will be the Employee Number, Cell B3 will be the From Date, Cell C3 will be the To Date. I need to find a for cell D3, the sum. For example, if A3 had 123456, B3 had 1/2/09, C3 had 1/3/09, then D3 would need to have 17.48 (8.88+8.6)
The way I'm currently looking at is adding a row between C and D on the data table below which contains the row number, then doing a VLOOKUP and a few indexes to pull the data but I'm concerned that pulling data from 7 different tables (for different types of employee hours) for 250 employees would make this take forever to recalculate.
Note that the table below has 12 rows for the 12 months of the years and the columns go all the way to 31. It can be counted on that if a date doesn't exist (for example, 2/30/09, the value in the cell will be 0 which means we can use ranges through the end of the month if needed) ......
i'm using a formula to derive some data in a column of an excel tab. it is -
=IF(H2=5,(CONCATENATE(YEAR(E2),(0),MONTH(E2))),CONCATENATE(YEAR(E2),MONTH(E2)))
how can i add a dynamic range to this so that it updates automatically when the number of rows expand?
I have a spread sheet I use to track tools. One tool may be checked out / returned several times a week. When I do custom sorting I end up with the Tools listed neatly by date and number. EX: ...
View 10 Replies View RelatedMy macro adds a sheet to an existing Excel workbook. However, this only works the first time. If I delete the added sheet and run the macro again I get the above error. The macro below call a second macro (see later )
View 2 Replies View RelatedI have a named range on a hidden sheet which populates data validation lists on 'sheet 1, cells b5-b55. My question is this...Is it possible to, upon entering a new item in b5 (for example), to have that item automatically added to the named range? (preferably through a button on a pop up form that opens w/ the question 'would you like to add to the list?, similar to an access form)...I've seen one example in the past which I found with a link from this site, but couldn't make it work. If the answer to my question is yes, could you also suggest a location of an example if you know of one?
View 14 Replies View Related