I am trying to treat this code with the column range as a variable. I don't know the proper way to write the code. Instead of this
lRowStart = Range("b1").End(xlDown).Row
I am trying something like this
g = 0
lRangeStart = Range(Columns(2 + g)).End(xlDown).Row
g = g +1
The problem with the first code is that it stays with the column "b1", but I would like to have my code move from column to column.
If you have used Excel to download data from Bloomberg, you probably noticed that the function "BDH" can return an array of variable length. I was trying to design such a function but could not find a way.
The basic feature of a function like this is that it can be entered like an ordinary formula (not an array formula), but it returns multiple values. The values will be displayed in the range that has the formula as its top left corner.
Range("A10").Select
ActiveCell.FormulaR1C1 = "write text"
How would the code be written so it does not actually select cell range A10? I have seen this somewhere where text is written but without actualy recording "clicking cell range".
I have a spreadsheet. In this sheet the data is added daily so one column is increased everyday. The rows may also be increased. What I am trying to do is to sum the data in each row for a range of first cell in the row to the last non empty cell in that row and this has to be done for all the rows. So I thought the for loop would be useful for such requirement and I tried to write the following code. But unable to write the sum formula in the last empty cell in a row and I get the value error. The code is as follows.....
Sub sum_on_LastEmptyCell()
'find the last empty row in column A
lrow = ThisWorkbook.Sheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Row + 1
'find the last empty column in a row
[Code] ........
Though one of my friend told that it can be done with "with and end with block but I am not aware of with and end with block.
It would be better if you tell me that how can I put the sum formula in my code. How can I use variables which return the row number and the column number in the sum range within the For loop because I want to put If Else condition for the calculations within the For loop i.e. if certain condition is true then I want this calculation to take place otherwise do something else. Moreover it will be easier for me to understand. Because I can use different formula based on different cells as well. Is there any way to do this?
Im using excel 2010 As it's 60 times quicker I was trying to speed up my code and replace all loops by putting the value into an array, and then transfer the array to the worksheet
It seems to be straightforward for math calculations like in this example:
[URL]
But no luck with the one below. I was trying to test it on a simple loop which replaces two types of string into the 3rd one:
Code:
Dim lastrow, lastrow2, i As Long
With Worksheets("KPI5")
lastrow2 = .Range("N" & Rows.Count).End(xlUp).Row
.Range("T7:T" & lastrow2).Value = .Range("F7:F" & lastrow2).Value
For i = 8 To lastrow2
If .Range("T" & i).Value = "Modification" Then
[code]....
Say 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 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.
I have a range "C1:Cx" where i want x to be the value from a value "overallLastRow". How do I write this?
This is just a simple question but I can't seem to find the answer in any of the hepl files. I want to refer to a range by using a variable but can't get the syntax right.
for example instead of
I am in need of code to sum the above range. But it is different range every day. My company will add the deposit amounts each day from each deposit sheet.
ie:
48000
80000
12000
3000
200
45000
10000
And so on. I have posted an example spreadsheet if needed. (The portion is green is all that needs to be summed but keep in mind it changes amount of rows included changes each day, and the yellow is where the solution goes, but I can easily change that). There are other formulas needed but I have figured those out. I need to run this macro daily just for the last group.
I have a problem of setting a range of row that might expand regularly.
I tried a simple method but...
Code:
' Count the used range row
Dim count As Integer
Dim usedrow As String
count = ActiveSheet.UsedRange.Rows.count
usedrow = "C" & count
' The items are in A1:A105
Set AllCells = Range("C4:usedrow")
I left my Excel books at home, so I can't even look up the answer, and it's incredibly frustrating, since I know it's something small that I'm missing.
I've got a code that I want to cycle thru 12 ranges, named "Retrieve1" thru "Retrieve12". This is the snippet of code that I'm having the issue with is:
For I = 1 To X
NextRange = "Retrieval" & I
Range(NextRange).Select
Application.Run Macro:="EssMenuRetrieve"
Next I
I only made the "NextRange" variable because I originally had "Retrieval" & I in the range select statement and it didn't work.
I am using this coding
LR = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
Range("A" & Row.counts & ":" & "D" & Rows.Count).Copy Destination:=Sheets("Sheet2").Range("A" & LR + 1)
And it keeps giving me the error "Object required"
I am trying to copy from A-D and the row is a variable to which row was checkmarked at that time.
I am trying to create a spreadsheet that will predict the quantity of a product sold in a given month based on the number of new clients that month and the frequency with which the existing clients will need that product to be replaced. E.g. Washing Up Liquid is required by every new client and they will need a new one each month. Toilet Cleaner is required by every new client and they will need a new one every 3 months.
I have 'hard coded' the two examples into the formula on the "Non-Variable" sheet. However the Frequency of Replacement is speculation; it may traspire that a bottle of Toilet Cleaner only lasts 2 months, in which case I would have to go into each cell and change the formula to reflect this. I believe Add or SUM Every nth Cell may hold the answer but I am failing to get it to work with my data due to being a novice and not really understanding the formulae used! The second sheet is my attempt.
As you can see, it counts 3 months forward from the first month, not 3 months BACK from the CURRENT month. It also simply doesn't work: Washing Up Liquid should equal 285. Ideally I would like to keep the format of the 1st sheet as this matches the rest of the the workbook (not included).
still trying to figure it out how the logic in VBA works
'this selects a block of sells and put them in variable rormu. Right?
Set test = ActiveSheet. Range("i65535").End(xlUp).Offset(-1, -3)
Set test2 = ActiveSheet.Range("i65535").End(xlUp)
Set formu = Range(test, test2)
'here i try to create another variable for a range after last used row
Set newg = ActiveSheet.Range("f65535").End(xlUp).Offset(2, 0)
Set newg2 = ActiveSheet.Range("f65535").End(xlUp).Offset(3, 3)
Set pst = Range(newg, newg2)
I have reached a point in my code where I have a variable range selected. I want to autofill the selected range of data to a new range.
My code is:
Range(Cells(7, LastReportColumn - 1), Cells(LastReportRow, LastReportColumn - 1)).Select
Set DestinationRange = Range(Cells(7, ReqMonths + 5), Cells(LastReportRow, ReqMonths + 5))
Selection.AutoFill Destination:=(DestinationRange), Type:=xlFillDefault
The select part works. However on the second line (set DestinationRange) I get a type missmatch.
I searched and I seached but I cant find the answer. I want to paste a set of cells in a blank range that is next to data already in the workbook. I cant get the right wording for the variables in the range I want to paste to. Here is what I got so far, the red text is what I am getting hung up on. (ya I know it sucks)
Dim mycel1 As Range
Dim mycel2 As Range
Set mycel1 = Range("A1").End(xlToRight).End(xlDown).Offset(0, 6)
Set mycel2 = Range("A2").End(xlToRight).Offset(0, 1)
Range("N2").select
Selection.Copy
Range(mycel1, mycel2).Paste
Dim varAAR As Variant
varAAR = Range(Selection, Selection.End(xlToRight)).Select
. Range(Selection, Selection.End(xlDown)).Select
But the way I understand it, varAAR will only equal the first line, I want it to be the range that is selected from both lines.
How to sum using variable start and end points?
For example,
Column A is the list of start dates, and Column B is the list of end dates to be used at the variables.
Column C is a list of dates, and Column D is the corresponding temperature data to Column C.
What I would like to do is create a new column (E) that sums the temperature data from Column D based on the start and end dates from Column A and B respectively (these dates to be used to match the dates in Column C).
I have a problem with a worksheet that my company accounting system exports every month.
Attached : sample of the worksheet.
In column ( F ) , I need a macro to do the following calculations:
1- Check for the Title - if it begins with "Cost Center"
2- Check for the Title - if it begins with "Account Code"
3- Detect the Range Start just below "Account Code" , and End with the row above "Total"
4- Concatenate the string written in each row of the range with the string in "Cost Center"
The Story is:
Each Month I've this worksheet with hundreds of Cost Centers and subsidiary Account Codes, And to be able to analyze the accounts efficiently I need to concatenate both Cost Centers & Account Codes manually ( as you see coloring sample in the attached file ). Which led to wasting many hours , and high risk of error while copying and pasting formulas.
I've attached a sample worksheet. I have a series of time values in ascending order (column A) and then 5 sets of data that correspond to the time values. I wish to be able to enter a minimum and maximum time range then selectively average the numbers from the sets of data that fall within the time range, but I can't think of a simple way of doing this.
View 5 Replies View RelatedMAX function with a variable range. I want find the highest value in a changeable range in a column. The problem for me is, how can I automatically change the range where the highest value is returned.
Here is an example:
Cell A1: 14
In cell A1 is the number entered that specifies the range. In this example the range is 14 rows =MAX(A18:A31).
A2 126.36
A3 126.16
A4 124.93
A5 126.09
A6 126.82
A7 126.48
[Code] .....
Using =MAX(A18:A31) returns 128.57. So far so fine.
But what is the MAX function if the range value refers to the number entered in cell A1? If I change the value in A1 from 14 to 20 how can I make the MAX function flexible that it refers to cell A1 as the range value?
Using the value 20 in cell A1 the MAX function would be =MAX(A12:A31). I can change this manually of course but I want a MAX function that refers to cell A1 as the range value.
I want also mention that the data series is update every day, so that each day a new value is added in column A, e.g. A32, A33, A34. and so on.
I'm trying to understand a code from work and I can't get it. I copied only a part of the code here so please don't run it. Also, I have manual inputs in columns L and M (nodes labels such as 1, 2 and 2,3) and section labels on column N (such as BarFT3, BarFT4 etc):
[Code] ..........
What is this loop doing? I just get the first For loop: goes through every lable on colum N (from 1 to ne) but then what?
What is happening to range nudo(n,j)? How works this ">" sign between nudo(n.j) and nn?
I need a simple VB Code to clear the cell :
K106 = BI210:BU269
Need to clear the range BI210:BU269
if K106 = BI271:BU341 then need to clear range BI271:BU341