Re: $$Excel-Macros$$ email extraction from gmail
give email id A1 box in sheet and run macro --- Sub Mail_Every_Worksheet() 'Working in 2000-2010 Dim sh As Worksheet Dim wb As Workbook Dim FileExtStr As String Dim FileFormatNum As Long Dim TempFilePath As String Dim TempFileName As String Dim OutApp As Object Dim OutMail As Object TempFilePath = Environ$(temp) \ If Val(Application.Version) 12 Then 'You use Excel 97-2003 FileExtStr = .xls: FileFormatNum = -4143 Else 'You use Excel 2007-2010 FileExtStr = .xlsx: FileFormatNum = 51 End If With Application .ScreenUpdating = False .EnableEvents = False End With Set OutApp = CreateObject(Outlook.Application) For Each sh In ThisWorkbook.Worksheets If sh.Range(A1).Value Like ?*@?*.?* Then sh.Copy Set wb = ActiveWorkbook TempFileName =sh.Name of _ ThisWorkbook.Name Set OutMail = OutApp.CreateItem(0) With wb .SaveAs TempFilePath TempFileName FileExtStr, _ FileFormat:=FileFormatNum On Error Resume Next With OutMail .To = sh.Range(A1).Value .CC = .BCC = .Subject = DAILY DASHBOARD .Body = DAILY DASHBOARD .Attachments.Add wb.FullName 'You can add other files also like this '.Attachments.Add (C:\test.txt) .Send 'or use .Display End With On Error GoTo 0 .Close SaveChanges:=False End With Set OutMail = Nothing Kill TempFilePath TempFileName FileExtStr End If Next sh Set OutApp = Nothing With Application .ScreenUpdating = True .EnableEvents = True End With End Sub = On Thu, Mar 22, 2012 at 8:58 AM, NOORAIN ANSARI noorain.ans...@gmail.comwrote: Dear Raj, Please try through below link. http://www.gmailextractor.com/ -- Thanks regards, Noorain Ansari *http://noorainansari.com/* *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ On Wed, Mar 21, 2012 at 11:56 PM, Raj Mahapatra rajafs...@gmail.comwrote: hi group, can you please advice me how to extract email id's from gmail or outlook using VBA Thanks Regards -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion
Re: $$Excel-Macros$$ Split text in cell
Hi,
PFA.
=MID(A2,FIND( ,A2,1)+1,LEN(A2)-MATCH(
,LEFT(RIGHT(A2,{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15}),1),0)-FIND(
,A2,1))
Regards,
MARIES.
On 3/22/12, L.K. Modi ca.mod...@gmail.com wrote:
Dear All,
Its good to see this resolution but can anyone explain this in simple
way
Regards
Modi
On Thu, Mar 22, 2012 at 4:51 AM, dguillett1 dguille...@gmail.com wrote:
Option Explicit
Sub getmiddleSAS()
Dim c As Range
Dim fs As Long
Dim ls As Long
For Each c In Range(a2:a10)
c = Application.Trim(c)
fs = InStr(c, )
ls = InStrRev(c, )
c.Offset(, 1) = Mid(c, fs, ls - fs)
Next c
Columns(b).AutoFit
End Sub
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguille...@gmail.com
*From:* NRao Mynampati ml.narasimha...@gmail.com
*Sent:* Wednesday, March 21, 2012 4:00 AM
*To:* excel-macros@googlegroups.com
*Cc:* Dilip Pandey dilipan...@gmail.com
*Subject:* $$Excel-Macros$$ Split text in cell
Hi Team,
How to remove a words left text and right text of reference
Please refer attachment.
Thanks in advance.
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
will not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
--
To post to this group, send email to excel-macros@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice
will not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
--
To post to this group, send email to excel-macros@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
--
To post to this group, send email to excel-macros@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
--
To post to this group, send email to excel-macros@googlegroups.com
Split a text.xls
Description: MS-Excel spreadsheet
$$Excel-Macros$$ Re: Need help with VBAfor Excel naming and using column names for looping
Asa, I tried what you said but am doing something wrong. First when I used the exact same string as both the Set variable and the Match variable it said it needed an object. It did not like identical names I guess? Then I created different object names and that would compile but I have tried several variations to get a correct simple value returned (what I really want to do will be more complex but I have to crack this fundamental first) and all get errors of different types. It may be that what I really need to do is remove the .Columns from your expression and then the variable would become an integer and I could use it as an index? But even if that is true and what I decide to do I would still like to understand what is wrong here. I have a set of columns and am just trying to get the color value of one of them out of the range of the column BWTStop which should be the object cBWTStop I think? ActiveCell in these attempts was the cell just to the right of one of the colored cells. The current uncommented version keeps returning 0 but the actual color value of the cell I want is 255. The commented out versions get runtime errors or compile errors. Sub Macro1() ' ' Macro1 Macro which sets the column names for the ChartData sheet ' ' Column names Dim BarDate As String Dim BarTime As String Dim Baropen As String Dim Barhigh As String Dim Barlow As String Dim Barclose As String Dim BWTStop As String Dim Short_Long_Cash As String ' Column reference object Dim cBarDate As Object Dim cBarTime As Object Dim cBaropen As Object Dim cBarhigh As Object Dim cBarlow As Object Dim cBarclose As Object Dim cBWTStop As Object Dim cShort_Long_Cash As Object With Sheets(ChartData).UsedRange 'All the columns will only have the used range. Set cBarDate = .Columns(WorksheetFunction.Match(BarDate, .Rows(1), 0)) Set cBarTime = .Columns(WorksheetFunction.Match(BarTime, .Rows(1), 0)) Set cBaropen = .Columns(WorksheetFunction.Match(BarOpen, .Rows(1), 0)) Set cBarhigh = .Columns(WorksheetFunction.Match(BarHigh, .Rows(1), 0)) Set cBarlow = .Columns(WorksheetFunction.Match(BarLow, .Rows(1), 0)) Set cBarclose = .Columns(WorksheetFunction.Match(BarClose, .Rows(1), 0)) Set cBWTStop = .Columns(WorksheetFunction.Match(BWTStop, .Rows(1), 0)) Set cShort_Long_Cash = .Columns(WorksheetFunction.Match(Short_Long_Cash, .Rows(1), 0)) End With ' 'With Selection.Font Dim mycolor 'mycolor = cBWTStop(Cells(ActiveCell.Offset(ActiveCell.Row - 1), ActiveCell.col)).Font.Color 'mycolor = Cells(4, cBWTStop).Font.Color mycolor = cBWTStop.Cells(ActiveCell.Row, ActiveCell.Column).Font.Color Debug.Print mycolor -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
$$Excel-Macros$$ Asa R. I tried what you said for the column addressing but I can not make it work. Can you assist?
Asa, I tried what you said but am doing something wrong. First when I used the exact same string as both the Set variable and the Match variable it said it needed an object. It did not like identical names I guess? Then I created different object names and that would compile but I have tried several variations to get a correct simple value returned (what I really want to do will be more complex but I have to crack this fundamental first) and all get errors of different types. It may be that what I really need to do is remove the .Columns from your expression and then the variable would become an integer and I could use it as an index? But even if that is true and what I decide to do I would still like to understand what is wrong here. I have a set of columns and am just trying to get the color value of one of them out of the range of the column BWTStop which should be the object cBWTStop I think? ActiveCell in these attempts was the cell just to the right of one of the colored cells. The current uncommented version keeps returning 0 but the actual color value of the cell I want is 255. The commented out versions get runtime errors or compile errors. Sub Macro1() ' ' Macro1 Macro which sets the column names for the ChartData sheet ' ' Column names Dim BarDate As String Dim BarTime As String Dim Baropen As String Dim Barhigh As String Dim Barlow As String Dim Barclose As String Dim BWTStop As String Dim Short_Long_Cash As String ' Column reference object Dim cBarDate As Object Dim cBarTime As Object Dim cBaropen As Object Dim cBarhigh As Object Dim cBarlow As Object Dim cBarclose As Object Dim cBWTStop As Object Dim cShort_Long_Cash As Object With Sheets(ChartData).UsedRange 'All the columns will only have the used range. Set cBarDate = .Columns(WorksheetFunction.Match(BarDate, .Rows(1), 0)) Set cBarTime = .Columns(WorksheetFunction.Match(BarTime, .Rows(1), 0)) Set cBaropen = .Columns(WorksheetFunction.Match(BarOpen, .Rows(1), 0)) Set cBarhigh = .Columns(WorksheetFunction.Match(BarHigh, .Rows(1), 0)) Set cBarlow = .Columns(WorksheetFunction.Match(BarLow, .Rows(1), 0)) Set cBarclose = .Columns(WorksheetFunction.Match(BarClose, .Rows(1), 0)) Set cBWTStop = .Columns(WorksheetFunction.Match(BWTStop, .Rows(1), 0)) Set cShort_Long_Cash = .Columns(WorksheetFunction.Match(Short_Long_Cash, .Rows(1), 0)) End With ' 'With Selection.Font Dim mycolor 'mycolor = cBWTStop(Cells(ActiveCell.Offset(ActiveCell.Row - 1), ActiveCell.col)).Font.Color 'mycolor = Cells(4, cBWTStop).Font.Color mycolor = cBWTStop.Cells(ActiveCell.Row, ActiveCell.Column).Font.Color Debug.Print mycolor -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
RE: $$Excel-Macros$$ Re: Need help with VBAfor Excel naming and using column names for looping
Hi Howard, It's late here, but you have at least one problem. In the following line: mycolor = cBWTStop.Cells(ActiveCell.Row, ActiveCell.Column).Font.Color you reference the activecell.row and .column; but that gives you the row and column for the active cell relative to the sheet as a whole. cBWTStop.Cells, though, needs a row and column specified relative to cBWTStop. In other words, cBWTStop.Cells(2,1) refers to the first data item in that column. The row happens in this case to be the same, since CBWTStop begins in the first row of your worksheet, but since cBWTStop is only one column, you should specify 1 for the column. This should work: mycolor = cBWTStop.Cells(ActiveCell.Row, 1).Font.Color This would be a more general solution in case cBWTStop didn't start at row 1 on your worksheet: mycolor = cBWTStop.Cells(ActiveCell.Row - cBWTStop.Cells(1).Row + 1, 1).Font.Color Or using Application.Intersect you can simplify it like this: mycolor = Application.Intersect(ActiveCell.EntireRow, cBWTStop).Font.Color When I get a chance, I will still try to respond to your previous messages. Asa From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of tangledweb Sent: Thursday, March 22, 2012 12:20 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Re: Need help with VBAfor Excel naming and using column names for looping Asa, I tried what you said but am doing something wrong. First when I used the exact same string as both the Set variable and the Match variable it said it needed an object. It did not like identical names I guess? Then I created different object names and that would compile but I have tried several variations to get a correct simple value returned (what I really want to do will be more complex but I have to crack this fundamental first) and all get errors of different types. It may be that what I really need to do is remove the .Columns from your expression and then the variable would become an integer and I could use it as an index? But even if that is true and what I decide to do I would still like to understand what is wrong here. I have a set of columns and am just trying to get the color value of one of them out of the range of the column BWTStop which should be the object cBWTStop I think? ActiveCell in these attempts was the cell just to the right of one of the colored cells. The current uncommented version keeps returning 0 but the actual color value of the cell I want is 255. The commented out versions get runtime errors or compile errors. Sub Macro1() ' ' Macro1 Macro which sets the column names for the ChartData sheet ' ' Column names Dim BarDate As String Dim BarTime As String Dim Baropen As String Dim Barhigh As String Dim Barlow As String Dim Barclose As String Dim BWTStop As String Dim Short_Long_Cash As String ' Column reference object Dim cBarDate As Object Dim cBarTime As Object Dim cBaropen As Object Dim cBarhigh As Object Dim cBarlow As Object Dim cBarclose As Object Dim cBWTStop As Object Dim cShort_Long_Cash As Object With Sheets(ChartData).UsedRange 'All the columns will only have the used range. Set cBarDate = .Columns(WorksheetFunction.Match(BarDate, .Rows(1), 0)) Set cBarTime = .Columns(WorksheetFunction.Match(BarTime, .Rows(1), 0)) Set cBaropen = .Columns(WorksheetFunction.Match(BarOpen, .Rows(1), 0)) Set cBarhigh = .Columns(WorksheetFunction.Match(BarHigh, .Rows(1), 0)) Set cBarlow = .Columns(WorksheetFunction.Match(BarLow, .Rows(1), 0)) Set cBarclose = .Columns(WorksheetFunction.Match(BarClose, .Rows(1), 0)) Set cBWTStop = .Columns(WorksheetFunction.Match(BWTStop, .Rows(1), 0)) Set cShort_Long_Cash = .Columns(WorksheetFunction.Match(Short_Long_Cash, .Rows(1), 0)) End With ' 'With Selection.Font Dim mycolor 'mycolor = cBWTStop(Cells(ActiveCell.Offset(ActiveCell.Row - 1), ActiveCell.col)).Font.Color 'mycolor = Cells(4, cBWTStop).Font.Color mycolor = cBWTStop.Cells(ActiveCell.Row, ActiveCell.Column).Font.Color Debug.Print mycolor -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss.
Re: $$Excel-Macros$$ Need output
Dear Seraj, Please try it.. and see attached sheet. Sub count_name() Dim i, j, c, x As Integer Dim rng As Range c = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row Sheet1.Range(d2 : d (c + 1)).ClearContents i = 1 j = 2 x = 1 For j = 2 To c For i = 2 To c If Sheet1.Range(a i).Value = Sheet1.Range(a j).Value Then If Sheet1.Range(d i).Value vbNullString Then j = j + 1 End If If Sheet1.Range(d i).Value = vbNullString Then Sheet1.Range(d i).Value = x End If End If Next i x = x + 1 Next j End Sub -- Thanks regards, Noorain Ansari *http://noorainansari.com/* *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ On Thu, Mar 22, 2012 at 11:29 AM, Seraj Alam seraj.ala...@gmail.com wrote: Hi All, Please find attached sheet and i need output though VBA and excel both. Thanks in advance. Thanks seraj -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com Excel Test(Solved).xls Description: MS-Excel spreadsheet
$$Excel-Macros$$ Re: Macro for Insert a line for missing serial numbers.
Dear Damimkader, while I am running this code I am getting the follwing error code run time error 13 type mismatch please help. My problem has been reproduced My current Data is like thisSr. No. S_DISNO S_DISC S_UNIT S_SELP Purchse quantity 495061 358 SILK DUPPATTA 0.90m 260.00 36.00 495062 359 SILK DUPPATTA 1.15m 385.00 33.00 495063 351 SILK DOTHI 8 cu 3.6 m 2645.00 4.00 495067 351 SILK DOTHI 8 cu 3.6 m 2745.00 3.00 495070 351 SILK DOTHI 8 cu 3.6 m 2965.00 3.00 I want my data as follows Sr. No. S_DISNO S_DISC S_UNIT S_SELP Purchse quantity 495061 358 SILK DUPPATTA 0.90m 260.00 36.00 495062 359 SILK DUPPATTA 1.15m 385.00 33.00 495063 351 SILK DOTHI 8 cu 3.6 m 2645.00 4.00 495064 351 SILK DOTHI 8 cu 3.6 m 2645.00 4.00 495065 351 SILK DOTHI 8 cu 3.6 m 2645.00 4.00 495066 351 SILK DOTHI 8 cu 3.6 m 2645.00 4.00 495067 351 SILK DOTHI 8 cu 3.6 m 2745.00 3.00 495068 351 SILK DOTHI 8 cu 3.6 m 2745.00 3.00 495069 351 SILK DOTHI 8 cu 3.6 m 2745.00 3.00 495070 351 SILK DOTHI 8 cu 3.6 m 2965.00 3.00 Copy the previous line only sr no has chamged by adding 1 On 22 March 2012 02:26, Damimkader S. Meeran damim.ka...@gmail.com wrote: Hi Sandeep, My code below assumes the following 1. The serial numbers are in column A 2. The Excel version you are using is 2007. 3. The code is good for 10,000 rows. Please modify if required. Sub Serial() Dim i As Long For i = 1 To 1 If Cells(i, 1) And Cells(i + 1, 1) Then If Cells(i + 1, 1) - Cells(i, 1) 1 Then Rows(i + 1 : i + 1).Select Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove i = i + 1 End If Else Exit For End If Next i End Sub On Wednesday, March 21, 2012 9:40:37 PM UTC+5:30, sandeep chhajer wrote: Dear all, Please find my attachment ( I know asking question in this way is a violation of our forum rule but sorry I don't have data card to copy the queries from excel) Thanking you in advance. Sandeep Chhajer. On Wednesday, March 21, 2012 9:40:37 PM UTC+5:30, sandeep chhajer wrote: Dear all, Please find my attachment ( I know asking question in this way is a violation of our forum rule but sorry I don't have data card to copy the queries from excel) Thanking you in advance. Sandeep Chhajer. -- Regards, Sandeep Kumar Chhajer. -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ Re: Match text to a list
This works just as well as the other code but still has the problem, If range (L6:L27) has the letters AA in it then it shows A as a match and says report A is already done On Wed, Mar 21, 2012 at 4:34 PM, Kris krishnak...@gmail.com wrote: Hi Replace Flg = Evaluate(ISNUMBER(LOOKUP(9.**999E+307,SEARCH(D18,L6:L27)))**) with Flg = Evaluate(ISNUMBER(MATCH(D18,L6:L27,0))) Kris -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
RE: $$Excel-Macros$$ Asa R. I tried what you said for the column addressing but I can not make it work. Can you assist?
Hi again Howard - a few more comments: First when I used the exact same string as both the Set variable and the Match variable it said it needed an object. It did not like identical names I guess? By Match variable, I assume you mean the string used in the WorksheetFunction.Match expression. That isn't a variable, it's just the text you used as a column heading in your worksheet. The only variable is the one you refer to as the Set variable. VBA doesn't care if they are the same or not. I'm not sure what the problem you had was, but it could have been in the line where you declared your variable, the Dim statement. You may have used the wrong variable type after the As keyword in that line. Speaking of your declarations, instead of declaring the ranges As Object, it is better to declare them as the specific type of object that they are -- Range objects: Dim cBarDate As Range and so forth. It may be that what I really need to do is remove the .Columns from your expression and then the variable would become an integer and I could use it as an index? I partially answered this question in the reply I just sent by debugging your code line that determines a cell's color, but I would like to add that. you have a point. For your code so far, the Range variable is fine. A Range variable is very flexible in how you can use it. Referring to a range using a variable that points to it is faster in VBA than using the coordinates of that range (which requires VBA to create a new object each time you do so). However, there are cases where you need coordinates, and if that's the main thing you will need, it may make sense to just store the coordinates. I don't remember (if you said) why you wanted to created these variables. Perhaps they would turn out not to be the optimal solution! Not to confuse things too much, but here is one more alternative to using MATCH, if you're using Excel 2007+: structured table references. See https://office.microsoft.com/en-us/excel-help/using-structured-references-wi th-excel-tables-HA010155686.aspx for info. In short, you can use the InsertTable command in Excel to make the data on your worksheet into a Table. Excel then handles that data in more efficient ways, and also automatically allows you to refer to all the columns by name, and provides names for the whole table, the headers, each column with it's header, each column without it's header, the data area without the headers, etc. You can use these structured references in cell formulae directly, but you can't use them in VBA directly. You would have to use them something like this in VBA: With Sheets(ChartData) ' The following structured reference returns the data range for the column, excluding the header row and any unused rows below the table. Set cBarDate = .Evaluate(ChartDataTable[BarDate]) Even though you can refer to these columns using the structered reference name from VBA at any time (by way of Evaluate), you should use variables as above if you will be referring to the same range more than once, otherwise your code will run comparitively slow. Asa From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of tangledweb Sent: Thursday, March 22, 2012 12:35 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Asa R. I tried what you said for the column addressing but I can not make it work. Can you assist? Asa, I tried what you said but am doing something wrong. First when I used the exact same string as both the Set variable and the Match variable it said it needed an object. It did not like identical names I guess? Then I created different object names and that would compile but I have tried several variations to get a correct simple value returned (what I really want to do will be more complex but I have to crack this fundamental first) and all get errors of different types. It may be that what I really need to do is remove the .Columns from your expression and then the variable would become an integer and I could use it as an index? But even if that is true and what I decide to do I would still like to understand what is wrong here. I have a set of columns and am just trying to get the color value of one of them out of the range of the column BWTStop which should be the object cBWTStop I think? ActiveCell in these attempts was the cell just to the right of one of the colored cells. The current uncommented version keeps returning 0 but the actual color value of the cell I want is 255. The commented out versions get runtime errors or compile errors. Sub Macro1() ' ' Macro1 Macro which sets the column names for the ChartData sheet ' ' Column names Dim BarDate As String Dim BarTime As String Dim Baropen As String Dim Barhigh As String Dim Barlow As String Dim Barclose As String Dim BWTStop As String Dim Short_Long_Cash As String ' Column reference object Dim cBarDate
RE: $$Excel-Macros$$ Need help with VBAfor Excel naming and using column names for looping
You can put anything you want on the first row, but in tables of data (I use the term tables loosely here), it is usually desireable to have column headers that describe what data is in each column. Excel has features that use column headers if they exist. For example, sorting and filtering. So, a default? Not in general, but it is a useful convention that Excel caters to. Actually, Excel caters to having the headers on the row above your data. It does not assume that your data starts on the 2nd row of the worksheet (even though that is common). Unless you select a table range manually (in the case of sorting and filtering), Excel assumes that the table is seperated from the rest of the worksheet by blank rows and columns, and if the first row of that separate area looks like it could be headers, it assumes it is. Sometimes people put summary information or descriptive text above a table of data, perhaps starting in row 1. Then, a blank row, then the headers, then the data. Even the blank row is not REQUIRED (Excel is very flexible), but it allows Excel to guess the range that constitutes your table and it's headers more easily. Asa -Original Message- From: Domain Admin [mailto:domainqu...@gmail.com] Sent: Monday, March 19, 2012 6:13 PM To: Asa Rossoff Subject: Re: $$Excel-Macros$$ Need help with VBAfor Excel naming and using column names for looping And I forgot one question (not sure how to get all this back to the forum but will try to stay there in future). Is it a default in Excel that the first row names are considered column headers and not part of the data set? I see it act that way, but find nothing that says so or explains it. On Mon, Mar 19, 2012 at 6:07 PM, Domain Admin domainqu...@gmail.com wrote: I can see my C programming background (yes I am old) is not a big help here. I need some object oriented experience to get this faster (or be younger). You final solution is perfect even if not adding a column while running as no other code has to change if new columns are added as long as all operations in the code are using the named references. Nowhere in the book does it use the Set command. It would do the same thing as you but without the Set keyword in front. If I understand correctly your way creates a reference by name and not using Set creates an object with that name? On Mon, Mar 19, 2012 at 4:21 PM, Asa Rossoff a...@lovetour.info wrote: Hi Howard, No worries :) Ask as many questions as needed. Best to send your replies to the list though, so you get the benefit of other replies (if only because I may not have time to reply myself). This also allows other VBA learners can benefit from the conversation. A defined name (either via range.name=mycolumn or names.add) that refers to a range sticks to the same data/cells even when rows and cells are inserted and deleted because Excel updates those name references automatically whenever those events occur. This is just as Excel does the same for references in cell formulae when insert and deletes occur. Usually if you are referring to ranges in VBA you do not use defined names, but just use range variables. Use defined names when the names need to be used from cell formulae, or as one method of retaining those names between sessions (file close, reopen). If you use only a range variable, and column C contains a certain field of data -- say, Birthday -- then you or the user inserts a column before C to add another field -- say, Anniversary... And you use code similar to the following: Public Birthday As Range Sub Workbook_Open() Set Birthday = Range(C:C) 'Column gets inserted here with heading Anniversary MsgBox Birthday.Cells(1,1).Value ' Display the first cell value (column heading) End Sub ...open the file, allowing the above event to execute. Message should say Birthday. ...then close the file... ...reopen...message will say Anniversary. The problem with the above is that although the Range variable will adapt correctly when a column is inserted and still refer to the Birthday column, every time the file is opened it is set explicitly to refer to column C again. A defined name is a simple solution since it is saved between sessions and automatically adapts. The next simplest solution that comes to mind, and is actually even smarter (adapts if user just changes the text of the column heading without inserting or deleting columns) is to use range.find or worksheetfunction.match to find the column with the correct heading, and use that. For example: Set Birthday = Rows(1).Find(Birthday).EntireColumn ...or: Set Birthday = Sheets(MySheet).UsedRange.Rows(1).Find(Birthday, LookIn:=xlValues).EntireColumn ...or: Set Birthday = Columns(WorksheetFunction.Match(Birthday, Range(1:1), 0)) ...or: With Sheets(MySheet).UsedRange Set Birthday = .Columns(WorksheetFunction.Match(Birthday, .Rows(1), 0)) ' Resulting range
RE: $$Excel-Macros$$ Need help with VBAfor Excel naming and using column names for looping
C is still an important language.. I started out on BASIC, then Clipper and Pascal in the 80s. Learned VB in the 90s as an obvious leap from my most comfortable BASIC background, and besides, I had a need to do Office programming. Played a little with C but never took my programming seriously enough/had a strong motivation to really study it. Nowhere in the book does it use the Set command. It would do the same thing as you but without the Set keyword in front. If I understand correctly your way creates a reference by name and not using Set creates an object with that name? The Set command is always needed to assign an object (technically a pointer to an object) to a variable. A variable assignment without Set is actually implicitly using the Let command, which assigns a value, never an object, to a variable. -Original Message- From: Domain Admin [mailto:domainqu...@gmail.com] Sent: Monday, March 19, 2012 6:08 PM To: Asa Rossoff Subject: Re: $$Excel-Macros$$ Need help with VBAfor Excel naming and using column names for looping I can see my C programming background (yes I am old) is not a big help here. I need some object oriented experience to get this faster (or be younger). You final solution is perfect even if not adding a column while running as no other code has to change if new columns are added as long as all operations in the code are using the named references. Nowhere in the book does it use the Set command. It would do the same thing as you but without the Set keyword in front. If I understand correctly your way creates a reference by name and not using Set creates an object with that name? On Mon, Mar 19, 2012 at 4:21 PM, Asa Rossoff mailto:a...@lovetour.info a...@lovetour.info wrote: Hi Howard, No worries :) Ask as many questions as needed. Best to send your replies to the list though, so you get the benefit of other replies (if only because I may not have time to reply myself). This also allows other VBA learners can benefit from the conversation. A defined name (either via range.name=mycolumn or names.add) that refers to a range sticks to the same data/cells even when rows and cells are inserted and deleted because Excel updates those name references automatically whenever those events occur. This is just as Excel does the same for references in cell formulae when insert and deletes occur. Usually if you are referring to ranges in VBA you do not use defined names, but just use range variables. Use defined names when the names need to be used from cell formulae, or as one method of retaining those names between sessions (file close, reopen). If you use only a range variable, and column C contains a certain field of data -- say, Birthday -- then you or the user inserts a column before C to add another field -- say, Anniversary... And you use code similar to the following: Public Birthday As Range Sub Workbook_Open() Set Birthday = Range(C:C) 'Column gets inserted here with heading Anniversary MsgBox Birthday.Cells(1,1).Value ' Display the first cell value (column heading) End Sub ...open the file, allowing the above event to execute. Message should say Birthday. ...then close the file... ...reopen...message will say Anniversary. The problem with the above is that although the Range variable will adapt correctly when a column is inserted and still refer to the Birthday column, every time the file is opened it is set explicitly to refer to column C again. A defined name is a simple solution since it is saved between sessions and automatically adapts. The next simplest solution that comes to mind, and is actually even smarter (adapts if user just changes the text of the column heading without inserting or deleting columns) is to use range.find or worksheetfunction.match to find the column with the correct heading, and use that. For example: Set Birthday = Rows(1).Find(Birthday).EntireColumn ...or: Set Birthday = Sheets(MySheet).UsedRange.Rows(1).Find(Birthday, LookIn:=xlValues).EntireColumn ...or: Set Birthday = Columns(WorksheetFunction.Match(Birthday, Range(1:1), 0)) ...or: With Sheets(MySheet).UsedRange Set Birthday = .Columns(WorksheetFunction.Match(Birthday, .Rows(1), 0)) ' Resulting range will be the used part of the Birthday column, e.g. C1:C101 End With The above methods and their variants will always find the column with the given heading, so they are very adaptable and will find the right column after closing/reopening the file or inserting/deleting columns, or changing column headings. Asa -Original Message- From: Domain Admin [ mailto:domainqu...@gmail.com mailto:domainqu...@gmail.com] Sent: Monday, March 19, 2012 3:23 PM To: Asa Rossoff Subject: Re:
Re: $$Excel-Macros$$ checkbox query
Good evening Sir, Tons for thanks for solving my query by sparing your valuable time. I tried your code and it is removing the options in the datasheet. Whereas, what I want is, at the opening of the datasheet it should have hidden data options and as per the tick marked checkbox data they have to be unhidden mode. regards shankar On Tue, Mar 20, 2012 at 7:21 PM, Kris krishnak...@gmail.com wrote: Hi Create a commandbutton. And put this code in the userform module. Dim Rng As Range Private Sub CommandButton1_Click() Dim i As Long Application.ScreenUpdating = False Rng.Rows.Hidden = False With Me For i = 0 To .Controls.Count - 1 Debug.Print .Controls(i).Caption If TypeName(.Controls(i)) = CheckBox Then If .Controls(i).Value Then Rng.Find(.Controls(i).Caption).EntireRow.Hidden = True End If End If Next End With Application.ScreenUpdating = True End Sub Private Sub UserForm_Initialize() Set Rng = Intersect(Range(c:c), ActiveSheet.UsedRange) Rng.Rows.Hidden = False End Sub Kris On Tuesday, 20 March 2012 18:02:54 UTC+5:30, NOORAIN ANSARI wrote: Dear Shankar, Please see attached sheet. -- Thanks regards, Noorain Ansari *http://noorainansari.com/* *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ On Tue, Mar 20, 2012 at 11:32 AM, Shankar Bheema shankar.n...@gmail.comwrote: Dear experts I have attached an excel file containing data of qualification. On the userform I created check boxes with similar to the datasheet data. I need action like, only the tickmarked check box data only shown on the data sheet all other have to go in hidden mode (even in print also). waiting for your reply. regards shankar sb -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. --**--** --** To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ checkbox query
Dear Noorain The query solved by Mr KRIS krishnak...@gmail.com is catering my need. Thank you for your cooperation my dear friend. On Tue, Mar 20, 2012 at 6:02 PM, NOORAIN ANSARI noorain.ans...@gmail.comwrote: Dear Shankar, Please see attached sheet. -- Thanks regards, Noorain Ansari *http://noorainansari.com/* *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ On Tue, Mar 20, 2012 at 11:32 AM, Shankar Bheema shankar.n...@gmail.comwrote: Dear experts I have attached an excel file containing data of qualification. On the userform I created check boxes with similar to the datasheet data. I need action like, only the tickmarked check box data only shown on the data sheet all other have to go in hidden mode (even in print also). waiting for your reply. regards shankar sb -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
$$Excel-Macros$$ creating hyperlink on userform
Dear Experts If a pdf file name is placed on a Label form and assume its location is in D:/. How it would be hyperlinked from the USerform. regards shankar sb -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
$$Excel-Macros$$ Re: Macro for Insert a line for missing serial numbers.
Hi Sandeep, Before I proceed to modify the code, could you let me know the following 1. Which version of excel are you using 2. Do you want the code to insert the rows equivalent to the missing serial numbers for e.g. if after number 3 you have 7, do you want the code to insert 4 rows in the middle of 3 and 7. Regards, Damimkader S. Meeran On Thursday, March 22, 2012 1:50:18 PM UTC+5:30, sandeep chhajer wrote: Dear Damimkader, while I am running this code I am getting the follwing error code run time error 13 type mismatch please help. My problem has been reproduced My current Data is like thisSr. No. S_DISNO S_DISC S_UNIT S_SELP Purchse quantity 495061 358 SILK DUPPATTA 0.90m 260.00 36.00 495062 359 SILK DUPPATTA 1.15m 385.00 33.00 495063 351 SILK DOTHI 8 cu 3.6 m 2645.00 4.00 495067 351 SILK DOTHI 8 cu 3.6 m 2745.00 3.00 495070 351 SILK DOTHI 8 cu 3.6 m 2965.00 3.00 I want my data as follows Sr. No. S_DISNO S_DISC S_UNIT S_SELP Purchse quantity 495061 358 SILK DUPPATTA 0.90m 260.00 36.00 495062 359 SILK DUPPATTA 1.15m 385.00 33.00 495063 351 SILK DOTHI 8 cu 3.6 m 2645.00 4.00 495064 351 SILK DOTHI 8 cu 3.6 m 2645.00 4.00 495065 351 SILK DOTHI 8 cu 3.6 m 2645.00 4.00 495066 351 SILK DOTHI 8 cu 3.6 m 2645.00 4.00 495067 351 SILK DOTHI 8 cu 3.6 m 2745.00 3.00 495068 351 SILK DOTHI 8 cu 3.6 m 2745.00 3.00 495069 351 SILK DOTHI 8 cu 3.6 m 2745.00 3.00 495070 351 SILK DOTHI 8 cu 3.6 m 2965.00 3.00 Copy the previous line only sr no has chamged by adding 1 On 22 March 2012 02:26, Damimkader S. Meeran damim.ka...@gmail.comwrote: Hi Sandeep, My code below assumes the following 1. The serial numbers are in column A 2. The Excel version you are using is 2007. 3. The code is good for 10,000 rows. Please modify if required. Sub Serial() Dim i As Long For i = 1 To 1 If Cells(i, 1) And Cells(i + 1, 1) Then If Cells(i + 1, 1) - Cells(i, 1) 1 Then Rows(i + 1 : i + 1).Select Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove i = i + 1 End If Else Exit For End If Next i End Sub On Wednesday, March 21, 2012 9:40:37 PM UTC+5:30, sandeep chhajer wrote: Dear all, Please find my attachment ( I know asking question in this way is a violation of our forum rule but sorry I don't have data card to copy the queries from excel) Thanking you in advance. Sandeep Chhajer. On Wednesday, March 21, 2012 9:40:37 PM UTC+5:30, sandeep chhajer wrote: Dear all, Please find my attachment ( I know asking question in this way is a violation of our forum rule but sorry I don't have data card to copy the queries from excel) Thanking you in advance. Sandeep Chhajer. -- Regards, Sandeep Kumar Chhajer. -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ Re: Macro for Insert a line for missing serial numbers.
Dear Damimkader S. Meeran, Thanks for your reply. With ref to your question: I use office 2010 version. And I want to insert 3 column in between (I.e 4,5,6). Sandeep Chhajer. Sent on my BlackBerry® from Vodafone -Original Message- From: Damimkader S. Meeran damim.ka...@gmail.com Sender: excel-macros@googlegroups.com Date: Thu, 22 Mar 2012 03:57:16 To: excel-macros@googlegroups.com Reply-To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Re: Macro for Insert a line for missing serial numbers. Hi Sandeep, Before I proceed to modify the code, could you let me know the following 1. Which version of excel are you using 2. Do you want the code to insert the rows equivalent to the missing serial numbers for e.g. if after number 3 you have 7, do you want the code to insert 4 rows in the middle of 3 and 7. Regards, Damimkader S. Meeran On Thursday, March 22, 2012 1:50:18 PM UTC+5:30, sandeep chhajer wrote: Dear Damimkader, while I am running this code I am getting the follwing error code run time error 13 type mismatch please help. My problem has been reproduced My current Data is like thisSr. No. S_DISNO S_DISC S_UNIT S_SELP Purchse quantity 495061 358 SILK DUPPATTA 0.90m 260.00 36.00 495062 359 SILK DUPPATTA 1.15m 385.00 33.00 495063 351 SILK DOTHI 8 cu 3.6 m 2645.00 4.00 495067 351 SILK DOTHI 8 cu 3.6 m 2745.00 3.00 495070 351 SILK DOTHI 8 cu 3.6 m 2965.00 3.00 I want my data as follows Sr. No. S_DISNO S_DISC S_UNIT S_SELP Purchse quantity 495061 358 SILK DUPPATTA 0.90m 260.00 36.00 495062 359 SILK DUPPATTA 1.15m 385.00 33.00 495063 351 SILK DOTHI 8 cu 3.6 m 2645.00 4.00 495064 351 SILK DOTHI 8 cu 3.6 m 2645.00 4.00 495065 351 SILK DOTHI 8 cu 3.6 m 2645.00 4.00 495066 351 SILK DOTHI 8 cu 3.6 m 2645.00 4.00 495067 351 SILK DOTHI 8 cu 3.6 m 2745.00 3.00 495068 351 SILK DOTHI 8 cu 3.6 m 2745.00 3.00 495069 351 SILK DOTHI 8 cu 3.6 m 2745.00 3.00 495070 351 SILK DOTHI 8 cu 3.6 m 2965.00 3.00 Copy the previous line only sr no has chamged by adding 1 On 22 March 2012 02:26, Damimkader S. Meeran damim.ka...@gmail.comwrote: Hi Sandeep, My code below assumes the following 1. The serial numbers are in column A 2. The Excel version you are using is 2007. 3. The code is good for 10,000 rows. Please modify if required. Sub Serial() Dim i As Long For i = 1 To 1 If Cells(i, 1) And Cells(i + 1, 1) Then If Cells(i + 1, 1) - Cells(i, 1) 1 Then Rows(i + 1 : i + 1).Select Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove i = i + 1 End If Else Exit For End If Next i End Sub On Wednesday, March 21, 2012 9:40:37 PM UTC+5:30, sandeep chhajer wrote: Dear all, Please find my attachment ( I know asking question in this way is a violation of our forum rule but sorry I don't have data card to copy the queries from excel) Thanking you in advance. Sandeep Chhajer. On Wednesday, March 21, 2012 9:40:37 PM UTC+5:30, sandeep chhajer wrote: Dear all, Please find my attachment ( I know asking question in this way is a violation of our forum rule but sorry I don't have data card to copy the queries from excel) Thanking you in advance. Sandeep Chhajer. -- Regards, Sandeep Kumar Chhajer. -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE
Re: $$Excel-Macros$$ Re: Macro for Insert a line for missing serial numbers.
Hi, I've modified the code to suit the needs. It has been worked upon in Excel 2007 i unfortunately do not have excel 2010. The code has the following assumptions 1. All serial numbers are in number format. The error message you were getting earlier is possibly because a serial number was a number stored in text format. You can be sure of this by checking if excel shows a green pointer next to the cell. Additionally I'm not sure if some of the VBA functions in 2007 would work on 2010. I tried to make the code universal so give it a try. Sub Serial() Dim i As Long, count As Long, j As Long, Diff As Long For i = 1 To 1 If Cells(i, 1) And Cells(i + 1, 1) Then Diff = Cells(i + 1, 1) - Cells(i, 1) If Diff 1 Then For j = 1 To (Diff - 1) Rows(i + 1 : i + 1).Select Selection.Insert Shift:=xlDown Rows(i : i).Select Selection.Copy Cells(i + 1, 1).Select ActiveCell.PasteSpecial xlPasteAll Cells(i + 1, 1) = Cells(i + 2, 1) - 1 Next j i = i + (Diff - 1) End If Else Exit For End If Next i End Sub Regards, Damimkader On Thursday, March 22, 2012 4:37:19 PM UTC+5:30, sandeep chhajer wrote: Dear Damimkader S. Meeran, Thanks for your reply. With ref to your question: I use office 2010 version. And I want to insert 3 column in between (I.e 4,5,6). Sandeep Chhajer. Sent on my BlackBerry® from Vodafone -- *From: * Damimkader S. Meeran damim.ka...@gmail.com *Sender: * excel-macros@googlegroups.com *Date: *Thu, 22 Mar 2012 03:57:16 -0700 (PDT) *To: *excel-macros@googlegroups.com *ReplyTo: * excel-macros@googlegroups.com *Subject: *$$Excel-Macros$$ Re: Macro for Insert a line for missing serial numbers. Hi Sandeep, Before I proceed to modify the code, could you let me know the following 1. Which version of excel are you using 2. Do you want the code to insert the rows equivalent to the missing serial numbers for e.g. if after number 3 you have 7, do you want the code to insert 4 rows in the middle of 3 and 7. Regards, Damimkader S. Meeran On Thursday, March 22, 2012 1:50:18 PM UTC+5:30, sandeep chhajer wrote: Dear Damimkader, while I am running this code I am getting the follwing error code run time error 13 type mismatch please help. My problem has been reproduced My current Data is like thisSr. No. S_DISNO S_DISC S_UNIT S_SELP Purchse quantity 495061 358 SILK DUPPATTA 0.90m 260.00 36.00 495062 359 SILK DUPPATTA 1.15m 385.00 33.00 495063 351 SILK DOTHI 8 cu 3.6 m 2645.00 4.00 495067 351 SILK DOTHI 8 cu 3.6 m 2745.00 3.00 495070 351 SILK DOTHI 8 cu 3.6 m 2965.00 3.00 I want my data as follows Sr. No. S_DISNO S_DISC S_UNIT S_SELP Purchse quantity 495061 358 SILK DUPPATTA 0.90m 260.00 36.00 495062 359 SILK DUPPATTA 1.15m 385.00 33.00 495063 351 SILK DOTHI 8 cu 3.6 m 2645.00 4.00 495064 351 SILK DOTHI 8 cu 3.6 m 2645.00 4.00 495065 351 SILK DOTHI 8 cu 3.6 m 2645.00 4.00 495066 351 SILK DOTHI 8 cu 3.6 m 2645.00 4.00 495067 351 SILK DOTHI 8 cu 3.6 m 2745.00 3.00 495068 351 SILK DOTHI 8 cu 3.6 m 2745.00 3.00 495069 351 SILK DOTHI 8 cu 3.6 m 2745.00 3.00 495070 351 SILK DOTHI 8 cu 3.6 m 2965.00 3.00 Copy the previous line only sr no has chamged by adding 1 On 22 March 2012 02:26, Damimkader S. Meeran damim.ka...@gmail.comwrote: Hi Sandeep, My code below assumes the following 1. The serial numbers are in column A 2. The Excel version you are using is 2007. 3. The code is good for 10,000 rows. Please modify if required. Sub Serial() Dim i As Long For i = 1 To 1 If Cells(i, 1) And Cells(i + 1, 1) Then If Cells(i + 1, 1) - Cells(i, 1) 1 Then Rows(i + 1 : i + 1).Select Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove i = i + 1 End If Else Exit For End If Next i End Sub On Wednesday, March 21, 2012 9:40:37 PM UTC+5:30, sandeep chhajer wrote: Dear all, Please find my attachment ( I know asking question in this way is a violation of our forum rule but sorry I don't have data card to copy the queries from excel) Thanking you in advance. Sandeep Chhajer. On Wednesday, March 21, 2012 9:40:37 PM UTC+5:30, sandeep chhajer wrote: Dear all, Please find my attachment ( I know asking question in this way is a violation of our forum rule but sorry I don't have data card to copy the queries from excel) Thanking you in advance. Sandeep Chhajer. -- Regards, Sandeep Kumar Chhajer. -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent,
$$Excel-Macros$$ Excel formula help needed
Hi Oxford Excel Gurus In the fill attached sheet PL i have been able to write a formula that can show how many number of years i want to view the report ie if i want to see 5 years report i will select from the drop down, and so forth. and the number o years specified will show In the 2nd sheet ie PL (2) I have also written a formula to view which year the report should begin from say year 2007 , or 2008 etc. the problem is how to combine these two formulae into one when i can have a situation where i can select a particular year to view the report and specify how many years from there to view the report. kindly help with a formula. tx -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com Annual Report.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ Re: Macro for Insert a line for missing serial numbers.
Thanks Damimkader. I will try and let you know. Sandeep Chhajer. Sent on my BlackBerry® from Vodafone -Original Message- From: Damimkader S. Meeran damim.ka...@gmail.com Date: Thu, 22 Mar 2012 05:20:05 To: excel-macros@googlegroups.com Cc: chhajersand...@gmail.com Subject: Re: $$Excel-Macros$$ Re: Macro for Insert a line for missing serial numbers. Hi, I've modified the code to suit the needs. It has been worked upon in Excel 2007 i unfortunately do not have excel 2010. The code has the following assumptions 1. All serial numbers are in number format. The error message you were getting earlier is possibly because a serial number was a number stored in text format. You can be sure of this by checking if excel shows a green pointer next to the cell. Additionally I'm not sure if some of the VBA functions in 2007 would work on 2010. I tried to make the code universal so give it a try. Sub Serial() Dim i As Long, count As Long, j As Long, Diff As Long For i = 1 To 1 If Cells(i, 1) And Cells(i + 1, 1) Then Diff = Cells(i + 1, 1) - Cells(i, 1) If Diff 1 Then For j = 1 To (Diff - 1) Rows(i + 1 : i + 1).Select Selection.Insert Shift:=xlDown Rows(i : i).Select Selection.Copy Cells(i + 1, 1).Select ActiveCell.PasteSpecial xlPasteAll Cells(i + 1, 1) = Cells(i + 2, 1) - 1 Next j i = i + (Diff - 1) End If Else Exit For End If Next i End Sub Regards, Damimkader On Thursday, March 22, 2012 4:37:19 PM UTC+5:30, sandeep chhajer wrote: Dear Damimkader S. Meeran, Thanks for your reply. With ref to your question: I use office 2010 version. And I want to insert 3 column in between (I.e 4,5,6). Sandeep Chhajer. Sent on my BlackBerry® from Vodafone -- *From: * Damimkader S. Meeran damim.ka...@gmail.com *Sender: * excel-macros@googlegroups.com *Date: *Thu, 22 Mar 2012 03:57:16 -0700 (PDT) *To: *excel-macros@googlegroups.com *ReplyTo: * excel-macros@googlegroups.com *Subject: *$$Excel-Macros$$ Re: Macro for Insert a line for missing serial numbers. Hi Sandeep, Before I proceed to modify the code, could you let me know the following 1. Which version of excel are you using 2. Do you want the code to insert the rows equivalent to the missing serial numbers for e.g. if after number 3 you have 7, do you want the code to insert 4 rows in the middle of 3 and 7. Regards, Damimkader S. Meeran On Thursday, March 22, 2012 1:50:18 PM UTC+5:30, sandeep chhajer wrote: Dear Damimkader, while I am running this code I am getting the follwing error code run time error 13 type mismatch please help. My problem has been reproduced My current Data is like thisSr. No. S_DISNO S_DISC S_UNIT S_SELP Purchse quantity 495061 358 SILK DUPPATTA 0.90m 260.00 36.00 495062 359 SILK DUPPATTA 1.15m 385.00 33.00 495063 351 SILK DOTHI 8 cu 3.6 m 2645.00 4.00 495067 351 SILK DOTHI 8 cu 3.6 m 2745.00 3.00 495070 351 SILK DOTHI 8 cu 3.6 m 2965.00 3.00 I want my data as follows Sr. No. S_DISNO S_DISC S_UNIT S_SELP Purchse quantity 495061 358 SILK DUPPATTA 0.90m 260.00 36.00 495062 359 SILK DUPPATTA 1.15m 385.00 33.00 495063 351 SILK DOTHI 8 cu 3.6 m 2645.00 4.00 495064 351 SILK DOTHI 8 cu 3.6 m 2645.00 4.00 495065 351 SILK DOTHI 8 cu 3.6 m 2645.00 4.00 495066 351 SILK DOTHI 8 cu 3.6 m 2645.00 4.00 495067 351 SILK DOTHI 8 cu 3.6 m 2745.00 3.00 495068 351 SILK DOTHI 8 cu 3.6 m 2745.00 3.00 495069 351 SILK DOTHI 8 cu 3.6 m 2745.00 3.00 495070 351 SILK DOTHI 8 cu 3.6 m 2965.00 3.00 Copy the previous line only sr no has chamged by adding 1 On 22 March 2012 02:26, Damimkader S. Meeran damim.ka...@gmail.comwrote: Hi Sandeep, My code below assumes the following 1. The serial numbers are in column A 2. The Excel version you are using is 2007. 3. The code is good for 10,000 rows. Please modify if required. Sub Serial() Dim i As Long For i = 1 To 1 If Cells(i, 1) And Cells(i + 1, 1) Then If Cells(i + 1, 1) - Cells(i, 1) 1 Then Rows(i + 1 : i + 1).Select Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove i = i + 1 End If Else Exit For End If Next i End Sub On Wednesday, March 21, 2012 9:40:37 PM UTC+5:30, sandeep chhajer wrote: Dear all, Please find my attachment ( I know asking question in this way is a violation of our forum rule but sorry I don't have data card to copy the queries from excel) Thanking you in advance. Sandeep Chhajer. On Wednesday, March 21, 2012 9:40:37 PM UTC+5:30, sandeep chhajer wrote: Dear all, Please find my attachment ( I know asking
Re: $$Excel-Macros$$ Re: Match text to a list
That's not true. MATCH(A,L6:L27,0) is not equal to MATCH(AA,L6:L27,0). Can you attach the workbook ? Kris -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ Compress of images
In the absence of a better explanation you might try sitting on them Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com From: Veeru TOC Sent: Thursday, March 22, 2012 12:15 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Compress of images Dear Group how to compress images -- ThanksRegards Veerendra.Vanam TOC TEAM Bhagyanagar Electrical Contractors H.No-5-9-286/65,Plot No-65 Rajiv Gandhi Nagar,Near Andhra Jyothi Press,IE-Kukatpally, Hyderabad-500072,-Andhra Pradesh 91-9177-444-840, -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ words split from a cell - with formulas only Not for VBA code
I already posted a reply Option Explicit Sub getmiddleSAS() Dim c As Range Dim fs As Long Dim ls As Long For Each c In Range(a2:a10) c = Application.Trim(c) fs = InStr(c, ) ls = InStrRev(c, ) c.Offset(, 1) = Mid(c, fs, ls - fs) Next c Columns(b).AutoFit End Sub Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com From: NRao Mynampati Sent: Thursday, March 22, 2012 12:22 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ words split from a cell - with formulas only Not for VBA code Please find the attachment of my criteria. Please refer column A and i want result in Column 'B what I need like that. Thanks in advance Simha 970-309-1552 -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ Need a small help, related to the Date and Time macro.....
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 _ Or Target.Row 2 Then Exit Sub If Target.Column = 1 Or Target.Column = 3 Then If Len(Application.Trim(Target)) 0 Then _ Target.Offset(, 1) = Now End If End Sub Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com From: Indrajit $nai Sent: Thursday, March 22, 2012 1:23 AM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Need a small help, related to the Date and Time macro. @ dguillett1, PFA for your reference. On Thu, Mar 22, 2012 at 4:28 AM, dguillett1 dguille...@gmail.com wrote: Without seeing what you want with an example I can’t help Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com From: Indrajit $nai Sent: Wednesday, March 21, 2012 5:21 PM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Need a small help, related to the Date and Time macro. Thanks @ Ranjan, but I don't track it in MS-Access File, can you incorporate this codes for MS-Excel? Thanks @ dguillett1, but I'm not able to use the same code for Logout time tracking in a different column, can you please help me once again? @ Abdulgani Shaikh, I have attached the same file in .xls format. On Wed, Mar 21, 2012 at 11:12 PM, Rajan_Verma rajanverma1...@gmail.com wrote: It’s a old post on this group ,I have created a small tool to track punch in and punch out Time , hope it can help. ‘= Hi Lokesh, I am attaching Two files here.. 1). Save Access file at Shared Location. 2). In Code Module Replace Thisworkbook.Path with shared Path 3). Fill AllEmpID table in Database with EMPID for validation. ObjConnection.ConnectionString = Provider=Microsoft.ACE.OLEDB.12.0;Data Source= ThisWorkbook.Path \table.accdb ; ‘= Regards Rajan From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Indrajit $nai Sent: Mar/Wed/2012 03:17 To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Need a small help, related to the Date and Time macro. Hi All, Need a small help from you all. I have attached a file with this mail. Now if you open the file you can see there is a button named Login, if you click on it, it will take the system time and date. There are two columns available, Name (Column A) and Login Time (Column B), now I want if the respective Column A is blank then it will not put the date and time in Column B, if there is a name in column A then only date and time will be captured in the column B, other wise it will give me a error message. If you have the code for the same then please provide me. Thanks in advance. -- Indrajit talk2indra...@gmail.com Disclaimer: This electronic message and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking any action in reliance on the contents of this information is strictly prohibited and may be unlawful. -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum
Re: $$Excel-Macros$$ Split text in cell
Macro looks for 1st space and last space and gets what is between. Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com From: L.K. Modi Sent: Wednesday, March 21, 2012 11:23 PM To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Split text in cell Dear All, Its good to see this resolution but can anyone explain this in simple way Regards Modi On Thu, Mar 22, 2012 at 4:51 AM, dguillett1 dguille...@gmail.com wrote: Option Explicit Sub getmiddleSAS() Dim c As Range Dim fs As Long Dim ls As Long For Each c In Range(a2:a10) c = Application.Trim(c) fs = InStr(c, ) ls = InStrRev(c, ) c.Offset(, 1) = Mid(c, fs, ls - fs) Next c Columns(b).AutoFit End Sub Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com From: NRao Mynampati Sent: Wednesday, March 21, 2012 4:00 AM To: excel-macros@googlegroups.com Cc: Dilip Pandey Subject: $$Excel-Macros$$ Split text in cell Hi Team, How to remove a words left text and right text of reference Please refer attachment. Thanks in advance. -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
$$Excel-Macros$$ Re: Help to find position of a particular number in a given data set
Hi Anand, Thank you for your response.Please refer to the calculation below. For repetitive items,rank formula removes subsequent rank for example 11 % rank 3 is not assigned due to multiple entry for the same standing. I want to ignore repetitive occurrences of rates and decide first, Second third highest rates and so on. For example you want to know position of 9%, the solution I am looking for should give 4th highest rate while the rank function gives rank 5. Please provide new solution if possible Thanks once again Fin. Inst. Rates in % Rank A 10 4 B 11 2 C 12 1 D 11 2 E 6 10 F 9 5 G 8 7 H 8 7 I 9 5 J 7 9 Atul Patel H: 905 785 8324 C: 647 898 4074 atul_pate...@yahoo.com Good to know you ! Sent from my iPhone On 2012-03-21, at 21:47, Anand Kumar anand...@gmail.com wrote: Hi Atul, Rank function seems to work perfectly fine here Copied the name of Institutions to A column and percentages to column B in C2 the formula was =RANK(B2,$B$2:$B$11,0) now drag the formula till C11 you get the desired result. Warm regards, Anand Kumar On Wednesday, 21 March 2012 02:54:21 UTC+5:30, Atul wrote: Hi, I need help to find position of a particular number in a given data set. Please refer to the table below. Fin. Inst.Rates in % A 10 B 11 C 12 D 11 E 6 F 9 G 8 H 8 I 9 J 7 In the above example, Rates are given for each financial institution. In descending order,the rate 12% is at the 1st position,11% is 2nd highest and so on. While using function LARGE or SMALL, we need to assign kth position to get the required result. I need to find (kth) position of given rate from the list, ignoring the duplicate items. Going back to the above example, 11% will be the second highest rate, 10% being the third highest rate and so on. Please let me know the function or combination of functions. Thank you for your help and time. Regards, Atul Patel H: 905 785 8324 C: 647 898 4074 atul_pate...@yahoo.com Good to know you ! Atul Patel H: 905 785 8324 C: 647 898 4074 atul_pate...@yahoo.com Good to know you ! TODAY(Beta) • Powered by Yahoo! Crying 4-year-old boy meets hoops hero Aubrey Barone never thought this would happen when she posted a video of her son Shane on YouTube. Privacy Policy -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ Excel formula help needed
HI, PFA. Regards, MARIES. On Thu, Mar 22, 2012 at 4:40 PM, hilary lomotey resp...@gmail.com wrote: Hi Oxford Excel Gurus In the fill attached sheet PL i have been able to write a formula that can show how many number of years i want to view the report ie if i want to see 5 years report i will select from the drop down, and so forth. and the number o years specified will show In the 2nd sheet ie PL (2) I have also written a formula to view which year the report should begin from say year 2007 , or 2008 etc. the problem is how to combine these two formulae into one when i can have a situation where i can select a particular year to view the report and specify how many years from there to view the report. kindly help with a formula. tx -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com Annual Report.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
$$Excel-Macros$$ Re: Help to find position of a particular number in a given data set
My apology! I am overseas. Just email reply will be ok How ever if you want to dicuss on this I can call you too Pl provide your contact. Sent from my iPhone On 2012-03-22, at 9:27, Anand Kumar anand...@gmail.com wrote: with an improper contact number you expect me to reach you??? On Thu, Mar 22, 2012 at 6:42 PM, Atul Patel atul_pate...@yahoo.com wrote: Hi Anand, Thank you for your response.Please refer to the calculation below. For repetitive items,rank formula removes subsequent rank for example 11 % rank 3 is not assigned due to multiple entry for the same standing. I want to ignore repetitive occurrences of rates and decide first, Second third highest rates and so on. For example you want to know position of 9%, the solution I am looking for should give 4th highest rate while the rank function gives rank 5. Please provide new solution if possible Thanks once again Fin. Inst. Rates in %Rank A 10 4 B 11 2 C 12 1 D 11 2 E 6 10 F 9 5 G 8 7 H 8 7 I 9 5 J 7 9 Atul Patel H: 905 785 8324 C: 647 898 4074 atul_pate...@yahoo.com Good to know you ! Sent from my iPhone On 2012-03-21, at 21:47, Anand Kumar anand...@gmail.com wrote: Hi Atul, Rank function seems to work perfectly fine here Copied the name of Institutions to A column and percentages to column B in C2 the formula was =RANK(B2,$B$2:$B$11,0) now drag the formula till C11 you get the desired result. Warm regards, Anand Kumar On Wednesday, 21 March 2012 02:54:21 UTC+5:30, Atul wrote: Hi, I need help to find position of a particular number in a given data set. Please refer to the table below. Fin. Inst. Rates in % A10 B11 C12 D11 E6 F9 G8 H8 I9 J7 In the above example, Rates are given for each financial institution. In descending order,the rate 12% is at the 1st position,11% is 2nd highest and so on. While using function LARGE or SMALL, we need to assign kth position to get the required result. I need to find (kth) position of given rate from the list, ignoring the duplicate items. Going back to the above example, 11% will be the second highest rate, 10% being the third highest rate and so on. Please let me know the function or combination of functions. Thank you for your help and time. Regards, Atul Patel H: 905 785 8324 C: 647 898 4074 atul_pate...@yahoo.com Good to know you ! Atul Patel H: 905 785 8324 C: 647 898 4074 atul_pate...@yahoo.com Good to know you ! -- Warm regards, Anand Kumar 08800710233 http://anandydr.googlepages.com/ There are 10 kinds of people: Those who understand binary and those who don't. -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
$$Excel-Macros$$ Excel conditional formatting-Urgent plz
Hello Experts, I need a conditional cell color showing where Region 1 showing the value over 10 should colour red and Region 2 value showing over 12 should show green. Attached file for the same. Region Value in $ 1 20 2 22 1 11 2 11 2 5 2 15 2 3 1 29 Regards, Vin -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com Book3.xls Description: MS-Excel spreadsheet
Re: $$Excel-Macros$$ Excel conditional formatting-Urgent plz
And what if both? Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com From: vinod rao Sent: Thursday, March 22, 2012 9:35 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Excel conditional formatting-Urgent plz Hello Experts, I need a conditional cell color showing where Region 1 showing the value over 10 should colour red and Region 2 value showing over 12 should show green. Attached file for the same. Region Value in $ 1 20 2 22 1 11 2 11 2 5 2 15 2 3 1 29 Regards, Vin -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ Excel conditional formatting-Urgent plz
Dear vinod, See attached sheet, hope it help you. -- Thanks regards, Noorain Ansari *http://noorainansari.com/* *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ On Thu, Mar 22, 2012 at 8:05 PM, vinod rao vinod.ma...@gmail.com wrote: Hello Experts, I need a conditional cell color showing where Region 1 showing the value over 10 should colour red and Region 2 value showing over 12 should show green. Attached file for the same. Region Value in $ 1 20 2 22 1 11 2 11 2 5 2 15 2 3 1 29 Regards, Vin -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com Sample_Workbook.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
$$Excel-Macros$$ Re: Excel conditional formatting-Urgent plz
Hi, Please review the below link it is very helpful with simple examples. http://www.free-training-tutorial.com/animations/conditional-formatting-examples.html Regards, Damimkader S. Meeran On Thursday, March 22, 2012 8:05:32 PM UTC+5:30, vnrao wrote: Hello Experts, I need a conditional cell color showing where Region 1 showing the value over 10 should colour red and Region 2 value showing over 12 should show green. Attached file for the same. Region Value in $ 1 20 2 22 1 11 2 11 2 5 2 15 2 3 1 29 Regards, Vin -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ Excel conditional formatting-Urgent plz
Hey Ansari, Don Ah!! very quick reply. Thank you very much , this helps. have a nice day. On Thu, Mar 22, 2012 at 8:32 PM, NOORAIN ANSARI noorain.ans...@gmail.comwrote: Dear vinod, See attached sheet, hope it help you. -- Thanks regards, Noorain Ansari *http://noorainansari.com/* *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ On Thu, Mar 22, 2012 at 8:05 PM, vinod rao vinod.ma...@gmail.com wrote: Hello Experts, I need a conditional cell color showing where Region 1 showing the value over 10 should colour red and Region 2 value showing over 12 should show green. Attached file for the same. Region Value in $ 1 20 2 22 1 11 2 11 2 5 2 15 2 3 1 29 Regards, Vin -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ Re: Excel conditional formatting-Urgent plz
Thank you Meeran :) On Thu, Mar 22, 2012 at 8:39 PM, Damimkader S. Meeran damim.ka...@gmail.com wrote: Hi, Please review the below link it is very helpful with simple examples. http://www.free-training-tutorial.com/animations/conditional-formatting-examples.html Regards, Damimkader S. Meeran On Thursday, March 22, 2012 8:05:32 PM UTC+5:30, vnrao wrote: Hello Experts, I need a conditional cell color showing where Region 1 showing the value over 10 should colour red and Region 2 value showing over 12 should show green. Attached file for the same. Region Value in $ 1 20 2 22 1 11 2 11 2 5 2 15 2 3 1 29 Regards, Vin -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ creating hyperlink on userform
Assuming that you have placed full path of your file in the caption of your label (label1 in this case) itself... *Private Sub Label1_Click()* *Application.ThisWorkbook.FollowHyperlink Label1.Caption* *End Sub* ...should work. HTH On Thu, Mar 22, 2012 at 4:17 PM, Shankar Bheema shankar.n...@gmail.comwrote: Dear Experts If a pdf file name is placed on a Label form and assume its location is in D:/. How it would be hyperlinked from the USerform. regards shankar sb -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ email extraction from gmail
thanks noorain and jitendra. On Thu, Mar 22, 2012 at 12:03 PM, Jitendra singh jeete2...@gmail.comwrote: give email id A1 box in sheet and run macro --- Sub Mail_Every_Worksheet() 'Working in 2000-2010 Dim sh As Worksheet Dim wb As Workbook Dim FileExtStr As String Dim FileFormatNum As Long Dim TempFilePath As String Dim TempFileName As String Dim OutApp As Object Dim OutMail As Object TempFilePath = Environ$(temp) \ If Val(Application.Version) 12 Then 'You use Excel 97-2003 FileExtStr = .xls: FileFormatNum = -4143 Else 'You use Excel 2007-2010 FileExtStr = .xlsx: FileFormatNum = 51 End If With Application .ScreenUpdating = False .EnableEvents = False End With Set OutApp = CreateObject(Outlook.Application) For Each sh In ThisWorkbook.Worksheets If sh.Range(A1).Value Like ?*@?*.?* Then sh.Copy Set wb = ActiveWorkbook TempFileName =sh.Name of _ ThisWorkbook.Name Set OutMail = OutApp.CreateItem(0) With wb .SaveAs TempFilePath TempFileName FileExtStr, _ FileFormat:=FileFormatNum On Error Resume Next With OutMail .To = sh.Range(A1).Value .CC = .BCC = .Subject = DAILY DASHBOARD .Body = DAILY DASHBOARD .Attachments.Add wb.FullName 'You can add other files also like this '.Attachments.Add (C:\test.txt) .Send 'or use .Display End With On Error GoTo 0 .Close SaveChanges:=False End With Set OutMail = Nothing Kill TempFilePath TempFileName FileExtStr End If Next sh Set OutApp = Nothing With Application .ScreenUpdating = True .EnableEvents = True End With End Sub = On Thu, Mar 22, 2012 at 8:58 AM, NOORAIN ANSARI noorain.ans...@gmail.comwrote: Dear Raj, Please try through below link. http://www.gmailextractor.com/ -- Thanks regards, Noorain Ansari *http://noorainansari.com/* *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ On Wed, Mar 21, 2012 at 11:56 PM, Raj Mahapatra rajafs...@gmail.comwrote: hi group, can you please advice me how to extract email id's from gmail or outlook using VBA Thanks Regards -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread
$$Excel-Macros$$ Re: Arry Formula help
Hi, I checked the VBAVlookup custom function, it is looking up using usernumber and the layout on the Txtlist sheet. If this is slowing down the performance of the application, I would recommend creating a column which concatenates the usernumber and layout column on the txt list sheet and on the Setts sheet write a vlookup combined with a concatenate function to accomplish what you are looking for (multivalued lookup) Please let me know if that helps or if you need more help. Regards, Damim On Thursday, March 22, 2012 10:26:58 PM UTC+5:30, Avinash wrote: Dear Experts, please open attached excel sheet and go to the Setts sub sheet for better understanding. basically i want to perform multi value lookup for the specific user. and to get this thing done i tried to Google it. now i got the VBA code for the multivalue lookup but it is taking too much time to give me the results. can anyone please help me with this and tell me how it works ( there is a VBA code in the attached file) please help me and thanks Don and Noorain for all your support. and those formulas and codes which you have given me are very very very very usefull in my work thnx a ton guys. Regards, Avinash -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ Split text in cell
Another Approach Copy and paste the below formula in 2nd Row of any cell other than A2 cell and drag it below. =IF((LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2), ,)))1, TRIM(MID(TRIM(A2), FIND( ,TRIM(A2))+1, (FIND(^,SUBSTITUTE(TRIM(A2), ,^,(LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2), ,)-1)-FIND( ,TRIM(A2))+1)), ) Refer the attachment file for better understanding. On Wed, Mar 21, 2012 at 2:30 PM, NRao Mynampati ml.narasimha...@gmail.comwrote: Hi Team, How to remove a words left text and right text of reference Please refer attachment. Thanks in advance. -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com Split text - Solution File.xls Description: MS-Excel spreadsheet
$$Excel-Macros$$ Re: Help to find position of a particular number in a given data set
Hello Atul, try this Assume B2:B11 is %, in C2 then copy down. =SUMPRODUCT((ISNUMBER(MATCH(ROW(INDEX(A:A,1):INDEX(A:A,RANK(B2,B$2:B$11))),INDEX(RANK(B$2:B$11,B$2:B$11),0),0))+0)) ___ HTH, Haseeb On Wednesday, March 21, 2012 12:24:21 AM UTC+3, Atul wrote: Hi, I need help to find position of a particular number in a given data set. Please refer to the table below. *Fin. Inst.* *Rates in %* A 10 B 11 C 12 D 11 E 6 F 9 G 8 H 8 I 9 J 7 In the above example, Rates are given for each financial institution. In descending order,the rate 12% is at the 1st position,11% is 2nd highest and so on. While using function LARGE or SMALL, we need to assign kth position to get the required result. I need to find (kth) position of given rate from the list, ignoring the duplicate items. Going back to the above example, 11% will be the second highest rate, 10% being the third highest rate and so on. Please let me know the function or combination of functions. Thank you for your help and time. Regards, *Atul Patel* H: 905 785 8324 C: 647 898 4074 atul_pate...@yahoo.com Good to know you ! *Atul Patel* H: 905 785 8324 C: 647 898 4074 atul_pate...@yahoo.com Good to know you ! -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
$$Excel-Macros$$ Re: Asa R. I tried what you said for the column addressing but I can not make it work. Can you assist?
First before I forget, thanks again for the help. Maybe I need to go find a more comprehensive book. The one I have does not mention the Set command, does not list Range as a data type, does not mention tables (which may be useful but that is too ambitious at this point). From the other thread, thanks for clarifying the column name item. I am very surprised there is not a way to specifically designate if a row is column names or data but seems easy to make it work. What I will ultimately be doing here if I can get over not having done any programming for 20 years and the always rough (for me at least) initial learning curve... is have a set of columns of data and need to run a gonzo number of simulations on the data where I am moving down the columns of cells and based upon the value of one cell relative to another make certain decisions and set certain other column values. The entire data set will change frequently due to parameters outside the working data set. So that is why I may want to use just a reference index and not the range so I can just loop down the columns until I hit bottom. I cleaned up the code based on what you said. It did turn out the order in which I wrote the code caused some errors. (I would like a way to have it not interrupt me with error messages when I am entering code and may move between lines that are not complete yet). I am getting it to work now due to a well duh error. I was reading the font color and needed the fill color. I am still confused still though why all the 3 methods you gave me in the code below seem to return the value from the same cell. What is this form really doing different from the others? mycolor = BWTStop.Cells(ActiveCell.Row - BWTStop.Cells(1).Row + 1, 1).Interior.Color Here is the new code: ' Column reference object Dim BarDate As Range Dim BarTime As Range Dim Baropen As Range Dim Barhigh As Range Dim Barlow As Range Dim Barclose As Range Dim BWTStop As Range Dim Short_Long_Cash As Range With Sheets(ChartData).UsedRange 'All the columns will only have the used range. Set BarDate = .Columns(WorksheetFunction.Match(BarDate, .Rows(1), 0)) Set BarTime = .Columns(WorksheetFunction.Match(BarTime, .Rows(1), 0)) Set Baropen = .Columns(WorksheetFunction.Match(BarOpen, .Rows(1), 0)) Set Barhigh = .Columns(WorksheetFunction.Match(BarHigh, .Rows(1), 0)) Set Barlow = .Columns(WorksheetFunction.Match(BarLow, .Rows(1), 0)) Set Barclose = .Columns(WorksheetFunction.Match(BarClose, .Rows(1), 0)) Set BWTStop = .Columns(WorksheetFunction.Match(BWTStop, .Rows(1), 0)) Set Short_Long_Cash = .Columns(WorksheetFunction.Match(Short_Long_Cash, .Rows(1), 0)) End With ' Dim mycolor mycolor = BWTStop.Cells(ActiveCell.Row, 1).Interior.Color Debug.Print mycolor mycolor = BWTStop.Cells(ActiveCell.Row - BWTStop.Cells(1).Row + 1, 1).Interior.Color Debug.Print mycolor mycolor = Application.Intersect(ActiveCell.EntireRow, BWTStop).Interior.Color Debug.Print mycolor -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ ****** FIVE YEARS COMPLETION******THANK YOU******
Congratulation Dear Ayush and all group members On Thu, Mar 22, 2012 at 4:25 AM, Ayush Jain jainayus...@gmail.com wrote: Dear members,** I am proud to announce that the forum has completed 5 years today. This is really great milestone of the forum. My sincere thanks to each one of you who keep this forum alive by asking questions and helping others by providing solutions. Over the last five years, I have seen decent growth in the number of posts and quality of solutions provided by the experts here. I have learned many things from this forum and experts. In my view, Learning never stops and the discussion forum is the best place to learn and share the skills. I hardly get time to resolve the excel queries after office hours but I try my best to moderate the forum closely. I visit the forum 2-3 times daily to approve the messages and to check everything is ok. Please let me know the improvements and changes you want to see in the forum. I would be happy to hear your feedback. Some key achievements of this forum in numbers: 1) In last 1.5 years, we have seen three people receiving the Microsoft MVP award from this forum. This is itself a big achievement for forum and I hope to see more deserving people get this award in future. 2) This is spam free forum and we hardly get spam messages. In last 5 years, 1000 members are banned for sending spam messages 3) We are 8400+ members with diversity in expertise, experience and location. 4) Over 7600+ topics discussed in five years Once again many thanks and congratulations to all of you. Keep posting and together we will achieve more milestones. Regards, Ayush Jain Microsoft MVP Forum Owner jainayus...@gmail.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- . -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
$$Excel-Macros$$ Re: Split text in cell
Hello, try also, =TRIM(SUBSTITUTE(MID(A2,FIND( ,TRIM(A2) ),LEN(A2)),TRIM(RIGHT(SUBSTITUTE(TRIM(A2), ,REPT( ,99)),99)),)) On Wednesday, March 21, 2012 12:00:09 PM UTC+3, nike wrote: Hi Team, How to remove a words left text and right text of reference Please refer attachment. Thanks in advance. On Wednesday, March 21, 2012 12:00:09 PM UTC+3, nike wrote: Hi Team, How to remove a words left text and right text of reference Please refer attachment. Thanks in advance. On Wednesday, March 21, 2012 12:00:09 PM UTC+3, nike wrote: Hi Team, How to remove a words left text and right text of reference Please refer attachment. Thanks in advance. -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
$$Excel-Macros$$ Insert rows with missing data
I have data like so: Country Year AgeGroup Time Count A 05 10-6mo 10 A 05 16-12mo5 - B 05 10-6mo 12 B 05 112-18mo 4 B 05 118-24mo 75 (I added the - dividing line just to help visualize things ... it's not actually in the spreadsheet.) Every unique group - defined by unique concatenation of Country+Year+AgeGroup should have four rows, one for each of these Time intervals: 0-6mo, 6-12mo, 12-18mo, and 18-24mo. The group defined by CountryA+05+1 is missing rows for 12-18 and 18-24. The group defined by CountryB+05+1 is missing rows for 6-12. How can I automatically detect when a unique group has 4 rows, and then insert the missing number of rows (doesn't matter where I insert them), imputing the value for County, Year, AgeGroup, and Time (i.e., the Time interval(s) that are missing). Count should get a value of 0. So the code would build and add these three rows: Country Year AgeGroup Time Count A 05 112-18mo 0 A 05 118-24mo 0 B 05 16-12mo0 Any suggestions? -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
$$Excel-Macros$$ Re: words split from a cell - with formulas only Not for VBA code
This is a duplicate thread. There are couple of replies to your last thread. Check out that please. ___ HTH, Haseeb On Thursday, March 22, 2012 8:22:08 AM UTC+3, nike wrote: Please find the attachment of my criteria. Please refer column A and i want result in Column 'B what I need like that. Thanks in advance Simha 970-309-1552 -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
RE: $$Excel-Macros$$ Split text in cell
=MID(A2,FIND( ,A2,1)+1,MAX(--(MID(A2,ROW(INDIRECT(1:LEN(A2))),1)= )*ROW(INDIRECT(1:LEN(A2-FIND( ,A2,1)) with CSE From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Ms-Exl-Learner . Sent: Mar/Thu/2012 11:13 To: excel-macros@googlegroups.com Subject: Re: $$Excel-Macros$$ Split text in cell Another Approach Copy and paste the below formula in 2nd Row of any cell other than A2 cell and drag it below. =IF((LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2), ,)))1, TRIM(MID(TRIM(A2), FIND( ,TRIM(A2))+1, (FIND(^,SUBSTITUTE(TRIM(A2), ,^,(LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2), ,)-1)-FIND( ,TRIM(A2))+1)), ) Refer the attachment file for better understanding. On Wed, Mar 21, 2012 at 2:30 PM, NRao Mynampati ml.narasimha...@gmail.com wrote: Hi Team, How to remove a words left text and right text of reference Please refer attachment. Thanks in advance. -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ Excel formula help needed
genius Maries Great solution. works to perfection. thanks boss On Thu, Mar 22, 2012 at 3:56 PM, Maries talk2mar...@gmail.com wrote: HI, Please find the attached file of Revised one. Ignore Previous one. Regards, MARIES. On Thu, Mar 22, 2012 at 7:07 PM, Maries talk2mar...@gmail.com wrote: Hi, PFA. =IF(B11=,,CHOOSE(Sheet1!$B$2,FML!B5,SIC!B5,GCB!B5,GGBL!B5)) Regards, MARIES On Thu, Mar 22, 2012 at 6:00 PM, hilary lomotey resp...@gmail.comwrote: PERFECT, exactly what i needed. tx so much. sorry i didnt mention this earlier in my mail, how do i handle a situation where you have you have figures under those years, how do the figures manipulate them with formulas for the right figures to appear under the respective years On Thu, Mar 22, 2012 at 1:13 PM, Maries talk2mar...@gmail.com wrote: HI, PFA. Regards, MARIES. On Thu, Mar 22, 2012 at 4:40 PM, hilary lomotey resp...@gmail.comwrote: Hi Oxford Excel Gurus In the fill attached sheet PL i have been able to write a formula that can show how many number of years i want to view the report ie if i want to see 5 years report i will select from the drop down, and so forth. and the number o years specified will show In the 2nd sheet ie PL (2) I have also written a formula to view which year the report should begin from say year 2007 , or 2008 etc. the problem is how to combine these two formulae into one when i can have a situation where i can select a particular year to view the report and specify how many years from there to view the report. kindly help with a formula. tx -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss.
RE: $$Excel-Macros$$ कविता: अपने Excel-Macro ग्रुप का उम्र,अब 5 साल हो गया.****** FIVE YEARS COMPLETION******THANK YOU******
Beautiful :) I hardly understand it, but I hear the poetry in it when read by Google Translate's screen reader, and in the rough translation. Thank YOU Noorain for all you do here, and for being our Poet in Residence; our poet laureate. From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of NOORAIN ANSARI Sent: Thursday, March 22, 2012 11:42 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ कविता: अपने Excel-Macro ग्रुप का उम्र,अब 5 साल हो गया.** FIVE YEARS COMPLETION**THANK YOU** जहाँ Excel के दीवानों की, है बहूत घनी आबादी. जहाँ Question,Answer करने की,है खूब आज़ादी. जहाँ के पोस्ट को follow कर-कर के,कितनो का Career खुशहाल हो गया. दोस्तों उस अपने Excel-Macro ग्रुप का उम्र, अब 5 साल हो गया. चाहे सुबह हो या शाम, चाहे दिन हो या रात. हर समय होती है यहाँ पोस्टिंग की बरसात. ये उपवन सजा है दोस्तों, कई Talented फूलों से. यहाँ शीत युद्ध चलती है अक्सर Excel के फर्मुलों से. Logic की खेती होती है यहाँ solutions के खलिहानों में. यहाँ मदद की होड़ मची रहती है EXCEL के दिवानो में.. जिसने पा लिया साथ इस ग्रुप का, समझो निहाल हो गया. दोस्तों अपने Excel-Macro ग्रुप का उम्र अब 5 साल हो गया. Haseeb,SAM,DON,Ashish जैसी हस्ती यहाँ पे बसती है. Man of BT Krishna के solution को दुनिया तरसती है. ASA,Rajan,Dilip,Maries सब के सब कोहिनूर है. Venkat,Sunny,Abhishekh भी कम नहीं मशहूर है. और भी यहाँ बहूत सारी शख्सियत है निराली. Ayush बाबू करते है इस बगीया की रखवाली. ये forum अब मिशाल नहीं रहा, बल्कि बेमिशाल हो गया. दोस्तों अपने Excel-Macro ग्रुप का उम्र अब 5 साल हो गया. -- Thanks regards, Noorain Ansari http://noorainansari.com/ http://excelmacroworld.blogspot.com/ http://excelmacroworld.blogspot.com/ -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ Need a small help, related to the Date and Time macro.....
Thanks @ dguillett1. On Thu, Mar 22, 2012 at 6:36 PM, dguillett1 dguille...@gmail.com wrote: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Count 1 _ Or Target.Row 2 Then Exit Sub If Target.Column = 1 Or Target.Column = 3 Then If Len(Application.Trim(Target)) 0 Then _ Target.Offset(, 1) = Now End If End Sub Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com *From:* Indrajit $nai talk2indra...@gmail.com *Sent:* Thursday, March 22, 2012 1:23 AM *To:* excel-macros@googlegroups.com *Subject:* Re: $$Excel-Macros$$ Need a small help, related to the Date and Time macro. @ dguillett1, PFA for your reference. On Thu, Mar 22, 2012 at 4:28 AM, dguillett1 dguille...@gmail.com wrote: Without seeing what you want with an example I can’t help Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com *From:* Indrajit $nai talk2indra...@gmail.com *Sent:* Wednesday, March 21, 2012 5:21 PM *To:* excel-macros@googlegroups.com *Subject:* Re: $$Excel-Macros$$ Need a small help, related to the Date and Time macro. Thanks @ Ranjan, but I don't track it in MS-Access File, can you incorporate this codes for MS-Excel? Thanks @ dguillett1, but I'm not able to use the same code for Logout time tracking in a different column, can you please help me once again? @ Abdulgani Shaikh, I have attached the same file in .xls format. On Wed, Mar 21, 2012 at 11:12 PM, Rajan_Verma rajanverma1...@gmail.comwrote: It’s a old post on this group ,I have created a small tool to track punch in and punch out Time , hope it can help. ‘=** ** Hi Lokesh, I am attaching Two files here.. 1). Save Access file at Shared Location. 2). In Code Module Replace *Thisworkbook.Path* with shared Path 3). Fill *AllEmpID* table in *Database* with EMPID for validation. ObjConnection.ConnectionString = Provider=Microsoft.ACE.OLEDB.12.0;Data Source= *ThisWorkbook.Path* \table.accdb ; ‘=** ** Regards Rajan *From:* excel-macros@googlegroups.com [mailto: excel-macros@googlegroups.com] *On Behalf Of *Indrajit $nai *Sent:* Mar/Wed/2012 03:17 *To:* excel-macros@googlegroups.com *Subject:* $$Excel-Macros$$ Need a small help, related to the Date and Time macro. Hi All, Need a small help from you all. I have attached a file with this mail. Now if you open the file you can see there is a button named Login, if you click on it, it will take the system time and date. There are two columns available, Name (Column A) and Login Time (Column B), now I want if the respective Column A is blank then it will not put the date and time in Column B, if there is a name in column A then only date and time will be captured in the column B, other wise it will give me a error message. If you have the code for the same then please provide me. Thanks in advance. -- Indrajit talk2indra...@gmail.com Disclaimer: This electronic message and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you are not the intended recipient you are hereby notified that any disclosure, copying, distribution or taking any action in reliance on the contents of this information is strictly prohibited and may be unlawful. -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in
RE: $$Excel-Macros$$ words split from a cell - with formulas only Not for VBA code
Hello Simha/NRao, All but the first and last words: =MID(TRIM($A2),FIND( ,TRIM($A2))+1,FIND(CHAR(1),SUBSTITUTE(TRIM($A2), ,CHAR(1),LEN(TRIM($A2))-LEN(SUBSTITUTE($A2, ,-FIND( ,TRIM($A2))-1) The formula ignores leading and trailing spaces, and also won't return leading and trailing spaces if there were extra spaces after the first or before the last word. Asa From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of NRao Mynampati Sent: Wednesday, March 21, 2012 10:22 PM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ words split from a cell - with formulas only Not for VBA code Please find the attachment of my criteria. Please refer column A and i want result in Column 'B what I need like that. Thanks in advance Simha 970-309-1552 -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
RE: $$Excel-Macros$$ words split from a cell - with formulas only Not for VBA code
Hi again; I saw you asked for a non-VBA solution in your subject and assumed
none had been posted yet,
but I see that three such solutions were already posted.
Here they are reiterated for your reference:
Complements of Maries:
=MID(A2,FIND( ,A2,1)+1,LEN(A2)-MATCH(
,LEFT(RIGHT(A2,{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15}),1),0)-FIND( ,A2,1))
Complements of Ms-Exl-Learner:
=IF((LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2), ,)))1,
TRIM(MID(TRIM(A2),
FIND( ,TRIM(A2))+1,
(FIND(^,SUBSTITUTE(TRIM(A2),
,^,(LEN(TRIM(A2))-LEN(SUBSTITUTE(TRIM(A2), ,)-1)-FIND(
,TRIM(A2))+1)),
)
Complements of Rajan:
=MID(A2,FIND( ,A2,1)+1,MAX(--(MID(A2,ROW(INDIRECT(1:LEN(A2))),1)=
)*ROW(INDIRECT(1:LEN(A2-FIND( ,A2,1))
with CSE
Mine:
=MID(TRIM($A2),FIND( ,TRIM($A2))+1,FIND(CHAR(1),SUBSTITUTE(TRIM($A2),
,CHAR(1),LEN(TRIM($A2))-LEN(SUBSTITUTE($A2, ,-FIND(
,TRIM($A2))-1)
Not sure that they all give the exact same result.
Asa
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of Asa Rossoff
Sent: Thursday, March 22, 2012 4:00 PM
To: excel-macros@googlegroups.com
Subject: RE: $$Excel-Macros$$ words split from a cell - with formulas only
Not for VBA code
Hello Simha/NRao,
All but the first and last words:
=MID(TRIM($A2),FIND( ,TRIM($A2))+1,FIND(CHAR(1),SUBSTITUTE(TRIM($A2),
,CHAR(1),LEN(TRIM($A2))-LEN(SUBSTITUTE($A2, ,-FIND(
,TRIM($A2))-1)
The formula ignores leading and trailing spaces, and also won't return
leading and trailing spaces if there were extra spaces after the first or
before the last word.
Asa
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of NRao Mynampati
Sent: Wednesday, March 21, 2012 10:22 PM
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ words split from a cell - with formulas only Not
for VBA code
Please find the attachment of my criteria.
Please refer column A and i want result in Column 'B what I need like
that.
Thanks in advance
Simha
970-309-1552
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
--
To post to this group, send email to excel-macros@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please
Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will
not get quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security
measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
--
To post to this group, send email to excel-macros@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
--
To post to this group, send email to excel-macros@googlegroups.com
RE: $$Excel-Macros$$ Re: Help to find position of a particular number in a given data set
Hi Atul, Not as tidy as Haseeb's response, But here is one solution: In C2: =IF(ROW(C$2)=ROW(),LARGE($B$2:$B$11,1),LARGE($B$2:$B$11,ROWS($B$2:$B$11)-COU NTIF($B$2:$B$11,INDEX(C$2:C$11,ROW()-2))+1)) In D2: =MATCH($B2,$C$2:$C$11,-1) Highlight and copy down. Your ranking is in column D. Column C is a helper column that just contains sorted rates without any duplicates. Column D finds the position of the current rate in that list, giving a ranking that fits your specifications. Asa From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Atul Patel Sent: Thursday, March 22, 2012 6:49 AM To: Anand Kumar Cc: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Re: Help to find position of a particular number in a given data set My apology! I am overseas. Just email reply will be ok How ever if you want to dicuss on this I can call you too Pl provide your contact. Sent from my iPhone On 2012-03-22, at 9:27, Anand Kumar anand...@gmail.com wrote: with an improper contact number you expect me to reach you??? On Thu, Mar 22, 2012 at 6:42 PM, Atul Patel atul_pate...@yahoo.com wrote: Hi Anand, Thank you for your response.Please refer to the calculation below. For repetitive items,rank formula removes subsequent rank for example 11 % rank 3 is not assigned due to multiple entry for the same standing. I want to ignore repetitive occurrences of rates and decide first, Second third highest rates and so on. For example you want to know position of 9%, the solution I am looking for should give 4th highest rate while the rank function gives rank 5. Please provide new solution if possible Thanks once again Fin. Inst. Rates in % Rank A 10 4 B 11 2 C 12 1 D 11 2 E 6 10 F 9 5 G 8 7 H 8 7 I 9 5 J 7 9 Atul Patel H: 905 tel:905%C2%A0785%208324 785 8324 C: 647 898 4074 tel:647%20898%204074 atul_pate...@yahoo.com Good to know you ! Sent from my iPhone On 2012-03-21, at 21:47, Anand Kumar anand...@gmail.com wrote: Hi Atul, Rank function seems to work perfectly fine here Copied the name of Institutions to A column and percentages to column B in C2 the formula was =RANK(B2,$B$2:$B$11,0) now drag the formula till C11 you get the desired result. Warm regards, Anand Kumar On Wednesday, 21 March 2012 02:54:21 UTC+5:30, Atul wrote: Hi, I need help to find position of a particular number in a given data set. Please refer to the table below. Fin. Inst. Rates in % A 10 B 11 C 12 D 11 E 6 F 9 G 8 H 8 I 9 J 7 In the above example, Rates are given for each financial institution. In descending order,the rate 12% is at the 1st position,11% is 2nd highest and so on. While using function LARGE or SMALL, we need to assign kth position to get the required result. I need to find (kth) position of given rate from the list, ignoring the duplicate items. Going back to the above example, 11% will be the second highest rate, 10% being the third highest rate and so on. Please let me know the function or combination of functions. Thank you for your help and time. Regards, Atul Patel H: 905 785 8324 C: 647 898 4074 atul_pate...@yahoo.com Good to know you ! Atul Patel H: 905 785 8324 C: 647 898 4074 atul_pate...@yahoo.com Good to know you ! -- Warm regards, Anand Kumar 08800710233 http://anandydr.googlepages.com/ There are 10 kinds of people: Those who understand binary and those who don't. -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you
$$Excel-Macros$$ Simple VBA to Fill in blanks
Hi, I'm nearly brain dead (tired) and can't think straight. I have a simple issue. A macro to fill in blank values if found with the value from the cell above. For example 101 1015 Would fill the blank rows with 101 until the next number 1015 is found, then it would use 1015 until another number... A file with sample data is attached. Thanks in advance. -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com Book1.xls Description: MS-Excel spreadsheet
RE: $$Excel-Macros$$ Arry Formula help
Hi Avinash, The problem is that you are use massive ranges -- entire columns, which is many millions of cells. I see you already have a definied name (rptList) that is a dynamic range for the data on your txt List sheet. You could just using that named range in your formulas would pretty much resolve your speed issues. However, you may want to use Excel 2007/2010's Table feature and make your data tables official Excel Tables. To do so, highlight each table of data, including the headers, and choose INSERT|TablesTable. Then go to FORMULAS|Defined NamesName Manager and rename the tables from Table1, Table2, etc. to suit (perhaps tbl_txtList, tbl_UserNames, etc.) Tables are automatically dynamic ranges, and Excel can handle them faster and more efficiently than other dynamic ranges, and it can sort and filter them more efficiently. Tables also give you structured references, which allow you to refer to tables, columns, headers, total rows, etc. by name. See https://office.microsoft.com/en-us/excel-help/using-structured-references-wi th-excel-tables-HA010155686.aspx for all the details.. but here are some examples for your file: in Setts!E8: =vbaVlookup(D2,tbl_txtList,3,A) in Setts!F8: =VLOOKUP(E8,tbl_txtList[[Report Key]:[Report]],3,FALSE) in Setts!A2: =IFERROR(MATCH([@UserName],[UserName],0),) in 'txt List'!A2: =MATCH([@Responsibility],tbl_UserNames[UserName],0) in 'txt List'!C2: =[@UserMap]--SUMPRODUCT(--([Responsibility]:[@Responsibility]=[@Responsi bility])) or even more efficient, for 'txt List'!C2: =[@UserMap]--COUNTIF([Responsibility]:[@Responsibility],[@Responsibility ]) in 'txt List'!E2: =MATCH([@Report],[Report],0) Note that Excel will write most structured references for you if you just select cells as normal while entering formulas. Other improvements are possible. To get the full advantage of the maintainability of named references in your tables, it's better to use the INDEX/MATCH combination than VLOOKUP so you don't have to refer to columns by number anywhere. It is possible to calculate the relative column number with COLUMNS(), but you may as well just use index/match. Asa From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Avinash Sent: Thursday, March 22, 2012 9:57 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Arry Formula help Dear Experts, please open attached excel sheet and go to the Setts sub sheet for better understanding. basically i want to perform multi value lookup for the specific user. and to get this thing done i tried to Google it. now i got the VBA code for the multivalue lookup but it is taking too much time to give me the results. can anyone please help me with this and tell me how it works ( there is a VBA code in the attached file) please help me and thanks Don and Noorain for all your support. and those formulas and codes which you have given me are very very very very usefull in my work thnx a ton guys. Regards, Avinash -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ Simple VBA to Fill in blanks
Dear Jim, Please try it.. Sub Fill_Blanks() Cells.SpecialCells(xlBlanks).Delete End Sub -- Thanks regards, Noorain Ansari *http://noorainansari.com/* *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ On Fri, Mar 23, 2012 at 7:04 AM, Jim Schug sch...@gmail.com wrote: Hi, I'm nearly brain dead (tired) and can't think straight. I have a simple issue. A macro to fill in blank values if found with the value from the cell above. For example 101 1015 Would fill the blank rows with 101 until the next number 1015 is found, then it would use 1015 until another number... A file with sample data is attached. Thanks in advance. -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ कविता: अपने Excel-Macro ग्रुप का उम्र,अब 5 साल हो गया.****** FIVE YEARS COMPLETION******THANK YOU******
superb noorain bhai. 2012/3/22 NOORAIN ANSARI noorain.ans...@gmail.com जहाँ Excel के दीवानों की, है बहूत घनी आबादी. जहाँ Question,Answer करने की,है खूब आज़ादी. जहाँ के पोस्ट को follow कर-कर के,कितनो का Career खुशहाल हो गया. दोस्तों उस अपने Excel-Macro ग्रुप का उम्र, अब 5 साल हो गया. चाहे सुबह हो या शाम, चाहे दिन हो या रात. हर समय होती है यहाँ पोस्टिंग की बरसात. ये उपवन सजा है दोस्तों, कई Talented फूलों से. यहाँ शीत युद्ध चलती है अक्सर Excel के फर्मुलों से. Logic की खेती होती है यहाँ solutions के खलिहानों में. यहाँ मदद की होड़ मची रहती है EXCEL के दिवानो में.. जिसने पा लिया साथ इस ग्रुप का, समझो निहाल हो गया. दोस्तों अपने Excel-Macro ग्रुप का उम्र अब 5 साल हो गया. *Haseeb,SAM,DON,Ashish* जैसी हस्ती यहाँ पे बसती है. Man of BT *Krishna* के solution को दुनिया तरसती है. *ASA,Rajan,Dilip,Maries* सब के सब कोहिनूर है. *Venkat,Sunny,Abhishekh* भी कम नहीं मशहूर है. और भी यहाँ बहूत सारी शख्सियत है निराली. *Ayush* बाबू करते है इस बगीया की रखवाली. ये forum अब मिशाल नहीं रहा, बल्कि बेमिशाल हो गया. दोस्तों अपने Excel-Macro ग्रुप का उम्र अब 5 साल हो गया. -- Thanks regards, Noorain Ansari ** http://excelmacroworld.blogspot.com/*http://noorainansari.com/* *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ कविता: अपने Excel-Macro ग्रुप का उम्र,अब 5 साल हो गया.****** FIVE YEARS COMPLETION******THANK YOU******
Dear Noorain, Superb *Best Regards,* *Venkat * *Chennai* 2012/3/23 NOORAIN ANSARI noorain.ans...@gmail.com जहाँ Excel के दीवानों की, है बहूत घनी आबादी. जहाँ Question,Answer करने की,है खूब आज़ादी. जहाँ के पोस्ट को follow कर-कर के,कितनो का Career खुशहाल हो गया. दोस्तों उस अपने Excel-Macro ग्रुप का उम्र, अब 5 साल हो गया. चाहे सुबह हो या शाम, चाहे दिन हो या रात. हर समय होती है यहाँ पोस्टिंग की बरसात. ये उपवन सजा है दोस्तों, कई Talented फूलों से. यहाँ शीत युद्ध चलती है अक्सर Excel के फर्मुलों से. Logic की खेती होती है यहाँ solutions के खलिहानों में. यहाँ मदद की होड़ मची रहती है EXCEL के दिवानो में.. जिसने पा लिया साथ इस ग्रुप का, समझो निहाल हो गया. दोस्तों अपने Excel-Macro ग्रुप का उम्र अब 5 साल हो गया. *Haseeb,SAM,DON,Ashish* जैसी हस्ती यहाँ पे बसती है. Man of BT *Krishna* के solution को दुनिया तरसती है. *ASA,Rajan,Dilip,Maries* सब के सब कोहिनूर है. *Venkat,Sunny,Abhishekh* भी कम नहीं मशहूर है. और भी यहाँ बहूत सारी शख्सियत है निराली. *Ayush* बाबू करते है इस बगीया की रखवाली. ये forum अब मिशाल नहीं रहा, बल्कि बेमिशाल हो गया. दोस्तों अपने Excel-Macro ग्रुप का उम्र अब 5 साल हो गया. -- Thanks regards, Noorain Ansari ** http://excelmacroworld.blogspot.com/*http://noorainansari.com/* *http://excelmacroworld.blogspot.com/*http://excelmacroworld.blogspot.com/ -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- * * * * * * -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
Re: $$Excel-Macros$$ macro vba coding for html file search and paste the path as hyperlink
Hi , See below link.. http://www.pcreview.co.uk/forums/re-search-html-access-vba-t3992723.html *Best Regards,* *Venkat * *Chennai* * * On Thu, Mar 22, 2012 at 12:13 AM, Raj Mahapatra rajafs...@gmail.com wrote: hi all i am looking for some vba codes which will search html files (bases file names in the sheet) from specified locations and paste the path of the same file as hyperlink in the sheet Thanks -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com -- * * * * -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. -- To post to this group, send email to excel-macros@googlegroups.com
