Home |
Search |
Today's Posts |
#1
|
|||
|
|||
lookup and if?
I have 2 sheets ... I need to do a query that says IF a number is
input into a cell (in a large array ie c1:z26) in sheet two , THEN the name that corresponds in that row(a1:a26) will be input into a certain cell in sheet one (aa1:zz52). The columns dont match up.. ARGH PLEASE HELP!!! Thanks, KAM |
#2
|
|||
|
|||
lookup and if?
First of all, "zz52" is not a valid cell reference in Excel. "IV" is column
256, which is the maximum available column. For the first part of your question, here is code to iterate through the input range and find the correct text from column A: Code:
Option Explicit Sub Macro1() Dim iRange As Range Dim iSheet As Worksheet 'Input Sheet Handle Dim oSheet As Worksheet 'Output Sheet Handle Dim aCell As Range Dim tStr As String Dim aRow As Long Dim aCol As Long 'Set the handle and range on the Input Sheet Set iSheet = ActiveWorkbook.Sheets("Sheet2") Set iRange = iSheet.Range("C1:Z26") 'Set the handle to the Output Sheet Set oSheet = ActiveWorkbook.Sheets("Sheet1") 'Iterate through the Input Range For Each aCell In iRange 'If the cell contains a number in the range of 1 to 26 If aCell.Value 0 And aCell.Value <= 26 Then 'These are the references for the source cell: aRow = aCell.Row aCol = aCell.Column 'Pull the text from Column A from the row enumerated in the current cell tStr = iSheet.Cells(aCell.Value, "A").Text 'TEST TEST TEST TEST 'MsgBox tStr 'END TEST 'Here is where you load Sheet 1, but you don't say where to put the data ??? 'This will load your lookup text into the same relative cell reference on Sheet 1 'You need to figure out exactly what you want to do here ... oSheet.Cells(aRow, aCol).Value = tStr Else tStr = "" End If Next End Sub "Kerry" wrote: I have 2 sheets ... I need to do a query that says IF a number is input into a cell (in a large array ie c1:z26) in sheet two , THEN the name that corresponds in that row(a1:a26) will be input into a certain cell in sheet one (aa1:zz52). The columns dont match up.. ARGH PLEASE HELP!!! Thanks, KAM |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Another way to lookup data | Excel Worksheet Functions | |||
lookup more than one cell | Excel Worksheet Functions | |||
Lookup Vector > Lookup Value | Excel Worksheet Functions | |||
Lookup function w/Text and Year | Excel Worksheet Functions | |||
double lookup, nest, or macro? | Excel Worksheet Functions |