Excel - If Then Statement Problem?

Question

I have a problem with Excel. I am attempting to create a macro that will check for various people in our dept in a spreadsheet and return the acct number and type that they worked. I am not sure how to write this. basically...what I want it to say is .... If A2 is not equal to D2 (in a different sheet) then delete row and goto A3. If A2 is equal to D2, then copy over the account number and type worked. Then go down through the spreadsheet. I am not sure where to begin on this.

Answer 1

I highly recommend that you use ADO. If you're familiar with SQL, you can use ADO to query an excel spreadsheet like a database. For example this code queries a spreadsheet called Data where ID = 201763A and dumps the matching records into a sheet called Output.

Public Sub Getdata()

Dim rst as ADODB.Recordset
Dim strConnection as String
Dim strSQL as String
Dim sht as Worksheet

strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Thisworkbook.Fullname & ";" & _
"Extended Properties=Excel 8.0;"

strSQL = "SELECT * FROM [Data$] " & _
"WHERE [ID] = '201763A'"

set rst = New.ADODB.Recordset

rst.CursorLocation = adUseClient

rst.Open strSQL, strConnection, adOpenStatic, _
adLockReadOnly, adCmdText

set sht = ThisWorkbook.Sheets("Output")

If Not rst.EOF Then

sht.Range("A1").CopyFromRecordset rst

Else

'No records returned

End If

rst.Close

set rst = Nothing

End Sub

Answer 2

That may be a fairly simple VBA script.

Are you looking to enter a person's name, and see the account number they are associated with?
Or are you looking to enter an account number, and want to see a list of people that are charging that account number?

If necessary, send me an email (from my profile).

Answer 3

I think to do something this complex you would have to write a function in VBA.