The Sheet below is an example of what I need to fill in daily
In column B I have the week commencing date (ie the Monday)
When the sheet is opened, I want the approriate day to be selected (ie the day before the current day)
Eg. if today is Friday 16th November, when the sheet is opened I want the cell for Sales on Thursday of the week which begins with 12/11/07 to be selected ie L5
If today is Monday 26th November i want Sales on Sunday of the week which begins with 19/11/07 to be selected ie U6 ....
I am trying to achieve the below objective but getting error message
Objective: select last non blank row and offset to next cell type a message in it error message: Runtime error 1004 : method 'Range' of Object '_Global' Failed
CODE:
Sub lastRowAll() myvar = ActiveSheet.UsedRange.SpecialCells(11).Column myrow = ActiveSheet.UsedRange.SpecialCells(11).Row Range(myvar, myrow).Offset(0, 1).Value = "Experiments with VBA" Range(myvar, myrow).Offset(0, 1).Activate End Sub
I am trying to find a macro that can search a sheet for any cell that contains the text "Not on AOI" selects a range that contains that cell, 81 rows below, and 2000 columns to the right, then cuts the selection and pastes it 162 rows below the original cell where the text was found. What's hard is that the number of columns between the "Not on AOI" cells is variable.
I'm very new to excel macros and the parts I think I've put together are:
i'm having a really hard time figuring out how to use target. offset to grab a range of cells. For Example:
I am currently running a macro that searches column "D:D" for data. When the data is found, i want to be able to Grab columns D to AG for that row, and apply conditional formatting. It seems like using Target.Offset should be able to accomplish this, but i can't figure out how to specify a range using Target.Offset. Currently, i have to specify each cell i'd like to select, and apply formatting one at a time, which has bloated my code considerably (and causes me to run out of room very quickly).
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
I am having some difficulties using a combination of IF and the OFFSET function to display a range of cell values from another column based on a simple condition. The values I need to display at the destination cells should be offset by 8 columns to the right and "X" rows down from the reference column. The value "X" is to be determined via the IF function to check for the row index number.
For example, if Index value "X" = 8, then display the value of B2 in cell I9. IF X = "9", display B2 in cell I10 etc.
I have attached a sample worksheet that provides some examples.
Find a specific text in my column B (example : "Proposal ID"), when "Proposal ID" is found, select this cell and offset to the column C (Offset(0, 1)). Then copy this cell value in another sheet.
This will be repeated with different texts (always in the column B), so if the text is not found, I need the macro to continue running.
how to play with data ranges and define names for charts to automatically update them by using the formula offset + counta. An example would be: =OFFSET(Sheet1!$B$7,0,0,COUNTA(Sheet1!$B$6:$B$37)-1).
So, the problem comes when there is a need to select a data range which is not that well defined like $B$6:$B$37. So, I need to import data regularly into my workbook, this data will be imported right below of the last cell with values.
Now, how would I manage to automatically select the data range from this new import for my chart? See attached example. I have highlighted in Yellow a potential import... there is no data in there so feel freel to fill it with any numbers you like.
I have tried to use a vlookup to find Country+Programme+Year (cat1 in the example) in the 'counta' part of the formula above. But I get lost,as a vlookup would retrieve a value and would not be useful for the data range?
(1) the user inputs a number into cells B2:B6 (arbitrarily shown as 3,4,3 and 2)
(2) the user then inputs a series of values into the respective columns E, G, I or K (corresponding to examples A-D, respectively) with SPACING between each value corresponding to the value entered in B2:B6.
For example, the value "4" is chosen in B3, so values are entered in column G at time = 0,4,8,12,16,20....etc
As another example, the value "3" is chosen in B4, so values are entered into column I at time = 8,11,14,17,20,23...etc, ie. the starting point is not necessarily t = 0.
(3) once the values have been entered as described in point (2) above, a series of results are automatically calculated in columns F,H,J and L.
NOTE: I have omitted the formulae for the calculation and have just entered arbitrary (color coded for clarity only-i don't need color coding!) numbers alongside each inputted value for the 4 examples A-D.
OK, so what I want to extract from the table for each example is the paired time AND result values, and then show them in a new table.
I have used an OFFSET formula (originally suggested by "daddylonglegs") to do this, and the results i GET are shown in columns O-V.
HOWEVER, what i WANT is the output as shown in columns Y-AF.
So as you will notice, there are 2 problems:
(1) Example C in columns S/T: value in cell B4 = 3, but the first value entered in column I does not start at a multiple of 3, so no values are returned in columns S/T because the offset function only uses row 6 as a reference point. What i want is the result shown in columns AC/AD.
How do i write a formula which says, "look down a column, find the first cell that has a value in it, and then offset from that cell/row" ?
(2) Example D in columns U/V: since the first input in column K starts a t = 12 (ie, cell K18), then i have 5 rows of empty space in columns U/V. What i want is the result shown in columns AE/AF.
Lastly, i should add that i am not necessarily stuck on using OFFSET function, since i keep reading that it is quite volatile.
Sheet1 is a list of names, then a few columns of numbers. Sheet2 contains calculations based of the numbers in sheet1.
Each sheet from 2 on is names after the names in sheet1 column A... A large cell at the top of each sheet contains the formula to display the sheet name: =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
So what I want to do now is... in cell, look for the cell in sheet1, and display the value in the cell offset by X so i can bring the numbers from sheet1 over.
In plain english the cell would say like... "look in sheet1 for "Name" then display the cell next to it"
I know to look in the next sheet its "=Base!B2" if i wanted b2 on sheet base. I know to use "=OFFSET(Base!B2, , 1, , )" too look at B2 and go right 1 column. But the issue is i dont want Base!B2.. i want whatever cell in Base column A that has the text that A1 has.
If i have a range say E12:O12 and want to find the last cell before blank, lets say it finds m12 as the cell with the value before blank. then with m12 it needs to determine weather row 11 in the same column has Text either "S" or "F" if "S" then m12 = t if "F" then m12 offset(1,-1) = t. And just to make things more difficult i need the range E12:O12 to step 2 as well until it gets to E208:O208 .Noting that row 11 never changes and will always have either an "S" Or an "F"
For Each C In Sheets("data").Range("A2", Range("A2").End(xlDown)) With C If IsDate(.Value) Then .Offset(0, 3).Value = Format(.Value, "yyyymmdd") Else .Offset(0, 3).Value = Right(.text, 4) End If End With Next C
which is supposed to read the cell offset 3 to the right, and then set to either YYYYMMDD or YYYY depenig on the value in the cell. I'm not sure how to specify the first part of this line - .Offset(0, 3).Value = Format(.Value, "yyyymmdd") - it keeps reading the cell in column A, rather than the offset cel.
how to explain this except by showing it, so I explained it as best I could in the attachment. Anyone think they have a solution for this? Let me know if you need more information. Thanks!
the macro will look at the value I entered in cell A1, then find all instances of it in Column A in every row that A1 matches, if the value in column E is "0", I need the macro to change the value in column E of that row from "0" to (that row's column B * $B$1)
I'm trying to do a column of individual discount calculations using a discount percentage found in a cell two to the right of a cell containing the text "Total SP:" that is always upwards and to the left of the cell where the formula goes (but could be two rows or could be 20). There are multiple "Total SP:" cells in the sheet - I always want the first one upwards. I have created the following but I get #NAME? where I hope to see the discounted value.
VB:
Dim Discount1 As Double Discount = Cells.Find(What:="Total SP:", After:=ActiveCell, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=True).Offset(0, 2) Range("R9:R" & LastRow).Formula = "=IF(J9>0,$E9*(1-Discount1),0)"
I've just thought, the Cells.Find needs to be redone for each cell where the formula is inserted to ensure it always catches the correct discount and this isn't going to do that - it's going to find it once and always use that single value.
I think this needs a Loop or something and to move the ActiveCell down one after the formula to get it to redo the Cells.Find.
I would like to use this formula many times in the sheet, so I would need a reference to the cell the formula is in, and have the SUM range until the next empty cell one column to the left.
So I would need something like (literally):
=SUM(Offset activecell (1,-1) : Offset activecell (1, (look for next empty cell -1)) / Offset activecell (0,-1)
I'm trying to look at a value in one column (Column A) and if it matches a set of criteria, I'm trying to change the value of another cell 6 columns to the right.(Column G) I've been trying for a couple of days now to get this working, but to no avail. I've tried to modify the code below that I've found on this site but can't quite figure out what I'm doing wrong.
Sub RatingsFix1SP() Dim FindWhat, rngCell As Range, i As Integer FindWhat = Array("BB", "B", "CCC", "CC", "C", "CCC+") For i = 0 To 3 For Each rngCell In Range("A2", Range("A" & Rows.Count).End(xlUp)) If InStr(rngCell, FindWhat(i)) <> 0 Then rngCell.Offset(0, 6) = 0.15 End If Next rngCell Next i End Sub
To try to clarify a bit: If column A in the attached spreadsheet has a rating code of BB, B, CCC, CC, C, or CCC+, I want the Haircut % rate in column G to be 15%. It seems the code that I'm using changes the % rate to 15% if any "B" or "C" shows up in any part of the string in column A.
I'm using the code below to perform the following:
Compare a column of project end dates (column G on the "In Flight Projects" sheet) with the dates in a row of column headers, and Where there is a match, count the number of projects which are being worked within that given month.
[Code] .....
The code works fine, but I'm having a little difficulty in making an amendment to the code:
In the following section of code I need to select the Range ("G8"), but then say if the value in the cell, offset minus 4 has the value of "C&R" then run the rest of the script :
[Code] .....
I've made the following changes, but I'm unable to get this to extract the figure despite not receiving any error message:
Is there any way to use the offset formula in reference to the location of another cell?
Here is a simplified version of my problem:
Sheet1!A1 refers to Sheet2!A1. Sheet1!B1 refers to Sheet2!D1. Sheet1!C1 refers to Sheet2!E1.
Is there any way that I can say that Sheet1!A1=Sheet2!A1 and then use the offset formula based on the location referenced in Sheet1!A1? The cells being referenced are merged cells one column wide and varying rows long, so there is no real pattern to follow except that the corresponding cells (the ones that I want to use the offset formula on) are all taking up the same amount of rows and are an equal amount of columns over (Sheet1!B1 refers to 3 columns over from Sheet2!A1, Sheet1!B1 refers to 4 columns over from Sheet2!A1). The spreadsheet is a fairly complex one and it would be much cleaner and less likely to have any mistakes if I was not referencing each cell individually.
This is only moving the selected cells across one, however - the content of the cells is what I wish to move so cell C25 has data pointing to another sheet cell A1 i.e. =Sheet1!$A$1 - can I edit this code so it alters the cell to reference B1, C1 etc....
I am trying to determine a cell row thru which I want to sum a column based on date. For example, if today is in the month of December, the offset is row 87. When the month changes to Jan 09, the offset will be 88.
I have the formula which does this for me, but I am not sure how to use it in the sum formula. So if I want the sum for December, it would be =SUM(K18:K87) and Jan would be =SUM(K18:K88). Here's the wrong formula:
=SUM(K18:K&(ROUND((TODAY()-39783)/30.35+87,0)))
What I cannot get is how to properly format the formula so that the :KXX increments based on the formula. I tried this: =SUM(K18:K(ROUND((TODAY()-39783)/30.35+87,0))) but get a NAME error.
I have a selection that I am going through with a 'for each' and then performing actions if the cells meet a certain criteria. I have been referencing nearby cells with the offset function, but now my sheet is too big and I need to change the code often and the offset function is difficult, is there anyway to refer to cells without the offset function, preferably by column letter
Is there a way to use the offset function with a cell reference for the first reference?
I have many columns of named ranges, each representing a month of financial statement data. I want to pull up certain cell values based on the months being compared.
For instance, if the user selects "May", I want to pull only the sales data for May 2013, May 2012 and May 2011. That data is in the 311 row of the columns for each individual month.
=OFFSET(ytdmay2013,311,0,1,1)
In short, I am using a vlookup to get the ytdmay2013 based on the chosen month of May in the input section. If the user then chooses June, the lookup will return ytdjun2013. I want to link the ytdjun2013 in the offset function.
ThisWorkbook.Sheets(2).Cells(nrow + 1 + j, 2 + high_count).Formula = "=VAR(" & ActiveCell.Offset(0, n_high_count -1) & "," & ActiveCell.Offset(0, -1) & ")" *n_high_count is the negative of high_count
I want "ThisWorkbook.Sheets(2).Cells(nrow + 1 + j, 2 + high_count)" to have the formula "=VAR(XX:XX)" where the range is the current row second column to the current row current column -1.
I am looking to create a Macro that says: look at all of the data in column E:E and if you find a cell that has the word "Ohio" in the string of information, then offset that cell by 3 to the right and make that cell equal to "4"
Sub Test()
Columns("E:E").Select Dim rng As Range, cell As Range Dim worksh As Worksheet Set worksh = Worksheets("Axys Layout") Set rng = Range("E:E") For Each cell In rng If InStr(1, cell, "Ohio", 1) Then cell.Offset(, 3).Value = 4 End If Next Ecell End Sub
This is not working and I'm not even sure that I am on the right track!
How would I find the first cell in column A containing a number, like HY42128PP, and then offset 1 row up from there to start my autofilter?
Also, to copy the worksheet headings, what code would copy all rows from row 1, down to 2 rows above the first cell in column A containing numbers, and insert those rows in another sheet?
