I am using a multi-tab spreadsheet in which one tab has an outside-generated report dumped in as the input, with other tabs referencing its data. The Input tab has two sets of near-duplicate data, and I need to reference the second set, which begins in different rows for each Input report. I can't do a specific vlookup, because the reference appears twice in the tab but I need the data following the second reference. Can I name a range, beginning at a changing point (the cell which reads "Exposure:Net") and continues to the end of the tab, and have functions referencing it work properly?
I have a range that I named. Let's say that range is called "RangeX". Let's say RangeX is defined by B2:E10
I have a series of formulas that give me the row and column numbers of the starting and ending cell of a subset range I want to create within RangeX. Let's say the starting cell is defined by (1,1) and the ending cell is defined by (3,4) within RangeX. Translated, the starting cell would be B2 and the ending cell would be E4.
How do I create a subset range using the starting cell (1,1) and (3,4) in VBA? I would like to use the row/column reference as RangeX itself could move around.
I have a workbook with two sheets. The first sheet is the data sheet.
Data Worksheet A B C D colA colA,B colA,B,C colA,B,C,D 104 20 21 CR10600S 104 10420 1042021 1042021DUS 104 20 21 CR10600SX 104 10420 1042021 1042021DUS 104 20 21 CR10603S 104 10420 1042021 1042021DUS 104 20 21 CR10603SX 104 10420 1042021 1042021DUS 104 20 22 CR49605S 104 10420 1042022 1042022HKZ 104 20 22 CR49605SX 104 10420 1042022 1042022HKZ 104 20 23 CR39601P 104 10420 1042023 1042023SPR The next sheet is where the magic happens. Separate this worksheet into two sections. The reference section (column A and B) and the entry section (column E-G). Column D is a formula that will concatenate column E-G together. In the entry section any number of combinations can be entered. In the reference section I need to take the style number and reference it back to the data sheet. Then take the possible combinations from that style (column E and greater) and see if any of those combinations exist on the other sheet in column D. If so then say added....otherwise say needed.
"....In Pricing Sheet" is where I am looking to put needed or added or something like it. A B C D E F G STYLE …IN PRICING SHEET Concat DEPT MCL SCL STYLE CR10600S Added 1042021 104 20 21 CR10600SX Added 1042022 104 20 22 CR10603S Added CR10603SX Added CR49605S Added CR49605SX Added CR39601P Needed Any ideas on how to do this in formulas and without code? I have tried and looked into index, match, dget and lookup. I just don't think I am finding the right combination of formulas.
I have a sumproduct function that is aimed at a big block of data. Is there a way that I can write the function to where it will always find the last row of the data set and adjust accordingly?
For example if right now the range is A1:G2000 and next month I add 20 rows of data I want the sumproduct ranges to automatically adjust to A1:G2020.
i need to copy a range from one workbook ("weekly data") to another ("Yearly data"). The range to copy is called 'weekly data'. The ranges to paste to are called 'week 1' through to 'week 52' (this is the yearly data sheet). On the yearly sheet there is a cell Po1' that week on week will state the week number ie 'week13', 'week14' etc. code to use this data in 'P1' to tell the macro to copy to the appropriate week. I have attached as far as i got (then failed due to lack of experiance).
presuming that both books are open
HTML windows("weekly data").activate sheets("weekly transfer").select range("weekly data").copy windows("yearly data").activate 'only one sheet in workbook range ' data from p1 .paste
In a worksheet is a table: a b c d e f g h i j h l m n o p q r s t
I'm looking for a procedure to (with the help of an array) reproduce this table exactly under the original one with one row between them but in the folowing form:
q m i e a r n j f b s o h g c t p l h d
the procedure should be applicable on any haphazard regardless of its place on a worksheet
when I cut or copy a range - and then select a different area - how do I reference to the original cut or copied selection, i.e. the "marching ants" range?
In the attached sheet. Cell C6 represents the concatenation of aspects of a product. I want to know if it is possible to use that cell as the named range (there is a corresponding named range) in a lookup. The lookup gives me the product code.
I'm trying to use the INDEX function to return a value from a named range. But I'm not directly typing the named range into the INDEX argument. Instead, I'm grabbing the named range from a vlookup table (essentially, another named range). So, if "vh" is the name of the named range, I'm not using the formula:
I have a 'receipt' worksheet with about 500+ lines of data, here is an example of what could be in cell A166: 1.1 NET_AMOUNT 742,523,253.83
In another worksheet, is there a way to look at the whole worksheet for column A in receipt to say: if 1.1 is true, capture text to the right of 'NET_AMOUNT' then format using =Dollar?
I want to apply conditional formatting to a range, but only to the number of rows in the sheet (to avoid formatting blank rows). I am not sure how to insert the row count into the range select.
NumRows = Application.WorksheetFunction.CountA(Range("A1:A65536")) gives me the number of rows, but how do I get that into
Range("A5:F18").Select
18 being the number I want to substitute the row count for.
So this looks for the dates found in C1 and D1, finds them in B1:B10, and returns the corresponding cells in A1:A10 for the array part of the PERCENTILE syntax.
I think the issue is that the lookup will return the value in A1:A10 rather than using the cell reference for the PERCENTILE.INC function...
I have a variable which contains the column number. How do I then reference a range in VBA using that column number, as opposed to the letter? I know how to convert the number into the letter, but I also know there has to be something more simpler. For instance, a form of the ADDRESS function, but in VBA?
The following sub to create and name wsheets results naming the new sheet with the value of "A9" only, but what I want to name the new sheet is "A9" + "B9". Sub CreateWorksheets() Dim newSheet As Worksheet, itemSheet As Worksheet Dim cell As Object Dim itemrange As String
Set itemSheet = Sheets("BIDFORM")
Application.ScreenUpdating = False itemrange = "A9:B9:" & itemSheet.Range("A9").End(xlDown).Address For Each cell In itemSheet.Range(itemrange) If SheetExists(cell.Value) = False Then Sheets.Add Before:=Sheets("BACK SHEET"), _ Type:="C:PathFile" Set newSheet = ActiveShee newSheet.Name = cell.Value End If Next cell Application.ScreenUpdating = True End Sub
I want to search a word in my Data sheet and want to know what is the column position. Example: I have a word "LastName" as a Column header in my Datasheet whose column position can be anywhere in the datasheet based on the table selected. So i want to write a generic code which give me COLUMN position for this word in the datasheet.
So if "LASTNAME" header is coming as 7th column it return me 7 if "LASTNAME" header is coming as 9th column it return me 9.
I have a list of numbers in Column A and in Column B I have a standard Sum formula which sums the adjacent number in Column A and the four numbers before it.
A B
1 =Sum(A1:A5) 3 =Sum(A2:A6) 5 ... 3 ... 5 6
I would like to have a formula which references another cell to define how many rows to sum from the starting cell. So in the above example, the total number of rows which are added together is 5. If I wanted to change all of the SUM formulas to add 6 rows, I would like to be able to change the number in the reference cell to 6 and all of the sum formulas would switch to adding 6 rows.
I am attempting to obtain the last non-empty row in a column of a spreadsheet by using the following formula
=SUMPRODUCT(MAX((A:A<>"")*(ROW(A:A))))
This works fine.
However, I'd like to make it more dynamic and be able to obtain a usable column reference (i.e., the A:A portion of the formula) from a named range (single cell).
In another tab, I have a report where a row containd the project name, and I want to find the maximum value of the dates in the ither tab, where my project name matches the project name on the tab data I am given.
So, if I have "Project-C", I need to know the max of the dates in the row for Project-C; but I dont know what row that will be on in the other tab. For info such as 'data1' I have been simply using vlookup using the project name as a key.
Using VBA, I am importing data from Access (Access2007) into Excel. The code that I use works as required but I need to refine it so that I can filter for the records that I need to import. I can import ALL records. I can import selected records by manually changing my SQL query. I would like to reference an array of values or a range of cells so that I do not need to manually change my query every time I want to run it.
[Code].....
This code gets all records for "Jeff" and "Bob" from the Access database. I need to query about 100 names at a time so manually inputting them is going to take forever. How I can make it work?
By using an array: [Code]....... or by using a range of cells:
I have a loop that executes roughly 7.7 million times when my VBA program runs. Neednless to say it take a long time to run - usually a couple days.
The inner-most loop contains a line of code from way back in my early vba programming days when I knew even less than the small amount I know about programming now (and if you can follow that sentence you might be able to understand some of the spaghetti code I write :-) ).
for k = 1 to n
if Worksheets("Personell").Range("D" & Trim(Str(k))) > dtCompleted then ...
Would it be faster to use this syntax (which I just found out about):
if Worksheets("Personell").Cells(k,4) > dtCompleted then ...
It would cut out 2 functions calls, trim() and str(), so it would be faster, right?
I am almost embarassed to post the Range("D" & Trim(Str(k)) line of code because it looks so convoluted now, but that's how I learned to program, just fumbling through until it ran...
So in short, I just wanted to confirm that the cells() syntax run faster before I spend an hour editing and tested.
I often have problems with the cells range reference method. For some reason I get an error and can't tell why. Other times it works fine. Is this just an unstable method to use or is this completely wrong? I'm assuming I am using the wrong syntax. It is definetly the range reference causing the problem.
Dim cnt1 as integer, cnt2 as integer,cnt3 as integer
cnt1 = 2 cnt2 = 50 cnt3 = 2
dim myrange as range
set myrange = sheets("sheet1").range(cells(2,cnt1), cells(cnt2,cnt3))
I have no idea why it won't work. I'm basically searching a columner range of cells.
