We would like to hear from you, whether it’s feedback about ANALYSISTABS Add-in / Dashboard Framework or a request for any topic, please let us hear from you. We’ll answer your questions the best that we can and give you the information you need. If you have any questions or any suggestion regarding our tutorials, please provide your valuable suggestions and feedback through email. If you are interested in knowing more about us please drop a mail and we are always happy to help you in solving your questions. You can email us directly or leave your comment in this page, depending on your needs:
Email: info@analysistabs.com
Website: ANALYSISTABS.COM
As an Excel Beginner i find easier to learn and practice. Thanks – Ganesh K
Thank you Ganesh!
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.
Thank you Ganesh!
Regards-PNRao!
Hi ,
I am a beginner for VBA, I would like to know how to compare 2 cell Data type values in the XLS .
My requirement is I have to compare each data type value which is belongs to other column( cell ) in the xls .
Example :- Let say Column “A” got Cybase Data type text value is ( PIC X(02)) and another column “C” for Oracle Database type text value is ( VARCHAR2(2) ) and I should be populated my result in 3rd column i.e “D” as expected “Matching Database value ” if not it should show as ” Not matching with Data type” . How should we do this ? could you please suggest or give mne the solution for this .
Thanks in Advance,
Regards,
Prasad M
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:\Users\jomosa\Desktop\mytest\”
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
I want to learn vba,excel sql
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 PNRao,
I’m happy found your website today. I wonder if you could give real example/study case of excel/vba macro . Started with some data, and then manipulated in excel report using excel formula and/or vba macro codes. I think it will be easy for some people to see real life sample in action from beginning until finish.
Thank you,Stella
Hello Stella, Thanks for your comments and suggestion.
I am working on real life projects and will be posted in few days.
Thanks-PNRao!
Hi I am from South Africa. Thanks for the excellent tutorials…. I have been using EXCEL since it first started and still have problems with VBA… This site gives us a better understanding than the books by —…. Keep up the excellent work… and I am not afraid to say that we are going to squeeze out every bit of your knowledge….. To all the visitors of this site, MARK this site and learn… Knowledge is power and every day we learn… no matter how old we get. Thank you from South Africa- Sew!
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!
hi pnrao i nedd to copy data one by one from sheet 1 to sheet 2 using loop can u please explain me
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 I am very much impressed about your website. And I want to learn VBA for beginner, But when I click on the link which provided it is directing to other things Ex: Learn to record HOW TO link
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/
I just want to know how could i connect excel with access. I mean i just want to save the data whatever i entered in excel into access table and manipulate data from excel in access i.e. retrieving data from access, updating data in access etc.
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!
Hello Sir, Thanks for this wonderful blog, I am excel user since last two years and now want to learn VBA. I found your blog from Google search and want to say I got a gem. Thanks, Manish
Am getting hooked to Excel, VBA even though I don’t need to do.
Hi Manish and Mohan,
Thanks for your comments. Enjoy learning VBA, you will find lot more free time for enjoying the life.
Best regards-PNRao!
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?
Hi Chandra,
Thanks for your comments.
When you merge Range A1:D8, Range A1 contains all this merged data. So, you can copy the Range A1 values into any other range or sheets.
Example Shee2.Range(“F5”).value=Range(“A1”).value.
If you want to, all cells of the merged range to be copied, you can use Range(“A1:D8”).Copy Destination:=Sheet2.Range(“F5”). This will paste from Range F5.
Hope this helps.
Thanks-PNRao.
This is of real help for a beginner like me. Hats Off to you Mr. PN Rao
Thanks for your comments Sebastian!
Thanks-PNRao!
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!
Hi Chrisham,
Thanks for your feedback. I will definitely consider these features in the next release of Analysistab excel add-in.
Thanks
PNRao!
i wan learnt vba macro please send the your contact no my contact no ——–97
Hi Ravinder,
Thanks for your interest to learn VBA with us. Currently we are not providing any training, online training will be available in couple of months.
We can inform you when we launch our taring programs.
Thanks-PNRao!
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?
Hi Revathy, Welcome to ANALYSISTBS!
You can find the last valid row in sheet or column as per your requirement.
Let us say your last row with data is 200
Then your code will be:
lastRow=200 ‘ Change this: This is the last row with data in your destination sheet
Sheets(“Sheet1”).Range(“A” &lastRow+1)=Sheets(“Sheet2”).Range(“A1”)
Here, Sheet2 is your source and Sheet1 is your destination sheet
Please refer the below procedures to find last row:
http://analysistabs.com/excel-vba/find-used-range-last-column-row/
http://analysistabs.com/excel-vba/finding-last-used-row-with-data-worksheet/
Hope this helps-Thanks-PNRao!
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
Hi Ismael,
Thanks for your feedback, we are happy that you found our site useful.
Thanks-PNRao!
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
Hi Rakesh, You can refer these tutorials:
http://www.ats.ucla.edu/stat/
You can also search in official SAS website for examples SAS Procs.
http://www.sas.com/en_us/home.html
Thanks-PNRao!
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.
Hi Muraleedharan,
Using hyperlinks to set the visibility of sheets is not recommended, it will go to infinite loop some times.
Instead- you can insert three shapes and call this macro for each button:
You can use the follow the below procedure to make only one particular sheet:
Open VBA Editor and Insert new module and add the below code.
And change your sheet names accrdingly, in place of “Name of Your 1st Sheet”, “Name of Your 2nd Sheet”, “Name of Your 3rd Sheet”
Sub sbShowSheet1()
Call sbShowOnlyOneSheet("Name of Your 1st Sheet")
End Sub
Sub sbShowSheet2()
Call sbShowOnlyOneSheet("Name of Your 2nd Sheet")
End Sub
Sub sbShowSheet3()
Call sbShowOnlyOneSheet("Name of Your 3rd Sheet")
End Sub
Sub sbShowOnlyOneSheet(ByVal sheetName As String)
Dim sht
For Each sht In ThisWorkbook.Sheets
If sht.Name = sheetName Then
sht.Visible = True
Else
sht.Visible = False
End If
Next
End Sub
Now insert 3 shapes on your worksheet from Insert Menu:
Right click on shape 1 and Assign Macro, Select ‘sbShowSheet1’
Right click on shape 2 and Assign Macro, Select ‘sbShowSheet2’
Right click on shape 3 and Assign Macro, Select ‘sbShowSheet3’
Hope this helps! Thanks-PNRao!
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
Hi Chandra,
ANALYSISTABS comments are prefixed with AT:
'AT: This code is calling while initializing the form: when run the for this code will run
'AT: This code is to read the data from sheet gsQRAListing (sheet name declared in your code) and find the list of values to be populated in the cboSection (cobmo box in the form)
'
Private Sub UserForm_Initialize()
'AT: Declaring the variables to store the values temporarily
Dim xlrange As Excel.Range
Dim sectionName As String
Dim rowCount As Long
Dim currentRow As Long
'AT: This will change the form caption/title as the value assined to gsPROGRAMNAME
Me.Caption = gsPROGRAMNAME
'If gwkbNewFile Is Nothing Then Set gwkbNewFile = Workbooks("Coca-ColaAmatilLimited_PapuaNewGuinea_S.xlsm")
With gwkbNewFile.Worksheets(gsQRAListing)
'AT:Rows with Data in Column A
rowCount = WorksheetFunction.CountA(.Range("A:A"))
'AT: Resize the Arry eqals to RowCount found in the above line
ReDim Values(rowCount - 1, 1)
'Looping each row in and getting the values from Column 3 based on criteria*
'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) 'AT:criteria
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 'AT:This is the arry it is creating to fill in the Cobo Box
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 ' AT: Finally filling the combo box with the arry 'Values'
End Sub
Thank you for your reply. Yes the code you have given is helpful.
I am sharing the link of my file in dropbox.
https://dl.dropboxusercontent.com/u/48823189/Project%20.xlsm
This is a sample project. I want to hide sheet1 automatically when i am in sheet 2. i.e. only one sheet should be visible all time. Please suggest a VB Code.
Thanks and Regards
Muralee
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.
Mr. Rao I wish to join for Excel VBA online Training classes. I am a beginner in VBA don’t have any programming background. Can I follow your classes and get benefit out of it.
Regards
Muralee
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!
I have one worksheet with 50 email address I want to send 50mails with worksheet data in email body not attachment.
Hi Kishore,
You can use ActiveSheet.MailEnvelope approach to easily send a range from a specific worksheet:
Sub Send_Excel_Range()
'Select a Range
ActiveSheet.Range("A1:D8").Select
'Start an Envelope
ActiveWorkbook.EnvelopeVisible = True
EmailToSend="emiladdress1.com;emiladdress1.com;...All your 50 addresses separated by semicolon"
'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()
'Select a Range
Sheets("Sheet1").Activate
Sheets("Sheet1").Range("A1:D8").Select
'Start an Envelope
ActiveWorkbook.EnvelopeVisible = True
'To:
EmailToSend = Sheets("Sheet2").Range("B1")
'if your email ids are in B1,B2,B3... like this, you have to use a for loop to form the above varible
For iCntr = 2 To 20 ' last row with email id
If Trim(Sheets("Sheet2").Range("B" & iCntr)) <> "" Then EmailToSend = EmailToSend & ";" & Sheets("Sheet2").Range("B" & iCntr)
Next
'CC:
EmailCCSend = "Your CC ids" ' or you can do it same like To, shown above
'BCC:
EmailBCCSend = "Your BCC ids" ' or you can do it same like To, shown above
'Subject:
EmailSubject = Sheets("Sheet2").Range("D1")
'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
Thnks so much sir and that is very much important.
Ex.
But sir i have one sheet like “team performance” ok sheet1 is my performance detail and ‘sheet2 is users email id coloum my subject coloum
In coloum “d”,cc colum,signature’ pls help on this
Today i reg. My email.id pls provide me procedur how to join your class.
Hi Kishor,
Thanks for showing your interest to learn VBA @ANALYSISTABS. We are re-working on the program and you can able to join on 1st August 2014. We will send an email notification with the discount coupon and you can use it while joining our classes.
Thanks-PNRao!
thanks sir,
same coding i can use with outlook supperot …….is it right .
Yes, it works well if your email application is Outlook!
Thanks-PNRao!
Can the tab on the excel quick access bar be changed from “ANALYSISTABS to “Analysistabs” (i.e from Upper to Proper
Hi Roy,
Sure! Thanks for the Suggestion. I w’ll implement this while releasing the next version (August 2014).
Thanks-PNRao!
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
Hi Muralee,
I remembered that we have placed the below code in every sheet module:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$1" Then
Call sbShowOnlyOneSheet("Sheet1")
ElseIf Target.Address = "$A$2" Then
Call sbShowOnlyOneSheet("Sheet3")
ElseIf Target.Address = "$A$3" Then
Call sbShowOnlyOneSheet("Sheet3")
End If
End Sub
And you need to change the Sheet1, Sheet2, Sheet3; in this code. You can provide me the file, so that I can edit the macro and send it back to you.
Please note: If your file is holds the real-time data, please delete it before sharing it online and provide the file, even empty file works.
Thanks
PNRao!
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
Hi Muralee,
Here is the updated file.
Project
I have done the small change to the existing code:
From: mysheet = Left(linkto, wherebang – 1) ‘————> This code will create issue when you have sheet name with more than word – your hyperlink bind the sheet name within single quotes “INCOME STATEMENT” will become ” ‘INCOME STATEMENT’ ” in your hyperlink address.
To: mysheet = Replace(Left(linkto, wherebang – 1), “‘”, “”) ‘Removed the single quotes, so that it will be same as your worksheet name.
And the other change in the individual sheets:
It should be Worksheets(“INDEX”).Select , instead of Worksheets(“sheet1”).Select.
Here is the updated file:
Project.xlsm
Hope this helps! Thanks-PNRao!
Dear Mr. Rao,
Excellent. Thank You very much for the code.
Regards
Muralee
You are welcome!
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
Hi Chandra,
Please send me the example files and detailed requirement to info@analysistabs.com.
Thanks-PNRao!