Find & Use Cell Address To Offset On Another Worksheet
Jul 27, 2007
I'm still getting to grips with the Find Command. Basically, I have the below code that looks at a line in a sheet(ws_Site) based on the Line ID Number that is in column A and then Finds that that LineID_Value in a different sheet(ws_main) and copies a value from column I on that line across. Problem is, if the cell I is blank it copies it across and essentially clears the cell if there was data in it.
How can I adjust the below code to only set ws_main.Range(c.Address).Offset(0, 8).Value = LineID_Value if LineID_Value is not blank?
For Each LineID In ws_Site.Range("A7:A" & SiteRows) ' Loop through all the cells in range
LineID_Value = LineID.Offset(0, 8).Value
'Dim c
With ws_main
Set c = .Range("A7:A3000").Find(LineID, LookIn:=xlValues)
'If Not c Is Nothing Then
ws_main.Range(c.Address).Offset(0, 8).Value = LineID_Value
'End If
End With
Next LineID
View 3 Replies
ADVERTISEMENT
May 22, 2014
I am struggling to find a macro which can look at a name in column 'BT' and search it in the address book of Outlook to then place the email address of that person in column 'ED'
There are 35,000+ people in the address book and there may be over 5 email addresses for one name, so is there any way a message can appear for the user to select which email address is correct if there is more than 1 contact for that name?
View 1 Replies
View Related
Dec 8, 2008
I have the following macro written:
Sub GT()Dim cell As rangeDim BeforeNull As StringDim FirstNull As StringDim InitialData As StringDim B As VariantFor Each cell In range("A1:A8")Alpha: InitialData = cell FirstNull = InStr(1, InitialData, " ", vbTextCompare) If FirstNull = 1 Then InitialData = Right(InitialData, Len(InitialData) - 1) cell = InitialData GoTo Alpha End If If FirstNull "" And FirstNull "0" Then BeforeNull = Trim(Left(InitialData, FirstNull - 1)) If FirstNull = "" Or FirstNull = "0" Then BeforeNull = InitialDatacell = BeforeNullNextEnd Sub
Now after the final Next, I need the macro to select the last cell changed. Is there a way to do this?
View 9 Replies
View Related
Feb 7, 2007
Find the cell address where the user created button placed in excel.
View 2 Replies
View Related
Aug 5, 2014
I have a formula that basically work, except is really complicated and slows the whole sheet down and then, for some reason, returns a circular referencing error or something. There must be a better way!
So the back story is that I have a spreadsheet that I'm using to track ~1200 employees, including salary, job title and, where I'm having trouble, reporting line.
Column [R] is the Line Manager (direct manager) and Column [S] is Department Head. Columns [T] - [Y] is "Manager", "Manager +1" (manager's manager), "Manager +2" (manager's manager's Manager), etc up to Manager +5.
'Line Manager' is input manually. 'Manager' = 'Line Manager'. Manager+1 to +5 just vlooks up off the same sheet. This part works fine and will update the whole reporting line if you change the manager.
Department head is where I'm having a problem! Essentially what I'm looking for is for the formula to look at the 5 cells to the right (the manager +# columns) and look for 'Mr Boss' and return one cell to the left since department heads report the the boss. There is one exception, its that some immediate reports of Lucy Lawful also are counted as Department Heads. For this purpose I vlooked up this list of exceptions on another sheet. My formula is this (entered as an array):
=IF(ISTEXT(T5),IF(OR(OFFSET(INDIRECT(ADDRESS(MAX((T5:Y5="Mr Boss")*ROW(T5:Y5)),MAX((T5:Y5="Mr Boss")*COLUMN(T5:Y5)),4)),0,-2,1)=Vlookups!H:H),OFFSET(INDIRECT(ADDRESS(MAX((T5:Y5="Mr Boss")*ROW(T5:Y5)),MAX((T5:Y5="Mr Boss")*COLUMN(T5:Y5)),4)),0,-2,1),OFFSET(INDIRECT(ADDRESS(MAX((T5:Y5="Mr Boss")*ROW(T5:Y5)),MAX((T5:Y5="Mr Boss")*COLUMN(T5:Y5)),4)),0,-1,1)),"")
Below is an example file. In my real file the formula works for everyone, not sure why its not working for half the employee now, but probably cos it's a useless formula.
Line Mgr Example help.xlsx
View 2 Replies
View Related
Apr 21, 2008
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?
View 13 Replies
View Related
Nov 1, 2006
- 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
View 9 Replies
View Related
Oct 5, 2013
In the following example, I need to create a formula to identify the cell address containing the string value "X"
BCDEFXABCDEFXABCDEF
Assuming this was row A, the formula needs to return $A$6 (or A6).
View 7 Replies
View Related
Aug 29, 2005
I'd like to have the cell address returned along with a value when I use the
MAX function.
View 13 Replies
View Related
Dec 8, 2005
I want a function that allows me to enter a range and then returns
the address of the cell with the minimum value. It is the Address I am
interested in, not the value.
View 14 Replies
View Related
Mar 3, 2007
Does anyone know the formula that produces the address (e.g. $A$1) of a cell within a table? I found the formula that gives the address of a single cell within a single row (or the address of a single cell within a single column). But I need the formula that gives the address of a cell where the row intersects the column. If I know the column header name and the row header name, how do I find the cell address for the two dimensional intersection?
View 11 Replies
View Related
Mar 8, 2009
I am trying to find a cell address from an area, like this:
@@A@@B@@C
1
2@@@hello
3
4
I need to find "hello" 's address from A1:C4, I found that the function MATCH works in single row/columns only.
View 2 Replies
View Related
May 4, 2009
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()
View 7 Replies
View Related
Jan 21, 2010
I need to find the last value cell in a row and display its contense. Is OFFSET the best way of doing it? Cell H3 will need to contain the formula and the row to seach is also row H. In H3 I want the value that is found in the right most cell on row H that contains anything.
View 3 Replies
View Related
Sep 3, 2006
On Sheet2 I have a table where I want M2 to do the following:
1.) Look at cell A2 where the name "John" is and B2 where the name "smith" is.
2.) Look at sheet1 and look in B2 and C2 for "John" and "smith"
3.) Once it matches the name, place the contents of sheet1, cell I2 into sheet2, cell M2.
So in short, match the name on sheet2 to the name on sheet1 and return the number in I2 to M2 on sheet2.
Maybe combining the persons name in to one column would make it easier?
View 9 Replies
View Related
Nov 13, 2006
I would like to be able to find all cells in row 2 that are "greater than 0". Then I would like to return the coresponding job number in row 4. I need to create a list of each job with the number of hous spent on that job next to it. I may have 30 different jobs but only 5 jobs that have hours for the specific week.
How I attemted to solve this is to create a table that listed all the hours from the largest number in row 2 to the smallest. Then I found the job number corresponding to that number of hours. The problem with this meathod is that if two jobs have the same amount of hours then the first job number is returned for both values that are the same. See attached example.
View 5 Replies
View Related
May 21, 2008
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)
View 7 Replies
View Related
Jul 1, 2014
I have a data table, called Table1 in Sheet1. In a simple form it is
Customer ID
Comment
A
asdf
B
jkl;
So in VBA, I need to figure a way to give me the cell address of the matching cell in Comments. So say Customer ID starts at A1 ... that means the Comment for Customer A is B2.
How would I get that reference in VBA?
View 2 Replies
View Related
Mar 16, 2014
construct a formula that finds a value in a range , then returns the cell address of that value.
Say, i wanted to find the amount 12385 from another sheet , range C2:AA12 (contains only numbers , no duplicates). result should give me the address of that value.
I have tried the address & match function but gives me an N/A error.
View 3 Replies
View Related
Oct 22, 2007
i had this formula at one time but lost it.
i am trying to figure out how to evaluate a column and get the cell address of the cell with the last entry
example
--A
1 e
2
3
4 g
5
6 h
all other cells under A6 are blank
the answer is A6
View 9 Replies
View Related
Nov 14, 2007
when I use the range.find function to find a certain value in a column.. i want to return the address of the cell.. and save it in a range variable. how would I do that?
so this is what i have now.. but Rng does not return as a range.. it returns 69... when i use ctrl + G and type ?rng
set rng = .range(A:A).find(what:=69, After:=.Cells(1, 1), Searchorder:=xlByRows, searchdirection:=xlPrevious)
ok so say it picks up..... cell A69... how do i get it to save range A69
View 9 Replies
View Related
Oct 16, 2006
Sub TestSearch()
Dim FoundCell As Range
Dim SearchString As Variant
'Specify Value to Search
SearchString = " Excel"
'Perform the Search and Return a Cell Value
Set FoundCell = Worksheets("Source").Range("A1:A10000").Find(" Excel", lookat:=xlPart)
If Not FoundCell Is Nothing Then
MsgBox "Found at " & FoundCell.Address
End If
End Sub
I'd like to take the code above, which returns a cell address which contains the text " Excel" and modify or add to it the capability to copy a range starting from the address returned and ending 102 cells further down in Column A on the Source sheet to A1 of the Sheet1 sheet. The crux of the problem seems to be converting the address to a row number then adding the value of the number of cells in the range to find the end row number then converting that back to an address??
View 5 Replies
View Related
Feb 1, 2009
I have a data sheet with employee information. Only one column. It prefixes information with codes, but keeps it in the same cell. I want to move data of certain types into their own columns, but the amount of data is variable, so I cannot simply move every Nth cell, etc.
i.e.
Employee#1
200 Firstname Lastname
204 99999999 (Employee ID)
G38 00005000 (i.e. Pension Deduction)
H38 00007580 (i.e. Benefits Deduction)
X96 00012099 (i.e. Staff Club Deduction)
Employee#2
200 Firstname Lastname
204 99999998
G38 00000775
X96 00001000
So you see some employees may have different codes altogether. But I know that I want all the cells that start with 200 to be offset (-1,1), and all the cells that start with 204 to be offset (-1,2), and so on so that basically I end up with columns of info instead of a one column list.
I have been reading and studying other peoples' macros, and am just starting to grasp the basic. When I wrote my own to accomplish this, I put this together, which doesn't work. But I don't know enough to know what I don't know.
View 7 Replies
View Related
Jul 30, 2012
I have 2 columns of data
column A = weeks (A2:A50) i.e. P1W1, P1W2, P1W3....P12W4 etc
column B = headcount (B2:B50) i.e. 5, 7, 5...10 etc
Essentially my criteria is "looking for last week and give me headcount" i.e. my lookup criteria is P7W5 ....however if P7W5 has no data, i want the lookup to go up or offset to the row above (it may be one to 4 rows above)....
View 3 Replies
View Related
Sep 20, 2007
How do I use VBA to send the value of a cell, for example, cell J77 to the variable "mastervalue1"?
I have a spreadsheet that has a column with dates ( in the format "mmm-yy") and next to it a column that has values. I want to pass the value of the cell that is next to the date cell that contains today's month to a variable in VBA. Auto Merged Post;here's an example of my spreadsheet
View 9 Replies
View Related
Feb 13, 2014
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.
View 1 Replies
View Related
Jun 21, 2013
I am using VLOOKUP to find the size of a cam to be installed in a tablet press, based on the product code it will be running.
The array has two columns: (W) Product Code, (X) Cam Size.
Array: W4:X437
The user selects the Product Code from a drop-down list in cell E5.
The resulting Cam Size is displayed in cell E7. The VLOOKUP works fine.
=IFERROR(VLOOKUP(E5,W4:X437,2,FALSE),"")
Occasionally, the cam size has to be updated. The user would then select a new cam size from a drop-down list in cell E9.
I have a "Update Cam Size" command button.
What I need to happen is for the value in E9 to replace the value in the array that is displayed in E7. Obviously, I have to know the location of the cell in the array, but I can't figure that part out. I've tried ADDRESS and MATCH functions, but it comes back with "#N/A" Value not available error.
=ADDRESS(MATCH(E7,W4:X437,0),2)
View 3 Replies
View Related
Sep 6, 2013
Is there any formula to find text and return its cell address? This is like when we use Ctrl+F function to find a text, and Excel then highlight the cell contains the referred text. At the same time, the name-bar displays the address of the cell. Now, is there any formula that can perform such task like this?
View 9 Replies
View Related
Dec 18, 2008
When I use VLOOKUP formula within a VBA routine ex:
For LoopCounter = 1 to LastRow
With Worksheets("0MATERIAL")
.Cells(RowCounter, 5).FormulaR1C1 = "VLOOKUP(RC[-2],'0MAT_UNIT'!R1C1:R65520C12,4,FALSE)"
End With
RowCounter = RowCounter + 1
Next LoopCounter
How can I find out what row or cell address that the VLOOKUP found the data? The above code works perfectly but the problem is I need data from that same row in columns 4-11. I replicated the .Cells line above and made each one a VLOOKUP (for column 4, 5, 6, etc...) but this kills the performance. I have around 32,000 rows in sheet 0MATERIAL and around 50,000 rows within sheet 0MAT_UNIT
View 9 Replies
View Related
Jun 21, 2012
How can I modify the following formula to find the contents of reference cell, for example if the cell reference (G$3) is equal to 9 I want it to use 8 instead.
Code:
IF(ISERROR(INDEX(tra!$E$2:$E$1100,MATCH(G$3,tra!$C$2:$C$1100,0))),"",
INDEX(tra!$E$2:$E$1100,MATCH(G$3,tra!$C$2:$C$1100,0)))
I tried using G$3-1 but this doesn't appear to work.
View 1 Replies
View Related
