I want to apply some logic to a range. However I want to offset the bottom of the range by 1 row.
Example: I have a range (B8:I19) already defined in my vba code. But I want to shade range (B8:I20).
MyRange = Range("B8:B19")
MyRange.Offset(1, 0).Interior.ColorIndex=3
This code also offsets the top of the range (row 9), which I don't want.
How can I only offest the bottom of the range?
I am having trouble with one line of this code that I can't figure out how to write correctly. I have set the range, but I need to offset by 10 rows up. My code fails at set rng.
View 6 Replies View RelatedI have an offset range ("TheCell") that does not seem be working how I would expect.
I don't know if its because the sheet I am working on was originaly German but I would be surprised if that was the case.
I Use sheet change to set the peramiters of the range and to test the value
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rowcell
Dim colcell
Dim typeit
colcell = ActiveCell.Column
rowcell = ActiveCell.Row
ActiveWorkbook.Names.Add Name:="iname", RefersToR1C1:="=" & "Tabelle2!R1C" & colcell
ActiveWorkbook.Names.Add Name:="Jname", RefersToR1C1:="=" & "Tabelle2!R" & rowcell & "C2"
typeit = Range("TheCell").Value
MsgBox typeit
End Sub
=OFFSET(Tabelle1!$B$4,MATCH(Jname,Tabelle1!$B$5:$B$57,0),MATCH(iname,Tabelle1!$B$4:$EX$4,0)-1)
but for some reason I am getting a runtime error 1004 worksheet failed.
I have used this technique before and its been fine so what mistake have I made :o)
I would like to work with a range of cells.
I have a headerCel (A2)and a footerCel (A20).
If I use the line
I am currently writing some VBA code to loop through and copy data from Excel file in sub folders into a master sheet of data. There are some 1200 Excel files (these are pre-2007 files), each containing a maximum of 600 lines of data. So far the code works until it gets to around 65000 rows (the old Excel limit). I am using and writing the code in a Macro-Enabled Excel 2007 file.
I am using a Range.Offset call, which is where the error is occurring, to copy in a name basically. What happened right before this was a Range.Copy operation from the old Excel file to a Range.PastSpecial in the new one. This worked fine, and actually went a few hundred rows past the pre-Excel 2007 limit. But when the single cell operation Range.Offset().Value is called next, it crashes with a Application or User-Defined error. The code works some 100+ times through the loop with no problem, and the file that it was working on is no different then the other files.
I am trying to create a macro that goes down Column A of my sheet and looks for a string, "Number". When it finds it, it resize (0, 14) and cuts. Then it pastes the array at an offset of (-1, 10) This is what I got so far:
View 4 Replies View RelatedI'm trying to copy a range, specified by an offset, then PasteSpecial that range to yet another location specified by an offset.
This is the code I have so far, which does not attempt to find the range to copy via an offset.
How do I activate four cells to the right of A5 using the offset function. I will need this to operate indivitually for each row so I cant use Range("B5:E5").Select. All I want to do is to merge the four cells to the right of column A
View 6 Replies View RelatedHow do I offset a range of cells?
I'm running simulations. Let's say I want to run the "for" loop 100 times. In the 1st iteration I want to copy Range(A1:A10) and paste the value to A31:A40 (this destination range is fixed for each iteration). In the 2nd iteration I want to copy Range(B1:B10) and paste to (or assign value to) A31:A40. In the 3rd iteration I want to copy Range(C1:C10) and paste to (or assign value to) A31:A40. And so on for 100 iteration. So each time my copied range moves one column to the right.
How do I achieve this in VBA code? Let's say I have defined/named Range("A1") and want to keep offsetting to the right but copying 10 cells in the range as I go along.
= OFFSET(CustomerInfo!$A$9,0,0, COUNTA(CustomerInfo!A:A),1)
for a "dynamic" range name...i like the concept, but my data validation in a cell that references this "dynamic" range name (with the offset function) doesn't recognize all the values in the range
(when i just range name it, the data validation sees all the names, but not all when i use the offset...)
given a range named xyz which refers to d1:e3
range("xyz")
where the 3rd row is blank. I want to examine the row after the range with
range("xyz").end(xlUp).Offset(1,0).Cells(1,1).value
I find that this code refers to the last row of the range which is blank. How can I be sure that I will be 1 row (offset=1) below the range?
I know how to create a dynamic named range so that no blanks appear in my data validation list but have only ever done this for a list in a column, i.e...
-----A-----
1--Apple
2--Orange
3--Pear
4--Bannana
5--
6--
A1:A6 would be named Fruit, then name manager, edit: =OFFSET($A$1,0,0,COUNT($A1:$A6),1)
The Data Validation list then shows Apple, Orange, Pear, Bannana. And if I add Peach into A5 later, that then appears in the list.
What I need though is to edit this as if the named range 'Fruit' covers cells A1:F1.
------A---------B-------C--------D-----------E-----------F
1--Apple---Orange---Pear---Bannana---
2--
I am designing a form that tracks call times. I have a control "cmdStart" that when clicked will enter the "Start" time in cell A4 and each time clicked will skip a column and enter the next time in cell C4. The same for a control "cmdEnd" that will enter the "End" time in cell B4 when clicked. Then the next end time clicked will go in cell D4. However, when the Start time enters the time in AE4 I need the next start time to start in A6 and do the same in this row until AE6 and then drop down again. The same with the "End" time when it reaches AF6. I think I could write the If and Else for each individual cell, but there must be a more efficient way to do this.
View 4 Replies View RelatedI am using the following macro to format a sheet - I recently added a auto-fill part to it which essentially moves a number from one column to another and autofills it in all the cells until it encounters another number of the same kind in the column it moved the previous number from.
When I try to run this macro it does everything upto the auto-fill part and then gives me error 1004 [the set cell = cell.offset(1,0) ] is highlighted in yellow.
I have a data set that alters each week which means at the moment I have to update the ranges manually each time. I would like to use a dynamic range and think I need to use the Offset and CountIf functions. Both the start and end of the ranges are dynamic. The range is based on grouping the numbers in Column E (LocNo)
What I would like to do or think the way to achieve this is by:
First looking at Column E
Count how many cells are equal to criteria. (CountIf)
offset 5 columns left from the first cell that meets the criteria (Offset -5)
offset X rows down based on how many cells meet the criteria (Offset X)
I've include a spreadsheet spread sheet which 'should' help illustrate the problem showing the ranges I would like and how the data changes each week.
I have data coming into my sheet from an Access file into columns b:l which automatically get covered by a named range which changes according to the size of the data coming in.
What I'd like to create is a named range which includes this original range as well as columns a and m:p. Is this possible using Offset at all?
On top of this my data from Access comes into row 2 and I'd like the named range to cover row 1 too.
I have a table that is 10 columns wide. On a separate worksheet I want to display the last 10 rows of the table - 100 cells in all.
Getting the value of one of the cells is easy enough - I used:
=OFFSET(DataTableFirstCell,COUNTA(Sheet2!$A:$A)-1,0)
If I copy/paste, or drag the fill handle to expand the target range I end up with the same formula and same value in all 100 target cells. I know I can edit each cell to modify the offset -1,-1, then -1,-2 then -1,-3 etc but that's mind-numbingly tedious and likely to introduce error.
There must be a way to have a cell reference another, then copy the formula to adjacent cells and and have the result show the content of cells adjacent to the original source.
I've been trying INDIRECT and INDEX with ROW and COLUMN - but I can't work it out.
I have a problem with an offset match function. What I want to do is look at the value in cell C3 and then match it to a location in a named range and then return the value 8 rows above. The problem is the named range contains non contiguous cells i.e. D10:AE10, D22:AE22, D34:AE34. So if the value appeared in the area D10:AE10 it would return the value in the matching column row 2. How to do this I have tried lots of combinations but they just come out #N/A.
View 3 Replies View Relatedi need to get an equation correct for a scheduling application
assume $f24 is a task frequency and U24 is our current cell location
=IF(OFFSET(U24,0,(-$F24+1),1,($F24-1))"","","X")
i am asking if (the range of cells to the left of current cells (range being task frequency -1 columns wide) are empty - and if so - put an x - if they are not empty - then leave it balnk.
i can get it to to work for a 2 weekly schdule but need it to work upto 52 weeks
i have a simple spreadsheet with the problem clearly defined for anyone who can help
- beer in it for anyone who can help - or can pay via paypal if required
I have used Offset() for creating dynamic Named Ranges in Excel 2003. I'm a little disturbed that this usage is no longer supported in Excel 2007? Has anyone else seen this breakdown? What alternatives are available.
In my brief investigation, it looks like opening the .xls in compatibility mode allows the usage to work. However, saving the workbook as a .xlsm (since I also have macros in it) causes any usage of the named range containing Offset() to return an error (both VBA and sheet functions).
I am trying to sum multiple columns of data by a sumif based on a criteria in column C, the columns are weekly dated, and I wish to match columns by offset this initial column with a start date and end date and sum columns in between, I have been getting close but only returns one columns values?
SUMIF(FilterCol,$C17&I$9,(StartCol:EndCol))
FilterCol is column for criteria match
Start Col is name range - OFFSET(FilterCol,0,'Summary totals line groups'!$G$4,1,1) - End col similar, (G4 is a match date to find column ref)
When I make a dynamic named range, I typically do something like:
=offset($A$1,,,counta(A:A),counta(1:1))
I remember awhile ago I saw (within some of the comments on Jon Peltier's site, I believe) a way to make a dynamic named range without using offset. Does anyone know how to do it?
I want to use a dynamic range in order to use this formula below in column A as long as there is something entered in column H = SUMIF($N:$N,A5,$I:$I). any ideas on syntax and will this be using the offset function within excel. I know I have posted a couple times about getting this formula to replicate itself down certain columns as long as someone enters text in another. I looked into the offset reply information somone posted but it seemed a little over my head.
View 4 Replies View RelatedCan i convert this
ActiveCell. Offset(-1, 0).Copy
With ActiveCell
.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
.ClearOutline
End With
So that i can copy/paste the Offset range such as (-1,-2) (-1,1)?
I've created a named range "Row_16" H16:M16. I've made a macro which colors the interior of this range..
Sub Fill_Row16()
Range("Row_16").Select
With Selection.Interior
.ColorIndex = 48
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
End With
End Sub
I would also like to add a secondary color (Red) to the row just below this range ie H17:M17. Is there a way to offset/ select an entire range.?
I am trying to select a range in VBA, where I know the name of the starting cell, and the size of the range are values returned earlier in the VBA function. I guess the pseudocode I am trying to do is: range(range("StartCell"):range("StartCell").Offset(x,y)).Select. Obviously this code doesn't work, but I don't know the correct way to select this range
View 2 Replies View RelatedMy worksheet has 1096 rows of data including headers. I am trying to create dynamic named ranges using the OFFSET function but when I check to see if they are capturing the entire range I see they are not.
Here is my formula:
OFFSET(SiteTrac!$FA$1,0,0,COUNTA(SiteTrac!$FA:$FA),1)
OR
OFFSET(SiteTrac!$FA$1,0,0,COUNTA(SiteTrac!$FA:$FA)-1,1)
OR
OFFSET(SiteTrac!$FA$1,0,0,COUNTA(SiteTrac!$FA:$FA)-1)
OR
OFFSET(SiteTrac!$FA$1,0,0,COUNTA(SiteTrac!$FA:$FA))
I have tried all four but every time it continues to stop on row 409. I have tried to name different columns and they all stop short too.
I am using this Formula =Sum(Offset(A1,,,D1)) where I put for example number 4 in D1 and I get the sum starting cell A1 till Cell A4
I want the range to be highlighted as well, but I don't know how to do it.
I would appreciate some ones help to correct a macro I'm trying to write. The macro copies 3 columns from Sheet1 to Sheet2 in a selected location. The 3rd column copied needs to be pasted in a different column in Sheet2.
View 6 Replies View RelatedI have a large file where I want to name about 30 ranges. Initially I had done this using the range addresses and it worked well, but I continue to modify the spreadsheet and add and delete rows, so I need to go back into the code and change the ranges manually. I know there has to be a better way.
What I wanted to try doing was finding some specific text. That text (in all the ranges) is actually one column to the left and 33 rows below where I want to start the named range. Additionally the named range will be a 10 x 33 range. For example, if the data starts in B1, the data to be named will be in B1:K33 and the search title will actually be in A34. Hopefully that's clear...
My original code looked like this:
Code:
ActiveWorkbook.Names.Add Name:="[MyRangeName]", RefersTo:=ActiveSheet.Range("B2:K33")
I was attempting to us the Find function with this code, but I can't seem to figure out how to tell it to look for the text (that would be in A34) and then name the range that would be offset by (-33,1) and then name the entire range.