$$Excel-Macros$$ Default entry in the Cells
hello Group I am creating a table where people have to respond to questions in Yes, No, N/A. there are nearly 3K question I want that all the questions to be answered even in case of following circumstances -if someone skips any question the Cell should contain N/A by default. -if someone deletes the answer the cell should still have N/A as default in a nutshell i do not want any cell where we have to answer these questions to remain blank. -- BR's Pulkit Goyal -- 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 To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
Re: $$Excel-Macros$$ 1. paste standard content to all sheets 2. paste sheet name to a specific cell
Hi Subu
Deal with item 2 first. On your master sheet put the following
formula into cell B11. This will show the current sheetname even if
it is changed.
=MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255)
Now, To copy the content of the blank sheets use the following (i
assume that the non-blank sheets will all have something in cell B11 -
if they do not make sure something is there!).
Sub copysheets()
Dim sh As Worksheet
For Each sh In Worksheets
If sh.Cells(11, "B") = "" Then
Worksheets("Sheet5").Cells.Copy _
Destination:=sh.Cells
End If
Next sh
End Sub
Best regards
David Grugeon
On 27/05/2012, .. wrote:
>
>
> Dear Rajan
>
>
>
> Thanks for the replyand...sorry for the delayed comment from me
>
> ok. I agree that you know intloop MUCH better than I do
>
> However your program is NOT working on my sp sheet
>
> The VBA starts copying OVER sheets with existing data and that is a problem
>
> for me
>
>
>
>
> regards
>
> Subu
>
>
>
>
>
>
>
> On Thursday, May 17, 2012 5:05:36 PM UTC+4, Rajan_Verma wrote:
>>
>> Hi Sabu,
>>
>> Intloop can not be 1 if your workbook have at least one worksheet with
>> data , IntLoop is actually starting From first Blank Worksheets Index,
>>
>> Suppose if you have 5 filled workhseets then 10 blank worksheets, IntLoop
>>
>> will be start from 6 and end with 10
>>
>>
>>
>> * *
>>
>> *Regards*
>>
>> *Rajan verma*
>>
>> *+91 7838100659 [IM-Gtalk]*
>>
>>
>>
>> *From:* excel-macros@googlegroups.com [mailto:
>> excel-macros@googlegroups.com] *On Behalf Of *..
>> *Sent:* 17 May 2012 1:54
>> *To:* excel-macros@googlegroups.com
>> *Subject:* Re: $$Excel-Macros$$ 1. paste standard content to all sheets
>> 2. paste sheet name to a specific cell
>>
>>
>>
>> From : Subu
>>
>>
>>
>> Dear Rajan other list members
>>
>> I'm a complete novice in VBA. Still, I think there is a problem in this
>> reply / code below
>>
>>- When you reach this statement "...Worksheets(intLoop).Paste...",
>>
>>Intloop is still = 1. i.e. value of Intloop is still 1. So
>>Worksheets(intLoop) is the first sheet
>>- So the program starts pasting from the first sheet ...which happens
>>to be a old sheet with data
>>- So Intloop has to be changed / reset to the first blank sheet
>>- How does one do that
>>
>> best regards
>>
>> Subu
>>
>>
>>
>>
>> On Wednesday, May 16, 2012 5:34:25 PM UTC+4, Rajan_Verma wrote:
>>
>> Try this ::
>>
>>
>>
>>
>>
>> Sub CopyPaste()
>>
>>
>>
>> Dim wkssheet As Worksheet
>>
>> Dim IntIndex As Integer
>>
>> Dim intLoop As Integer
>>
>> Dim StrSrcName As String
>>
>> For Each wkssheet In ThisWorkbook.Worksheets
>>
>> If wkssheet.UsedRange.Cells.Count > 1 Then
>>
>> IntIndex = wkssheet.Index
>>
>> Exit For
>>
>> End If
>>
>> Next wkssheet
>>
>>
>>
>> StrSrcName = InputBox("Please enter the Source Sheet Name") ' I will
>> suggest to COpy paste the sheet Name
>>
>> Worksheets(StrSrcName).UsedRange.Copy
>>
>> For intLoop = IntIndex To Worksheets.Count
>>
>> Worksheets(intLoop).Paste
>>
>> Worksheets(intLoop).Range("B11") = Worksheets(intLoop).Name
>>
>> Next
>>
>> Application.CutCopyMode = xlCopy
>>
>> End Sub
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> * *
>>
>> *Regards*
>>
>> *Rajan verma*
>>
>> *+91 7838100659 [IM-Gtalk]*
>>
>>
>>
>> *From:* excel-macros@googlegroups.com [mailto:
>> excel-macros@googlegroups.com] *On Behalf Of *..
>> *Sent:* 16 May 2012 6:22
>> *To:* excel-macros@googlegroups.com
>> *Subject:* $$Excel-Macros$$ 1. paste standard content to all sheets 2.
>> paste sheet name to a specific cell
>>
>>
>>
>> Dear Members
>>
>> I use XL 2003, Win XP. I have the following *request*
>>
>> 1. I have a workbook with approx 170 sheets
>> 2. The first 90 or so sheets have some content in them (call them the old
>>
>> sheets), the remaining 80 or so are blank as of now (call them new
>> sheets)
>> 3. I wish to take the content from one of the old sheets ...say sheet # 70
>>
>> and paste it to all the blank / new sheets [same source , paste on all
>> *blank* sheets, paste only on blank sgeets]
>> 4. Is there an easy way , with or without VB
>>
>> 5. Once (4) above is accomplished,
>> 6. I need to past the sheet name of the *new* sheets - only new sheets on
>>
>> to cell B 11 of each of the new sheet. i.e. IF the name on sheet #100 is
>> Delhi, I wish to copy the Text "Delhi" (without the quotes) to Cell B 11
>>
>> of sheet # 100
>>
>> any help towards the above would be gratefully acknowledged
>>
>>
>> thanks in adv. and regards
>>
>> Subu
>>
>> --
>> 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 regard
$$Excel-Macros$$ latest data to be highlighted
Dear Friends, Please help me with a formula or any other solution. I wants to remove the old & keep only latest dated data from the attached sample file. The file contains history of cabs going for PM service to WS on particular days. I wants to get when the last time it visited WS. As of now I am taking count of cab in table & also finding the number of time ( 1st, 2nd, 3rd ) it visited WS. Thus if cab visited 3 times I have to remove its 1st & 2nd visit, like wise I need to do for all such cabs...but this method is lengthy... Can you please help me with some formula where in at least I can get the latest dated data highlighted? Best Regards, Amit Disclaimer: This message and its attachments contain confidential information and may also contain legally privileged information. This message is intended solely for the named addressee. If you are not the addressee indicated in this message (or authorized to receive for addressee), you may not copy or deliver any part of this message or its attachments to anyone or use any part of this message or its attachments. Rather, you should permanently delete this message and its attachments (and all copies) from your system and kindly notify the sender by reply e-mail. Any content of this message and its attachments that does not relate to the official business of Meru Cab Company Pvt. Ltd. must be taken not to have been sent or endorsed by any of them. Email communications are not private and no warranty is made that e-mail communications are timely, secure or free from computer virus or other defect. -- 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 To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com Latest PM_service.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
Re: $$Excel-Macros$$ 1. paste standard content to all sheets 2. paste sheet name to a specific cell
Dear Rajan
Thanks for the replyand...sorry for the delayed comment from me
ok. I agree that you know intloop MUCH better than I do
However your program is NOT working on my sp sheet
The VBA starts copying OVER sheets with existing data and that is a problem
for me
regards
Subu
On Thursday, May 17, 2012 5:05:36 PM UTC+4, Rajan_Verma wrote:
>
> Hi Sabu,
>
> Intloop can not be 1 if your workbook have at least one worksheet with
> data , IntLoop is actually starting From first Blank Worksheets Index,
>
> Suppose if you have 5 filled workhseets then 10 blank worksheets, IntLoop
> will be start from 6 and end with 10
>
>
>
> * *
>
> *Regards*
>
> *Rajan verma*
>
> *+91 7838100659 [IM-Gtalk]*
>
>
>
> *From:* excel-macros@googlegroups.com [mailto:
> excel-macros@googlegroups.com] *On Behalf Of *..
> *Sent:* 17 May 2012 1:54
> *To:* excel-macros@googlegroups.com
> *Subject:* Re: $$Excel-Macros$$ 1. paste standard content to all sheets
> 2. paste sheet name to a specific cell
>
>
>
> From : Subu
>
>
>
> Dear Rajan other list members
>
> I'm a complete novice in VBA. Still, I think there is a problem in this
> reply / code below
>
>- When you reach this statement "...Worksheets(intLoop).Paste...",
>Intloop is still = 1. i.e. value of Intloop is still 1. So
>Worksheets(intLoop) is the first sheet
>- So the program starts pasting from the first sheet ...which happens
>to be a old sheet with data
>- So Intloop has to be changed / reset to the first blank sheet
>- How does one do that
>
> best regards
>
> Subu
>
>
>
>
> On Wednesday, May 16, 2012 5:34:25 PM UTC+4, Rajan_Verma wrote:
>
> Try this ::
>
>
>
>
>
> Sub CopyPaste()
>
>
>
> Dim wkssheet As Worksheet
>
> Dim IntIndex As Integer
>
> Dim intLoop As Integer
>
> Dim StrSrcName As String
>
> For Each wkssheet In ThisWorkbook.Worksheets
>
> If wkssheet.UsedRange.Cells.Count > 1 Then
>
> IntIndex = wkssheet.Index
>
> Exit For
>
> End If
>
> Next wkssheet
>
>
>
> StrSrcName = InputBox("Please enter the Source Sheet Name") ' I will
> suggest to COpy paste the sheet Name
>
> Worksheets(StrSrcName).UsedRange.Copy
>
> For intLoop = IntIndex To Worksheets.Count
>
> Worksheets(intLoop).Paste
>
> Worksheets(intLoop).Range("B11") = Worksheets(intLoop).Name
>
> Next
>
> Application.CutCopyMode = xlCopy
>
> End Sub
>
>
>
>
>
>
>
>
>
> * *
>
> *Regards*
>
> *Rajan verma*
>
> *+91 7838100659 [IM-Gtalk]*
>
>
>
> *From:* excel-macros@googlegroups.com [mailto:
> excel-macros@googlegroups.com] *On Behalf Of *..
> *Sent:* 16 May 2012 6:22
> *To:* excel-macros@googlegroups.com
> *Subject:* $$Excel-Macros$$ 1. paste standard content to all sheets 2.
> paste sheet name to a specific cell
>
>
>
> Dear Members
>
> I use XL 2003, Win XP. I have the following *request*
>
> 1. I have a workbook with approx 170 sheets
> 2. The first 90 or so sheets have some content in them (call them the old
> sheets), the remaining 80 or so are blank as of now (call them new sheets)
> 3. I wish to take the content from one of the old sheets ...say sheet # 70
> and paste it to all the blank / new sheets [same source , paste on all
> *blank* sheets, paste only on blank sgeets]
> 4. Is there an easy way , with or without VB
>
> 5. Once (4) above is accomplished,
> 6. I need to past the sheet name of the *new* sheets - only new sheets on
> to cell B 11 of each of the new sheet. i.e. IF the name on sheet #100 is
> Delhi, I wish to copy the Text "Delhi" (without the quotes) to Cell B 11
> of sheet # 100
>
> any help towards the above would be gratefully acknowledged
>
>
> thanks in adv. and regards
>
> Subu
>
> --
> 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 ans
Re: $$Excel-Macros$$ 2 Values in the same cell
Thanks Rajan for your prompt reply.
On Sat, May 26, 2012 at 2:48 PM, Rajan_Verma wrote:
> These lines is just to adjust values within cells
>
> ** **
>
> If Target Cell is rngOutput.Offset(lngLoopCounter) then
>
> rngOutput.Offset(lngLoopCounter).Offset(-1, -1).Value =
> rngCell.Value Put the value in TopLeft cell Target Cell
>
> rngOutput.Offset(lngLoopCounter).Offset(1, 1).Value =
> rngCell.Value For Put the value in BottomRight Cell from Target Cell
>
> ** **
>
> * *
>
> *Regards*
>
> *Rajan verma*
>
> *+91 7838100659 [IM-Gtalk]*
>
> ** **
>
> *From:* excel-macros@googlegroups.com [mailto:
> excel-macros@googlegroups.com] *On Behalf Of *rao
> *Sent:* 26 May 2012 2:44
>
> *To:* excel-macros@googlegroups.com
> *Subject:* Re: $$Excel-Macros$$ 2 Values in the same cell
>
> ** **
>
> Hi ,
>
> I am not able to understand this part, could you please explain.
>
> rngOutput.Offset(lngLoopCounter).Offset(-1, -1).Value =
> rngCell.Value
>
> rngOutput.Offset(lngLoopCounter).Offset(1, 1).Value =
> rngCell.Value
>
>
>
> cheers,
>
> Prabhakar
>
> On Sat, May 26, 2012 at 2:41 PM, rao wrote:
>
> Thanks Buddy..I got...
>
> ** **
>
> ** **
>
> Cheers,
>
> Prabhakar
>
> ** **
>
> On Sat, May 26, 2012 at 2:03 PM, Rajan_Verma
> wrote:
>
> Only select one cell For Output
>
>
>
> * *
>
> *Regards*
>
> *Rajan verma*
>
> *+91 7838100659 [IM-Gtalk]*
>
>
>
> *From:* excel-macros@googlegroups.com [mailto:
> excel-macros@googlegroups.com] *On Behalf Of *rao
> *Sent:* 26 May 2012 1:46
>
>
> *To:* excel-macros@googlegroups.com
> *Subject:* Re: $$Excel-Macros$$ 2 Values in the same cell
>
>
>
> Thanks Rajan,
> I am seeing a runtime error while selecting the range for the
> "rngOutput"..Could you please advice on this.I have selected range from
> A1:C14
>
> *Set rngOutput = Application.InputBox("Please select Output Cells", , , ,
> , , , 8)*
>
>
>
> Cheers,
>
> Prabhakar
>
>
>
> On Wed, May 16, 2012 at 6:23 PM, Rajan_Verma
> wrote:
>
> *Are you looking for this*
>
>
>
> See the attached Sheet
>
>
>
> *Sub ManageThem()*
>
> **
>
> *Dim rngRange As Range*
>
> *Dim rngOutput As Range*
>
> *Dim rngCell As Range*
>
> *Dim lngLoopCounter As Long*
>
> *Dim rngBorders As Range*
>
> *Set rngRange = Application.InputBox("Please select value(s)", , , ,
> , , , 8)*
>
> *If rngRange.Columns.Count < 2 Or rngRange.Columns.Count > 2 Then*
>
> *MsgBox "Please Select Correct Range", vbInformation*
>
> *End*
>
> * End If*
>
> **
>
> *lngLoopCounter = 0*
>
> *Set rngOutput = Application.InputBox("Please select Output Cells", ,
> , , , , , 8)*
>
> **
>
> *For Each rngCell In rngRange*
>
> *If rngCell.Value <> "" Then*
>
> *rngOutput.Offset(lngLoopCounter).Offset(-1, -1).Value =
> rngCell.Value*
>
> *rngOutput.Offset(lngLoopCounter).Offset(1, 1).Value =
> rngCell.Value*
>
> *Set rngBorders =
> Range(rngOutput.Offset(lngLoopCounter).Offset(-1, -1),
> rngOutput.Offset(lngLoopCounter).Offset(1, 1))*
>
> *With rngBorders*
>
> *.Borders(xlEdgeLeft).Weight = xlThin*
>
> *.Borders(xlEdgeRight).Weight = xlThin*
>
> *.Borders(xlEdgeTop).Weight = xlThin*
>
> *.Borders(xlEdgeBottom).Weight = xlThin*
>
> *End With*
>
> *lngLoopCounter = lngLoopCounter + 3*
>
> **
>
> *Else*
>
> *End*
>
> *End If*
>
> *Next*
>
> **
>
> *End Sub*
>
> * *
>
> * *
>
> *Regards*
>
> *Rajan verma*
>
> *+91 7838100659 [IM-Gtalk]*
>
>
>
> *From:* excel-macros@googlegroups.com [mailto:
> excel-macros@googlegroups.com] *On Behalf Of *David Grugeon
> *Sent:* 16 May 2012 11:59
>
>
> *To:* excel-macros@googlegroups.com
> *Subject:* Re: $$Excel-Macros$$ 2 Values in the same cell
>
>
>
> The answer is "No" AFAICS
>
>
>
> However you could use two columns (one formatted left top and one right
> bottom). Put borders around the two but no border between them. If you
> want the gridlines to disappear you can give both cells a fill colour of
> white.
>
>
>
> Regards
>
> David Grugeon
>
> On 16 May 2012 05:44, joseph camill wrote:
>
>
>
> On Wed, May 16, 2012 at 1:08 AM, joseph camill
> wrote:
>
> Attached is an example
>
>
>
> On Wed, May 16, 2012 at 12:48 AM, wrote:
>
> Hi,
>
> Is it possible to concatenate two values in one cell and seperate the
> first value to top left and second value to bottom right of the same cell.
>
> For e.g. £25m and €12m both in cell a1. And
Re: $$Excel-Macros$$ doubt
Hi Don, I apologies,next time onwards i will try to use meaningful subject line..Thanks Don On Sat, May 26, 2012 at 5:49 PM, dguillett1 wrote: > In the future PLEASE try to use a meaningful subject line (doubt is not > meaningful) and then explain your problem in the posting as well as in the > attached file. > > Don Guillett > Microsoft MVP Excel > SalesAid Software > dguille...@gmail.com > > *From:* vijayajith VA > *Sent:* Saturday, May 26, 2012 5:44 AM > *To:* excel-macros@googlegroups.com > *Subject:* $$Excel-Macros$$ doubt > > Hi all, > > Please find the attached query..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 > > To unsubscribe, send a blank email to > excel-macros+unsubscr...@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 > > To unsubscribe, send a blank email to > excel-macros+unsubscr...@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 To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
Re: $$Excel-Macros$$ Copy Validation while splitting workbooks
see if it helps
Private Sub CommandButton2_Click()
Dim rng As Range
Dim i, j, c As Long
Dim wb As Workbook, srwb As Workbook
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Set wb = ThisWorkbook
Set rng = wb.Sheets(1).Range("F1:F" &
wb.Sheets(1).Range("f65356").End(xlUp).Row)
rng.AdvancedFilter Action:=xlFilterCopy, copytoRange:=Sheet1.Range("O1"),
Unique:=True
For i = 2 To Sheet1.Cells(Rows.Count, "O").End(xlUp).Row
On Error Resume Next
wb.Sheets(1).AutoFilterMode = False
wb.Sheets(1).Range("$A$1:$k$" &
wb.Sheets(1).Range("a65356").End(xlUp).Row).AutoFilter Field:=6,
Criteria1:=wb.Sheets(1).Cells(i, 15).Value
Set srwb = Workbooks.Add
wb.Sheets(1).Range("$A$1:$k$" &
wb.Sheets(1).Range("a65356").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy
Destination:=srwb.Sheets(1).Range("a1")
wb.Sheets("Validation").Copy after:=srwb.Sheets(1)
srwb.SaveAs ThisWorkbook.Path & Application.PathSeparator & Sheet1.Cells(i,
15).Value & ".xlsx"
srwb.Save
srwb.Close
Next i
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
On Sat, May 26, 2012 at 6:15 PM, Amol Jadhav wrote:
> Hi David,
>
> I was doing the same thing for last 3 months, but few days before I got
> macro from this group itself which save my lot time.
> Actully I have team of 60 members so its very difficult to distribute work
> and collate it back.
>
> Regards,
> Amol
> On 26 May 2012 18:01, David Grugeon wrote:
>
>> Hi Amol
>>
>> I have sometimes solved problems like this, by, rather than copying
>> part of the workbook, copying the whole workbook and then deleting the
>> parts i did not want in the copy. I do not know if this approach
>> would work for you.
>>
>> Regards
>>
>> David grugeon
>>
>> On 26/05/2012, Amol Jadhav wrote:
>> > Hi Rajan,
>> >
>> > Thanks for quick reply
>> >
>> > Some part of work is done. Still problems remain same like -- In
>> splitted
>> > files if you check validation is not working, If you check in main
>> > file range G:i there is validation and values comes from validation
>> sheet.
>> > I want same validation in splitted workbooks, so user can select
>> > appropriate option.
>> >
>> > Regards,
>> > Amol Jadhav
>> >
>> > On 26 May 2012 14:54, Rajan_Verma wrote:
>> >
>> >> Hi
>> >>
>> >> ** **
>> >>
>> >> Try this:
>> >>
>> >> ** **
>> >>
>> >> *Sub SplitAllbyCode()*
>> >>
>> >> * *
>> >>
>> >> *Dim rngRangeAs Range*
>> >>
>> >> *Dim wksSheetAs Worksheet*
>> >>
>> >> *Dim ArrUniqe*
>> >>
>> >> *Dim lngUniqeCount As Long*
>> >>
>> >> *Dim wbkNew As Workbook*
>> >>
>> >> * *
>> >>
>> >> *Set wksSheet = ThisWorkbook.Worksheets("Data")*
>> >>
>> >> *With wksSheet*
>> >>
>> >> *Set rngRange = Intersect(Range("rngStart").CurrentRegion,
>> >> Range("rngStart").CurrentRegion.Offset(2))*
>> >>
>> >> *Application.ScreenUpdating = False*
>> >>
>> >> *rngRange.Columns(6).Copy Range("rngRemoveDuplicate")*
>> >>
>> >> *Range("rngRemoveDuplicate").CurrentRegion.RemoveDuplicates 1*
>> >>
>> >> *ArrUniqe = Range("rngRemoveDuplicate").CurrentRegion*
>> >>
>> >> *For lngUniqeCount = LBound(ArrUniqe) To UBound(ArrUniqe)*
>> >>
>> >> *Range("rngStart").CurrentRegion.Rows(2).AutoFilter 6,
>> >> ArrUniqe(lngUniqeCount, 1)*
>> >>
>> >> *
>> >> Range("rngStart").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy*
>> >>
>> >> *Set wbkNew = Workbooks.Add*
>> >>
>> >> *wbkNew.Worksheets(1).Paste*
>> >>
>> >> *wbkNew.SaveAs ThisWorkbook.Path & "\" &
>> >> ArrUniqe(lngUniqeCount, 1)*
>> >>
>> >> *wbkNew.Close 1*
>> >>
>> >> *Next lngUniqeCount*
>> >>
>> >> *End With*
>> >>
>> >> *wksSheet.AutoFilterMode = False*
>> >>
>> >> *Application.ScreenUpdating = True*
>> >>
>> >> *Range("rngRemoveDuplicate").CurrentRegion.ClearContents*
>> >>
>> >> * MsgBox lngUniqeCount & " Files has been splited, Plase find
>> your
>> >> files at " & vbCrLf & ThisWorkbook.Path*
>> >>
>> >> *'Free Memory*
>> >>
>> >> *Set rngRange = Nothing*
>> >>
>> >> *Set wksSheet = Nothing*
>> >>
>> >> *Erase ArrUniqe*
>> >>
>> >> *Set wbkNew = Nothing*
>> >>
>> >> * *
>> >>
>> >> *End Sub*
>> >>
>> >> * *
>> >>
>> >> *See the post on
>> >> *
>> >>
>> http://excelpoweruser.blogspot.in/2012/05/split-data-into-multiple-workbook.html
>> >>
>> >>
>> >> ** **
>> >>
>> >> * *
>> >>
>> >> * *
>> >>
>> >> *Regards*
>> >>
>> >> *Rajan verma*
>> >>
>> >> *+91 7838100659 [IM-Gtalk]*
>> >>
>> >> ** **
>> >>
>> >> *From:* excel-macros@googlegroups.com [mailto:
>> >> excel-macros@googlegroups.com] *On Behalf Of *Amol Jadhav
>> >> *Sent:* 26 May 2012 2:18
>> >> *To:* excel-macros@googlegroups.com
>> >> *Subject:* $$Excel-Macros$$ Copy Validation while splitting
>> workbooks
>> >>
>> >> ** **
>> >>
>> >> Hi Experts,
>> >>
>> >>
>> >>
>> >> I want to make some changes in existing macro, Currently I have m
Re: $$Excel-Macros$$ Copy Validation while splitting workbooks
Hi David,
I was doing the same thing for last 3 months, but few days before I got
macro from this group itself which save my lot time.
Actully I have team of 60 members so its very difficult to distribute work
and collate it back.
Regards,
Amol
On 26 May 2012 18:01, David Grugeon wrote:
> Hi Amol
>
> I have sometimes solved problems like this, by, rather than copying
> part of the workbook, copying the whole workbook and then deleting the
> parts i did not want in the copy. I do not know if this approach
> would work for you.
>
> Regards
>
> David grugeon
>
> On 26/05/2012, Amol Jadhav wrote:
> > Hi Rajan,
> >
> > Thanks for quick reply
> >
> > Some part of work is done. Still problems remain same like -- In splitted
> > files if you check validation is not working, If you check in main
> > file range G:i there is validation and values comes from validation
> sheet.
> > I want same validation in splitted workbooks, so user can select
> > appropriate option.
> >
> > Regards,
> > Amol Jadhav
> >
> > On 26 May 2012 14:54, Rajan_Verma wrote:
> >
> >> Hi
> >>
> >> ** **
> >>
> >> Try this:
> >>
> >> ** **
> >>
> >> *Sub SplitAllbyCode()*
> >>
> >> * *
> >>
> >> *Dim rngRangeAs Range*
> >>
> >> *Dim wksSheetAs Worksheet*
> >>
> >> *Dim ArrUniqe*
> >>
> >> *Dim lngUniqeCount As Long*
> >>
> >> *Dim wbkNew As Workbook*
> >>
> >> * *
> >>
> >> *Set wksSheet = ThisWorkbook.Worksheets("Data")*
> >>
> >> *With wksSheet*
> >>
> >> *Set rngRange = Intersect(Range("rngStart").CurrentRegion,
> >> Range("rngStart").CurrentRegion.Offset(2))*
> >>
> >> *Application.ScreenUpdating = False*
> >>
> >> *rngRange.Columns(6).Copy Range("rngRemoveDuplicate")*
> >>
> >> *Range("rngRemoveDuplicate").CurrentRegion.RemoveDuplicates 1*
> >>
> >> *ArrUniqe = Range("rngRemoveDuplicate").CurrentRegion*
> >>
> >> *For lngUniqeCount = LBound(ArrUniqe) To UBound(ArrUniqe)*
> >>
> >> *Range("rngStart").CurrentRegion.Rows(2).AutoFilter 6,
> >> ArrUniqe(lngUniqeCount, 1)*
> >>
> >> *
> >> Range("rngStart").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy*
> >>
> >> *Set wbkNew = Workbooks.Add*
> >>
> >> *wbkNew.Worksheets(1).Paste*
> >>
> >> *wbkNew.SaveAs ThisWorkbook.Path & "\" &
> >> ArrUniqe(lngUniqeCount, 1)*
> >>
> >> *wbkNew.Close 1*
> >>
> >> *Next lngUniqeCount*
> >>
> >> *End With*
> >>
> >> *wksSheet.AutoFilterMode = False*
> >>
> >> *Application.ScreenUpdating = True*
> >>
> >> *Range("rngRemoveDuplicate").CurrentRegion.ClearContents*
> >>
> >> * MsgBox lngUniqeCount & " Files has been splited, Plase find
> your
> >> files at " & vbCrLf & ThisWorkbook.Path*
> >>
> >> *'Free Memory*
> >>
> >> *Set rngRange = Nothing*
> >>
> >> *Set wksSheet = Nothing*
> >>
> >> *Erase ArrUniqe*
> >>
> >> *Set wbkNew = Nothing*
> >>
> >> * *
> >>
> >> *End Sub*
> >>
> >> * *
> >>
> >> *See the post on
> >> *
> >>
> http://excelpoweruser.blogspot.in/2012/05/split-data-into-multiple-workbook.html
> >>
> >>
> >> ** **
> >>
> >> * *
> >>
> >> * *
> >>
> >> *Regards*
> >>
> >> *Rajan verma*
> >>
> >> *+91 7838100659 [IM-Gtalk]*
> >>
> >> ** **
> >>
> >> *From:* excel-macros@googlegroups.com [mailto:
> >> excel-macros@googlegroups.com] *On Behalf Of *Amol Jadhav
> >> *Sent:* 26 May 2012 2:18
> >> *To:* excel-macros@googlegroups.com
> >> *Subject:* $$Excel-Macros$$ Copy Validation while splitting
> workbooks
> >>
> >> ** **
> >>
> >> Hi Experts,
> >>
> >>
> >>
> >> I want to make some changes in existing macro, Currently I have macro
> >> which creates multiple files according to user and it working fine. But
> >> now
> >> I want to make some changes
> >>
> >>
> >>
> >> If you check attached file, I have created validation and dependent
> >> validation in field range G:I (Dependent validation Means if I select
> >> "Completed" option from status field, next field will give option
> >> accordingly. Using Name range).
> >>
> >>
> >>
> >> What I want is while creating files as per user field, macro will also
> >> copy validations
> >>
> >> & validation sheet , and will paste it into each workbook
> >>
> >>
> >>
> >> PFA
> >>
> >>
> >>
> >> Regards,
> >>
> >> Amol
> >>
> >>
> >>
> >>
> >>
> >> --
> >> 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
Re: $$Excel-Macros$$ Calculator
Dear David, Thank you very much, really good work. Thanks & Regards, Prajakt Pande +971551388482 ** skype:- prajaktpande On Sat, May 26, 2012 at 4:22 PM, David Grugeon wrote: > Hi Prajakt > > First, I worked up that the volume was the weight divided by the rows. > Then i worked out that a box 60x40x20 would have a volume of 0.0480 > > So the sides need to be 60/40/20 adjusted for the change in volume. > The adjustment is according to the cube root of the ratio change so it > is the cube root of the volume calculated as above divided by 0.0480. > > Best regards > David grugeon > > On 26/05/2012, Prajakt Pande wrote: > > Dear David, > > * > > * > > *it is working fine. thanks a lot.* > > * > > * > > *But dear request you to can you explain the formula, i didn't > understand.* > > * > > * > > * > > * > > Thanks & Regards, > > Prajakt Pande > > +971551388482 > > ** > > > > > > > > On Sat, May 26, 2012 at 5:53 AM, David Grugeon > > wrote: > > > >> Hi Prajakt > >> > >> Try the attached > >> > >> On 26 May 2012 02:58, Prajakt Pande wrote: > >> > >>> Dear Noorain & Group Expert, > >>> > >>> I am attached 1 excel file, in that user will enter only total weight > >>> and > >>> total row. and after hitting calculate button Length, Breadth, height > >>> suppose to calculate and shown to text box. > >>> > >>> > >>> All calculation will be in Information sheet. > >>> > >>> > >>> > >>> Thanks & Regards, > >>> Prajakt Pande > >>> +971551388482 > >>> ** > >>> > >>> -- > >>> 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 > >>> > >>> To unsubscribe, send a blank email to > >>> excel-macros+unsubscr...@googlegroups.com > >> > >> > >> > >> > >> -- > >> David Grugeon > >> > >> -- > >> 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 > >> > >> To unsubscribe, send a blank email to > >> excel-macros+unsubscr...@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 > > > > To unsubscribe, send a blank email to > > excel-macros+unsubscr...@googlegroups.com > > > -- > David Grugeon > > -- > 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 an
Re: $$Excel-Macros$$ Copy Validation while splitting workbooks
Hi Amol
I have sometimes solved problems like this, by, rather than copying
part of the workbook, copying the whole workbook and then deleting the
parts i did not want in the copy. I do not know if this approach
would work for you.
Regards
David grugeon
On 26/05/2012, Amol Jadhav wrote:
> Hi Rajan,
>
> Thanks for quick reply
>
> Some part of work is done. Still problems remain same like -- In splitted
> files if you check validation is not working, If you check in main
> file range G:i there is validation and values comes from validation sheet.
> I want same validation in splitted workbooks, so user can select
> appropriate option.
>
> Regards,
> Amol Jadhav
>
> On 26 May 2012 14:54, Rajan_Verma wrote:
>
>> Hi
>>
>> ** **
>>
>> Try this:
>>
>> ** **
>>
>> *Sub SplitAllbyCode()*
>>
>> * *
>>
>> *Dim rngRangeAs Range*
>>
>> *Dim wksSheetAs Worksheet*
>>
>> *Dim ArrUniqe*
>>
>> *Dim lngUniqeCount As Long*
>>
>> *Dim wbkNew As Workbook*
>>
>> * *
>>
>> *Set wksSheet = ThisWorkbook.Worksheets("Data")*
>>
>> *With wksSheet*
>>
>> *Set rngRange = Intersect(Range("rngStart").CurrentRegion,
>> Range("rngStart").CurrentRegion.Offset(2))*
>>
>> *Application.ScreenUpdating = False*
>>
>> *rngRange.Columns(6).Copy Range("rngRemoveDuplicate")*
>>
>> *Range("rngRemoveDuplicate").CurrentRegion.RemoveDuplicates 1*
>>
>> *ArrUniqe = Range("rngRemoveDuplicate").CurrentRegion*
>>
>> *For lngUniqeCount = LBound(ArrUniqe) To UBound(ArrUniqe)*
>>
>> *Range("rngStart").CurrentRegion.Rows(2).AutoFilter 6,
>> ArrUniqe(lngUniqeCount, 1)*
>>
>> *
>> Range("rngStart").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy*
>>
>> *Set wbkNew = Workbooks.Add*
>>
>> *wbkNew.Worksheets(1).Paste*
>>
>> *wbkNew.SaveAs ThisWorkbook.Path & "\" &
>> ArrUniqe(lngUniqeCount, 1)*
>>
>> *wbkNew.Close 1*
>>
>> *Next lngUniqeCount*
>>
>> *End With*
>>
>> *wksSheet.AutoFilterMode = False*
>>
>> *Application.ScreenUpdating = True*
>>
>> *Range("rngRemoveDuplicate").CurrentRegion.ClearContents*
>>
>> * MsgBox lngUniqeCount & " Files has been splited, Plase find your
>> files at " & vbCrLf & ThisWorkbook.Path*
>>
>> *'Free Memory*
>>
>> *Set rngRange = Nothing*
>>
>> *Set wksSheet = Nothing*
>>
>> *Erase ArrUniqe*
>>
>> *Set wbkNew = Nothing*
>>
>> * *
>>
>> *End Sub*
>>
>> * *
>>
>> *See the post on
>> *
>> http://excelpoweruser.blogspot.in/2012/05/split-data-into-multiple-workbook.html
>>
>>
>> ** **
>>
>> * *
>>
>> * *
>>
>> *Regards*
>>
>> *Rajan verma*
>>
>> *+91 7838100659 [IM-Gtalk]*
>>
>> ** **
>>
>> *From:* excel-macros@googlegroups.com [mailto:
>> excel-macros@googlegroups.com] *On Behalf Of *Amol Jadhav
>> *Sent:* 26 May 2012 2:18
>> *To:* excel-macros@googlegroups.com
>> *Subject:* $$Excel-Macros$$ Copy Validation while splitting workbooks
>>
>> ** **
>>
>> Hi Experts,
>>
>>
>>
>> I want to make some changes in existing macro, Currently I have macro
>> which creates multiple files according to user and it working fine. But
>> now
>> I want to make some changes
>>
>>
>>
>> If you check attached file, I have created validation and dependent
>> validation in field range G:I (Dependent validation Means if I select
>> "Completed" option from status field, next field will give option
>> accordingly. Using Name range).
>>
>>
>>
>> What I want is while creating files as per user field, macro will also
>> copy validations
>>
>> & validation sheet , and will paste it into each workbook
>>
>>
>>
>> PFA
>>
>>
>>
>> Regards,
>>
>> Amol
>>
>>
>>
>>
>>
>> --
>> 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
>>
>> To unsubscribe, send a blank email to
>> excel-macros+unsubscr...@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
Re: $$Excel-Macros$$ Calculator
Hi Prajakt First, I worked up that the volume was the weight divided by the rows. Then i worked out that a box 60x40x20 would have a volume of 0.0480 So the sides need to be 60/40/20 adjusted for the change in volume. The adjustment is according to the cube root of the ratio change so it is the cube root of the volume calculated as above divided by 0.0480. Best regards David grugeon On 26/05/2012, Prajakt Pande wrote: > Dear David, > * > * > *it is working fine. thanks a lot.* > * > * > *But dear request you to can you explain the formula, i didn't understand.* > * > * > * > * > Thanks & Regards, > Prajakt Pande > +971551388482 > ** > > > > On Sat, May 26, 2012 at 5:53 AM, David Grugeon > wrote: > >> Hi Prajakt >> >> Try the attached >> >> On 26 May 2012 02:58, Prajakt Pande wrote: >> >>> Dear Noorain & Group Expert, >>> >>> I am attached 1 excel file, in that user will enter only total weight >>> and >>> total row. and after hitting calculate button Length, Breadth, height >>> suppose to calculate and shown to text box. >>> >>> >>> All calculation will be in Information sheet. >>> >>> >>> >>> Thanks & Regards, >>> Prajakt Pande >>> +971551388482 >>> ** >>> >>> -- >>> 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 >>> >>> To unsubscribe, send a blank email to >>> excel-macros+unsubscr...@googlegroups.com >> >> >> >> >> -- >> David Grugeon >> >> -- >> 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 >> >> To unsubscribe, send a blank email to >> excel-macros+unsubscr...@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 > > To unsubscribe, send a blank email to > excel-macros+unsubscr...@googlegroups.com -- David Grugeon -- 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$$ doubt
In the future PLEASE try to use a meaningful subject line (doubt is not meaningful) and then explain your problem in the posting as well as in the attached file. Don Guillett Microsoft MVP Excel SalesAid Software dguille...@gmail.com From: vijayajith VA Sent: Saturday, May 26, 2012 5:44 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ doubt Hi all, Please find the attached query..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 To unsubscribe, send a blank email to excel-macros+unsubscr...@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 To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
Re: $$Excel-Macros$$ Copy Validation while splitting workbooks
Hi Rajan,
Thanks for quick reply
Some part of work is done. Still problems remain same like -- In splitted
files if you check validation is not working, If you check in main
file range G:i there is validation and values comes from validation sheet.
I want same validation in splitted workbooks, so user can select
appropriate option.
Regards,
Amol Jadhav
On 26 May 2012 14:54, Rajan_Verma wrote:
> Hi
>
> ** **
>
> Try this:
>
> ** **
>
> *Sub SplitAllbyCode()*
>
> * *
>
> *Dim rngRangeAs Range*
>
> *Dim wksSheetAs Worksheet*
>
> *Dim ArrUniqe*
>
> *Dim lngUniqeCount As Long*
>
> *Dim wbkNew As Workbook*
>
> * *
>
> *Set wksSheet = ThisWorkbook.Worksheets("Data")*
>
> *With wksSheet*
>
> *Set rngRange = Intersect(Range("rngStart").CurrentRegion,
> Range("rngStart").CurrentRegion.Offset(2))*
>
> *Application.ScreenUpdating = False*
>
> *rngRange.Columns(6).Copy Range("rngRemoveDuplicate")*
>
> *Range("rngRemoveDuplicate").CurrentRegion.RemoveDuplicates 1*
>
> *ArrUniqe = Range("rngRemoveDuplicate").CurrentRegion*
>
> *For lngUniqeCount = LBound(ArrUniqe) To UBound(ArrUniqe)*
>
> *Range("rngStart").CurrentRegion.Rows(2).AutoFilter 6,
> ArrUniqe(lngUniqeCount, 1)*
>
> *
> Range("rngStart").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy*
>
> *Set wbkNew = Workbooks.Add*
>
> *wbkNew.Worksheets(1).Paste*
>
> *wbkNew.SaveAs ThisWorkbook.Path & "\" &
> ArrUniqe(lngUniqeCount, 1)*
>
> *wbkNew.Close 1*
>
> *Next lngUniqeCount*
>
> *End With*
>
> *wksSheet.AutoFilterMode = False*
>
> *Application.ScreenUpdating = True*
>
> *Range("rngRemoveDuplicate").CurrentRegion.ClearContents*
>
> * MsgBox lngUniqeCount & " Files has been splited, Plase find your
> files at " & vbCrLf & ThisWorkbook.Path*
>
> *'Free Memory*
>
> *Set rngRange = Nothing*
>
> *Set wksSheet = Nothing*
>
> *Erase ArrUniqe*
>
> *Set wbkNew = Nothing*
>
> * *
>
> *End Sub*
>
> * *
>
> *See the post on
> *
> http://excelpoweruser.blogspot.in/2012/05/split-data-into-multiple-workbook.html
>
>
> ** **
>
> * *
>
> * *
>
> *Regards*
>
> *Rajan verma*
>
> *+91 7838100659 [IM-Gtalk]*
>
> ** **
>
> *From:* excel-macros@googlegroups.com [mailto:
> excel-macros@googlegroups.com] *On Behalf Of *Amol Jadhav
> *Sent:* 26 May 2012 2:18
> *To:* excel-macros@googlegroups.com
> *Subject:* $$Excel-Macros$$ Copy Validation while splitting workbooks
>
> ** **
>
> Hi Experts,
>
>
>
> I want to make some changes in existing macro, Currently I have macro
> which creates multiple files according to user and it working fine. But now
> I want to make some changes
>
>
>
> If you check attached file, I have created validation and dependent
> validation in field range G:I (Dependent validation Means if I select
> "Completed" option from status field, next field will give option
> accordingly. Using Name range).
>
>
>
> What I want is while creating files as per user field, macro will also
> copy validations
>
> & validation sheet , and will paste it into each workbook
>
>
>
> PFA
>
>
>
> Regards,
>
> Amol
>
>
>
>
>
> --
> 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
>
> To unsubscribe, send a blank email to
> excel-macros+unsubscr...@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$$ doubt
Hi Rajan/ashish..
Its working Fine thankyu so much..
On Sat, May 26, 2012 at 4:52 PM, Rajan_Verma wrote:
> Use this in F5 and Drag
>
> ** **
>
> =VLOOKUP(F$4,INDIRECT("L"&MATCH(E5,K:K,0) &":P" & COUNTA(P:P)+1),5,0)
>
> * *
>
> *Regards*
>
> *Rajan verma*
>
> *+91 7838100659 [IM-Gtalk]*
>
> ** **
>
> *From:* excel-macros@googlegroups.com [mailto:
> excel-macros@googlegroups.com] *On Behalf Of *vijayajith VA
> *Sent:* 26 May 2012 4:15
> *To:* excel-macros@googlegroups.com
> *Subject:* $$Excel-Macros$$ doubt
>
> ** **
>
> Hi all,
>
> Please find the attached query..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
>
> To unsubscribe, send a blank email to
> excel-macros+unsubscr...@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
>
> To unsubscribe, send a blank email to
> excel-macros+unsubscr...@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
To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
RE: $$Excel-Macros$$ doubt
Use this in F5 and Drag
=VLOOKUP(F$4,INDIRECT("L"&MATCH(E5,K:K,0) &":P" & COUNTA(P:P)+1),5,0)
Regards
Rajan verma
+91 7838100659 [IM-Gtalk]
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com]
On Behalf Of vijayajith VA
Sent: 26 May 2012 4:15
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ doubt
Hi all,
Please find the attached query..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
To unsubscribe, send a blank email to
excel-macros+unsubscr...@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
To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
Re: $$Excel-Macros$$ doubt
Try the below condition and see the attached sheet. =SUMPRODUCT(($K$2:$K$48=E5)*($L$2:$L$48=$F$4)*($P$2:$P$48)) Ashish On Sat, May 26, 2012 at 4:14 PM, vijayajith VA wrote: > Hi all, > > Please find the attached query..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 > > To unsubscribe, send a blank email to > excel-macros+unsubscr...@googlegroups.com -- Thanks & regards. Ashish Bhalara 9624111822 -- 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 To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com question (Solve).xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
$$Excel-Macros$$ doubt
Hi all, Please find the attached query..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 To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com question.xlsx Description: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
RE: $$Excel-Macros$$ 2 Values in the same cell
These lines is just to adjust values within cells
If Target Cell is rngOutput.Offset(lngLoopCounter) then
rngOutput.Offset(lngLoopCounter).Offset(-1, -1).Value =
rngCell.Value Put the value in TopLeft cell Target Cell
rngOutput.Offset(lngLoopCounter).Offset(1, 1).Value = rngCell.Value
For Put the value in BottomRight Cell from Target Cell
Regards
Rajan verma
+91 7838100659 [IM-Gtalk]
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On
Behalf Of rao
Sent: 26 May 2012 2:44
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ 2 Values in the same cell
Hi ,
I am not able to understand this part, could you please explain.
rngOutput.Offset(lngLoopCounter).Offset(-1, -1).Value =
rngCell.Value
rngOutput.Offset(lngLoopCounter).Offset(1, 1).Value = rngCell.Value
cheers,
Prabhakar
On Sat, May 26, 2012 at 2:41 PM, rao wrote:
Thanks Buddy..I got...
Cheers,
Prabhakar
On Sat, May 26, 2012 at 2:03 PM, Rajan_Verma wrote:
Only select one cell For Output
Regards
Rajan verma
+91 7838100659 [IM-Gtalk]
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On
Behalf Of rao
Sent: 26 May 2012 1:46
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ 2 Values in the same cell
Thanks Rajan,
I am seeing a runtime error while selecting the range for the
"rngOutput"..Could you please advice on this.I have selected range from A1:C14
Set rngOutput = Application.InputBox("Please select Output Cells", , , , , , ,
8)
Cheers,
Prabhakar
On Wed, May 16, 2012 at 6:23 PM, Rajan_Verma wrote:
Are you looking for this
See the attached Sheet
Sub ManageThem()
Dim rngRange As Range
Dim rngOutput As Range
Dim rngCell As Range
Dim lngLoopCounter As Long
Dim rngBorders As Range
Set rngRange = Application.InputBox("Please select value(s)", , , , , , , 8)
If rngRange.Columns.Count < 2 Or rngRange.Columns.Count > 2 Then
MsgBox "Please Select Correct Range", vbInformation
End
End If
lngLoopCounter = 0
Set rngOutput = Application.InputBox("Please select Output Cells", , , , ,
, , 8)
For Each rngCell In rngRange
If rngCell.Value <> "" Then
rngOutput.Offset(lngLoopCounter).Offset(-1, -1).Value =
rngCell.Value
rngOutput.Offset(lngLoopCounter).Offset(1, 1).Value = rngCell.Value
Set rngBorders = Range(rngOutput.Offset(lngLoopCounter).Offset(-1,
-1), rngOutput.Offset(lngLoopCounter).Offset(1, 1))
With rngBorders
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).Weight = xlThin
.Borders(xlEdgeTop).Weight = xlThin
.Borders(xlEdgeBottom).Weight = xlThin
End With
lngLoopCounter = lngLoopCounter + 3
Else
End
End If
Next
End Sub
Regards
Rajan verma
+91 7838100659 [IM-Gtalk]
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On
Behalf Of David Grugeon
Sent: 16 May 2012 11:59
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ 2 Values in the same cell
The answer is "No" AFAICS
However you could use two columns (one formatted left top and one right
bottom). Put borders around the two but no border between them. If you want
the gridlines to disappear you can give both cells a fill colour of white.
Regards
David Grugeon
On 16 May 2012 05:44, joseph camill wrote:
On Wed, May 16, 2012 at 1:08 AM, joseph camill wrote:
Attached is an example
On Wed, May 16, 2012 at 12:48 AM, wrote:
Hi,
Is it possible to concatenate two values in one cell and seperate the first
value to top left and second value to bottom right of the same cell.
For e.g. £25m and €12m both in cell a1. And both are clearly seperate from each
other.
Thanks,
Joseph
Sent on my BlackBerry® from Vodafone
--
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 BAN
Re: $$Excel-Macros$$ 2 Values in the same cell
Hi ,
I am not able to understand this part, could you please explain.
rngOutput.Offset(lngLoopCounter).Offset(-1, -1).Value =
rngCell.Value
rngOutput.Offset(lngLoopCounter).Offset(1, 1).Value =
rngCell.Value
cheers,
Prabhakar
On Sat, May 26, 2012 at 2:41 PM, rao wrote:
> Thanks Buddy..I got...
>
>
> Cheers,
> Prabhakar
>
> On Sat, May 26, 2012 at 2:03 PM, Rajan_Verma wrote:
>
>> Only select one cell For Output
>>
>> ** **
>>
>> * *
>>
>> *Regards*
>>
>> *Rajan verma*
>>
>> *+91 7838100659 [IM-Gtalk]*
>>
>> ** **
>>
>> *From:* excel-macros@googlegroups.com [mailto:
>> excel-macros@googlegroups.com] *On Behalf Of *rao
>> *Sent:* 26 May 2012 1:46
>>
>> *To:* excel-macros@googlegroups.com
>> *Subject:* Re: $$Excel-Macros$$ 2 Values in the same cell
>>
>> ** **
>>
>> Thanks Rajan,
>> I am seeing a runtime error while selecting the range for the
>> "rngOutput"..Could you please advice on this.I have selected range from
>> A1:C14
>>
>> *Set rngOutput = Application.InputBox("Please select Output Cells", , ,
>> , , , , 8)*
>>
>> ** **
>>
>> Cheers,
>>
>> Prabhakar
>>
>> ** **
>>
>> On Wed, May 16, 2012 at 6:23 PM, Rajan_Verma
>> wrote:
>>
>> *Are you looking for this*
>>
>>
>>
>> See the attached Sheet
>>
>>
>>
>> *Sub ManageThem()*
>>
>> **
>>
>> *Dim rngRange As Range*
>>
>> *Dim rngOutput As Range*
>>
>> *Dim rngCell As Range*
>>
>> *Dim lngLoopCounter As Long*
>>
>> *Dim rngBorders As Range*
>>
>> *Set rngRange = Application.InputBox("Please select value(s)", , , ,
>> , , , 8)*
>>
>> *If rngRange.Columns.Count < 2 Or rngRange.Columns.Count > 2 Then
>> *
>>
>> *MsgBox "Please Select Correct Range", vbInformation*
>>
>> *End*
>>
>> * End If*
>>
>> **
>>
>> *lngLoopCounter = 0*
>>
>> *Set rngOutput = Application.InputBox("Please select Output Cells",
>> , , , , , , 8)*
>>
>> **
>>
>> *For Each rngCell In rngRange*
>>
>> *If rngCell.Value <> "" Then*
>>
>> *rngOutput.Offset(lngLoopCounter).Offset(-1, -1).Value =
>> rngCell.Value*
>>
>> *rngOutput.Offset(lngLoopCounter).Offset(1, 1).Value =
>> rngCell.Value*
>>
>> *Set rngBorders =
>> Range(rngOutput.Offset(lngLoopCounter).Offset(-1, -1),
>> rngOutput.Offset(lngLoopCounter).Offset(1, 1))*
>>
>> *With rngBorders*
>>
>> *.Borders(xlEdgeLeft).Weight = xlThin*
>>
>> *.Borders(xlEdgeRight).Weight = xlThin*
>>
>> *.Borders(xlEdgeTop).Weight = xlThin*
>>
>> *.Borders(xlEdgeBottom).Weight = xlThin*
>>
>> *End With*
>>
>> *lngLoopCounter = lngLoopCounter + 3*
>>
>> **
>>
>> *Else*
>>
>> *End*
>>
>> *End If*
>>
>> *Next*
>>
>> **
>>
>> *End Sub*
>>
>> * *
>>
>> * *
>>
>> *Regards*
>>
>> *Rajan verma*
>>
>> *+91 7838100659 [IM-Gtalk]*
>>
>>
>>
>> *From:* excel-macros@googlegroups.com [mailto:
>> excel-macros@googlegroups.com] *On Behalf Of *David Grugeon
>> *Sent:* 16 May 2012 11:59
>>
>>
>> *To:* excel-macros@googlegroups.com
>> *Subject:* Re: $$Excel-Macros$$ 2 Values in the same cell
>>
>>
>>
>> The answer is "No" AFAICS
>>
>>
>>
>> However you could use two columns (one formatted left top and one right
>> bottom). Put borders around the two but no border between them. If you
>> want the gridlines to disappear you can give both cells a fill colour of
>> white.
>>
>>
>>
>> Regards
>>
>> David Grugeon
>>
>> On 16 May 2012 05:44, joseph camill wrote:
>>
>>
>>
>> On Wed, May 16, 2012 at 1:08 AM, joseph camill
>> wrote:
>>
>> Attached is an example
>>
>>
>>
>> On Wed, May 16, 2012 at 12:48 AM, wrote:
>>
>> Hi,
>>
>> Is it possible to concatenate two values in one cell and seperate the
>> first value to top left and second value to bottom right of the same cell.
>>
>> For e.g. £25m and €12m both in cell a1. And both are clearly seperate
>> from each other.
>>
>> Thanks,
>> Joseph
>> Sent on my BlackBerry® from Vodafone
>>
>> --
>> 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 mem
Re: $$Excel-Macros$$ 2 Values in the same cell
Thanks Buddy..I got...
Cheers,
Prabhakar
On Sat, May 26, 2012 at 2:03 PM, Rajan_Verma wrote:
> Only select one cell For Output
>
> ** **
>
> * *
>
> *Regards*
>
> *Rajan verma*
>
> *+91 7838100659 [IM-Gtalk]*
>
> ** **
>
> *From:* excel-macros@googlegroups.com [mailto:
> excel-macros@googlegroups.com] *On Behalf Of *rao
> *Sent:* 26 May 2012 1:46
>
> *To:* excel-macros@googlegroups.com
> *Subject:* Re: $$Excel-Macros$$ 2 Values in the same cell
>
> ** **
>
> Thanks Rajan,
> I am seeing a runtime error while selecting the range for the
> "rngOutput"..Could you please advice on this.I have selected range from
> A1:C14
>
> *Set rngOutput = Application.InputBox("Please select Output Cells", , , ,
> , , , 8)*
>
> ** **
>
> Cheers,
>
> Prabhakar
>
> ** **
>
> On Wed, May 16, 2012 at 6:23 PM, Rajan_Verma
> wrote:
>
> *Are you looking for this*
>
>
>
> See the attached Sheet
>
>
>
> *Sub ManageThem()*
>
> **
>
> *Dim rngRange As Range*
>
> *Dim rngOutput As Range*
>
> *Dim rngCell As Range*
>
> *Dim lngLoopCounter As Long*
>
> *Dim rngBorders As Range*
>
> *Set rngRange = Application.InputBox("Please select value(s)", , , ,
> , , , 8)*
>
> *If rngRange.Columns.Count < 2 Or rngRange.Columns.Count > 2 Then*
>
> *MsgBox "Please Select Correct Range", vbInformation*
>
> *End*
>
> * End If*
>
> **
>
> *lngLoopCounter = 0*
>
> *Set rngOutput = Application.InputBox("Please select Output Cells", ,
> , , , , , 8)*
>
> **
>
> *For Each rngCell In rngRange*
>
> *If rngCell.Value <> "" Then*
>
> *rngOutput.Offset(lngLoopCounter).Offset(-1, -1).Value =
> rngCell.Value*
>
> *rngOutput.Offset(lngLoopCounter).Offset(1, 1).Value =
> rngCell.Value*
>
> *Set rngBorders =
> Range(rngOutput.Offset(lngLoopCounter).Offset(-1, -1),
> rngOutput.Offset(lngLoopCounter).Offset(1, 1))*
>
> *With rngBorders*
>
> *.Borders(xlEdgeLeft).Weight = xlThin*
>
> *.Borders(xlEdgeRight).Weight = xlThin*
>
> *.Borders(xlEdgeTop).Weight = xlThin*
>
> *.Borders(xlEdgeBottom).Weight = xlThin*
>
> *End With*
>
> *lngLoopCounter = lngLoopCounter + 3*
>
> **
>
> *Else*
>
> *End*
>
> *End If*
>
> *Next*
>
> **
>
> *End Sub*
>
> * *
>
> * *
>
> *Regards*
>
> *Rajan verma*
>
> *+91 7838100659 [IM-Gtalk]*
>
>
>
> *From:* excel-macros@googlegroups.com [mailto:
> excel-macros@googlegroups.com] *On Behalf Of *David Grugeon
> *Sent:* 16 May 2012 11:59
>
>
> *To:* excel-macros@googlegroups.com
> *Subject:* Re: $$Excel-Macros$$ 2 Values in the same cell
>
>
>
> The answer is "No" AFAICS
>
>
>
> However you could use two columns (one formatted left top and one right
> bottom). Put borders around the two but no border between them. If you
> want the gridlines to disappear you can give both cells a fill colour of
> white.
>
>
>
> Regards
>
> David Grugeon
>
> On 16 May 2012 05:44, joseph camill wrote:
>
>
>
> On Wed, May 16, 2012 at 1:08 AM, joseph camill
> wrote:
>
> Attached is an example
>
>
>
> On Wed, May 16, 2012 at 12:48 AM, wrote:
>
> Hi,
>
> Is it possible to concatenate two values in one cell and seperate the
> first value to top left and second value to bottom right of the same cell.
>
> For e.g. £25m and €12m both in cell a1. And both are clearly seperate from
> each other.
>
> Thanks,
> Joseph
> Sent on my BlackBerry® from Vodafone
>
> --
> 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
RE: $$Excel-Macros$$ 2 Values in the same cell
Only select one cell For Output
Regards
Rajan verma
+91 7838100659 [IM-Gtalk]
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On
Behalf Of rao
Sent: 26 May 2012 1:46
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ 2 Values in the same cell
Thanks Rajan,
I am seeing a runtime error while selecting the range for the
"rngOutput"..Could you please advice on this.I have selected range from A1:C14
Set rngOutput = Application.InputBox("Please select Output Cells", , , , , , ,
8)
Cheers,
Prabhakar
On Wed, May 16, 2012 at 6:23 PM, Rajan_Verma wrote:
Are you looking for this
See the attached Sheet
Sub ManageThem()
Dim rngRange As Range
Dim rngOutput As Range
Dim rngCell As Range
Dim lngLoopCounter As Long
Dim rngBorders As Range
Set rngRange = Application.InputBox("Please select value(s)", , , , , , , 8)
If rngRange.Columns.Count < 2 Or rngRange.Columns.Count > 2 Then
MsgBox "Please Select Correct Range", vbInformation
End
End If
lngLoopCounter = 0
Set rngOutput = Application.InputBox("Please select Output Cells", , , , ,
, , 8)
For Each rngCell In rngRange
If rngCell.Value <> "" Then
rngOutput.Offset(lngLoopCounter).Offset(-1, -1).Value =
rngCell.Value
rngOutput.Offset(lngLoopCounter).Offset(1, 1).Value = rngCell.Value
Set rngBorders = Range(rngOutput.Offset(lngLoopCounter).Offset(-1,
-1), rngOutput.Offset(lngLoopCounter).Offset(1, 1))
With rngBorders
.Borders(xlEdgeLeft).Weight = xlThin
.Borders(xlEdgeRight).Weight = xlThin
.Borders(xlEdgeTop).Weight = xlThin
.Borders(xlEdgeBottom).Weight = xlThin
End With
lngLoopCounter = lngLoopCounter + 3
Else
End
End If
Next
End Sub
Regards
Rajan verma
+91 7838100659 [IM-Gtalk]
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On
Behalf Of David Grugeon
Sent: 16 May 2012 11:59
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ 2 Values in the same cell
The answer is "No" AFAICS
However you could use two columns (one formatted left top and one right
bottom). Put borders around the two but no border between them. If you want
the gridlines to disappear you can give both cells a fill colour of white.
Regards
David Grugeon
On 16 May 2012 05:44, joseph camill wrote:
On Wed, May 16, 2012 at 1:08 AM, joseph camill wrote:
Attached is an example
On Wed, May 16, 2012 at 12:48 AM, wrote:
Hi,
Is it possible to concatenate two values in one cell and seperate the first
value to top left and second value to bottom right of the same cell.
For e.g. £25m and €12m both in cell a1. And both are clearly seperate from each
other.
Thanks,
Joseph
Sent on my BlackBerry® from Vodafone
--
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
--
David Grugeon
--
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
qui
Re: $$Excel-Macros$$ 2 Values in the same cell
Thanks Rajan,
I am seeing a runtime error while selecting the range for the
"rngOutput"..Could you please advice on this.I have selected range from
A1:C14
*Set rngOutput = Application.InputBox("Please select Output Cells", , , , ,
, , 8)*
Cheers,
Prabhakar
On Wed, May 16, 2012 at 6:23 PM, Rajan_Verma wrote:
> *Are you looking for this*
>
> ** **
>
> See the attached Sheet
>
> ** **
>
> *Sub ManageThem()*
>
> **
>
> *Dim rngRange As Range*
>
> *Dim rngOutput As Range*
>
> *Dim rngCell As Range*
>
> *Dim lngLoopCounter As Long*
>
> *Dim rngBorders As Range*
>
> *Set rngRange = Application.InputBox("Please select value(s)", , , ,
> , , , 8)*
>
> *If rngRange.Columns.Count < 2 Or rngRange.Columns.Count > 2 Then*
>
> *MsgBox "Please Select Correct Range", vbInformation*
>
> *End*
>
> *End If*
>
> **
>
> *lngLoopCounter = 0*
>
> *Set rngOutput = Application.InputBox("Please select Output Cells", ,
> , , , , , 8)*
>
> **
>
> *For Each rngCell In rngRange*
>
> *If rngCell.Value <> "" Then*
>
> *rngOutput.Offset(lngLoopCounter).Offset(-1, -1).Value =
> rngCell.Value*
>
> *rngOutput.Offset(lngLoopCounter).Offset(1, 1).Value =
> rngCell.Value*
>
> *Set rngBorders =
> Range(rngOutput.Offset(lngLoopCounter).Offset(-1, -1),
> rngOutput.Offset(lngLoopCounter).Offset(1, 1))*
>
> *With rngBorders*
>
> *.Borders(xlEdgeLeft).Weight = xlThin*
>
> *.Borders(xlEdgeRight).Weight = xlThin*
>
> *.Borders(xlEdgeTop).Weight = xlThin*
>
> *.Borders(xlEdgeBottom).Weight = xlThin*
>
> *End With*
>
> *lngLoopCounter = lngLoopCounter + 3*
>
> **
>
> *Else*
>
> *End*
>
> *End If*
>
> *Next*
>
> **
>
> *End Sub*
>
> * *
>
> * *
>
> *Regards*
>
> *Rajan verma*
>
> *+91 7838100659 [IM-Gtalk]*
>
> ** **
>
> *From:* excel-macros@googlegroups.com [mailto:
> excel-macros@googlegroups.com] *On Behalf Of *David Grugeon
> *Sent:* 16 May 2012 11:59
>
> *To:* excel-macros@googlegroups.com
> *Subject:* Re: $$Excel-Macros$$ 2 Values in the same cell
>
> ** **
>
> The answer is "No" AFAICS
>
> ** **
>
> However you could use two columns (one formatted left top and one right
> bottom). Put borders around the two but no border between them. If you
> want the gridlines to disappear you can give both cells a fill colour of
> white.
>
> ** **
>
> Regards
>
> David Grugeon
>
> On 16 May 2012 05:44, joseph camill wrote:
>
> ** **
>
> On Wed, May 16, 2012 at 1:08 AM, joseph camill
> wrote:
>
> Attached is an example
>
> ** **
>
> On Wed, May 16, 2012 at 12:48 AM, wrote:
>
> Hi,
>
> Is it possible to concatenate two values in one cell and seperate the
> first value to top left and second value to bottom right of the same cell.
>
> For e.g. £25m and €12m both in cell a1. And both are clearly seperate from
> each other.
>
> Thanks,
> Joseph
> Sent on my BlackBerry® from Vodafone
>
> --
> 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
>
>
>
>
>
> ** **
>
> --
> David Grugeon
>
> --
> FORUM RULES (986+ members already BANN
Re: $$Excel-Macros$$ RE:-Horizontal Filter
Dear Sir , Thank a lot :) Regards abhishek On Sat, May 26, 2012 at 1:21 PM, Rajan_Verma wrote: > ** ** > > Hi, > > Excel don’t have inbuilt Horizontal Filter Feature > > Hope it will help See the Post here : > > ** ** > > http://blog.livedoor.jp/andrewe/archives/50283818.html > > ** ** > > * * > > *Regards* > > *Rajan verma* > > *+91 7838100659 [IM-Gtalk]* > > ** ** > > *From:* excel-macros@googlegroups.com [mailto: > excel-macros@googlegroups.com] *On Behalf Of *abhishek agarwal > *Sent:* 26 May 2012 1:17 > *To:* excel-macros@googlegroups.com > *Subject:* $$Excel-Macros$$ RE:-Horizontal Filter > > ** ** > > > Dear Sir, > > I Have Some Query Regarding The Horizontal Filter > how Can We use this can u please share the example in excel sheet > > > > > > Regards > Abhishek > > -- > 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 > > To unsubscribe, send a blank email to > excel-macros+unsubscr...@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 > > To unsubscribe, send a blank email to > excel-macros+unsubscr...@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 To unsubscribe, send a blank email to excel-macros+unsubscr...@googlegroups.com
