I'm trying to define a dynamic column range call 'Cost'
If it starts in Column C, row 4 [C4]. I want the range that to go down to the Row I have defined as 'subtotal'
The user will be able to insert new rows above 'subtotal'
How can I define Column C4 so that any new row added will be including in the defined row range 'Cost'?
I have been tring to define a dynamice range in VBA. At the moment, I use following method to Define the range,
Sub OTC_Future_Total()
Dim DynamicRange As Range
Worksheets("FinalReport").Select
Range("e9").Select
Set DynamicRange = Range(Selection, Selection.End(xlDown))
DynamicRange.Select...
I have an worksheet that I import a csv into, each day a new csv is added to the bottom of the previous csv data. I have some code that extracts the date and month # from the cell and places them in helper columns. The code find the last used cell in the helper column and the imported data column to find the first and last row of the new day.
This part works fine. However, I assigned a variable name to the first and last variables and would like to uses these row number to define a range in order to use the range for a vlookup or find operation. This is where I get stuck. I want to use column x and row (variable from first bit of code) to column y and row (variable from first bit of code). Then use a vlookup/find whatever works to find the text I need and get the data. Tips on looping the code would be welcome as well.
I have searched for answers to the problem, on the board and web, but have not found a solution that works for me. (at least that I could get to work!!)
Below is the code as I have it
Public Sub Enter_Date()
Dim DateA As Date
Dim DateB As Date
Dim Cnt As Integer
Dim End_of, Beg_of As Integer
Dim Count As Integer
Dim NumtoFill As Integer
I would like to name a range based on a row number and a column number. I have found the row and column number using this code, but I can't seem to get the rest right.
Sub LastRowCol()
Dim intLastRow As Integer
Dim intLastCol As Integer
Dim ws As Worksheet
For Each ws In Worksheets
intLastRow = ws.Cells.SpecialCells(xlCellTypeLastCell).Row
intLastCol = ws.Cells.SpecialCells(xlCellTypeLastCell).Column
I'm trying to create a user defined function to drop into dblookup functions that will always reference the first cell in an associated column and the first cell in the row of an associated column.
I came up with the following, however the problem is that the udf always returns the values for the active cell's, row and columns.
Function SRCref2()
Application.Volatile
myfield1 = ActiveCell.Column
Dim1 = Columns(myfield1).Range("a1").Value
SRCref2 = Dim1
End Function
Function SRCref3()
Application.Volatile
myfield2 = ActiveCell.Row
Dim2 = Rows(myfield2).Range("a1").Value
SRCref3 = Dim2
End Function
I would nest these in a formula such as "=srcsum(srcref2(), srcref3())".
Ideally I could then use this same funtion anywhere instead of having to use and worry about the accuracy of "=srcsum($a$2,$b$1)"."=srcsum($a$3,$b$1)"."=srcsum($a$4,$b$5)".etc.
It seems like there should be a current cell method or something but I can't find it and I'm not smart enough it seems to use vb to designate some kind of floating intersect as a base for a row and column reference.
Of course you can make a Range like this
View 3 Replies View RelatedI need a Text to Column VBA code that will find a dynamic range. Example: I will be sorting range B1:X20. After it has been sorted, I need a VBA Text to Columns code to be applied to dynamic range H1:H20. I can write VBA language that will identify H1 as Range1 and H20 as Range2. How do I get the VBA to identify Range1:Range2 for Text to Column to be applied? Can "Dim" languace assist in locating the dynamic range?
View 5 Replies View RelatedI am using the below code to sum from "L2" to LastRow2
Code:
LastRow2 = ActiveSheet.Range("L6500").End(xlUp).row
LastRow3 = ActiveSheet.Range("L6500").End(xlUp).End(xlUp).row
Cells(LastRow2 + 3, 16).Formula = "=SUM(L2:L" & LastRow2 & ")"
How can I replace “L2” with “LastRow3” which is a dynamic starting point for the range sum?
I need to create a validated list of month choices, and the list needs to be filled with the names (Month and year, I.E. Feb-07) of the last 13 months for which we have data.
(We get our data about 8 weeks late, so being that it is mid april now I only have data now up to the end of Feb-08. I have created a chart in which the viewer can change the month to view but up to now it has only been for the current year, now they want to see the past 13 months, obviously I don't want to go in and change this every time the next months' data is entered.
So, what offset formula do I use to reference the month-year names which are spread across Row 1, presently in columns C thru N, and the next months data (March) will go in column O, then April's in column, P, etc
I have tried several variations of this; =OFFSET(C1,0,0,0, COUNTA($1:$!)) but how do I get only the last 13 months?
I was wondering if there was a way to create a two cell dynamic range that doesn't expand, but instead shifts.
The cells that I'm interested in are always at the bottom of the column of data. For example, the first two cells I want as my range are C13 and D13. Then, for my next use of the range, I would like it to include C14 and D14.
I want to refer to columns by column order (i.e. A=1, B=2, etc.)
I want to sum a range for dynamic columns and fixed widths (i.e. B2:B5 or F2:F5, etc).
i have C_N as variant and i am trying to write the formula but desperately getting syntax error.
Code:
Range("M5") = WorksheetFunction.Sum(Range(C_N& "2" : C_N&"5")))
i tried before to make another easier formula like
Code:
=Range("N5:N" & LastRow)
and it worked.
Can you use Row & Column numbers in a Formula the way you can in VBA?
I want to do the same as Range(Cells(5,2)) in VBA EXCEPT in a Formula
because I want to use named ranges for the Row & column entries. (And I don't want to have to run a macro every time a change is made. The spreadsheet is huge enough already. It's slow on my machine & I have the biggets baddest PC in the company!)
Using Formulas only, (not VBA) I would like to create a Dynamic Named Range, LastUsedRow, which is the ROW NUMBER of the Last Used Cell in Column C
(it would = 470)
Also I have an existing Named Range HeaderRowNum (it = 16)
Currently I have a LOT of formulas like:
=SUMPRODUCT(($E17:$E470)*(--(CO17:CO470>0)))
problem is any new data must be added between Rows 17 & 470
So I would like to create dynamic new forumlas to read like:
SUMPRODUCT( (Cells(HeaderRowNum+1,5) : (LastUsed Row,5)) * (--(Cells(HeaderRowNum+1,93) : (LastUsed Row,93))>0) )...............
I have a long (~42,000) list of dates and times (some including zeros). I would like to name these ranges and use a formula to find the last entry in each column. I know the lists will never go over 45,000.
The data is in this format:
1/31/14
23:58:00
1/30/14
[Code]....
The data will not always be consecutive, so I imagine the criteria would be something like (if there were such a thing) "ISDATE" or something of the sort.
How would I write a formula to dynamically calculate the row number of the last date or time in the column? (In this case, it would be 6.)
I've got two pivot table reports working off one dataset.
I've named the range Recharge with the formula as below..
=OFFSET('Recharge'!$A$1,0,0,COUNTA('ABC Recharge'!$A:$A),16)
But this uses column A as the longest column... but sometimes it will be column I - how can the formula be adapted ? or can it be ? i've been looking at the Max function and trying to incorporate that but my limited brainpower has gone to mush.
How would I select a dynamic range in VBA without using name a range in Excel. I have searched for possible answers, however they use xldown, or xlup which works great for non-empty cells. My problem need to select the area where there are empty cells. Here is a snap shot of the data looks like:
Trade Date A B C D E
2008-Feb-11 450,432
2008-Feb-13
2008-Feb-15
The cell with number is where I got the formulas. Now i wish to fill that formula with the rest of the area. However, the problem is the number of rows and number of columns may change in the future. So I need to select those empty areas in a dynamic way.
I want to define a dynamic named range based on the last date in a range (AE4 down). Unfortunately there are gaps in this range where no date is in a cell.
View 5 Replies View RelatedSelecting the range from Multiple Column data.
Currently, it is:
[Code]....
I have data from columns A:E
I want to define a Range() in VBA. have cell1 and Cell2 in the format of rows and cols. ie. Cell1 = Row 1, Col 2. Cell2 is dynamic, can be sometimes row100, Col200, or Row23, Col 1000. May i know how to define it in VBA?
View 2 Replies View RelatedI have two columns containg the arrival and departure hours of workers. From these columns I must define the workshift by specifiyng the time range for each shift. AZ contain the arrival hours while BA the departure.
The formula I use doesn't return the results correctly because some shifts are almost the same. i.e: if shift one starts (arrival hours) between 05:00 and ends at 12:59 and shift 1-2 starts at 08:00 and ends at 23:59 then it will go with the first shift even though the times in range belong second shift.
why this won't work.
Code:
Dim DRange As String
Dim ERange As String
Dim SRange As String
EndRow = Range("A65536").End(xlUp).Row
DRange = Range("D1", "Z" & EndRow)
ERange = Range("E1", "Z" & EndRow)
SRange = DRange
how to define the end of a range using a variable determined by a rng.Rows.Count command (or anything else)? Is this even possible? Here is what I currently have:
Set rng = Range("A1:A10000")
For i = rng.Rows.Count To 1 Step -1
If rng.Cells(i).Value = "0" Then rng.Cells(i).EntireRow.Delete
Next
I'd like to define the end of the range with more accuracy than I'm currently doing.
I'm trying to define a range to be copied during execution of a macro. The range to be copied must include all contiguous data from a defined starting range to the rightmost column and the bottommost row containing data (boht of which can change depending on input data). I use <End>+<RightArrow> and <End>+DownArrow> to define the range when working in Excel (not under macro control).
I used the macro recorder to define the code for the process described above, with results shown below:
Sub Macro2()
Sheets("Source_Info").Select
Range("A2:D6").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Working_Data").Select
Range("A2").Select
ActiveSheet.Paste
End Sub
When I run the macro, however, the End(xlToRight) and End(xlDown) commands do not have any effect (i.e., the rang is not made any bigger when these statements are executed).
I've used the same code in previous macros (with previous versions of Excel and Windows), and it worked fine. I'm running Excel 2003 under Windows XP Professional now.
is it possible to creaate a macro to highlight a range that starts at a1 to a? and there could be blanks amongst that range. the range will be unknown
I am searching for data, upwards from cell A1400. Once I find data (cell > 0), I need to count up 27 rows and then select these rows for columns A to E. Then I want to copy this block of data and paste it. I have been having difficulty with the selection of a block of data which will be in a different position depending upon the data used. The code I am using is below. The two lines that I need to replace with a Variables cell range reference are:
Range("A1268:E1242").Select
Range("A1268:E1242").Activate
Sub CalculateATR()
Sheets("Data").Range("a1400").Select
Do
If ActiveCell.Value > 0 Then
ActiveCell.Select
Exit Do
Else
ActiveCell.Offset(-1, 0).Select
End If
Loop Until ActiveCell.Value > 0
Range("A1268:E1242").Select
Range("A1268:E1242").Activate
Selection.Copy
Range("G5:K31").Select
ActiveSheet.Paste
Else
End If
End Sub
how to set up an array within a worksheet
View 3 Replies View Relatedhow do you go about referencing a cell by using a value of an integer? For example, I want to select cell A5. I have the number i= 5 (in my vba code) and I want to use that together with the row letter A.
View 4 Replies View RelatedHow to define column in MS Query (ODBC)?
i have one column like Date of purchase. what i want is to define column based on date of purchase the results showing month of the date of purchase
is there a simple way to do a vlookup (or any other more appropriate formula) using the value of another cell to define the named range relevant to that lookup.
eg I will have two different ranges for fitness performance levels, each of these will be named "Male" and "Female".
I would like to do a lookup based on a formula as follows: Try this formula. Column letter & Row number are for example only. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas Z100 =vlookup(D4,NAMED RANGE,2,false)
the Named range would be a value in the same row ie B4 - being Male or Female
so I am hoping for something like Try this formula. Column letter & Row number are for example only. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas Z100 =vlookup(D4,B4.Value,2,false)
otherwise I could just use an If and do something like: Try this formula. Column letter & Row number are for example only. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas).
Many more Excel Formulas Z100 =if(B4="Male",vlookup(D4,Male,2,false),vlookup(D4,Female,2,false) )
I need to be able to work out a percentage based on a value range. ie.
Cell D2 to D5 will define the percentages (so I can adjust it and play)
D2 = 18%
D3 = 15%
D4 = 13%
D5 = 10%
I need to work out:
If the value in cell A10 is between 11 and 200 calculate on D2, if between 201 - 500 calculate on D3, if between 501 - 2000 calculate on D4 ect ect
Cells A10 to A100 will have some figures put in them.I then want to calculate the %value used based on weather the figure in one of the A10 to A100 cells falls in a range. The data being put into the cells in column A will vary from report to report.
I'm trying to automate the calculations :
I am working on some code to copy data from one sheet to another, but I'm not sure if I'm going about defining the copy range correctly. I would like it so that the user can highlight a range of cells on the sheet between A3 and F last row (last row based on col C). However the user should be able to highlight rows in any column between A and F, and they could highly the records with just one column or multiple columns. It should take the row numbers of the highlighted range and use the that as the row number to extract the data from.
[Code] ........
Im currently getting runtime error 13 on 'CopyRange = Selection.Rows'
'CopyRange = Selection.Row' returns the single row number for the first cell in the selection, but i need the range of all the rows in the selection.