Contact Us

Thank you Ganesh!

Hi Mahendra,
We have provided many topics for Excel VBA tutorials, you can learn from our site. And regarding SQL, we have only the basics required for Business Analyst here. Advanced SQL tutorials will be posted soon.

Tutorials

Thanks-PNRao

Hello Stella, Thanks for your comments and suggestion.
I am working on real life projects and will be posted in few days.

Thanks-PNRao!

Thanks a lot, you are most welcome to our blog! I try my level best to share my knowledge in Excel VBA and Data analysis.

Regards-PNRao!

You can use for loop to do this. If you want to copy the data from Cell A1 to A10

For iCntr=1 to 10
Sheet2.Cells(iCntr,1)=Sheet1.Cells(iCntr,1)
Next iCntr


Hope thus helps!
Thanks-PNRao!

Hi Suresh,

Thanks for your intrest, here is the link for learning basics of VBA:

http://analysistabs.com/excel-vba/excel-macros-and-vba-for-beginners/

Also check our Other Tutorials for VBA:

http://analysistabs.com/tutorials/

Hi Ankit,
You can use ADO and SQL to your tasks, please check the following tutorials on Excel, VBA and SQL.

http://analysistabs.com/excel-vba/ado-vba-connecting-database-sql/

http://analysistabs.com/excel-vba/interact-with-other-applications/

Thanks-PNRao!

Am getting hooked to Excel, VBA even though I don’t need to do.

Thanks a lot sir for your Tutorial. It’s awesome. I have seen lot’s of Tutorial. But your way of teaching on net it’s marvelous.Sir Tell me one thing How Can I copy merged cell data in different sheet or different cell. Suppose that A1 to D8 merged data. I have to copy this data in different sheet or different cell. How can I?

This is of real help for a beginner like me. Hats Off to you Mr. PN Rao

Very good progress of this website. Good jobs “analysistabs” team members for upgrading website. Not only me many of them are Loyal Customer to this website.

You have got a great website with some very useful tutorials. I have immediately signed up to your blog. Just installed your add-in and its very unique I must say. A couple of suggestions:
1) For the List All Files and Folders, you have a very helpful procedure there, what could even be better is a provision for hyperlinks.
2) For the same List All Files And Folders, I would have preferred a seperate column for the the Folder Name and to be repeated throught out the column. In that way if there is a needs to sort by Folder, it would be easy. Currently the Folder and Files appear in the same column and the file name appears idented.
3) As someone who benefits from such tutorials, it would help a lot to see the source code. There’s definitely a ton that I could learn on your Add-in developed. Thanks PNRao!

i wan learnt vba macro please send the your contact no my contact no ——–97

I have a query. I want to fetch data, say from cell A1 to the cell that has the last entry which i’m not sure of because it may increase or decrease according to the data entry of the user. What loop and code do I write for that?

Dear AnalysisTabs Team,

Thank you so much for this website.
Really an amazing and interesting place to be for newbies like me.

Keep up with the wonderful job.

Cheers

Shameem

I know base sas, but to do predective modelling we require to gave statistics knowledge, please can you suggest me some links where i can learn regression,t test,time series etc

I have an excel file contains 3 sheets two of them hidden.
I wrote a VBA code to toggle between sheets by hyperlink.
I wish to have a code to hide automatically the visible sheet when i click the hyperlink to the hidden sheet.
I mean at any point of time only one sheet should be visible.
How to acheive this by a VBA code.
pl help.

What would be the output
Private Sub UserForm_Initialize()
Dim xlrange As Excel.Range
Dim sectionName As String
Dim rowCount As Long
Dim currentRow As Long

Me.Caption = gsPROGRAMNAME

‘If gwkbNewFile Is Nothing Then Set gwkbNewFile = Workbooks(“Coca-ColaAmatilLimited_PapuaNewGuinea_S.xlsm”)

With gwkbNewFile.Worksheets(gsQRAListing)
rowCount = WorksheetFunction.CountA(.Range(“A:A”))

ReDim values(rowCount – 1, 1)

‘I’ve limited the number of rows that are checked for data to 3 times the number of
‘filled rows, this should easily cover any spaces in the row data
For Each xlrange In .Range(“A1:A” & (rowCount * 3))
If xlrange ” Then
values(currentRow, 0) = .Cells(currentRow + 1, 1)

sectionName = .Cells(xlrange.Row, xlrange.Column + 1)
If .Cells(xlrange.Row, xlrange.Column + 2) ” Then _
sectionName = sectionName & ” > ” & .Cells(xlrange.Row, xlrange.Column + 2)
sectionName = sectionName & ” > ” & .Cells(xlrange.Row, xlrange.Column + 3)

values(currentRow, 1) = sectionName
Else
Exit For
End If

currentRow = currentRow + 1
Next xlrange
End With

‘Get the Before/After Columns for the review.
With gwkbNewFile.Worksheets(gsQRATable)
Set xlrange = getLastCell(aColumn, gwkbNewFile.Worksheets(gsQRATable))

lastBefore = xlrange.Column
lastAfter = lastBefore + 1
End With

cboSection.List = values
End Sub

You are almost done!
Paste the same code in all three sheet modules:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
linkto = Target.SubAddress
wherebang = InStr(1, linkto, "!")
If wherebang > 0 Then
mysheet = Left(linkto, wherebang - 1)
Worksheets(mysheet).Visible = True
For Each sht In ThisWorkbook.Sheets
If sht.Name <> mysheet Then sht.Visible = False 'hide except the required sheet
Next
End If
End Sub


Thanks-PNRao!

Thanks a lot Mr. Rao. The code is perfect. Thank you for the support.

Hi Muralee,

Thanks for your interest to learn VBA with us. Yes, we are re-working on our training program to help every one to make sure both Beginners & Advanced users / Programmers & non-Programmers are benefiting from our online class modules. We are going to launch the Demo class room soon, you can visit our online class room before enrolling for our complete program. Then you can decide to join if you find the demo class is useful and understandable.

Thanks-PNRao!

Hi Kishore,

You can use ActiveSheet.MailEnvelope approach to easily send a range from a specific worksheet:


Sub Send_Excel_Range()

'Set required options and send
With ActiveSheet.MailEnvelope
.Item.To = EmailToSend
.Item.Subject = "Required Subject"
.Item.Send
End With
End Sub


Hope this helps!
Thanks-PNRao!

Thnks for this!
Sir,
I have workbook that have 2sheet i give ex.
“Sheet1 is my email body & sheet2 is my mail id “coloumA1, for users name,B1:- users email id and D1:-subject then cc:,bcc,To is my B1.because every day i short listed few users so i can use only few uaers id and send mail with worksheet , i cant piassibal every time make changes in vb.

Hope this helps…

Sub Send_Excel_Range()

'Set required options and send
With ActiveSheet.MailEnvelope
.Item.To = EmailToSend
.Item.cc = EmailCCSend
.Item.bcc = EmailBCCSend
.Item.Subject = EmailSubject
.Item.Send
End With
End Sub


thanks sir,

same coding i can use with outlook supperot …….is it right .

Can the tab on the excel quick access bar be changed from “ANALYSISTABS to “Analysistabs” (i.e from Upper to Proper

Dear Mr. Rao,
Pl refer to my query dated 30th June 2014 and your reply dated 1st July 2014. The code you have suggested is working fine if we didn’t change the sheet name. If we change the sheet name I am getting Run time error 9 Type mismatch!
How to solve? Please help.
Regards
Muralee

Dear Mr. Rao,

Thank you for your support. Pl find the link below.
https://dl.dropboxusercontent.com/u/48823189/Project.xlsm
Please help me to sort out the issue.

Regards
Muralee

Dear Mr. Rao,
Excellent. Thank You very much for the code.
Regards
Muralee

I have a two different worksheet with the name of GXA319 or another is GXA3120 with same content.
With same column name like first worksheet name GXA319
Block Byte Bits Value BroadcastSheetCoordinates
AA 77 88 XKS Chandra shekhar
In another work sheet the same thing is there name GXA3120
Block Byte Bits Value BroadcastSheetCoordinates
AA 77 88 XKS Chandra shekhar
Fist of call by selected by some browse button and there is an option to choose the particular sheet from both XMLS. The compared output is copied to new sheet which highlight the changes in some color I need a macro code for this.. please help me in this matter

I have 50 users i want saving workbook auto because every time i told to my team is not possible so pls provide me 50 users auto saving workbook vba coding (every 10min auto save)

This blog is really awesome!!!

I have 30 name in a1 cell and also 50 users excel sheet on the path i want those only 30 workbook compile in the one sheet…

How do do you add data to the Create New Dasboard panel.

Hi Ken,

It is working fine, please press F5 and try downloading again.

Thanks-PNRao!

PNRao,
All set. I viewed your HTML source, found the link and downloaded from there.
Best,
Ken

Compile error in hidden module: modUpdata
Unable to run ANALYSTABS cannot rub the macro”asbPnraoCreateDashboard”.

I have a problem with Worksheet Change event in Excel. The following is a simple worksheet containing the bare details

col D Col F col G col H col I ….
Composition of X Y Z
Materials %
Material X 100 0 0 0 0 0 0
Y 0 100 0 0 0 0 0
Z 0 0 100 0 0 0 0
XY 30 70 0 0 0 0 0
YZ 0 50 50 0 0 0 0
XZ 40 0 60 0 0 0 0
Like this, it goes more than 3000 rows. I am selecting Material composition (col D) through a drop down list. I am able to put values for materials X,Y and Z if they are single and 100%. But I face problems when it comes to combined materials namely XY,YZ,XY etc. I can give any one value for these and the other share must be calculated. Can you suggest a procedure for this?
Values in col D are subject to change every now and then, accordingly the values in other columns must change. Await your suggestion please.

would like to get help from you for customizing excel charts based on our company corporate brand. whats the best way to do it is it styles or having different addin for branding

Hello,

Thank you for your Free Excel add in. Unfortunately, keep getting error messages. I have 2010.
http://screencast.com/t/aTIoVQDNv
http://screencast.com/t/SmfrkFrR
http://screencast.com/t/KlkXWRBqP
http://screencast.com/t/F0eBwmrrONt

Any ideas / help? Thank you.

I am an 81 year old non-programmer who taught himself the basics of excel and loves to use it … in the past year, I took an interest in Sudoku puzzles and have attempted to develop my own “model” to assist in solving them … I take much pleasure in doing it, and am continually enhancing the workbook … however, when I look at Sudoku forums on line, almost all have “solvers” that are made by programmers and their models just process quickly and insert answers into the puzzle … mine is VERY GRAPHIC AND COLORFUL and requires the user [me] to look at “hints” and then enter data…

Anyhow, in one of my “versions” I wanted to create a MACRO to be able to change FILL color and FONT color, type and size … that was way above my excel capabilities and I located someone who took my INCORRECT macro and emailed me a VBA [something?] and told me to insert it in my workbook’s macro … it worked GREAT!

Then, recently, I revised the basic structure by adding columns with other formula to develop hints … well, since “my” macro was based on moving across columns and searching for blanks, the ADDITIONAL columns have messed it up …

I looked at the VBA code in “my” macro, but I don’t understand VBA [never learned it and maybe I’m too old to do it now . ] … but I do see parts of the VBA that relate to columns [ie…. “cOffset = 6 To 16”] so I just counted the position of the new columns and changed the code to read cOffset = 7 To 19, etc — I revised all references to columns and then tried the Macro … needless to say, it FAILED!

I realize this is probably too simple a problem for you and your forum readers but if you “might” be interested I would gladly email you a word doc I’ve made that contains screen prints of the OLD model and the NEW model [not the actual excel files, unless you might want them] and a copy of the MACRO that was first used …

If you read this, thank you for your patience with this old man …

Sincerely,

Manuel {Manny] Slawkin

Today at 10:13 AM
Dear Mr. [?] Rao …
I’m so happy that you ACTUALLY did respond to my inquiry …
I am attaching the word doc file I mentioned, and ALSO my most recent Excel version of my “model” [if that would be of interest/use to you] …
If you would like to see my process steps for solving SUDOKU, I could also send you my attempt at “self-instructions” …
Thank you, again, for your attention to my request …
Sincerely,
Manuel [Manny] Slawkin

Hi sir,
Can you help me for auto macro run on specific time when my workbooks are closed.
Becuase all events are found after sheet opning so can i use outlook vba for opning workbook because my outlook is any time open when i am on leave. So pls suggest as your thinking.

how to remove analysistabs from excel?

Hi Sir
Somebody programmed an Excel-tool for me with Makros.
Some list are assumed in dropdown fields, text-fields and multiple selection-fields.

Now the problem is: It runs on windows, but not on Macs. Too many users unfortunally work on Apple Macs.
I am not able to solve the problem. It ist just 1 mask. All the data are in one excel. Its all virgin.

Can I send the Excel-file to a supporter? Can somebody just change the codes on the makro of the 6-7 dropdowns and 15 multiple selection-fields. I will do a donation or pay an amount.
Thank you for an answer.
kind regards
Kai

Hi Manikanta,

Make sure you are referring the Activesheet of the Activeworkbooksheet object.
For example: You can open the VBA editor and place the below code in ThisWorkbook module of the PERSONAL.XLSB, it will print 1 at Range A1 of the active sheet any time you open any excel file:
Private Sub Workbook_Open()
ActiveWorkbook.ActiveSheet.Range(“A1”) = 1
End Sub

Please note: This code will print 1 in whenever you open any Excel file, please change it accordingly.

Thanks
PNRao!

Hi Ravi, We are working on the training program, and we will notify when the course is available.
Thanks
PNRao!

Can you point me in the direction I need to look for a way to use VBA to compare two tables, both in separate workbooks, for various conditions. I am starting by just wanting to do a simple vlookup to check if items in table 1 are also in table 2. Both tables are somewhat identical, same type of data one set pulled today the other pulled last week. I am trying to compute the differences and save to a 3rd table in a 3rd file. What I currently am trying to do is just get a simple hard coded VLookup to work with the table names I have, but I am missing something and it errors out.

The sub I currently have looks as follows; but File names listed in as the top two variables are assigned by a separate sub.

Public LatestMasterTrackerFileName As Variant
Public EarlierMasterTrackerFileName As Variant
Public LatestMasterTrackerWorkbook As Variant
Public EarlierMasterTrackerWorkbook As Variant
Public TempValue As Boolean

Public MyLatestMasterTrackerTable As ListObject
Public MyEarlierMasterTrackerTable As ListObject

Sub CompareEarlierToLatest()
Workbooks(LatestMasterTrackerWorkbook).Worksheets(“Indoor – All Regions”).Activate
ActiveSheet.ListObjects(“Table_exts019_ProposalTracker_vwFinancialSummaryIndoor2014”).DataBodyRange.Select
Set MyLatestMasterTrackerTable = ActiveSheet.ListObjects(“Table_exts019_ProposalTracker_vwFinancialSummaryIndoor2014”)

Workbooks(EarlierMasterTrackerWorkbook).Worksheets(“Indoor – All Regions”).Activate
ActiveSheet.ListObjects(“Table_exts019_ProposalTracker_vwFinancialSummaryIndoor2014”).DataBodyRange.Select
Set MyEarlierMasterTrackerTable = ActiveSheet.ListObjects(“Table_exts019_ProposalTracker_vwFinancialSummaryIndoor2014”)

TempValue = Application.WorksheetFunction.VLookup(MyEarlierMasterTrackerTable.DataBodyRange(1, 1).Value, LatestMasterTrackerWorkbook.Worksheets(16).Range(“Table_exts019_ProposalTracker_vwFinancialSummaryIndoor2014”), 2, False)
MsgBox TempValue

End Sub

Hi

I need a coding for particular unicode files move from source folder to destination folder.

Eg : Source folder contains 50 files and in those file few file only having unique code 429147 in column P. So those particular files should move from source folder to destination folder. Please help me.

Hi,

I have established ADo connection to SQL throughVBA successfully,but while trying to pass teh sql query as input from excel sheet i could see teh errors due to long query.Can you please help on this

Hi,

I want to learn Macros and came to your website while going through the web. I’m beginner and don’t have any experience about macros. Reading books on macros was not helping since it’s too much of theory and I was not getting good practice – hence no/minimal learning. I liked your website so can you please help me as to where should I start learning about macros on your website (If you can send the link, it will be very helpful).

Thanks!
Abhishek .

Dear Mr. Rao,

I wish to have a vba code or function in excel to find whole number from a range of whole numbers after a certain %, for a large spreadsheet.

For e.g. let the range of numbers are 26100 & 27000

Let X be a whole in the above range and X-X*18% = Y also must be a whole number and Y-Y*5% must be a whole number.

I know there must be more than one whole number satisfies the above condition.

How to find those X using a vba code or a function in excel

Thanks and Regards
Muralee

Dear Mr. Rao,

To clarify my query a little better it is like this.

Let X be a whole number between two numbers say 2500 and 2600, including both. Let Y = X plus or minus X*N% and Z = Z plus or Minus Z*M%. I wish to get a function or a VBA Code to find X which satisfy the above condition.

Thanks and Regards
Muralee

I am a intermediate level VBA programmer. However I am having an issue with transfering data from Excel to Access. I need to accomplish it with a button click. Any assistance would be appreciated.

To clarify the above…
It’s usedrange of one spreadsheet being captured as screenshot and to B inserted into another Active workbook after renaming the screenshot

How do I become a member of this site?

sir, we want to in excel , mm/dd/yyyy to dd/mm/yyyy, plz help

Dear Team,

does the “Project management Template ” are equally useful for PMs handling small projects in which cost of employees and project cst should not be mentioned, just the teams, tasks assigned and project tracking are to mentioned?

Dear Sir, sfolder was written thrice by mistake !! please read as below :-

When using fso.deletefolder sfolder ( sFolder is a variable containing the path and folder name ) the folder is deleted from the defined path.But the folder doesn’t go to recycle bin and is permanently deleted .Now is it possible to route the deleted folder to recycle bin using the same vba code in some other way…

Regards

Venkat

Venkat September 9, 2015 at 5:35 PM – Reply
Hi Mr.P.N Rao !
Dear Sir, My query is while using for each ws in activeworkbook.worksheets
‘for adding hyperlinks in sheet 1 for accessing each of these sheets the subaddress syntax goes like below could u explain the first part ” ‘ ” and the last part ” ‘ ! a1″

SubAddress:=” ‘ ” & Worksheets(n).Name & ” ‘ ! a1″

Regards
Venkat

Hi I have one excel worksheet which contains 25 tables for 25 different clients. One table contains day wise data for transactions over period of 3-4 years for one client. For e.g. Table 1 is From Column A to Column N for Client no.1 & rows contain day wise transactions for 3-4 years, Table 2 from column P to Column AC is for Client no. 2 so on and so forth. Each table has 14 Columns and I want to copy Three important column from each table into another worksheet and while copying I don’t want few cells which are blank (These cells are no transactions days for a client. Each client’s no transaction day may be different).
In nutshell I want to copy client wise three columns from one worksheet in to separate sheet with ignoring blank cells. I have gone through Macro & array formulae & filter which will copy non blank cell data into another worksheet. Array formulae is making file very slow. But how do I do it for all 25 tables ? Can any one help me for macro in relation to this?

hi sir,

how can i get automatic date & time in excel?

Scenario: in my team we are answering customers queries through facebook, email, sms. how can we calculate the wait time here automatically?

pls, need your help.

Hi – I need your help on the below. I want to copy certain columns from Master Sheet – let’s say A to G and S to Summary Sheet, both of the sheets have the same headings. But the thing is workbook is protected with a password “1234”. I tired with one of the codes i found in your website by adding the following code in the existing summary sheet macro. But it is throwing an error ” Compile error in hidden module: wsSummary.”

Sub Move_Data_From()
Sheets(“Master – All Fees”).Columns(“A:G”).Copy Destination:=Sheets(“Foreign Summary”).Range(“C:I”)

Could you please advise what should be included in the above code for the macro to work. Appreciate your help in this regard.

Thanks!

Hi Iam finding this site very useful,Thanks a lot for providing all this information.

I have a doubt,I would very much thankful if u can help me on this.

I am writing a code , where in the first sheet i will get 5 input values from user and in 2nd sheet i have 20 values(5 will be populated from sheet1,remaining 15 are constant values.. when i execute the code i want the data in sheet2 to be populated into a fixed length flatfile.

I found a sheet over internet where i can select a row and and populate the file, now i want to modify in such a way that it should automatically select and generate button should be in first sheet.can u please help.

Regards,
Vinayak

Respected Sir, I have to convert some numeric values generated by excel calculation (say, these are in Column A) into words (say, these should be generated in Column B). I am using the following macro and using =words(A1), I am somehow managing the matter. But I am facing a problem. I need the numeric value should be generated in words with 2 decimal places. When in Column A, say in A1, a numeric value becomes 25.4789, it becomes in Column B1 ‘ Twenty Five Point Four Seven Eight Nine’. But I need ‘ Twenty Five Point Four Seven’, even I don’t want ‘ Twenty Five Point Four Eight’. Another Problem, when in Column A1, a value becomes 0, in Column B1 remains blank without generating Zero. Please edit the macro or provide me proper macro. ——————————————————————————————————————————————————————————————Function words(fig, Optional point = “Point”) As String
Dim digit(14) As Integer
alpha = Array(“, “One”, “Two”, “Three”, “Four”, “Five”, “Six”, “Seven”, “Eight”, “Nine”, “Ten”, “Eleven”, “Twelve”, “Thirteen”, “Fourteen”, “Fifteen”, “Sixteen”, “Seventeen”, “Eighteen”, “Nineteen”, “Twenty”, “Thirty”, “Forty”, “Fifty”, “Sixty”, “Seventy”, “Eighty”, “Ninety”)
figi = Trim(StrReverse(Str(Int(Abs(fig)))))
For i = 1 To Len(figi)
digit(i) = Mid(figi, i, 1)
Next
For i = 2 To Len(figi) Step 3
If digit(i) = 1 Then
digit(i) = digit(i – 1) + 10: digit(i – 1) = 0
Else: If digit(i) > 1 Then digit(i) = digit(i) + 18
End If
Next
For i = 1 To Len(figi)
If (i Mod 3) = 0 And digit(i) > 0 Then words = “hundred ” & words
If (i Mod 3) = 1 And digit(i) + digit(i + 1) + digit(i + 2) > 0 Then _
words = Choose(i / 3, “thousand “, “million “, “billion “) & words
words = Trim(alpha(digit(i)) & ” ” & words)
Next
If fig Int(fig) Then
figc = StrReverse(figi)
If figc = 0 Then figc = ”
figd = Trim(WorksheetFunction.Substitute(Str(Abs(fig)), figc & “.”, “))
words = Trim(words & ” ” & point)
For i = 1 To Len(figd)
If Val(Mid(figd, i, 1)) > 0 Then
words = words & ” ” & alpha(Mid(figd, i, 1))
Else: words = words & ” Zero”
End If
Next
End If
If fig < 0 Then words = "Negative " & words
End Function

Greetings from Sri Lanka

Dear Rao,

First of all wish to thank you for sharing your wealth of knowledge with the rest of the world and I pray the Gods blessing will be with you always.

I just need to ask your advice for me to open and close xlsm files Wk1, Wk2, Wk3, Wk4 & Wk5 defining file the file name variable and open one at a time. When Wk1 is opened and while moving to Wk2 I would like to know the VBA coding on a command control to save and close WK1 and open Wk2 and etc.

Along with that will I be able to navigate from Userform_X of Wk1 to Userform_Y of Wk2?

Appreciate your kind advice and sharing the VBA Codes. And Sorry for troubling you on this matter.

God Bless you and your family,

Gamini

Dear sir , i am Leaning ADO in excel from your templates only , i write a sql in ADO query , but i did not get my results
please give solution for that

Sub CheckVendor()

Dim CnTDS As New ADODB.Connection
Dim StCNTDS As String
Dim DBPath As String
Dim StRsVendor As String
Dim RsVendor As New ADODB.Recordset

DBPath = ThisWorkbook.FullName
StCNTDS = “Provider=MSDASQL.1;DSN=Excel Files;DBQ=” & DBPath & “;HDR=Yes’;”
CnTDS.Open StCNTDS

StRsVendor = “Select [CREDITORS$].[Account Code],[CREDITORS$].[Name]” & _
“From [CREDITORS$],[MASTER$]” & _
“Where [CREDITORS$].[Account Code] [MASTER$].[Account Code]” & _
“Group By [CREDITORS$].[Account Code],[CREDITORS$].[Name];”

RsVendor.Open StRsVendor, CnTDS

Sheets(“NotFound”).Range(“A2”).CopyFromRecordset RsVendor

‘RsVendor.RecordCount = Nothing

End Sub

Hello team,
I am trying to create a macro to format all headings in a ms word document .
code should automatically find the headings from the content page and format accordingly.
Moreover if the document contains any tables then it should convert it into a image.
Can you please help me by giving some ideas.

give vb code for find particular row contain name,father name , surname from three column in excel 13

Hi, I downloaded the project mgmt package on my laptop using excel 2010.
I worked with it and like it. I tried to bring it to my job that uses excel 2007
and I found the dashboard graphics are blurry and misaligned. Is this a function of the
different versions or because I copied it from my laptop to my work desktop . Do I need to buy
another version or pay for an additional copy for work. I need to do this quickly as I need this to
organize my team.
Thanks

I have an Excel file with first 7 characters of file names in Column C. There are about 1,000 rows. The associated files are stored under multiple sub folders under a main folder. These folders are presently in a flash drive. What would be the best way to open the files using the cell pointer (pointing to different cells in column C) and a macro button to run the code? I am looking for a VBA code to do this. Thanks for helping.

Hi
Thanks for this wonderful site. I have an issue with a macro solution

I have many different excel files in a folder(mytest) on my desktop (link2015.xlsx, vlookupTest.xlsm). My intention to do a macro vlookup from one of the file to the other file.I need to use a wildcard to get one of the files (link2015.xlsx) because it will be changing 2ce in a month.

By clicking the button, it gave an error message: link2015.xlsx could not be found. It surprising because excel got the name right. But couldnt use it. I also tried many things like closing the dir() function and other stuff, but it didnt work. This is just the best output I got.

Normally by using the full file name directly, it works perfectly well, but using the wildcard operation never worked

PLS COULD YOU HELP ME OUT WITH THE SOLUTION. I also attach the screenshot of the error

Private Sub CommandButton1_Click()
Dim fruits As String
Dim i As Integer
Dim wbk As Workbook
Dim directory As String
Dim fileName As String

directory = “C:UsersjomosaDesktopmytest”
fileName = Dir(directory & “link*.xlsx”)

Set wbk = Workbooks.Open(fileName)

For i = 2 To 5
fruits = Cells(i, 4).Value
Sal = Application.WorksheetFunction.VLookup(fruits, wbk.Worksheets(“Sheet1”).Range(“A2:B10”), 2, False)
Cells(i, 5) = Sal

Next i

End Sub

Hi AnalysisTabs

Good day,

Thanks for the useful compilation of Msg box codes.

However, there are some typo/error in the code for VBA MsgBox: vbSystemModal
Site:http://analysistabs.com/vba/msgbox-examples-macros/

Regards
Wei Jen