Does anybody know what is wrong with my VBA code?

[NewbieNoob]

Private Sub Button_Click()
On Error Resume Next
Dim Dept_Row As Long
Dim Dept_Clm As Long
NewTable = NewSheet.Range("A2:A10")
DataTable = DataSheet.Range("A2:B10")
Dept_Row = NewSheet.Range("B2").Row
Dept_Clm = NewSheet.Range("B2").Column
For Each Cl In NewTable
  NewSheet.Cells(Dept_Row, Dept_Clm) = Application.WorksheetFunction.VLookup(Cl, DataTable, 2, False)
  Dept_Row = Dept_Row + 1
Next Cl
End Sub

I have a new sheet simply called NewSheet. In column A, I want to input some values, and then have it look at a different sheet called DataSheet, then look up the values in column A on that sheet, then match the values and copy the values from column B in DataSheet over to column B in NewSheet.

 

[Joe4]

If your sheets are named "NewSheet" and "DataSheet", then you need to reference them like this:

Sheets("NewSheet")[COLOR=#333333].Range("A2:A10")[/COLOR]

unless you create a sheet variable like this:

Dim NewSheet as Worksheet
Set NewSheet = Sheets("NewSheet")

 

[NewbieNoob]

If your sheets are named "NewSheet" and "DataSheet", then you need to reference them like this:

Sheets("NewSheet")[COLOR=#333333].Range("A2:A10")[/COLOR]

unless you create a sheet variable like this:

Dim NewSheet as Worksheet
Set NewSheet = Sheets("NewSheet")

Thank you very much!

 

[Joe4]

You are welcome!

The thing to understand that anything (NewSheet, DataSheet) like that which is not in quotes (or referenced some other way) in considered to be a variable. And all variables need to be assigned some value manually.

So Excel was seeing NewSheet and DataSheet as two unassigned variables of variant type.