How to use Vlookup with the Indirect function in VBA to get data from multiple worksheets. More details available at this website link: www.exceltrain...
Hello sir, My computer is having windows 7 home edition and here dtpicker option is not available. Can you help to find alternatives of dtpicker... Thank you in advance...
Hi, I found this video very interesting, I do have a question actually is it a problem I have, I have this formula: = VLOOKUP (D $ 2; INDIRECT ($ B $ 1); 3; FALSE), what is now the problem, both D $ 2 and the INDIRECT changed, the row 2 just stays 2 in between so actually it is D $ 2 +3 and it becomes G $ 2 and so on, with Indrect it is the same, so I go first row per row but the next row shifts 1 (or +3) and it becomes for example: = VLOOKUP (G $ 2, INDIRECT ($ E $ 1), 3, FALSE), how can I solve this, can this be done with a loping, if so you can give me an example.
Hi Dinesh Ji, i can export data from a excel sheet to pdf, but i am not able to protect that pdf file, because the pdf file can be editable by Acrobat reader, pls help me how to protect pdf file
Thanks sir for your video, I have one problem can't solved. How can i get massage in VBA if my balance is due. Ex: invoice total amount is 1000/= ,Payment value 500/= so I need stop next step(finish transaction) untill I get 500 or more than invoice value. Help me sir.
Hi Sir, Thanks a lot for video. I want to do similar thing for closed workbook for which I have path on my open sheet. My Z1 cell has path for closed workbook that is 'J:\Model\[Book2.xlsx]Sheet1'!$A$5:$W$26 But i am using Vlookup(A2, INDIRECT(Z1), 3, FALSE) it is not working. Where as Vlookup(A2, 'J:\Model\[Book2.xlsx]Sheet1'!$A$5:$W$26, 3, FALSE), this works, Please help. Thank you in Advance Regards, Akash
"Restrict excel file opening on certain computers using Physical MAC adress" purpose: i want to restrict my employee to do work at only on office computers not on any other personal computer, so i want to add the physical MAC address of my 3 office computers in VBA coding...., so that excel file should not open on any other computer "
You can try this strategy but do not forget to add your own MAC addresses: Sub GetUserName_Environ() Dim ObjWshNw As Object Set ObjWshNw = CreateObject("WScript.Network") MsgBox ObjWshNw.UserName MsgBox ObjWshNw.ComputerName MsgBox ObjWshNw.UserDomain myMACAddr End Sub Function GetMyMACAddress() As String 'Declaring the necessary variables. Dim strComputer As String Dim objWMIService As Object Dim colItems As Object Dim objItem As Object Dim myMACAddress As String strComputer = "." Set objWMIService = GetObject("winmgmts:\\" & strComputer & " oot\cimv2") Set colItems = objWMIService.ExecQuery("SELECT * FROM Win32_NetworkAdapterConfiguration WHERE IPEnabled = True") For Each objItem In colItems If Not IsNull(objItem.IPAddress) Then myMACAddress = objItem.macaddress Exit For Next GetMyMACAddress = myMACAddress End Function Sub auto_open() Dim myMACAddr As String myMACAddr = Application.Clean(GetMyMACAddress) MsgBox myMACAddr Select Case UCase(myMACAddr) Case Is = "##:##:##:##:##:##", "##:##:##:##:##:##", "##:##:##:##:##:##" OKMACAddr = True Case Else OKMACAddr = False MsgBox "Not authorized to use this File!" End Select End Sub Here we get username, computername, userdomain and the MAC address to ensure that only the authorized people get access to the file. You'll have to tweak this code for your needs.
![เขามัทรี - เอ็กซ์ ศุภกฤต - จอนนี่มิวสิค [ Official MV ]](http://i.ytimg.com/vi/AS9ufKuQIVs/mqdefault.jpg)
NICE VIDEO SIR ....HELPFULL
Once again another great tip.
your tutorial were awesome!
plz make a tutorial on "Restrict excel file opening on certain computers using Physical MAC adress"
Thanks Dinesh. This formula does not have to be modified to drag across and down. =VLOOKUP($A2,INDIRECT(B$1&"!$A$1:$B$4"),2,0)
Thank you!
Nice sir
Hello sir, My computer is having windows 7 home edition and here dtpicker option is not available. Can you help to find alternatives of dtpicker...
Thank you in advance...
Hi, I found this video very interesting, I do have a question actually is it a problem I have, I have this formula: = VLOOKUP (D $ 2; INDIRECT ($ B $ 1); 3; FALSE), what is now the problem, both D $ 2 and the INDIRECT changed, the row 2 just stays 2 in between so actually it is D $ 2 +3 and it becomes G $ 2 and so on, with Indrect it is the same, so I go first row per row but the next row shifts 1 (or +3) and it becomes for example: = VLOOKUP (G $ 2, INDIRECT ($ E $ 1), 3, FALSE), how can I solve this, can this be done with a loping, if so you can give me an example.
I'll have to check.
Hi Dinesh Ji, i can export data from a excel sheet to pdf, but i am not able to protect that pdf file, because the pdf file can be editable by Acrobat reader, pls help me how to protect pdf file
Watch this video: th-cam.com/video/eYIRC-M4B0k/w-d-xo.html
Thanks sir for your video, I have one problem can't solved. How can i get massage in VBA if my balance is due. Ex: invoice total amount is 1000/= ,Payment value 500/= so I need stop next step(finish transaction) untill I get 500 or more than invoice value. Help me sir.
Hi Sir,
Thanks a lot for video.
I want to do similar thing for closed workbook for which I have path on my open sheet.
My Z1 cell has path for closed workbook that is 'J:\Model\[Book2.xlsx]Sheet1'!$A$5:$W$26
But i am using Vlookup(A2, INDIRECT(Z1), 3, FALSE) it is not working.
Where as Vlookup(A2, 'J:\Model\[Book2.xlsx]Sheet1'!$A$5:$W$26, 3, FALSE), this works, Please help.
Thank you in Advance
Regards,
Akash
Use the RANGE property for Z1.
Sir, I dont know how to do it and cant find anything relevant on internet.
"Restrict excel file opening on certain computers using Physical MAC adress"
purpose:
i want to restrict my employee to do work at only on office computers not on any other personal computer, so i want to add the physical MAC address of my 3 office computers in VBA coding....,
so that excel file should not open on any other computer "
You can try this strategy but do not forget to add your own MAC addresses:
Sub GetUserName_Environ()
Dim ObjWshNw As Object
Set ObjWshNw = CreateObject("WScript.Network")
MsgBox ObjWshNw.UserName
MsgBox ObjWshNw.ComputerName
MsgBox ObjWshNw.UserDomain
myMACAddr
End Sub
Function GetMyMACAddress() As String
'Declaring the necessary variables.
Dim strComputer As String
Dim objWMIService As Object
Dim colItems As Object
Dim objItem As Object
Dim myMACAddress As String
strComputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "
oot\cimv2")
Set colItems = objWMIService.ExecQuery("SELECT * FROM Win32_NetworkAdapterConfiguration WHERE IPEnabled = True")
For Each objItem In colItems
If Not IsNull(objItem.IPAddress) Then myMACAddress = objItem.macaddress
Exit For
Next
GetMyMACAddress = myMACAddress
End Function
Sub auto_open()
Dim myMACAddr As String
myMACAddr = Application.Clean(GetMyMACAddress)
MsgBox myMACAddr
Select Case UCase(myMACAddr)
Case Is = "##:##:##:##:##:##", "##:##:##:##:##:##", "##:##:##:##:##:##"
OKMACAddr = True
Case Else
OKMACAddr = False
MsgBox "Not authorized to use this File!"
End Select
End Sub
Here we get username, computername, userdomain and the MAC address to ensure that only the authorized people get access to the file. You'll have to tweak this code for your needs.