Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Row & Column Intersection Value


how to write a macro that reads the point where a cell and collumn intersect?

Below is my example:

1 6.25 3 9
7.25 10960700 10711700 10481100 10267000

7.5 10756600 10513300 10288000 10078800

7.75 10557200 10319500 10099400 9895000

8 10362400 10130100 9915100 9715400

8.25 10172200 9945200 9735100 9539900

How do I write a macro to find the intersection of 7.75 and 3, which is 10099400?


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Select Intersection Of Column And Row
First although it returns the correct column, it always returns Row 1. Beyond me! Secondly I could really use some help in a line or two of code to select the cell at the intersection of the column and row.

Sub test()

Dim name As String
Dim number As String
Dim col As Integer
Dim row As Integer
Dim RowNumber As Integer
Dim ColNumber As Integer
Dim rng As Range

ActiveWorkbook.Sheets("Sheet1").Activate

name = InputBox("type a name from Column A")...........


View Replies!   View Related
Highlighting Column And Row Intersection Cells
Any code or formula to highlight the intersected cells between a column and a row for any selected cell ?

Ex: if i select E25 the cells of column E1:E25 as well as the cells of Row A25:E25 to be highlighted in a certain color , and so on for any selected cell..


View Replies!   View Related
Cell/Range Names Based On Row & Column Intersection
i have a table in excel with 36 rows and 36 columns i.e. 36x36=1296 individual cells. Columns and rows have headers/labels i.e. 36 rows with row label headings and similarly 36 columns with column label headings. I would like to automatically create unique cell names for the 1296 cells based on where the columns and rows intersect using the row and column labels. The cell name format I would like to create is [ColumnLabel_RowLabel].

I've manually created the cell names in the attached file as an example of what i would am seeking (i've greyed the cells that I've done this for).


View Replies!   View Related
Date Ranges Intersection
There is a database with work plans for a project cycle, which contains employee names, types of activities, planned working hours for each activity (not in time format, just number) and dates when these hours will take place. Each employee may have one or multiple activities and each activity has different number of hours planned to complete and different dates. My aim is to calculate the number of hours planned for each employee (to sum all activities) within a certain period of time.

So, what is the question? I need to calculate the number of workdays within the intersection of the planned period for the activity and my given period of time.

I'll give a small example to illustrate what I mean.

I'm calculating planned working hours for this week, which is 11/23/2008-11/29/2008. One of our employees, let's call him John, is going to work on three activities, A, B, and C. We have the following plans:

Activity A - 16 hours - 11/24/2008-11/27/2008
Activity B - 45 hours - 11/15/2008-11/25/2008
Activity C - 10 hours - 11/26/2008-12/01/2008

As you can see, activity A is going to be complete within this week, it's perfectly fine. Activities B and C should be counted separately, because they include days earlier or later than this week. Is there any way I can easily geet the number of workdays that are included in the plan period and this week?

Another thing to mention is that I'm working in Excel 2003 and it's not acceptable for me to install the Tools Pack, because I need this firmula to work on other employees computers and there is no chance they all will install it too.

View Replies!   View Related
Cross Reference Maybe Intersection
I am having to figure out a particularly painful excel formula. What i need is a formula that will count the number of times an issue label in text shows up, but it has to match a certain time.

we can get the excel sheet to round the time to the nearest thirty minutes, so if a call comes in at 3:12pm it rounds it to 3pm. We label the calls that come in with a issue description also like "dvr issue." So i need a formula that will allow me to count the number of instances where if it says 3pm AND dvr issue it will only count it and give me a total in a specified cell. Is that possible?

View Replies!   View Related
Sumif To Return An Intersection
I have the following data sets

worksheet1

TRANCODES 234568 .........99
PARTS
003-0623-00
005-0030-00

worksheet2

PARTSTRANCODE SUM_QTY
003-0018-01 28 10
003-0018-01 40 10
003-0021-00 8 25
003-0021-04 4 63

In worksheet1 I need to compare the part number and the trancode then
return the quantity from worksheet2 -

View Replies!   View Related
Lookup Intersection Of 2 Headings
I am trying to find out if i can reference to a table on Excel. For example; if i enter in one cell the word 'january' (a header on the left of the table) and 'week 1' (a header on the top of the table) in another cell, can i set it to produce the value in the table that coresponds to the two headers.

View Replies!   View Related
Find Two Cells Then Return Value At Intersection
I have a code which locates a value in a column, and locates a word in a row. I've then tried to return the value that intersects these to findings. eg. The code finds a value in "A10", and a value in "E5", The code then needs to return the value in "A5".

View Replies!   View Related
Look Up 2 Number Ranges And Find Intersection
How would I create a formula that would match a number in a range by column and another number in a range by rows, then fin the intersection of those 2 numbers?

View Replies!   View Related
Intersection Point Return Data
I have 100 rows and 100 columns, where row 1 is dates, and column A is times, and within are numbers. On a second sheet I have a column (A) with random dates and another (B) with times. What I need is a third column (C)on the second sheet that looks at the date and time, finds the intersection on the first sheet, and returns the number found there.

View Replies!   View Related
Finding Intersection On Seperate Worksheet
I cannot figure the formula for finding an intersection of two points on a seperate worksheet. I have attached the workbook.

View Replies!   View Related
Intersection Of Date Range And Current Month
I need to compute the number of days that a given range of dates has in common with any given month. So an example might be:

Cell A1: Range Start Date (say it's 1/1/07)

Cell A2: Range End Date (say it's 2/15/07)

Cell A3: Month Indicator (say it's 2, meaning February)

Cell A4: Days of Intersection (should be 15 in this case, meaning that 2/1-2/15 were the days of February that were also in the range 1/1-2/15)

Cell A4 is what I'm trying to create the formula for.

I'm in Excel 2000 and am having trouble installing the add-on.

View Replies!   View Related
Find & Return Intersection Of Matrix
I have a 10 x 15 matrix named "Range1"

I have named the range of Column headings in that matrix as "Weight", and the range of Row headings as "Height". And then all the matrix cells contain some values.

Now I need to find the value from intersection of height and weight (please see the attached excel file)

Attention: I need to use excel VBA code to do the trick. (Not Worksheet formula using for example INDEX and MATCH)

View Replies!   View Related
Determine Intersection Of Target To Multiple Ranges
This code works fine, but it does not look professional. I tried to write like: If Application.Intersect(Target, rng1, rng2, rng4, rng5) Is Nothing Then Exit Sub

it did not work, but as it is down here it worked

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng1 As Range, rng2 As Range, rng3 As Range, rng4 As Range, rng5 As Range
On Error Goto NoValidation
Set rng1 = Range("B28:B55")
Set rng2 = Range("D28:D55")
Set rng4 = Range("H28:H55")
Set rng5 = Range("J28:J55")
If Application.Intersect(Target, rng1) Is Nothing Then
If Application.Intersect(Target, rng2) Is Nothing Then
If Application.Intersect(Target, rng4) Is Nothing Then
If Application.Intersect(Target, rng5) Is Nothing Then
Exit Sub
End If
End If
End If
End If
End If
If Target. Validation.InCellDropdown Then Application.SendKeys "%{Up}"
NoValidation:
Err.Clear
End Sub

View Replies!   View Related
Search From Every Row Of Column In A Column And Return Row Number
how to find text from a row in column, where is a lot of text and return row number, of located text?

View Replies!   View Related
Report The Value Of An Intersecting Row And Column, Without Numbering The Row And Column
# STUDENTS THAT GOT 100% IN EACH SUBJECT IN EACH SCHOOLKKVRockfortCampionENGLISH232013MATHS382518PHYSICS422515CHEMISTRY483020BIOLOGY503523

Consider the above table. Ca someone help me understand how to report the value of the # of students that got 100% (output) in a given school (input 1), and given subject (input 2).

In other words, if the inputs are the following:
Input Cell 1: Rockfort
Input Cell 2: Pysics

Then, Output Cell should report: 25

I know one option is to use the INDEX function, but with index function, I have to provide Row_Num & Col_Num as inputs. That doesn't fit my example above, as my inputs are specific values from the leading row and the leading column of the array in question.

View Replies!   View Related
Highlighting Active Cell's Row, Along With Any Row That Shares Same Value In That Column
Is it possible to click on a cell in column C, and have the wishlist below happen:

That active cell's row is hightlighted.

Any cell in that column that has the same value as active cell is also highlighted.

Plus, any cell in another sheet that has that value it's row is highlighted too.

Example:

I click on C5 in Sheet 2 its value is 45000789 it row is highlighted, this value also appears in C3 in the same sheet, so it's row is highlighted as well. Plus, in sheet 1 in C10 this value appears and it's row is highlighted as well. When any of the values are clicked again the highlight is removed from all parties.

View Replies!   View Related
Lookup Row In Column & Heading In Row
I'm trying to enter info in a cell based on what is entered in two other cells.

So I have a dropdown box for the species on another sheet as well as a dropdown box for rail sizes. Then I have to crossreference them to come up with an upcharge number. Sorry for my basic lingo. I hope it's understandable.

View Replies!   View Related
Conditional Format A Row Based On The Value Of The A Column Of The Row
I have the following code. I am trying to conditional format a row based on the value of the A column of the row.I have a total of 3 rows. I have to use VBA because in time I may have 4 rows of data. The problem is I after the first do while loop the code stops and the code doesn't proceed to format row 2.

View Replies!   View Related
Pass Row Number Of Last Used Column Row To Variable
This line in my code is causing an invalid qualifer error message:

lngNew = wsNew. Range("B65536").End(xlUp).Row.Offset(1, 0)

Option Explicit
Sub Disney_DumpData()
Dim wbBook As Workbook
Dim wbNew As Workbook
Dim ws As Worksheet
Dim wsNew As Worksheet
Dim rng As Range
Dim rngNew As Range
Dim rngUnit As Range
Dim rngUnitPaste As Range
Dim lngNew As Long
Dim Cnt As Integer
Application. ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False
Set wbBook = ThisWorkbook
Cnt = 0
'Dump To New File
'/Define new workbook
Set wbNew = Application.Workbooks.Add.....................

View Replies!   View Related
Multiple Row, Single Column Cell Blocks Into Single Row, Multiple Column Format
I have a text file containing internet explorer browser history. The file has data in the following format (in Excel all data is in 1 column): ...

View Replies!   View Related
Match Text In Column Of Another Workbook & Return Nth Column Same Row
I have two excel files.
File #1 is a varying length and has 4 columns with ID #s in Column A.
File #2 is a two column file that contains a list of ID #s in column A AND modified ID#s in column B.

I need to match the ID #s in column A from file# 1 to the list in column A of file#2 then replace ALL instances of the matching ID #s in column A of file#1, with the values found in column B of file #2.

I've tried using the substitute function but I could only replace one found ID at a time.

View Replies!   View Related
Function To Put In A New Column To Get The Abbreviated Year And Month Of A Previous Column In The Same Row
Is there a function to put in a new column to get the abbreviated year and month of a previous column in the same row???

Ex. Row A B C D
3/14/2007 5/16/2007 2007-3 2007-5

Instead of using the YEAR MONTH funcion for each row is there a universal function that I could designate to the whole column of C and D something like C=YEAR(A(row#))???

View Replies!   View Related
Macro - If Cell In Column Is Blank Then Input Text Into Same Row In Another Column
I am looking for a Macro that will search a column for blank cells, and when one is found will add text to the same row in another column. For example: The below is a spreadsheet. I am trying to find something that will search through column "C" in this case and add text (of my choice) to Column "A" if the cell is Blank. So Since cell C1 is Blank then Type "ERROR" is cell A1.

A B C D E
1 X X X X
2 X X X X
3 X X X X X
4 X X X X
5 X X X X

View Replies!   View Related
Data To Shift On Column 2 After Reaching The Row Limit Of 65,536 In Column 1
i am using the code posted by Reafidy on [html] http://www.ozgrid.com/forum/showthread.php?t=62708[/html] to create a list of all possible combinations. The only problem i have is that sometimes the total no. of combinations exceed the excel row limit of approx 65,536. once the row limit in column 1 exceeds 65k, the remaining combinations automatically shift on column 2 and then on 3 n so on.

View Replies!   View Related
VBA Find First Populated Column In A Row, Starting At Column X
How would I write something that finds the first populated column in a row, starting at column C and moving to the right?

View Replies!   View Related
Reverse Vlookup (get Value From Reference One Column And Return Value By Corresponding Row From Another Column)
I want to get value from reference one column and return value by corresponding row from another column. This can be done by vlookup column. But it find value from 1st column and return value from given relative column. That mean find from Left column and return value from right side given column number.

But I want find from Given column number and return value from given left column. For Further detail see the Attached file.

View Replies!   View Related
Copy Multi-Column/Row Range Into Single Column
I have a contiguous multi column, multi row numeric range that I want to copy (and sort in ascending order) into a single column on another sheet in the same workbook. What VBA code could achieve this, or alternatively can this be achieve via formulas?

View Replies!   View Related
Find Value In Column & Return Cell On Same Row- Different Column
I have an excel sheet I am working on and in columns F1:F2000 I have an IF statment, I need to be able to add more "IF"s to it but I will exceed the 1024 char limit. Is there a way I can put this formula into VB as a function called DocumentType() and then in excel F1:F2000 =DocumentType()?

Is it as simple as:

Function DocumentType()

If(...........)

End Function

View Replies!   View Related
Automatically Insert Row When Row Value In Column Changes
I'm unable to find VBA code to insert a blank row when the value in Column L changes. For example if cell L2 = 400 and cell L3=500 I need to insert a blank row between L2 and L3. I need the macro to search the entire sheet which will have variable numbers of rows but Column L will always have data.

View Replies!   View Related
Delete Entire Row If Column A Matches Column D
Does anyone have a macro that will delete entire row if column a matches column d

View Replies!   View Related
Formula To Go Through Each Row And If The 2nd Column Is Yes Then Return The First Column
I have a collection of data like the following:
Week1 | yes
Week2 | no
Week3 | no
Week4 | yes
Week5 | no
...
Week28 | yes

I need a formula to go through each row and if the 2nd column is yes then return the first column. I could then copy and paste this formula into a the next cell, add an argument saying the returned value cannot be the same as the previous ones, and so on. Essentially this is a series of nested IF statements. However, Excel prevents more than 7 statements from being nested, when I need all 28 to be searched. I cannot think of a way to get VLOOKUP, INDEX & MATCH, or defined names to make this work, because it needs to step through each row.

View Replies!   View Related
Copy Non Blank Cells In Column Up 1 Row To Another Column
I need a macro to copy and paste data from Col A to Col B. But I need it pasted 1 row up. In other words if A6 has data I need it pasted in B5 and Bolded. I got a start but don't know how to finish.

Dim ii As Long
For ii = lastrow To 6 Step -1
If Not IsEmpty(. Range("A" & ii).Value) Then ****.Range("A" & ii).Copy*****
Next ii

View Replies!   View Related
If Column F Has “Out” And Column K Has Any Contents Copy The Row
I’d like to check each row in Column F and Column K of Sheet Check. If Column F has the contents “Out” and Column K has any contents inside its cell, I’d like to copy that row and insert it into Sheet Alert. As a result the same row will exist in Sheet Check and Sheet Alert. This code will cut the row out of Sheet Check and paste the row into Sheet Alert if the contents “Out” is found in Column F.

View Replies!   View Related
Find Column A Of Same Row Anywhere On Row
I typically record macros that need to copy information like a company name from say sheet 2 and paste in a specific cell in sheet 1. As the cursor can be anywhere on a specific row I usually create a column that holds something like zz.. and then in the macro go

Shift + Spacebar to select the line I need to work from then Ctrl + F to find zz.. and that is how I know where the cursor is as a starting point.

How is it possible to have the cursor know how to find a specific point i.e. column A from any specific column on that same particular row?


View Replies!   View Related
Copy From Row In Column To Last Used Row
i hav a file with over 2000+ rows of data..i'd only like to copy out F7:F77 only..However the length of information in the reticle data (refer sample) may varies..so from cell F7 i found the last used cell using --> range("F7").End(xldown).Select

how do i select the whole column F7:F77 from there so i can copy it out?

View Replies!   View Related
Asymmetric Fill (fill Out Column B Referencing Column A, But Only Incrementing By 1 Row In A Every 2 Cells In B)
Is there some easy way to fill out column B referencing column A, but only incrementing by 1 row in A every 2 cells in B?

Example:

Column A:
A1 = 1
A2 = 2
A3 = 3
etc...

Column B (I would like to fill this, referencing column A):
B1 = A1
B2 empty
B3 = A2
B4 empty
B5 = A3
etc

View Replies!   View Related
Highlight Blank Cells Red In Column G If Not Blank In Column C (same Row)
I would like a macro to do this...If a cell in column G is blank and the cell in the same row in column C is NOT blank, highlight the blank cell in column G Red.

I need the search to stop ONLY when it gets down to the bottom-most row of data in column A.

Note: Any row headers will always be in row 1 only

Current...

View Replies!   View Related
Last Row, Last Column
When I hit CNTRL-END in a worksheet Excel places the cursor at the intersection of the Last Row and the Last Column in the workbook. Is there a way to get these values programatically? I have over 100 columns to process and they each have a different number of entries. It changes every day so I can't just say "Column AA has the most entries".

View Replies!   View Related
Column To Row
Is it possible to change the column to row with reference!
See the attachment for more clearance!

I need a drag down ways in rows. (not manually!)

View Replies!   View Related
Last Used Column In Row
The columns will always be changing. So I just need a simple code to show me the last column but I need to paste the info into the cell over. I have listed the code I've gotten so far but don't know how to tell it to move over one column/cell over so that I can paste the information. Any help would be nice. I've searched and haven't found anything close.

Sub
Range("IV2").Select
Selection.End(xlToLeft).Select
End Sub

View Replies!   View Related
Look Up In Column A Of "AS" Once It Find That Number I Need It To Return The Number Thats In Column E Of That Row To Sheet "IS"column D
i have 2 sheets one called "IS" and the other called "AS" in cell a2 of "IS" is a number that i need to have excel look up in column a of "AS" once it find that number i need it to return the number thats in column e of that row to sheet "IS"column d. summary: a2 of "IS" looked up on sheet "AS" and returns the number in column e to cell d2 of "IS"

View Replies!   View Related
Delete Row If Zero In Column
I have a master list of employees, which is pulled from another workbook using a formula like this: ='S:PII ProductionP2 crew listscrew pII[1st shift PII staffing.xls]4310'!B9

If a position is currently empty, then the column (J) that lists the employee number will show a zero. I would like this spreadsheet to show only the positions which are currently filled. Is there an easy way to do this?

View Replies!   View Related
Column - Row Loop
I am trying to loop through columns and rows to get the sum of each and delete that column that equals zero. I have-

Dim lastrow
Dim r, c As Integer
Dim RowSum
RowSum = 0

ActiveSheet.UsedRange.Select
lastrow = Selection.Rows.Count

For r = 2 To 80
For c = 2 To lastrow
RowSum = RowSum + ActiveSheet.Cells(r, c).Value ' here I get error Application-defined or object-defined error

Next c
If RowSum = 0 Then
ActiveSheet.Cells(r, c).EntireColumn.Delete
End If
Next r

What am I doing wrong?

View Replies!   View Related
Returning ROW Or COLUMN
since my previous post got a little complex I will make it easier so I can continue puzzling myself.

I have a value "10:00"

and a table:
00:00 08:00
08:01 12:00
12:01 14:00
etc

Now I sue LOOKUP("10:00",A1:B3). This will return "12:00"

But I would not want it to return this VALUE but want it to return ROW or COLUMN. I tried ROW(LOOKUP.....) but this doesn't work.

How do I look it up and then return either B, or 2 (in this example)

Once I have this I combine my 3 way lookup by combining my new found knowledge

View Replies!   View Related
Lookup Value In Row And Column
I am trying to create a formula that will look up the data in Sheet 2, A3 and if found in Sheet 1 to then look up Sheet 2, C1 in Sheet 1 and then enter the data if found into C3. Is there a way to do this?

I have attached a sample workbook.

View Replies!   View Related
Value Picked In The First Column Regardless Of The Row
I have a drop down list in rows of a column to fill out a ledger. The items have multiple account number that I want to put in a second column. I can do the lookups, but I want to have one cell that equals the value picked in the first column regardless of the row. For example if I pick a value for A1 I want it to equal O1, and if I pick a value for A2 I want it to equal O1 and so on, in order to activate a lookup for the second list.

View Replies!   View Related
Find Last Row In Column
Is it possible with a formula to determine what the last row is in a
certain column which is "Not Empty"?


View Replies!   View Related
Copy To Last Row In Column
The problem i have is that i need to copy data that is in i2 to the last row with data in it. The lasrt row is varible

View Replies!   View Related
Concatenate Column With Row Value
I have created vbscript that references a particular column and row. This cell is referred to many times in the scripting but the row may change from time to time. I would like to know how I can define the row value at the top and reference this row within my vbscript.

Dim row As Integer
row = 1

'vb script coding referring to cell
ws.Range("B&row").Copy Destination:= Sheets("Summary").Range("B1")
ws.Range("E&row").Copy Destination:=Sheets("Summary").Range("B16")
ws.Range("F&row").Copy Destination:=Sheets("Summary").Range("B17")

I tried to concatenate the row to the column but it doesn't seem to work. When I would like to change the row value I would like to only make the change where row is defined rather than within each line of coding.

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved