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?

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 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?

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

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

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

thanks sir,

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

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,
Excellent. Thank You very much for the code.
Regards
Muralee

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)

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

how to remove analysistabs from excel?

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?

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?

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 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