This is in regards to this script:
Set range = Sheets("VTOL").Cells(Rows.Count, "A").End(xlUp).Offset(1)
Loc = ws.Name & "!" & Range("O1").Address
ActiveSheet.Hyperlinks.Add Anchor:=range, Address:="", _
SubAddress:=Loc, TextToDisplay:=ws.Name
It adds sheets it finds starting at cell A2. But I actually want it to start at cell A3.
I'm drawing a blank here.
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.
I use the standard OFFSET-COUNT-MATCH method to create dynamic named ranges in my Excel projects. Needless to say, this method won't work on a spreadsheet with formulas extending beyond the current range. The count function counts the cells containing formulas, even though they may contain no data. Does anyone know how to construct a formula that will IGNORE the "formula only" cells??
View 3 Replies View RelatedI have attached the .xls file.
The basic setup is as follows:
(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.
- Find a value on a sheet “ORM” in Column G5:G33
-Get the corresponding value of B5:B33 of that cell
-Place that corresponding value in a canned remark: “You have the number 2 in Block, 12, 14, 25 22.
-Place this canned remark in TextBox31
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"
and also t = time()
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 have a formula in cel B1 : =SUM(A2:A100) / A1
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:
[Code] ....
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.
I am looking for a vba to offset the cells.
The code I have currently is:
[Code] ........
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.
Cell offset within macro
I have a very basic macro controlled by a button as follows
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
PHP Code:
For Each Cel in SelectionIf Cel = x Thenvalue1 = Cel.Offset(0,39).Valuevalue2 = Cel.Offset(0,5).ValueEnd IfNext CelÂ
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.
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 ....
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?
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 RelatedCopying the target cell to target.offset(0,-3)
View 5 Replies View RelatedI have code where the user inputs a date, the macro finds this cell and sets rngFound as the location of that cell. The macro then creates a new workbook with new worksheet. Using rngFound as a reference, I need to sum the cells one below and five to the right and put this sum into a cell on the new worksheet.
I did a little research and found the offset which takes care of the referencing, but I cannot figure out how to incorporate this into a sum function in vba.
Below is my attempt. Previous to this part of the code, I am active in the newly created worksheet so I am attempting to input a formula based on offsetting the found range. Another problem is that I am going to have to repeat this step for 50 rows going down. Is there a way to set a loop? Or would I have to write out this code, changing the ranges and offsets 50 times?
Code:
Range("D2").Select
ActiveCell.Formula = Sum[(OrigWks.Range(rngfound.Offset(1,0))):(OrigWks.Range(rngfound.offset(1,4)))]
I am inserting a picture into a cell and resizing it to fit the cell. The problem I am having is that the cell covers the border at the top of the cell. Is there a way I can offset the picture in the cell so it does not cover the border?
Code:
Sub Sig()
Dim filselect As String
ActiveSheet.Unprotect Password:="159357"
filselect = Application.GetOpenFilename( _
FileFilter:="Picture Files (*.png;*.jpg),*.png;*.jpg", _
[Code] ........
How can I make this statement take the value from the Offset cell to populate the text box and not take the value from the text box to popluate the Offset Cell?
With txtBoarded
ActiveCell.Offset(0, 1) = .Value
End With
This code is in the user form initialize code.
I need to make the active cell go to the beginning of the row (Column A). I tried setting up a variable to be the row.value, but that didn't work (the way I wrote it, which doesn't surprise me).
View 6 Replies View RelatedI am wanting to be able to change the data in a spreadsheet cell(s) using a visual basic form. I've attached my spreadsheet file, so that you get a better idea of what I'm trying to do. What I want to be able to do is, on the VB form, to be able to click on the list box to choose a client, then choose the module number (option box) next to it which will then show the client's marks for that module in the textboxes at the bottom of the form. Then to be able to change the marks that student has for that module. I've given it a lot of thought, but I'm really stuck at this moment in time.
View 7 Replies View Related