PDA

View Full Version : 2 Files



Alvinkiang
06-21-2006, 07:43 PM
:hi:Hi, i am new to Vba Coding as mostly i did vb2005 coding so i hope to get as much help as possible.
Thanks In advance.

I have 2 exel file, A and B, A have 2 Columns which the first contain name while 2nd contain cost.. in file b i use the Vb editor and make a form.

The form has one txtbox and 1 button.
user is to key in the name of the item which is in 1st column of File A..

QUESTION 1. How can i Select and Compare the Whole Colums to see which one matcheS??

Question 2.After able to get Question 1. , lets say there are 2 of that name, how can i take the cost of both name which is in colums 2 and display it into the New Excel File???
:dunno

acw
06-21-2006, 10:53 PM
Hi

This assumes that the data is in sheet1 of book1. Code below is for the command button from the form run from the output book.

Private Sub CommandButton1_Click()
Set datafile = Workbooks("book1").Sheets("sheet1")
With datafile
Set findit = .Range("A:A").Find(what:=TextBox1.Text)
If Not findit Is Nothing Then
firstadd = findit.Address
ThisWorkbook.Sheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Value = findit
ThisWorkbook.Sheets("sheet1").Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Value = findit.Offset(0, 1).Value
Set findit = .Range("A:A").FindNext(findit)
While findit.Address <> firstadd
ThisWorkbook.Sheets("sheet1").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Value = findit
ThisWorkbook.Sheets("sheet1").Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Value = findit.Offset(0, 1).Value
Set findit = .Range("a:a").FindNext(findit)
Wend
End If
End With


End Sub



HTH

Tony