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) |
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