I am trying to OFFSET from cell A1 based upon a variable in cell A2. The cell I need to OFFSET to is also located in column A, but it could always differ based upon the variable in A2. Here is the piece of code performing this OFFSET.
View 7 Replies
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 RelatedI have a spreadsheet with 2 worksheets. I have a variable cell and a display cell on Worksheet A and two other cells with information on Worksheet B that I need copied over to Worksheet A based on what is entered for the variable on Worksheet A.
I want to be able to enter a variable in Worksheet A, cell B25. I then want the code to look up the variable I just entered at B25 on Worksheet B, column A. Once the code finds the variable, then I want the code to copy what is in column B and enter it into Worksheet A cell C25.
In other terms, I want to enter "Bob" in Worsheet A, B25. On Worksheet B, I have 50 different names of people in column A and additional info directly to the right of their names in column B. When I enter "Bob", I want the code to find "Bob" on the Worksheet B and enter "is worthless" on Worksheet A, cell C25.
I have tried and got the code to work with several If/then statements and I accomplished the same using a select case arguement as well using something along the lines of "if B25="Bob" then C25=worksheet B("B2")" assuming Bob is in cell A2. My concern is the info on Worksheet B is fluid. I need to be able to sort and add additional lines at any given time. My data set is going to be between 500 and 800 rows. Any time I add a row and re-sort the data set, I want the code to find "Bob" in column A and then display what is in column B in Worksheet A, C25. I can continue to use the if/then statements, but they are static and if I add a new row near the top, I would have to re-number all the if/then statments in the code.
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 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
Unzip Code - Works without Variables, Breaks with Variables.... This has been driving me bananas...
I have the
I 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.
Can a Function give two or more output variables. e.g.
Sub a()
x = 5
result = Y(x)
End Sub
Function Y (x As Integer) As Integer
Dim B
B = ... * x
Y = ... * B
this will give back Y as a result. But if I want to get 2 or more output variables (let's say I need to get also B into sub) from one function, how should I do that?
I need this because function works with large matrix and I want to extract some values appeared in between.
I'm trying to loop through a range in excel from access, checking where the titles (in Excel row 1) match with the fields (in a recordset in Access that is passed to the function) - and where they do, I want to dimension a variable to hold the column number - I'm not sure it's possible, but I'd be interested to know either way. The line I'm asking about is at the bottom of the code - the rest of the code is just to give context...
Sub ImportGeneric(rsImported As ADODB.Recordset, rsConfirmed As ADODB.Recordset)
Dim fd As FileDialog
Dim xl As New Excel.Application
Dim wb As Excel.Workbook
Dim ws As Worksheet
Dim iFilePicked As Integer
Dim strFilePath As String
fd.Filters.clear
fd.Filters.Add "Excel files", "*.xls"
fd.ButtonName = "Select"
iFilePicked = fd.Show
If iFilePicked = -1 Then
strFilePath = fd.SelectedItems(1)
Else ..................
I am trying to work out how to nest offset in a code using search replace. The goal is to find a value from cell A1 (example) and replace the values in the cells next to the cell containing samuel and the cell containing samuel.
A sort of search and remove data tool if you like So for example:
[QUOTE]A1: "samuel" (the search criteria)
Search range is: B1:D400
(for example)
Cell B40 = samuel
C40 = Driver
D40 = year
So, the macro is activated and finds "samuel" in B40 and I would like C40 & D40 replaced with "" The code I'm using is below: (this is just replacing the cell containing the search criteria with "test". I would like to nest offset(0,2) & offset(0,3).Value = ""
Sub Macro1()
Range("B1:D400").Select
Selection.Replace What:=Sheets("Sheet1").Range("A1").Value, Replacement:="test", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub
i have a "problem" to empty / reset my variables. I defined them as vHour1_KW2 where the "1" is from 1 to 21 and the "2" starts from 1 to 53. Now I want to erase all of this variables or to set the value of them to "0".
At moment I use following
vHour1_KW1 = 0
vHour1_KW2 = 0
...
vHour1_KW53 = 0
vHour2_KW1 = 0
vHour2_KW2 = 0
...
vHour2_KW53 = 0
until...............................
Using Offset
My
Private Sub OptionButton1_Click()
Set primary = Worksheets("Monitor Testing").Cells(20, 4)
For Counter = 1 To 3
If primary.Offset(0, 4)
Any way I could SUM a YTD using VLOOKUP and OFFSET? In another worksheet, I need to reference Account Income #, go to the worksheet shown below, find the Account Income number, Offset and sum of the columns in that row to the YTD figure.
Attached is a sample : EXAMPLE.xlsx
Account
Income
9/30/2012
8/31/2012
7/31/2012
6/30/2012
5/31/2012
[Code] ......
EXAMPLE.xlsx
I'm trying to lookup a string in a column from another sheet and than return the row on which that string was found. I have two files, one in which my OFFSET/MATCH worked and another in which they didn't work properly.
My problem is this: I have multiple instances of a string in a column and i want to get all the info on that string (the rest of the columns). Let me explain in examples.
In column A on Sheet 2 i have
4 2 4
2 3 4
5 3 2
2 2 2
4 1 3
I need to have in Sheet 1:
4 2 4
4 1 3
Where the first 4 (bolded) is the reference value.
In sheet 1 i have a column A2 which is the reference. In column B2 i have
=MATCH(A2;'Sheet 2'!A1:A16051;1) - this worked on a file but returned a bad value on the other (donno why)
In the second file i used =MATCH(A2;'Sheet 2'!A2:A10552;0)+1 which returned the good value.
For the second value (row 4 1 3) i used =IF(OFFSET('Sheet 2'!A1;B2-2;0)=A2;OFFSET(Sheet 2'!A1;B2-2;0);0). This worked well on the first file but didn't work on the second.
I got the commands above from someone that used to update this file so i don't know exactly how they work
I am trying to create a formula to determine if my students made a point in high standers, which means if they got a level 3 and about in 2009 verse the student's 2008. Now if they stayed on the same level did they make learning gains. So what I need to do is look at the grade level of the student (Colum C), then 2009 Math Developmental Score (Colum J), and Finally 2008 Math Developmental Score (Colum M). So if the student scored a level 2 in 2008 and a level 3 in 2009 he gets a point, but if he stayed on the same level. The formula needs to look see if he made learning gains. So if the student is in grade 4 and was a level 3 in 2008 and 2009 he would need to score 163 points higher in order to get a point. I have created a helper table with the information need to calculate the information. I have been working on this for about a week I can’t seem to get it right.
View 3 Replies View RelatedIn row E10:Z10 I have a series of values and in row E6:Z10 I have dates associated with each of the values in row 10.
I would like to create a formula to look in E10:Z10 for the maximum value and then return the date associated with that value. For example, if the maximum value is in P10, I would like the formula to return the date in P6.
I have tried without success to use the OFFSET function as follows:
=OFFSET(MAX(E10:Z10),-4,0)
I trying to out together a small script which performs the following:
-Select cell B7 and offset this by 12 columns and one row
-Resize this by 14 columns, then
-Apply the font format to the selected range from the first to the last row
The code below is what I've put together so far, and although this runs without issue, it's not applying the formatting.
[Code] .......
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] ....
I have a number (>100) of excel workbooks (consistent format) on a server and I am trying to create a master spreadsheet with links to specific cells. I started using formulae based on Concatenate and Indirect functions but always had to use F2 & F9 to get a result. I am now working on a macro to build semi dynamic links, I am some way there but need some expert help please.
In Col A I have the path and file name for all my source files - I have used the Filename *.xls & FoundFiles for this.
In Col B is my specific file name(s) extracted from Col A using an If , Len and Text function.
My macro declares variables; File = Range (“B#”) and Data(21) - an array of 21 specific worksheet cell references
I then select C1 and with active.formula insert the consistent path, File and Data(1).
Then C2 with path name, File and Data(2). Then C3 path name, File and Data(3). & So on for the 21 variables in the array - not elegant but it works okay
I am now at the point where I need to move down the rows and repeat this for each source.
I am wondering (hoping ) there is a simple way of offsetting this for the 100 rows / source files. I would also like to be able to set the offset repeat by counting the number of populated rows in Col A.
The code (from memory so syntax may be a bit wonkey):
I have a rather details and complicated procedure that in effect is designed to find a value (>=) than a text box value inputed.
There are 2 other list boxes (5 & 6) that are also designed to narrow down the search.
The values available in list box 5 are a customers name in column C.
(Sample sheet highlighted value in Yellow)
The values available in list box6 are a product type.
(Sample sheet highlighted in blue)
When i select a value in list box5 and in listbox6, i enter a numerical value into text box111.
I then click on a command button, to populate listbox3 with all found values in column C that are greater than or equal to the value in the text box.
Now comes the part i cannot seem to be able to do.
I need to be able to click on a selected value in list box 3, and find the value that is between 2-22 rows above it in column A and place this value in text box 4.
(List box 3 values are ONLY ever
I have used the Selection x1Down code to select all rows to the next one with data in but now need some code to deselect the final row (I want to delete all of the blank rows above)
View 2 Replies View RelatedI am trying to write two different codes to copy a range of data. Basically I am trying to understand how the OFFSET function works in VBA. The first code listed works fine and is as follows:
View 5 Replies View RelatedI 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'm copying values from one workbook to another. Each value has to be checked, so I do this in two steps.
View 2 Replies View Relatedhow to make offset for the cells to the row -3 in colum B
View 9 Replies View RelatedI have 3 workbooks, the first one contains the data and the second is the “Table1” containing "Normal records" and the third is the “Table 2” containing only "900 Records". Tables 1 and 2 will be linked to Access…..
Here’s my issue….
From workbook 1, I would like to copy data from certain cells that contain data to Table 1
From workbook 1, I would like to copy data from certain cells that contain 900 service records data to Table 2
In workbook 1, I have identified which information i want to keep by using the following formula,
i have a table with customers and the rentals they have made. in the table it has their customer number and then the item code of the item they have rented out.
i want to have a table that brings up the item codes of what they have rented but with a vlookup it only brings back the first item they have rented and not the ones after that.
=IF(MATCH($A7,$BX$15:$BX$112,0)>150,"",OFFSET($BX$14,MATCH($A7,$BX$15:$BX$112,0),1))
If there is not match, I get #N/A in the cell. I would prefer to get a value of zero (or ""). I've been experimenting with ISNA and ISERROR.
i have an excel file that come from a michine i use at work
start
100 pos
101 neg
102 neg
103 neg
start
101 neg
102 neg
102 neg
start
101 pos
102 pos
103 pos
where the first column is one continous stream on data(of variable length). what i need to do is every time the word start occurs copy down to the next start-1 and paste it to the same row is first start but over to the right ie
start start start
100 pos 100 neg 100 pos
101 neg 101 neg 101 pos
102 neg 102 neg 102 pos
103 neg 102 neg 103 pos
