Minggu, 03 Maret 2013

RE: [ExcelVBA] Find a cell then insert a vlookup

 

I used a match and index to accomplish what I believe you are trying to do.
In the Excel spreadsheet, in the range A1 to D12 I put this data(top US cities for what it's worth):
1

New York

New York

8213839

2

Los Angeles

California

3794640

3

Chicago

Illinois

2824584

4

Houston

Texas

2076189

5

Philadelphia

Pennsylvania

1517628

6

Phoenix

Arizona

1476331

7

San Diego

California

1284347

8

San Antonio

Texas

1258733

9

Dallas

Texas

1246185

10

Detroit

Michigan

921147

11

San Jose

California

908870

12

Indianapolis

Indiana

789250

In the range I8 to J10, I added this data:
Indianapolis

1

Chicago

2

Dallas

3

The code that I wrote will find the first value in column J, which is the number 1. It then search the table for "Indianapolis", and then replace the number 1 with the population of 789250. Here is the code:

Option Explicit
Sub FindDataAndVLookup()
Dim SearchCell As String, SearchCellValue As String, PopulatedCell As String, _
PopulatedCellValue As String, LookupCell As String, LookupCellValue As String, _
NewValue As String
Dim CurrentRow As Integer
Dim TableLookup As Range
Set TableLookup = Sheets("Sheet1").Range("A1:D12")
CurrentRow = 1
SearchCellValue = ""
Dim NewMatch
Dim EndOfData As Boolean
EndOfData = False
Do Until EndOfData = True
SearchCell = ("J" & LTrim(Str(CurrentRow)))
SearchCellValue = Range(SearchCell).FormulaR1C1
If Len(SearchCellValue) > 0 Then
Do While Len(SearchCellValue) > 0
SearchCell = ("J" & LTrim(Str(CurrentRow)))
SearchCellValue = Range(SearchCell).FormulaR1C1
If Len(SearchCellValue) > 0 Then
LookupCell = ("I" & LTrim(Str(CurrentRow)))
LookupCellValue = Application.WorksheetFunction.Clean(Range(LookupCell).FormulaR1C1)
NewMatch = Application.WorksheetFunction.Match(Sheets("Sheet1").Range(LookupCell), Sheets("Sheet1").Range("B1:B12"), 0)
NewValue = Application.WorksheetFunction.Index(Sheets("Sheet1").Range("D1:D12"), NewMatch)
Range(SearchCell).FormulaR1C1 = NewValue
CurrentRow = CurrentRow + 1
Else
EndOfData = True
End If
Loop
End If

CurrentRow = CurrentRow + 1

Loop

End Sub

From: ExcelVBA@yahoogroups.com [mailto:ExcelVBA@yahoogroups.com] On Behalf Of Manny
Sent: Friday, March 01, 2013 3:30 PM
To: ExcelVBA@yahoogroups.com
Subject: [ExcelVBA] Find a cell then insert a vlookup

Hello Wizards!
I have a file that is sorted in such a way that the value in Column J will be null but only up to a point. What is the code I would use to ask VBA to find the first cell in column J that is not null? The first non-null cell should be the integer "1". Then I want to have VBA insert a vlookup function in the cell to the left of this cell. And finally I want the function to be auto-filled down the last row containing data.

I look forward to your responses.

Thank you,

[Non-text portions of this message have been removed]

__._,_.___
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (2)
Recent Activity:
----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------
.

__,_._,___

Tidak ada komentar:

Posting Komentar