Snasui Excel Forum

Code: Select all

Sub lookup()
Dim name, lname As Range

Set name = Range("A2")
Set lname = Range("B2")

With Workbooks("a.xlsx").Worksheets(1)
Dim myrange As Range
Set myrange = Range("A:B")
End With

lname.Value = Application.WorksheetFunction.VLookup(name, myrange, 2, False)


End Sub

Code: Select all

Sub lookup()
Dim name, lname As Range
Set name = Range("A2:A8")
Set lname = Range("B2:B8")
With Workbooks("a.xlsx").Worksheets(1)
Dim myrange As Range
Set myrange = .Range("A:B")
End With
lname.Value = Application.IfError(Application.WorksheetFunction.VLookup(name, myrange, 2, False), "")
End Sub

Code: Select all

With Workbooks("a.xlsx").Worksheets(1)
Dim myrange As Range
Set myrange = .Range("A:B")
End With

Code: Select all

 Range("A:B")

Code: Select all

.Range("A:B")

Code: Select all

Sub lookup()
Dim name, lname, r, t, m, e, sg As Range
Set name = Range("C4")
Set lname = Range("E4")
Set r = Range("G4")
Set t = Range("C7")
Set m = Range("C8")
Set e = Range("C9")
Set sg = Range("E12")
With Workbooks("a.xlsx").Worksheets(1)
Dim myrange As Range
Set myrange = .Range("A:G")
End With
lname.Value = Application.IfError(Application.WorksheetFunction.VLookup(name, myrange, 2, False), "")
r.Value = Application.IfError(Application.WorksheetFunction.VLookup(name, myrange, 3, False), "")
t.Value = Application.IfError(Application.WorksheetFunction.VLookup(name, myrange, 4, False), "")
m.Value = Application.IfError(Application.WorksheetFunction.VLookup(name, myrange, 5, False), "")
e.Value = Application.IfError(Application.WorksheetFunction.VLookup(name, myrange, 6, False), "")
sg.Value = Application.IfError(Application.WorksheetFunction.VLookup(name, myrange, 7, False), "")
End Sub

Code: Select all

Sub lookup()
Dim name, lname, myrange As Range
Set name = Range("C4")
Set lname = Range("E4")
With Workbooks("a.xlsx").Worksheets(1)
Set myrange = .Range("A:G")
End With
With Application
lname.Value = .IfError(.VLookup(name, myrange, 2, 0), "")
Range("G4") = .IfError(.VLookup(name, myrange, 3, 0), "")
Range("C7") = .IfError(.VLookup(name, myrange, 4, 0), "")
Range("C8") = .IfError(.VLookup(name, myrange, 5, 0), "")
Range("C9") = .IfError(.VLookup(name, myrange, 6, 0), "")
Range("E12") = .IfError(.VLookup(name, myrange, 7, 0), "")
End With
End Sub

Code: Select all

..Other code
name.Copy
Application.ScreenUpdating = False
Workbooks.Open ("C:\Users\it-support.cmk\Desktop\àÅÕ¹ẺDBform\a.xlsx")
Range("A" & Row.Count).End(xlUp).PasteSpecial xlPasteValues 'ติดที่บรรทัดนี้ครับ error ตลอด แก้ยังไงก็ติดทุกที
Application.CutCopyMode = fasle
Other code...

Code: Select all

' Other code...
Application.ScreenUpdating = False
Workbooks.Open ("C:\Users\it-support.cmk\Desktop\เลียนแบบDBform\a.xlsx")
name.Copy
Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
Application.CutCopyMode = False
' Other code...

Code: Select all

Range("A" & Row.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues

Code: Select all

Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues

Code: Select all

Application.CutCopyMode = fasle

Code: Select all

Application.CutCopyMode = False

Code: Select all

Sub save()
Dim name, lname, r, t, m, e, sg As Range
Set name = Range("C4")
Set lname = Range("E4")
Set r = Range("G4")
Set t = Range("C7")
Set m = Range("C8")
Set e = Range("C9")
Set sg = Range("E12")

'ตั้งแต่ตรงนี้
Application.ScreenUpdating = False
Workbooks.Open ("C:\Users\it-support.cmk\Desktop\àÅÕ¹ẺDBform\a.xlsx")
name.Copy
Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
Application.CutCopyMode = False
lname.Copy
Range("A" & Rows.Count).End(xlUp).Offset(0, 1).PasteSpecial xlPasteValues
Application.CutCopyMode = False
r.Copy
Range("A" & Rows.Count).End(xlUp).Offset(0, 2).PasteSpecial xlPasteValues
Application.CutCopyMode = False
t.Copy
Range("A" & Rows.Count).End(xlUp).Offset(0, 3).PasteSpecial xlPasteValues
Application.CutCopyMode = False
m.Copy
Range("A" & Rows.Count).End(xlUp).Offset(0, 4).PasteSpecial xlPasteValues
Application.CutCopyMode = False
e.Copy
Range("A" & Rows.Count).End(xlUp).Offset(0, 5).PasteSpecial xlPasteValues
Application.CutCopyMode = False
sg.Copy
Range("A" & Rows.Count).End(xlUp).Offset(0, 6).PasteSpecial xlPasteValues
Application.CutCopyMode = False
'ถึงตรงนี้

Workbooks("a.xlsx").save
Workbooks("a.xlsx").Close


End Sub

Code: Select all

Sub save()
Dim name, lname, r, t, m, e, sg As Range
Set name = Range("C4")
Set lname = Range("E4")
Set r = Range("G4")
Set t = Range("C7")
Set m = Range("C8")
Set e = Range("C9")
Set sg = Range("E12")
Application.ScreenUpdating = False
Workbooks.Open ("C:\Users\it-support.cmk\Desktop\เลียนแบบDBform\a.xlsx")
 Range("A" & Rows.Count).End(xlUp).Offset(1).Value = name.Value
 Range("A" & Rows.Count).End(xlUp).Offset(, 1).Value = lname.Value
 Range("A" & Rows.Count).End(xlUp).Offset(, 2).Value = r.Value
 Range("A" & Rows.Count).End(xlUp).Offset(, 3).Value = t.Value
 Range("A" & Rows.Count).End(xlUp).Offset(, 4).Value = m.Value
 Range("A" & Rows.Count).End(xlUp).Offset(, 5).Value = e.Value
 Range("A" & Rows.Count).End(xlUp).Offset(, 6).Value = sg.Value
 ActiveWorkbook.save
 ThisWorkbook.Activate
 Application.ScreenUpdating = True
End Sub

Code: Select all

 Range("A" & Rows.Count).End(xlUp).Offset(1) = name
 Range("A" & Rows.Count).End(xlUp).Offset(, 1) = lname
 Range("A" & Rows.Count).End(xlUp).Offset(, 2) = r
 Range("A" & Rows.Count).End(xlUp).Offset(, 3) = t
 Range("A" & Rows.Count).End(xlUp).Offset(, 4) = m
 Range("A" & Rows.Count).End(xlUp).Offset(, 5) = e
 Range("A" & Rows.Count).End(xlUp).Offset(, 6) = sg