X
xlsxlsxlsxls
Hi, I've posted this a while back, thanks for all the input howeve
still can't get it to do what I want. Almost there though. Here i
situation. I have 2 worksheets. "SearchData" is my main sheet with
columns.
Col#1
NAME
Anne
Barry
Bill
Bruce
Carl
Eve
Frank
Greg
Janet
Col#2
NUMBER
5551111
5552222
5553333
5554444
5555555
5556666
5557777
5558888
5559999
This will serve as a database sheet, so that each month I get a ne
sheet with names only the formula will return the corresponding number
or 0 if not found. So this new sheet is called "New" and only has
column of names of which some are found in "SearchData" sheet.
Col#1
NAME
Anne
Ben
Bob
Bud
Carl
Eve
Frank
Gus
Jill
John
Ken
Moe
Pam
Sam
I've tried piecing together several codes and this is what I came u
with. The message box w/ rows count (not needed) was supposed to denot
which cell I was on thus when code loops, the formula would go to a2
a3, a4....and so on. But as the code is run, it copies the same formul
all the way down. How do I get it to go consequetively down each row an
finish when it comes to blank row?
Sub vlookup()
Dim x As Integer
Sheets("new").Select
Range("A2").Select
'get range
Range(Selection, Selection.End(xlDown)).Select
With Selection
x = .Rows.Count
MsgBox x
End With
Worksheets("new").Select
Range("a2").Select
Do While Not IsEmpty(ActiveCell)
'
ActiveCell.Offset(0, 1).Select
ActiveCell.Value
"=IF(ISNA(MATCH(A2,Searchdata!$A:$A,0))=FALSE,VLOOKUP(new!$A2,Searchdata!$A$2:$B$64000,2,FALSE),0)"
ActiveCell.Offset(0, -1).Select
ActiveCell.Offset(1, 0).Select
Loop
MsgBox "done"
End Sub
' note: I tried setting my rows count =x and then having my formul
as..........AX instead of A2. Manually copying and pasitng from the th
b2 on down will get the desired result, but can't get code to do it.
Thank
still can't get it to do what I want. Almost there though. Here i
situation. I have 2 worksheets. "SearchData" is my main sheet with
columns.
Col#1
NAME
Anne
Barry
Bill
Bruce
Carl
Eve
Frank
Greg
Janet
Col#2
NUMBER
5551111
5552222
5553333
5554444
5555555
5556666
5557777
5558888
5559999
This will serve as a database sheet, so that each month I get a ne
sheet with names only the formula will return the corresponding number
or 0 if not found. So this new sheet is called "New" and only has
column of names of which some are found in "SearchData" sheet.
Col#1
NAME
Anne
Ben
Bob
Bud
Carl
Eve
Frank
Gus
Jill
John
Ken
Moe
Pam
Sam
I've tried piecing together several codes and this is what I came u
with. The message box w/ rows count (not needed) was supposed to denot
which cell I was on thus when code loops, the formula would go to a2
a3, a4....and so on. But as the code is run, it copies the same formul
all the way down. How do I get it to go consequetively down each row an
finish when it comes to blank row?
Sub vlookup()
Dim x As Integer
Sheets("new").Select
Range("A2").Select
'get range
Range(Selection, Selection.End(xlDown)).Select
With Selection
x = .Rows.Count
MsgBox x
End With
Worksheets("new").Select
Range("a2").Select
Do While Not IsEmpty(ActiveCell)
'
ActiveCell.Offset(0, 1).Select
ActiveCell.Value
"=IF(ISNA(MATCH(A2,Searchdata!$A:$A,0))=FALSE,VLOOKUP(new!$A2,Searchdata!$A$2:$B$64000,2,FALSE),0)"
ActiveCell.Offset(0, -1).Select
ActiveCell.Offset(1, 0).Select
Loop
MsgBox "done"
End Sub
' note: I tried setting my rows count =x and then having my formul
as..........AX instead of A2. Manually copying and pasitng from the th
b2 on down will get the desired result, but can't get code to do it.
Thank