Private Sub test_45929()
Dim xl As New Excel.Application
Dim wb As Excel.Workbook
Dim sh1 As Excel.Worksheet
Dim sh2 As Excel.Worksheet
Dim lngLastRowInSheet1 As Long
Dim lngLastRowInSheet2 As Long
Dim var As Variant
Dim i As Long
'Set wb = xl.Workbooks.Open("d:\book11.xlsx")
Set wb = xl.Workbooks.Open("place your workbook here")
Set sh1 = wb.Sheets(1)
Set sh2 = wb.Sheets(2)
'get lastrow in sh1
lngLastRowInSheet1 = sh1.Range("A" & sh1.Rows.Count).End(xlUp).Row
'get lastrow in sh2
lngLastRowInSheet2 = sh2.Range("A" & sh2.Rows.Count).End(xlUp).Row
On Error Resume Next
For i = 2 To lngLastRowInSheet1
var = xl.WorksheetFunction.VLookup(sh1.Range("a" & i).Value, sh1.Range("a2:a" & lngLastRowInSheet2), 1, 0)
If Err Then
' error means Vlookup did not find it
' its entirely up to you to decide
' what to do.
Err.Clear
Else
' we found the data in sheet2
' Debug.Print var & ""
MsgBox var & " was found in sheet2"
End If
Next
wb.Close False
xl.Quit
Set wb = Nothing
Set xl = Nothing
MsgBox "done processing workbook!"
End Sub
Thanks a lot, i got an idea to work on.Vlookup is you validator.
if vlookup finds it in sheet2 then it is valid.
otherwise if an error occurs, meaning nothing
is found then what shall you do?
copy and paste this sample code a module.
replace "place your workbook here" with the path and name
of your excel workbook.
Code:Private Sub test_45929() Dim xl As New Excel.Application Dim wb As Excel.Workbook Dim sh1 As Excel.Worksheet Dim sh2 As Excel.Worksheet Dim lngLastRowInSheet1 As Long Dim lngLastRowInSheet2 As Long Dim var As Variant Dim i As Long 'Set wb = xl.Workbooks.Open("d:\book11.xlsx") Set wb = xl.Workbooks.Open("place your workbook here") Set sh1 = wb.Sheets(1) Set sh2 = wb.Sheets(2) 'get lastrow in sh1 lngLastRowInSheet1 = sh1.Range("A" & sh1.Rows.Count).End(xlUp).Row 'get lastrow in sh2 lngLastRowInSheet2 = sh2.Range("A" & sh2.Rows.Count).End(xlUp).Row On Error Resume Next For i = 2 To lngLastRowInSheet1 var = xl.WorksheetFunction.VLookup(sh1.Range("a" & i).Value, sh1.Range("a2:a" & lngLastRowInSheet2), 1, 0) If Err Then ' error means Vlookup did not find it ' its entirely up to you to decide ' what to do. Err.Clear Else ' we found the data in sheet2 ' Debug.Print var & "" MsgBox var & " was found in sheet2" End If Next wb.Close False xl.Quit Set wb = Nothing Set xl = Nothing MsgBox "done processing workbook!" End Sub
Thank yougoodluck and hoping for the best.
okay sureCrossposted https://www.access-programmers.co.uk/forums/threads/remove-spaces-from-drop-down-list.310633/
Suryu, please do not post the same question in different forums.
Dim wb As object
Dim xlWS As object
Dim MyConnection As ADODB.Connection
Dim MyRecordset As ADODB.Recorset
Dim Myquery As String
Dim daoRcd As DAO.Recordset
Dim daoQueryDef As DAO.QueryDef
Dim db As DAO.Database
On Error Resume Next
set oXL= Createobject("excel.application")
oXL.visible = True
set wkbk=oXl.Workbooks.add
set sht = wkbk.sheets(1)
wb.Sheets("Sheet1").cells(1,1).value ="PO"
wb.Sheets("Sheet1").cells(1,2).value ="PO Number"
wb.Sheets("Sheet1").cells(1,3).value ="Invoice Date"
wb.Sheets("Sheet1").cells(1,4).value ="Invoice Amt"
wb.sheets("sheet1").Name ="PODetails"
wb.Sheets.Add(After:=Sheets("PODetails")).Name= ''Order"